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

Related

Категории