How to create Excel sheets with spreadsheet gem and use number formats for cells like money or date

Updated . Posted . Visible to the public.

The following snippet demonstrates how you could create excel files (with spreadsheet gem Show archive.org snapshot ) and format columns so that they follow a specific number format like currencies or dates do.

require 'rubygems'
require 'spreadsheet'

Spreadsheet.client_encoding = 'UTF-8'

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet :name => 'test'

money_format = Spreadsheet::Format.new :number_format => "#,##0.00 [$€-407]"
date_format = Spreadsheet::Format.new :number_format => 'DD.MM.YYYY'

# set default column formats
sheet1.column(1).default_format = money_format
sheet1.column(2).default_format = date_format
sheet1.row(0).push "just text", 5.98, DateTime.now

book.write 'test.xls'

That's how to insert Links into a excel cell

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
sheet1[0,0] = Spreadsheet::Link.new 'www.google.com', 'link text'
book.write 'spreadsheet_with_link.xls'

Further information can be found in the API documentation Show archive.org snapshot or quick guide Show archive.org snapshot .

Ulrich Berkmüller
Last edit
Deleted user #6
License
Source code in this card is licensed under the MIT License.
Posted by Ulrich Berkmüller to makandra dev (2012-01-26 11:51)