Ruby Spreadsheet Row Background Color

ruby spreadsheet row background color

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

is there a way to fetch the color code of a cell in excel using a ruby gem?

I don't think Roo gem has it (seems like there was an attempt 4 days ago, but it wasn't merged, and the contributor closed the PR).

You could use the Spreadsheet gem instead to fetch the background color.

# get bg color at 1st Sheet, 3rd row, 5th column.
# I didn't run this.. go to gem repository for more accurate examples?
book = Spreadsheet.open '/path/to/an/excel-file.xls'
sheet = book.worksheet(0)
row = sheet.row(2)
cell_format = row.format(4)
color = cell_format.pattern_bg_color

Writing an excel sheet with dynamic background colors in Ruby

You can set the cell backgrounds dynamically using the Conditional Formating feature of the write_xlsx gem.

Here is an example from the gemfile:

#!/usr/bin/env ruby
# -*- coding: utf-8 -*-

require 'rubygems'
require 'write_xlsx'

workbook  = WriteXLSX.new('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet

# Light red fill with dark red text.
format1 = workbook.add_format(
    :bg_color => '#FFC7CE',
    :color    => '#9C0006'
)

# Green fill with dark green text.
format2 = workbook.add_format(
    :bg_color => '#C6EFCE',
    :color    => '#006100'
)

# Some sample data to run the conditional formatting against.
data = [
    [ 90, 80,  50, 10,  20,  90,  40, 90,  30,  40 ],
    [ 20, 10,  90, 100, 30,  60,  70, 60,  50,  90 ],
    [ 10, 50,  60, 50,  20,  50,  80, 30,  40,  60 ],
    [ 10, 90,  20, 40,  10,  40,  50, 70,  90,  50 ],
    [ 70, 100, 10, 90,  10,  10,  20, 100, 100, 40 ],
    [ 20, 60,  10, 100, 30,  10,  20, 60,  100, 10 ],
    [ 10, 60,  10, 80,  100, 80,  30, 30,  70,  40 ],
    [ 30, 90,  60, 10,  10,  100, 40, 40,  30,  40 ],
    [ 80, 90,  10, 20,  20,  50,  80, 20,  60,  90 ],
    [ 60, 80,  30, 30,  10,  50,  80, 60,  50,  30 ]
]

# This example below highlights cells that have a value greater than or
# equal to 50 in red and cells below that value in green.

caption = 'Cells with values >= 50 are in light red. ' +
          'Values < 50 are in light green'

# Write the data.
worksheet1.write('A1', caption)
worksheet1.write_col('B3', data)

# Write a conditional format over a range.
worksheet1.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :format   => format1,
        :criteria => '>=',
        :value    => 50
    }
)

# Write another conditional format over the same range.
worksheet1.conditional_formatting('B3:K12',
    {
        :type     => 'cell',
        :format   => format2,
        :criteria => '<',
        :value    => 50
    }
)

workbook.close

The output file will look like this:

Roo gem: cell background color

I looked around a little more and Roo does not seem to provide any advanced functions for custom inspection.

So I switched gems. I'm using spreadsheet now.

spreadsheet = Spreadsheet.open(file_path)
sheet = spreadsheet.worksheet("278")
row = sheet.row(5)
background_color = row.format(5).pattern_bg_color

Not able to style Excel with spreadsheet gem (Ruby)

Well, it was not possible to format the existing excel then write it as a new Excel. Formatting was lost in that.
To overcome I created a new excel (populated with my existing data read from the old excel) formatted it as I want then used

book.write "xxx.xls"

Ruby Spreadsheet gem format cell stops working on third sheet

You should not create multiple formats.
Instead create them once and reuse them where needed.

The Issue is:

4.times { |x| sheet.row(0).set_format(x, title_format) }

creates 4 new formats then

4.times { |x| sheet.row(7).set_format(x, title_format) }

creates 4 more. Even though they all look the same they are actually 8 separate formats. So just in the code you posted you have created 16 separate styles in that workbook.

Excel can only handle so many formats before it gets really upset (usually resulting in corruption)

The Note portion of that site (under Cause) does not apply to programmatically adding styles when adding styles programmatically it will create a separate reference for each new style with out determining if such style already exists

Instead try this:

  TITLE_FORMAT = Spreadsheet::Format.new(weight: :bold,pattern: 1,pattern_fg_color: :silver)
  HEADER_FORMAT =  Spreadsheet::Format.new(color: :white,pattern: 1,pattern_fg_color: :xls_color_48,weight: :bold)

  def make_xls
      book = Spreadsheet::Workbook.new
      sheet = book.create_worksheet 
      4.times { |x| sheet.column(x).width = 30 }
      4.times { |x| sheet.row(0).set_format(x, TITLE_FORMAT) }
      4.times { |x| sheet.row(1).set_format(x, HEADER_FORMAT ) }
      4.times { |x| sheet.row(7).set_format(x, TITLE_FORMAT) }
      4.times { |x| sheet.row(8).set_format(x, HEADER_FORMAT ) }
   end

And see if that helps

---

Related Topics