Give style to your MS Excel sheets in Ruby

I recently got a requirement to generate MS Excel workbook with different worksheets in Ruby on Rails. Also, I had to give style to the column headers, set background color for table of content and make columns justified.

I came across http://rubyonwindows.blogspot.com and spreadsheet links which are very helpful if you want to try anything with MS Excel in Ruby.

I installed spreadsheet gem (sudo gem install spreadsheet), but giving styles did not seem very straightforward

So, I thought of giving it a shot. Let's first try to create a workbook with three worksheets.

workbook = Spreadsheet::Workbook.new
worksheet_for_books = workbook.create_worksheet(:name => "Books")
worksheet_for_stationaries = workbook.create_worksheet(:name => "Stationary")
worksheet_for_laptops = workbook.create_worksheet(:name => "Laptops")

Now, set headers in all worksheets:
for worksheet in workbook.worksheets
  row = 0
  (0..2).each do |column|
    worksheet[row, column] = "Name_#{column}"
  end
end

In the above code snippet, you might have noted that:

• Iterating over worksheet is very simple, just like array.
• Setting value to a cell is very simple, just worksheet[row, column] = value

Now let's make the column headers bold and justified:
format_header = Spreadsheet::Format.new(:weight => :bold, :align => :justify)
for worksheet in workbook.worksheets
  (0..4).each do |column|
    worksheet.row(0).set_format(column, format_header)
  end
end


Now let's try to add background color 'yellow' to these headers:

format_header = Spreadsheet::Format.new(:weight => :bold, :align => :justify, :pattern_fg_color => :yellow)

for worksheet in workbook.worksheets

  (0..4).each do |column|

    worksheet.row(0).set_format(column, format_header)

  end

end

It didn't work! why? no it's not because we have added :pattern_fg_color , you might be thinking :pattern_bg_color is for background, but this is wrong, this is what I found in the references.

"Use :pattern_fg_color to set background of a cell"

Anyways, even this option didn't work for me. I couldn't understand why. But I found a work around(reference) and here is the enhanced version:

Create color_format class and put it in a library

class ColorFormat < Spreadsheet::Format

   def initialize(opts)

     super opts

   end

end

And our code will look like:

require "color_format"

format_header = ColorFormat.new(:weight => :bold, :align => :justify, :pattern_fg_color => :yellow, :pattern => 1)

for worksheet in workbook.worksheets

  (0..4).each do |column|

    worksheet.row(0).set_format(column, format_header)

  end

end

And that's it!