Can not copy cell style

[cyberkostyan]

I’m using spreadsheet (version 1.1.4) within Rails(5.0.1) app and I’m stuck with some cell style formatting. In my app I have Report model which has_many Documents - which are "Microsoft Excel 97/2000/XP” files. My goal is to combine all files into one XLS, where 1 file is located on separate worksheet of target file.

Initially I was trying to use simple method add_worksheet, assuming that all styles will be automatically transferred

  def archive
    Spreadsheet.client_encoding = 'UTF-8'
    book = Spreadsheet::Excel::Workbook.new
    @report.documents.each do |doc|
      if doc.document
        book_src = Spreadsheet.open(doc.attach.file.path)
             book.add_worksheet book_src.worksheet(0)
      end
    end

    f = Tempfile.new(['archive', '.xls'])
    book.write( f )

    respond_to do |format|
      format.html
      format.xls { send_file f }
    end
  end

But it fails with such error, when trying to write a file:

NoMethodError - undefined method `content' for nil:NilClass:
  spreadsheet (1.1.4) lib/spreadsheet/excel/workbook.rb:70:in `shared_string'
  spreadsheet (1.1.4) lib/spreadsheet/excel/worksheet.rb:86:in `shared_string'
  spreadsheet (1.1.4) lib/spreadsheet/excel/reader.rb:623:in `read_labelsst'
  spreadsheet (1.1.4) lib/spreadsheet/excel/reader.rb:706:in `block in read_row'
  spreadsheet (1.1.4) lib/spreadsheet/excel/reader.rb:671:in `read_row'
  spreadsheet (1.1.4) lib/spreadsheet/excel/worksheet.rb:59:in `row'
  spreadsheet (1.1.4) lib/spreadsheet/worksheet.rb:158:in `block in each'
  spreadsheet (1.1.4) lib/spreadsheet/worksheet.rb:157:in `each'
  spreadsheet (1.1.4) lib/spreadsheet/excel/worksheet.rb:47:in `each'
  spreadsheet (1.1.4) lib/spreadsheet/worksheet.rb:198:in `format_dates!'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:110:in `block in sanitize_worksheets'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:108:in `sanitize_worksheets'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:396:in `write_from_scratch'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:647:in `write_workbook'
  spreadsheet (1.1.4) lib/spreadsheet/writer.rb:12:in `write'
  spreadsheet (1.1.4) lib/spreadsheet/workbook.rb:129:in `write'
...

Then I’ve decided to change my strategy and iterate through the all data and get more control on each cell

  def archive
    Spreadsheet.client_encoding = 'UTF-8'
    book_dst = Spreadsheet::Excel::Workbook.new

    @report.documents.each do |doc|
      if doc.document
        book_src = Spreadsheet.open(doc.attach.file.path)
        # book.add_format book_src.formats  # should I add this? Nothing changed
        # book.default_format = book_src.default_format # do I have to copy defaults? Nothing changed 

        sheet_src = book_src.worksheet(0)
        sheet_dst = book_dst.create_worksheet(name: doc.server.name)
        sheet_dst.default_format = sheet_src.default_format

        sheet_src.each_with_index do |row_src, r|
          sheet_dst.row(r).default_format = sheet_src.row(r).default_format
          sheet_dst.row(r).height = sheet_src.row(r).height
          row_src.each_with_index do |cell_src, c|
            # sheet_src[0,0] # needed to ensure read
            sheet_dst.format_column c, nil, width: sheet_src.column(c).width
            sheet_dst[r, c] = sheet_src[r, c]
            sheet_dst.row(r).set_format(c, row_src.format(c))
            Rails.logger.debug "--> Styled FONT #{row_src.format(c).pattern_fg_color} "
          end
        end

        # Merges multiple cells into one. WARNING !!!
        # def merge_cells start_row, start_col, end_row, end_col
        # but merged_cells returns [start_row, end_row, start_col, end_col]
        # sheet_src[0,0] # needed to ensure read
        sheet_src.merged_cells.each do |start_row, end_row, start_col, end_col|
          sheet_dst.merge_cells start_row, start_col, end_row, end_col
        end

      end
    end

    f = Tempfile.new(['archive', '.xls'])
    book_dst.write( f )

    respond_to do |format|
      format.html
      format.xls { send_file f }
    end
  end

