Reading and looping through Excel

Ruby
1

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

2

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

3

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

4

Joel P. 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()’?

5

Arup R. 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 :slight_smile:

6

Does this look like what you need (bottom of the page)?

7

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

8

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 P. [email protected]
To: ruby-talk ML [email protected]
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)?

9

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

10

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

I will test during the day!

Br
cristian

11

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 B. escribió:

12

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

13

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

14

try replacing
wb.saved = true
with
wb.save

15

Thanks Joel!

Wayne

16

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:

17

Here are some options…
Given that the variable “sht” is the sheet:

The maximum row reached by the sheet’s data (if you delete a row without
saving, the number will still be the same):

[6] pry(main)> sht.usedrange.rows.count
=> 35

Count the cells containing data within a specific column:

[7] pry(main)> excel.worksheetfunction.counta(sht.range(‘A:A’))
=> 35.0

Count all the rows:

[8] pry(main)> sht.rows.count
=> 1048576

Count the rows matching a specific criteria:

[9] pry(main)> excel.worksheetfunction.countif(sht.range(‘A:A’),’>=5’)
=> 30.0

18

I am trying to count the number of rows of an excel sheet using win32ole
gem,can someone tell me what is the method used to count the number of
rows of an excel sheet. I used count but unable to get the count of teh
rows in an excel sheet. Thanks