Generating and Parsing Excel Spreadsheets

Problem

Your program needs to parse data from Excel spreadsheets, or generate new Excel spreadsheets.

Solution

To generate Excel files, use the spreadsheet library, available as a third-party gem (see the See Also section below for where to get it). With it you can create simple Excel spreadsheets. As of this writing, spreadsheet does not support formulas or large spreadsheets (seven megabytes is the limit).

This code creates an Excel spreadsheet containing some random numbers with a total, and saves it to disk:

	require 
ubygems
	require spreadsheet/excel

	SUM_SPREADSHEET = sum.xls
	workbook = Spreadsheet::Excel.new(SUM_SPREADSHEET)
	worksheet = workbook.add_worksheet(Random numbers and their sum.)
	sum = 0
	random_numbers = (0..9).collect { rand(100) }
	worksheet.write_column(0, 0, random_numbers)

	format = workbook.add_format(:bold => true)
	worksheet.write(10, 0, "Sum:", format)
	worksheet.write(10, 1, random_numbers.inject(0) { |sum, x| sum + x })
	workbook.close

To parse an Excel file, use the parseexcel library, also available as a third-party download. It can parse simple data out of the Excel file format. This code parses the Excel file generated by the previous code:

	require  
parseexcel/parser
	workbook = Spreadsheet::ParseExcel::Parser.new.parse(SUM_SPREADSHEET)

	worksheet = workbook.worksheet(0)
	sum = (0..9).inject(0) do |sum, row|

	 sum + worksheet.cell(row, 0).value.to_i
	end

	worksheet.cell(10, 0).value # => "Sum:"
	worksheet.cell(10, 1).value # => 602.0
	sum # => 602

Like spreadsheet, parseexcel doesn recognize spreadsheet formulas.

Discussion

The comma-separated file is the lingua franca for spreadsheet data, but sometimes you must deal with real spreadsheet files. You can save other peoples time by accepting their Excel spreadsheets as input, instead of insisting they convert everything to CSV for you. And nothing impresses manager types like an automatically generated spreadsheet file they can poke at.

The spreadsheet and parseexcel libraries are only suitable for creating or parsing simple spreadsheets: more or less the ones that export well to comma-delimited format. If you want to handle more complex Excel files from Ruby, you have a couple options. The POI Java library can write various Microsoft Office files, and it has Ruby bindings. If you e running on a Windows computer that has Excel installed, you can use Rubys built-in win32ole library to communicate with the Excel installation.

Hopefully this will be fixed by the time you read this, but just in case: spreadsheets generated with spreadsheet may show up as black-on-black in some spreadsheet programs (Gnumeric is one). This is because spreadsheet generates workbooks with a default format that specifies no background color. So each spreadsheet program uses its default color, and some of them make unfortunate choices. Heres a subclass of Workbook that specifies default text and background colors, so that you don end up with a black-on-black spreadsheet:

	class ExcelWithBackground < Spreadsheet::Excel
	 def initialize(*args)
	 super(*args)
	 @format = Format.new(:bg_color => white, :fg_color => lack)
	 end
	end

	workbook = ExcelWithBackground.new(SUM_SPREADSHEET)
	# …

See Also

  • You can download parseexcel from http://download.ywesee.com/parseexcel/
  • The spreadsheet homepage is at http://rubyspreadsheet.sourceforge.net/; its available as a gem (http://prdownloads.sourceforge.net/rubyspreadsheet/), but since its not hosted on RubyForge, you can just install it with gem install spreadsheet-excel: you must download the gem and run gem install on the local gem file
  • POI (http://jakarta.apache.org/poi/index.html) and its Ruby bindings (http://jakarta.apache.org/poi/poi-ruby.html)
  • Information on scripting Excel in Ruby (http://www.rubygarden.org/ruby?ScriptingExcel)
  • The "Ruby and Microsoft Windows" chapter in the Pickaxe BookProgramming Ruby by Dave Thomas, with Chad Fowler and Andy Hunt (Pragmatic Bookshelf)


Strings

Numbers

Date and Time

Arrays

Hashes

Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming

XML and HTML

Graphics and Other File Formats

Databases and Persistence

Internet Services

Web Development Ruby on Rails

Web Services and Distributed Programming

Testing, Debugging, Optimizing, and Documenting

Packaging and Distributing Software

Automating Tasks with Rake

Multitasking and Multithreading

User Interface

Extending Ruby with Other Languages

System Administration



Ruby Cookbook
Ruby Cookbook (Cookbooks (OReilly))
ISBN: 0596523696
EAN: 2147483647
Year: N/A
Pages: 399

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net