Everything becomes perfect, beside one thing - the difference in cell's background colours. I have completely different colours from the original file. I suppose I should copy some colour map from the origin file?

[zdavatz]

Thanks for reporting. Did you find anything search in Github?

[cyberkostyan]

I guess, I have two issues:

1. Failed to write XLS with many worksheets created by method add_worksheet
2. There is some info how to read and how to set background color, but nothing about how to copy colour map. Eventually, :xls_color_22 isn't green as in the source file(sheet_src).

[zdavatz]

I guess copy is not implemented yet.

[zdavatz]

Adding worksheets should work. Did you break down your code to test?

[cyberkostyan]

Successfully added, but failed on write. I simplified the code to just copy one worksheet from another and save. Here is source XLS ("config/sheet1.xls") https://cloud.larkit.ru/index.php/s/eEQdTEY1mOTaJmt

  def copyXLS
    Spreadsheet.client_encoding = 'UTF-8'
    book_dst = Spreadsheet::Excel::Workbook.new
    book_src = Spreadsheet.open("config/sheet1.xls")
    book_dst.add_worksheet book_src.worksheet(0)
    f = Tempfile.new(['archive', '.xls'])
    book_dst.write( f )
  end

Exception is on book_dst.write( f )

NoMethodError - undefined method `content' for nil:NilClass:
  spreadsheet (1.1.4) lib/spreadsheet/excel/workbook.rb:70:in `shared_string'
  spreadsheet (1.1.4) lib/spreadsheet/excel/worksheet.rb:86:in `shared_string'
  spreadsheet (1.1.4) lib/spreadsheet/excel/reader.rb:623:in `read_labelsst'
  spreadsheet (1.1.4) lib/spreadsheet/excel/reader.rb:706:in `block in read_row'
  spreadsheet (1.1.4) lib/spreadsheet/excel/reader.rb:671:in `read_row'
  spreadsheet (1.1.4) lib/spreadsheet/excel/worksheet.rb:59:in `row'
  spreadsheet (1.1.4) lib/spreadsheet/worksheet.rb:158:in `block in each'
  spreadsheet (1.1.4) lib/spreadsheet/worksheet.rb:157:in `each'
  spreadsheet (1.1.4) lib/spreadsheet/excel/worksheet.rb:47:in `each'
  spreadsheet (1.1.4) lib/spreadsheet/worksheet.rb:198:in `format_dates!'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:110:in `block in sanitize_worksheets'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:108:in `sanitize_worksheets'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:396:in `write_from_scratch'
  spreadsheet (1.1.4) lib/spreadsheet/excel/writer/workbook.rb:647:in `write_workbook'
  spreadsheet (1.1.4) lib/spreadsheet/writer.rb:12:in `write'
  spreadsheet (1.1.4) lib/spreadsheet/workbook.rb:129:in `write'

[zdavatz]

What happens if you do not copy but write a completely new file?

[cyberkostyan]

If I just do

book_dst = Spreadsheet::Excel::Workbook.new
f = Tempfile.new(['archive', '.xls'])
book_dst.write( f )

I've got error no implicit conversion of nil into Integer

[zdavatz]

What do you want to do? Copy an existing workbook? Opening, modifying and saving an existing book should work.

[cyberkostyan]

My goal is to combine many XLS files into one target XLS file, where each file is located on separate worksheet of target file. The example above was just to show the main issue which I have when trying to add new worksheet.

[zdavatz]

Have you tried this: http://stackoverflow.com/questions/3741662/how-can-i-create-new-spreadsheet-worksheets-in-ruby-using-the-spreadsheet-gem

[cyberkostyan]

Thanks for the link, sorry, but it has no any relation to the style issue. Maybe you can help me to change the color palette?
Or how to set new RGB color for :xls_color_22 ?

[zdavatz]

Can you provide me with a full test-script and files so I can test? What software created the original file that you are trying to modify? I can not reproduce this.