Ruby Spreadsheet gem format cell stops working on third sheet

Question

I am trying to set the background-color for a table. it works well with this code. But if my workbook has more then 3 sheets, the set_format will not work on third sheet row(8) 4th cell. All formatting will not work from there on. every xls file generated has the same result. it stops at that particular cell all the rest of the workbook has no more styling. Any suggestion?

please see the attached picture.

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) }

      insert_values..
   end

  def title_format
    Spreadsheet::Format.new(
      weight: :bold,
      pattern: 1,
      pattern_fg_color: :silver
    )
  end

  def header_format
    Spreadsheet::Format.new(
      color: :white,
      pattern: 1,
      pattern_fg_color: :xls_color_48,
      weight: :bold
    )
  end

4th cell missing format

Any suggestion is appreciated, I am using spreadsheet (1.2.6), RoR6.

Answer 1

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