Forum: Ruby Reading and looping through Excel

Hi all!

I have a question regarding reading excel files and looping through the
worksheets within a workbook.

I have approx 12 excel files. Each file(workbook) has different amount
of worksheets. These files are created by a system that exports
transactions for each month. The problem is that it exports in an old
version of excel and the workbook is divided in several worksheets
(65000 rows per worksheet). The labels are in the first worksheet.

What I would like to create is a script that reads a workbook and loops
through all worksheets and stores all rows in a csv file (including
labels). The csv file is then given the same name of the original excel
workbook.

(If I could create a script that does loops through all workbooks would
be great.)

Any suggestions how to loop through all worksheets within a workbook? Is
it possible to count the number of worksheets?

Br
cristian

cristian cristian wrote in post #1093477:
> Hi all!
>
> I have a question regarding reading excel files and looping through the
> worksheets within a workbook.
>
> I have approx 12 excel files. Each file(workbook) has different amount
> of worksheets. These files are created by a system that exports
> transactions for each month. The problem is that it exports in an old
> version of excel and the workbook is divided in several worksheets
> (65000 rows per worksheet). The labels are in the first worksheet.

You can use Ruby1.9.3 standard library for that - "win32ole" and "CSV" .


Thanks

Here's something to get you started, connecting to excel... after that
you can just use the API to excel's functions (record a macro, and check
out the functions it uses when you do stuff).

Caveat Lector: Some of the lines have been squashed into multiple lines
by the width restrictions.

FYI I used tab-delimited as I find commas too common.

_______________________________
require 'win32ole'

begin
excel = WIN32OLE.connect('excel.application')
rescue
excel = WIN32OLE.new('excel.application')
end

excel.DisplayAlerts = false

wb = excel.Workbooks.Open(Dir.pwd.gsub('/','\\') + "\\DRM.xlsx")

output_string = ''

#Loop through sheets
wb.sheets.each do |sh|

  #Turn the sheet into a string
  temp_array = sh.range( sh.cells( 1, 1 ), sh.cells.specialcells( 11 )
).value
  temp_array.each { |ar| output_string << "#{ ar.join("\t") }\n" }

end

File.write 'test.txt', output_string

wb.Close(0)
excel.Quit unless excel.visible

Joel Pearson wrote in post #1093501:
> Here's something to get you started, connecting to excel... after that
> you can just use the API to excel's functions (record a macro, and check
> out the functions it uses when you do stuff).
>
> Caveat Lector: Some of the lines have been squashed into multiple lines
> by the width restrictions.
>
> FYI I used tab-delimited as I find commas too common.
>
> _______________________________
> require 'win32ole'
>
> begin
> excel = WIN32OLE.connect('excel.application')
> rescue
> excel = WIN32OLE.new('excel.application')
> end
>
> Why the above style? asking just for curiosity. Why "connect"?

>
> wb.Close(0)
> excel.Quit unless excel.visible

what the command 'unless excel.visible' does? And why 'wb. close(0)' 
instead 'wb.Close()'?

Arup Rakshit wrote in post #1093511:
> what the command 'unless excel.visible' does? And why 'wb. close(0)'
> instead 'wb.Close()'?

excel.visible is because of this line:
excel = WIN32OLE.connect('excel.application')
If we've highjacked an existing excel instance we don't want to close 
it.

wb.close(0) means don't save the file.
I've never tried it with just "()", give it a go and see what happens :)

I see some value in something like this for a couple of my scripts. 
However,
since I develop and run the script on a Mac, I have been saving the 
files out as
CSV files before using them. Leaving them as excel files would be 
wonderful. Is
there anything like win32ole that can be used on non Window platforms?

Wayne

Does this look like what you need (bottom of the page)? 
http://www.apeth.net/matt/aserrata.html

Joel:

Thanks. That may work. I'll just have to query for the OS and have one 
routine
for PC and one for Mac.

Wayne


----- Original Message ----
From: Joel Pearson <lists@ruby-forum.com>
To: ruby-talk ML <ruby-talk@ruby-lang.org>
Sent: Thu, January 24, 2013 10:27:44 AM
Subject: Re: Reading and looping through Excel

Does this look like what you need (bottom of the page)?
http://www.apeth.net/matt/aserrata.html

I've done something similar using the spreadsheet gem, it's easy to open 
a workbook and iterate over the rows extracting what you want

here's the guide
http://spreadsheet.rubyforge.org/files/GUIDE_txt.html


To open all the files:

dir = "./yourdirectory/"
files = Dir.entries(dir)

files. each do |file|
  book = Spreadsheet.open dir + file
  sheet = book.worsheet 0

   ……. your work

end



--
Pablo


El jueves, 24 de enero de 2013 a las 17:41, Wayne Brisette escribió:

+1 on the spreadsheet gem. It makes excel file reading such a trivial 
task.

Thank you for all suggestions! and sorry for late reply!

I will test during the day!

Br
cristian

cristian cristian wrote in post #1093707:
> Thank you for all suggestions! and sorry for late reply!
>
> I will test during the day!
>
> Br
> cristian


I wrote a code where the below has been used :

  wbs.cells(rows,2).value = 'DONE'
  rows=rows + 1
  end

      wb.saved = true
      wb.Close(0)
      excel.Quit()

But when the script has been completed and I opened the excel to check 
what are the records has been processes. Couldn't see a single `done` 
written into column-2 of that excel. So any mistake in my above part I 
did?

Thanks

try replacing
wb.saved = true
with
wb.save

I played around with this gem and it's very nice. However, it doesn't 
seem to like the newer Excel formats (XML).

What are people doing with these newer .xlsx formatted files? I can do a 
'save as', then the gem works fine, but that seems to be 
counter-productive if I was trying to deploy something like this to my 
department.

Wayne

support for newer formats is the reason I moved to win32ole.
If you want to do it gem-style, have a look at this one:
https://github.com/randym/axlsx

Thanks Joel!

Wayne