Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ruby spreadsheet row background color

I am trying to parse an excel spreadsheet using "spreadsheet". How could I get the background color of each row?

like image 975
fflyer05 Avatar asked Oct 11 '11 17:10

fflyer05


2 Answers

book = Spreadsheet::Workbook.new 
sheet = book.create_worksheet :name => 'Name'
format = Spreadsheet::Format.new :color=> :blue, :pattern_fg_color => :yellow, :pattern => 1
sheet.row(0).set_format(0, format) #for first cell in first row

or

sheet.row(0).default_format = format #for entire first row

you can iterate over each row/cell and apply style exactly where you want

like image 95
okliv Avatar answered Nov 23 '22 19:11

okliv


I was looking around for colors that you can use for the background color of a cell. For example:

Spreadsheet::Format.new({ :weight => :bold, :pattern => 1, :pattern_fg_color => :silver })

I couldn't find good info on which colors I could use for :pattern_fg_color. I decided to look for Excel help and found: http://dmcritchie.mvps.org/excel/colors.htm (at "The DOS assignments of the 16 colors").

It looks like the top 16 colors work:

0 Black, 1 Navy, 2 Green, 3 Teal, 4 Maroon, 5 Purple 6 Olive, 7 Silver, 8 Gray, 9 Blue, 10 Lime, 11 Aqua, 12 Red, 13 Fuschia, 14 Yellow, 15 White

like image 24
Moemars Avatar answered Nov 23 '22 19:11

Moemars