Excel file modification without win32ole

Ruby
1

Greetings,

I’ve been coding some internal tools for my company with Ruby for
several
months and I’d like to distribute some of those tools to other employees
too.
I have one specific tool, which does some calculations and iterations,
displays the values in a table and saves everything to a specified Excel
template. The toolkit I’ve been using is Tk in W2k, Linux and OSX.

The problem is the win32ole class. Handling Excel files through the
class is
very slow, and if I pack my program to an exe file with rubyscript2exe,
saving
…xls files through win32ole halts the whole program. I’ve been
searching some
better classes for reading and writing Excel files with no success. The
template I’m using is quite complex with many formulae and vba scripts.

I’m sorry I can’t publish any code here, but if somebody could give me
some
good hints for better ways to modify and save .xls even without MS
Office, the
next working week would be much nicer… :slight_smile:

2

Julius de Bruijn wrote:

.xls files through win32ole halts the whole program. I’ve been searching some
better classes for reading and writing Excel files with no success. The
template I’m using is quite complex with many formulae and vba scripts.

I’m sorry I can’t publish any code here, but if somebody could give me some
good hints for better ways to modify and save .xls even without MS Office, the
next working week would be much nicer… :slight_smile:

See http://raa.ruby-lang.org/project/spreadsheet/ if you don’t mind
that it uses the Excel 95 format.

Regards,

Dan

3

See http://raa.ruby-lang.org/project/spreadsheet/ if you don’t mind
that it uses the Excel 95 format.

That’s the module I’ve been trying to use in my program instead of
win32ole class. The problem is that the spreadsheet class doesn’t
support opening an Excel template, it only creates a new file. My
current Excel template is quite complex with seven worksheets, hundreds
of formulae and several hundred lines of VBA, so creating it from
scratch with Ruby’s spreadsheet module is out of question.

The problem with win32ole was it’s halt when using a compiled
rubyscript2exe version of the program. With small tweaking, I made it
to work. I was using “require ‘win32ole’” in a method rather than
loading it in the beginning of the file, which was not very smart move.
The slowness of ole commands and the requirement of Excel still bugs
me, but maybe I can live with it for now.

4

See Spreadsheet::ParseExcel http://download.ywesee.com/parseexcel

http://download.ywesee.com/parseexcel

Spreadsheet::ParseExcel - Get information from an Excel file.

Version: 0.4.0
Date: 2006-02-16

Short Description:
Spreadsheet::ParseExcel allows you to get information out of a
simple Excel file
This Package is an - as of today incomplete - translation of
Kawai Takanoris Perl-Module.

Requirements

  • ruby 1.8

Install

De-Compress archive and enter its top directory.
Then type:

$ ruby setup.rb config
$ ruby setup.rb setup
($ su)

ruby setup.rb install

You can also install files into your favorite directory
by supplying setup.rb some options. Try “ruby setup.rb --help”.

Usage

workbook = Spreadsheet::ParseExcel.parse(path_to_file)
worksheet = workbook.worksheet(0)
worksheet.each(1) { |row|
puts row.at(0)
}