Excel sheet generation

Rails
1

Hi,

Does anyone know of a package that will help with generation of
spreadsheets? (xls files, not csv)

Thanks,
Fredrik

2

Fredrick, I understand there are 2 routes you can take.

  1. write to an open format - this likely depends on the version of
    Office you want to support.
  2. Write a native office document using the win32 gem win32ole.

I used the second option to generate word documents.

Do a google search for win32ole and ruby - you’ll find a few examples
that should get you started.

Cheers,
Jodi
General Partner
The nNovation Group inc.
www.nnovation.ca/blog

3

Fredrik wrote:

Hi,

Does anyone know of a package that will help with generation of
spreadsheets? (xls files, not csv)

Thanks,
Fredrik

Hi,

I think you can use XHTML Templates and serve them with a .xls file
extension and application header. I’ve done this with word docs and
works a treat.

For Example (With Word):

  1. Create a “template” document in microsoft word with place holders for
    dynamic data
  2. Save as Microsoft HTML
  3. Use this document to develop a template.
  4. Serve up as a Word Document with a .doc extension.

I assume you can this with Excel too.

4

On 1/17/07, Jodi S. [email protected] wrote:

that should get you started.
The Ruby-POI bindings are another option. I’ve never used these myself,
however, it seems like a great cross-platform solution.

http://jakarta.apache.org/poi/poi-ruby.html

5

Thanks guys, that was a bunch of options, some of which I had no idea
you could do. That definately solves my problem. :slight_smile:

-Fredrik

6

I currently use spreadsheet/excel
(http://raa.ruby-lang.org/project/spreadsheet/) and it’s been working
quite well for me. It ends up looking like this:

class SpreadsheetController < ApplicationController

require ‘spreadsheet/excel’
include Spreadsheet

def item_list
file_path = “tmp/file.xls” # this should actually be a Tempfile
object
workbook = Excel.new(file_path)
worksheet = workbook.add_worksheet
format_text = workbook.add_format(:font => ‘Verdana’)
format_header = workbook.add_format(:font => ‘Verdana’, :bold =>
true, :bg_color => 22, :top => 1, :bottom => 1, :left => 1, :right => 1)

# Set column widths
worksheet.format_column(0, 12, format_text)
worksheet.format_column(1, 15, format_text)

#Output column headings
worksheet.write(0, 0, 'Item Code', format_header)
worksheet.write(0, 1, 'Item Name', format_header)

#Output actual data
items = Item.find(:all)
items.each_with_index do |item, i|
  worksheet.write(i + 1, 0, item.code, format_text)
  worksheet.write(i + 1, 1, item.name, format_text)
end

workbook.close

send_file file_path, :filename => 'Item List.xls', :type =>

‘application/vnd.ms-excel’
end

end

7

Fredrik wrote:

I am looking for something to read excel files too, and from what I
saw, it’s planned for in the POI extensions, but don’t think it is
implemented yet.

Although I haven’t had any need for it within any Rails applications, I
have used ParseExcel to do this in a few stand-alone ruby scripts, and
it’s worked well for me. It should work equally well in a Rails app.
Check it out!

http://raa.ruby-lang.org/project/parseexcel/

8

I had some time to do some testing and the spreadsheet writer was very
familiar from php :slight_smile: and worked great (current implementation).

I did try out the win32ole, but I could not get it to work. Got some
file missing error. After browsing around it seemed like you have to
have office installed, since it was talking about people installing it
with wine, etc under linux. Is this the case? I did try it under
windows.

The POI extensions seemed ok too, but I could not see what they offer
beyond the excel writer gem for my needs (for now download data, modify
and upload it back)

I am looking for something to read excel files too, and from what I
saw, it’s planned for in the POI extensions, but don’t think it is
implemented yet.

Please let me know if you get a chance about the POI and win32ole
assumptions I made if they are correct or not.

Thanks,
Fredrik

9

I know this is a little late and it’s not a package, but this might
help someone searching down the road: Excel 2003 supports XML formats:
http://www.microsoft.com/office/xml/default.mspx

There’s even a blog:

From what I could gather, it it needs excel 2003.

10

Gosh, I’m gonna have to work overtime now to check all these things
out.

I think Chris’ suggestion about the parse excel would be a nice
complement to the excel writer I’m currently using. This is nothing
overly complicated I’m trying to do.

I have written some java code, but that was a long time ago. Not
familiar with much of it.

Thanks again guys. Hopefully all your answers will help someone else
too.

Fredrik

11

Yet another way to do it, probably more comfortable if you come from a
Java background.

Use Rjb (Ruby - Java bridge), and use the Java Excel API
(Domain parking page).

Here’s some code to read an Excel file:
require ‘rjb’

require_gem ‘rjb’, ‘>= 1.0.2’
…

#Returns a Java jxl.Workbook object which may then be used natively.
But don’t forget that it’s a Java object!
def ESimplyUtil.parse_excel(filename)
Rjb::load(“#{RAILS_ROOT}/lib/jxl.jar”, [‘-Xmx512M’])

file_class = Rjb::import('java.io.File')
workbook_class = Rjb::import('jxl.Workbook')
workbook = workbook_class.getWorkbook(file_class.new(filename))

end

Writing is similarly straightforward. In my app. I ended up writing CSV
using FasterCSV, but I did test out writing with JExcelAPI and it worked
fine.

Wes

12

Believe me, Fredrik, I didn’t go with the Java code on the first try.

There was something about parseexcel that was missing for me that forced
me in that direction.

I would recommend parseexcel first, the Java thing is a “just in case.”

Wes

13

I know this is a little late and it’s not a package, but this
might help someone searching down the road: Excel 2003
supports XML formats: http://www.microsoft.com/office/xml/default.mspx

There’s even a blog:
Brian Jones: Office Solutions | Microsoft Learn

This is the route I would go for a next-gen version of the spreadsheet
package if I had to do over again. We’ve got XML generating libraries in
pure Ruby (Jim W. has one out there, can’t think of the name atm),
or a wrapper around a C lib would work. From there it’s just a matter of
working out the interface, which you could probably keep very similar
(if not identical) to the current implementation.

This would solve several problems with the current implementation:

  • Formula support
  • Solves 7mb limit (afaik)
  • Human readable
  • Greater compatibility with other clients
  • MUCH easier to debug and maintain. Looking for bugs in packed binary
    strings SUCKS.

Feel free to pass this on to Hannes on the RubyForge project page. Or
better yet, join the project and start working on the implementation. I
still have admin rights on the project as well, so if you’re serious
about creating an XML based implementation, you can contact me offlist
as well: djberg96 at nospam at gmail dot com

http://rubyforge.org/projects/spreadsheet/

Regards,

Dan

This communication is the property of Qwest and may contain confidential
or
privileged information. Unauthorized use of this communication is
strictly
prohibited and may be unlawful. If you have received this communication
in error, please immediately notify the sender by reply e-mail and
destroy
all copies of the communication and any attachments.

14

Matt,
With reference to your post in Ruby group, I need your favor.
I am facing the same situation as you stated. I want to generate a
document file with the help of dynamic data from database. I want to
achieve this using Ruby. How can create template for holding dynamic
data from database and how can we interact this with Ruby. Please help
me out.
I’ll appreciate if you can help me.
Regards,

15

hi everyone…
thanks guys… thanks Chris G…
its cool man…

On Jan 18, 3:18 am, Chris G. [email protected]