Images & formatting lost after writing to existing file

[edithemmings]

I have the following two models in a Rails app:

class ModelA
  has_one_attached :template_file
end

class ModelB
  has_one_attached :export_file
end

My goal is to make a copy of the template file from ModelA, write data to that copy, and save it to the export file on ModelB. My ModelA template file is an xls attachment with 4 worksheets. The first worksheet has a styled header row. I am writing only to the columns under those headers. The other worksheets in the file have images in them.

The result of my code is a copy of the file with the same worksheets with my data written to the first sheet as-expected--except all images are missing and some of the header formatting is lost (for example, text alignment is lost, but background color is not).

If I comment out the call to write_to_sheet(sheet) in the build_and_attach_export method, then I end up with a perfect copy of the original file saved as export_file. However any attempt to write to any cell in the sheet results in the lost images and formatting.

def build_and_attach_export
  # pull down template/copy to tempfile (this is working fine)
  template = model_a.template_file
  filename_base = template.blob.filename.base
  filename_ext = template.blob.filename.extension_with_delimiter
  tmp = Tempfile.new([filename_base, filename_ext], encoding: 'ascii-8bit')
  tmp.write(template.download)
  tmp.rewind

  # parse tmp file using Spreadsheet (also working fine)
  book = Spreadsheet.open(tmp)
  tmp.unlink
  sheet = book.worksheet(0)

  # This seems to be the origin of my problems. Yes, it writes to the file, but it messes up other elements of the file.
  write_to_sheet(sheet)

  # write to IO for upload (also working fine)
  io = StringIO.new
  book.write(io)
  io.rewind
  blob = ActiveStorage::Blob.create_and_upload!(io: ,  filename: 'export.xls',  content_type: 'application/vnd.ms-excel')
  model_b.export_file.attach(blob)
end

def write_to_sheet(sheet)
  # note: source_data is an array of arrays(rows) of strings(cell values)
  source_data.each_with_index do |row_vals, row_idx|
    row_vals.each_with_index do |val, col_idx|
      next unless val

      # I tried some different syntax from the Spreadsheet docs, but any attempt to write to the file messes up my images/formatting
      sheet.rows[row_idx + 1][col_idx] = val
    end
  end
end

Does anyone have any insight into why this might be happening?

[zdavatz]

Have you tried writing to a new file? Does that work?

[edithemmings]

Have you tried writing to a new file? Does that work?

@zdavatz Could you be more specific? It looks like you're suggesting writing my data to a new file instead of the tmp copy of the template, which would defeat the purpose of what I am trying to do. It is the existing images and styling in the template that are getting lost when I write to unrelated cells/sheets.

[zdavatz]

which software created the original file you are trying to edit with the Spreadsheet Gem?

[edithemmings]

which software created the original file you are trying to edit with the Spreadsheet Gem?

The original template file was made manually by a human. The file that I am parsing in Spreadsheet and writing to is a Tempfile that I wrote the original file into (variable tmp in the above example).

[zdavatz]

Was the Original File created by Openoffice or MS Office? On Mac or on Windows? Any chances of seeing the original file?

[edithemmings]

Was the Original File created by Openoffice or MS Office? On Mac or on Windows? Any chances of seeing the original file?

I'm not able to share the original file unfortunately. And I do not know the circumstances of its original creation. My client has many template files like this that have existed for a long time. If there are ways they could re-create the templates for a better chance of success, then I am all ears.

[zdavatz]

I would try to created the templates with Ruby spreadsheet from the start, then modify the file and then save as a new file. Sometimes the software creating the original files adds binary information, that Ruby Spreadsheet does not add, then all fails. So if you can, I would go the whole way with Ruby Spreadsheet.

[edithemmings]

I would try to created the templates with Ruby spreadsheet from the start, then modify the file and then save as a new file. Sometimes the software creating the original files adds binary information, that Ruby Spreadsheet does not add, then all fails. So if you can, I would go the whole way with Ruby Spreadsheet.

Ok that could explain it, but I'm still not understanding why this works perfectly:

book = Spreadsheet.open(tmp)

io = StringIO.new
book.write(io)
io.rewind

But this destroys the binary info:

book = Spreadsheet.open(tmp)
sheet = book.worksheet(0)
sheet.rows[1][0] = 'hello'

io = StringIO.new
book.write(io)
io.rewind

What am I missing?

[zdavatz]

Can we try to reproduce this with a sample file of yours?

[edithemmings]

Can we try to reproduce this with a sample file of yours?

I was able to reproduce with this template file: template.xls

And here is the resulting export file with "hello" written to it. The image on the second sheet has been lost. export.xls

[zdavatz]

Ok, so the original file was created by Miguel Rocafort. Which software did he use to create the file? Which OS? Did he use the spreadsheet gem to the create the file template.xls?

[edithemmings]

Ok, so the original file was created by Miguel Rocafort. Which software did he use to create the file? Which OS? Did he use the spreadsheet gem to the create the file template.xls?

Not sure who that is, but the spreadsheet gem was not used to create the template file. These templates are created by users -- not with this gem, so using the gem to produce test templates does not match my use case. I used this chrome extension to create the above test file: https://chromewebstore.google.com/detail/xls-editor/iobjaooppmgjlgomfpaohhncpfjpigaf

From what I understood you were looking to see the behavior reproduced, and the above test file matches what I'm seeing with my users' files.

[edithemmings]

It’s looking like this gem may not be the right fit for my case. I wish I had a deeper understanding (and more time) so I could contribute to researching, but realistically at this point I just need a workaround. If I can get my client on board with converting template files to xlsx, then I can use the RubyXL gem with the same logic, and the file remains intact.

Thanks so much for your time and effort! I’m happy to keep discussing/providing context if its of any further value.