Format missing after certain rows and columns

[stephen-tech4]

Hi,

I have the below method to generate an excel, but the format did not apply from row=4, col=7 onwards. Supposed all the cells should have this format Spreadsheet::Format.new(weight: :bold, size: 14, horizontal_align: :right).

require 'spreadsheet'

def generate(filename:)
  excel = Spreadsheet::Workbook.new

  # Create worksheet
  sheet = excel.create_worksheet(name: "Sample Excel")

  10.times do |row|
    10.times do |col|
      sheet.row(row).set_format(
        col,
        Spreadsheet::Format.new(weight: :bold, size: 14, horizontal_align: :right)
       )

      # Issue: It does not apply to row=4, col=7 onwards
      sheet.row(row).push("row=#{row}, col=#{col}")
    end
  end

  excel.write(filename)
end

To replicate this, simply paste the codes above into rails console, then run it generate(filename: "test.xls"). The excel will be created at the current directory.

I am not sure if my codes have bugs. Please help!

Rails version: 7.1.3 Ruby version: 3.2.2 Spreadsheet version: 1.3.1

Thanks!

[zdavatz]

Can please you try with a pure Ruby script, that does not depend on Rails?

[stephen-tech4]

I think there is a problem with my codes.

I should initialize the format once only and reuse it, instead of initialize it in every loop.

require 'spreadsheet'

# Solution: Initialize format once and for all
FORMAT_STYLE = Spreadsheet::Format.new(weight: :bold, size: 14, horizontal_align: :right)

def generate(filename:)
  excel = Spreadsheet::Workbook.new

  # Create worksheet
  sheet = excel.create_worksheet(name: "Sample Excel")

  10.times do |row|
    10.times do |col|
      # Reuse the FORMAT_STYLE
      sheet.row(row).set_format(
        col,
        FORMAT_STYLE
       )

      sheet.row(row).push("row=#{row}, col=#{col}")
    end
  end

  excel.write(filename)
end

Here is where I found the explanation: Ruby Spreadsheet gem format cell stops working on third sheet by engineersmnky

Why not too many different cell formats

Thanks!

[stephen-tech4]

Can please you try with a pure Ruby script, that does not depend on Rails?

I have tried both codes in irb without Rails. The results are the same.

[zdavatz]

Yes, try to refactor your code without thinking of Rails.

[zdavatz]

Did you come up with a solution? Can you share your solution here please?

[stephen-tech4]

Yes, the solution is to lessen the initialization of the Spreadsheet::Format.

My mistake in first example that I initialized it in every loop, thus excel detected too many formats and became malfunction.

# Initialize once
FORMAT_STTYLE = Spreadsheet::Format.new(weight: :bold)

def generate
  # ...
  # Reuse the FORMAT_STYLE
  10.times do |row|
    10.times do |col|
      # Don't: initialize in the loop as excel will treat it as 1 format each initialization
      # 100 formats here
      # sheet.row(row).set_format(col, Spreadsheet::Format.new(weight: :bold)

      # Do's: Reuse the constant instead
      # Only 1 format here
      sheet.row(row).set_format(col, FORMAT_STYLE)
    end
  end

  # ...
end

And it works!

[zdavatz]

Thank you for sharing!