Export to Excel with spreadsheet gem

While using the spreadsheet gem to export data to a Microsoft Excel file I encountered various problems and lack of proper documentation forced me to dig pretty deep. So here are a few tips you might find useful.

The gem works with a workbook object and worksheets in this workbook. To get started:

workbook = Spreadsheet::Workbook.new
sheet1 = workbook.create_worksheet name: worksheetname

To write a row of data as usually is done in reports you can use:

sheet1.row(1).replace [ 'col1', 'col2', 'col3']

Rows and Columns are 0 based, so col1 would be printed on A2 (Excel coords).

Now to the beauty of it all, formatting, first of all, create a format:
bold = Spreadsheet::Format.new :weight => :bold
bold20 = Spreadsheet::Format.new :weight => :bold, size: 20
nobold20 = Spreadsheet::Format.new size: 20
bold_grey_bg = Spreadsheet::Format.new pattern_bg_color: :cyan, weight: :bold, pattern: 1, pattern_fg_color: :black

Use :weight to set bold, italic etc, pattern_bg_color for background, to use a font you have to create a Font object and assign it with font: font_object. You have to use pattern: 1 for the background to show, haven't figured this one out yet.

Available colors(0.6.5.9):


COLORS = [ :builtin_black, :builtin_white, :builtin_red, :builtin_green, :builtin_blue, :builtin_yellow, :builtin_magenta, :builtin_cyan, :text, :border, :pattern_bg, :dialog_bg, :chart_text, :chart_bg, :chart_border, :tooltip_bg, :tooltip_text, :aqua, :black, :blue, :cyan, :brown, :fuchsia, :gray, :grey, :green, :lime, :magenta, :navy, :orange, :purple, :red, :silver, :white, :yellow ]

If you need more colors please refer to http://rubyforge.org/forum/message.php?msg_id=95796 as there it is explained how to add all the colors of Excel to the gem.

Instead of modifying the gem though, I would suggest using the following two lines at startup, they modify constants which isn't very nice but maybe preferable to modifying the gem just to get some color working.

Spreadsheet::Excel::Internals::SEDOC_ROLOC.update(:blue_gray => 0x001f)
Spreadsheet::Column.singleton_class::COLORS << :blue_gray

 To set a format you can use
sheet.row(3).default_format = format_object (e.g. bold20, created before)

or a certain cell
sheet1.row(8).set_format(1, bold_grey_bg)

Ok, that's it, if something else comes up I'll edit this post.


Popular posts from this blog

Caleidoscopio de dos naciones

Changing jobs