Read excel data in an array, doesn't work

ruby-talk
1

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

2

Found it guys, nvm.

saudaziz@gmail.com wrote:

···

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

3

saudaziz@gmail.com wrote:

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.

4

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:no@spam.please.net]
Sent: Thursday, August 18, 2005 06:21 AM
To: ruby-talk ML
Subject: Re: read excel data in an array, doesn't work

saudaziz@gmail.com wrote:

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.

5

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

6

Kevin Olbrich wrote:

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.