Reading and looping through Excel

ruby-talk
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

···

--
Posted via http://www.ruby-forum.com/.

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

···

--
Posted via http://www.ruby-forum.com/\.

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

--
Posted via http://www.ruby-forum.com/.

4

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

···

--
Posted via http://www.ruby-forum.com/.

5

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

I will test during the day!

Br
cristian

···

--
Posted via http://www.ruby-forum.com/.

6

try replacing
wb.saved = true
with
wb.save

···

--
Posted via http://www.ruby-forum.com/.

7

support for newer formats is the reason I moved to win32ole.
If you want to go it gem-style, have a look at this one:

···

--
Posted via http://www.ruby-forum.com/.

8

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()'?

···

--
Posted via http://www.ruby-forum.com/\.

9

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

10

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

--
Posted via http://www.ruby-forum.com/.

11

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

···

--
Posted via http://www.ruby-forum.com/\.

12

Thanks Joel!

Wayne

···

On Feb 1, 2013, at 4:30 AM, Joel Pearson wrote:

support for newer formats is the reason I moved to win32ole.
If you want to go it gem-style, have a look at this one:
GitHub - randym/axlsx: xlsx generation with charts, images, automated column width, customizable styles and full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification. Check out the README for some examples of how easy it is. Best of all, you can validate your xlsx file before serialization so you know for sure that anything generated is going to load on your client's machine.

--
Posted via http://www.ruby-forum.com/\.

13

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 :slight_smile:

···

--
Posted via http://www.ruby-forum.com/\.

14

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ó:

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 (mailto:lists@ruby-forum.com)>
To: ruby-talk ML <ruby-talk@ruby-lang.org (mailto: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)?
AppleScript Book Afterthoughts

--
Posted via http://www.ruby-forum.com/\.

15

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

···

On Thursday, January 24, 2013, Dix wrote:

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ó:

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 <javascript:_e({}, 'cvml',
'lists@ruby-forum.com');>>
To: ruby-talk ML <ruby-talk@ruby-lang.org <javascript:_e({}, 'cvml',
'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)?
AppleScript Book Afterthoughts

--
Posted via http://www.ruby-forum.com/\.

16

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

···

On Jan 24, 2013, at 4:50 PM, Siddharth Venkatesan wrote:

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

On Thursday, January 24, 2013, Dix wrote:
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