Write data into Excel Sheet

Ruby
1

Hi I am new to Ruby.

can any one suggest me how we can write the data into the Excel Sheet
from the array.

Can we use the command like this

$worksheet.Range(“b1:b12”).Insert = a[k]

Can I insert in excel sheet via the Insert command or some other command
is used??

Thanks in advance.

2

Pranjal J. wrote:

Hi I am new to Ruby.

can any one suggest me how we can write the data into the Excel Sheet
from the array.

Can we use the command like this

$worksheet.Range(“b1:b12”).Insert = a[k]

Can I insert in excel sheet via the Insert command or some other command
is used??

Thanks in advance.

check this out:

$worksheet.Range(“b1:b12”).value=a[k]

-r.

3

Rodrigo B. wrote:

Pranjal J. wrote:

Hi I am new to Ruby.

can any one suggest me how we can write the data into the Excel Sheet
from the array.

Can we use the command like this

$worksheet.Range(“b1:b12”).Insert = a[k]

Can I insert in excel sheet via the Insert command or some other command
is used??

Thanks in advance.

check this out:
http://rubyonwindows.blogspot.com/

$worksheet.Range(“b1:b12”).value=a[k]

-r.

Hi
I tried the method told by you , but sorry to say it is writing only
the last value in the excel sheet.
The code is as follows :

require ‘watir’
require ‘test/unit’

include Watir

class ExcelTest
$excel = WIN32OLE.new(‘excel.Application’)
$path = File.join(‘D:’,‘Ruby’,‘array.xls’)
$workbook = $excel.WorkBooks.Open($path)
$worksheet = $workbook.WorkSheets(“One”)
$worksheet.Select

a = Array.new

a =
[“3118”,“2102”,“1789”,“1301”,“1302”,“1303”,“1304”,“1245”,“1230”,“121”,“4293”,“122”,“209”,“1235”,“1247”,“2104”,]

i = 0
until i > 15
puts a[i]
$worksheet.Range("a1:a16").value = a[i]
i += 1

end

end

The output coming is as follows:
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104
2104

Thanks in advance.

4

On Dienstag, 2008-April-1 07:35:50, Pranjal J. wrote:

I tried the method told by you , but sorry to say it is writing only
the last value in the excel sheet.

But to the all cells in the range, doesn’t it?

i = 0
until i > 15
puts a[i]
$worksheet.Range("a1:a16").value = a[i]
i += 1

end

Well, this code takes each of the 16 values, one after another,
and tells Excel to assign it to the range A1:A16.
When Excel gets one value for a range, it will write it
to all the cells in the range. So all your values are written,
thus only the last value remains. :frowning:

However, you could try the following:

$worksheet.Range("a#{i+1}").value = a[i]

or (writing the loop more in ruby-style):

a.each_with_index do |val, i|
puts val
$worksheet.Range(“a#{i+1}”).value = val
end

or you can write the whole range in one go (that’s what I would do).
If the range is horizontal, this is trivial:

$worksheet.Range(“a1:p1”).value = a

In your case, the cells form (a piece of) a column,
so let’s create a column-array,
that is an array of one-element arrays:

$worksheet.Range(“a1:a16”).value = [a].transpose

Hope that helps.

Sven

PS: In case you are not aware of it,
your code cantains unnecessary things.
For example the line
a = Array.new
or the class definition.