read excel data in an array, doesn't work

[saudaziz]

I am trying to read entire row (with as many non-null columns are
present) into data variable from excel sheet, but the following code
only appears to read data for first 4 columns. Am i doing something
wrong? How can i make it read it x number of columns instead of just 4.

def getExcelStuff
excel = WIN32OLE::new('excel.Application')
workbook =
excel.Workbooks.Open('C:\ruby\watir\watir-v1_4\examples\logging\POPbugs.xls')
worksheet = workbook.Worksheets(1)
worksheet.Select
excel['Visible'] = true

line = '1'
data = []
while worksheet.Range("a#{line}")['Value']
data << worksheet.Range("a#{line}:d#{line}")['Value']
line.succ!
end
data.each { |x| print x, "\n\n\n" }
#0.upto(9) do |x|
# $logger.log( x "\n" )
#end


end

 

[saudaziz]

Found it guys, nvm.

 

[Clifford Heath]

Found it guys, nvm.

Please post your working version! I need to use Ruby to load values
into a spreadsheet this week and a working example would be great.

Clifford Heath.

 

[Kevin Olbrich]

Are you trying to dump output to an excel file, or are you trying to add
values to an existing spreadsheet?

If you just want to dump the data, you can just create a CSV file and excel
will read it just fine. Ruby even has a CSV file handler. Integrating it
into an existing sheet is not as easy.

_Kevin

-----Original Message-----
From: Clifford Heath [mailto:[email protected]]
Sent: Thursday, August 18, 2005 06:21 AM
To: ruby-talk ML
Subject: Re: read excel data in an array, doesn't work

Found it guys, nvm.

Please post your working version! I need to use Ruby to load values into a
spreadsheet this week and a working example would be great.

Clifford Heath.

 

[saudaziz]

Nah, what i am trying to do is simply read row by row in excel file and
assign each cell in a row to a variable. This assigned variable with
the help of Watir module will then be assigned to a textbox value on a
form on a web page. So my main goal now is to split the row up as cells
and assign each cell a variable.

Following worked for me, simple mistake i had range values defined only
upto Column D, now they are upto J (the length that i want it to be).

def getExcelStuff
excel = WIN32OLE::new('excel.Application')
workbook =
excel.Workbooks.Open('C:\ruby\watir\watir-v1_4\examples\logging\xxx.xls')
worksheet = workbook.Worksheets(1)
worksheet.Select
excel['Visible'] = true

line = '1'
data = Array.new
while worksheet.Range("a#{line}")['Value']
data << worksheet.Range("a#{line}:j#{line}")['Value']
data.each { |x| print x, "\n\n\n".chomp }
line.succ!
end

end

 

[Clifford Heath]

Are you trying to dump output to an excel file, or are you trying to add
values to an existing spreadsheet?

I know I can do the CSV route, but I wanted to progressively
dump clumps of data into an existing sheet that's already open,
and have it recalculate. It's not mandatory for my app, but
would be cute.

Clifford Heath.