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:
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:
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.
To set a format you can use
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.
The gem works with a workbook object and worksheets in this workbook. To get started:
workbook = Spreadsheet::Workbook.new
sheet1 = workbook.create_worksheet name: worksheetnameTo 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
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: :blackUse :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
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.
