r:id not set correctly in random files. Likely axlsx error.

[jasonknebel]

Thanks again for your work maintaining this gem. My isn't directly with axlsx_rails, but likely with axlsx itself and was just wondering if you had any insight as the team has not found anything online about the issue.

Recently, we upgraded axlsx to version 2.1.0-pre from 2.0.1 and since then we have been encountering an issue where the sheet appears blank when the file is opened. This happens seemingly at random; we generate the same file and it sometimes has the error and sometimes not.

We generate several hundred reports using axlsx_rails. We compared the files which displayed the information correctly in Excel/LibreOffice to those which did not. The content was always there in xl/worksheets/sheet1.xml. The difference between the working and non-working files was in /xl/workbook.xml.

In the workbook XML, there is a line which adds the reference to the working sheet, in my understanding. In the working files, this line is invariably <sheet name="MyDoc" sheetId="1" r:id="rId4"></sheet>. Looking at other contents of the excel file, it makes sense that this reference should be 4.

The non-working files have r:id="rId6" (or sometimes r:id="rId8", r:id="rId10", or r:id="rId12"). As we can tell, this value is set here. When this was changed to rId4, the same file now appears correctly when opening the file.

Have you encountered any error like this before? We probably are doing something wrong, but the system worked 100% okay on version axlsx version 2.0.1. Do you know of anyway to ensure the r:id is set properly?

[straydogstudio]

Hi Jason,

I've not done a lot with references myself. What is the code that sets the reference?

Unfortunately I don't have anything in my experience on this error. And I've not tried to see the difference between 2.0.1 to 2.1.0-pre. These days I don't do daily work in ruby (unfortunately) and I have little time for support. But if I can this weekend I'll dive into the gem differences just to see if anything comes up on rID. I'm curious as to where it is getting the wrong reference.

Any chance you can post your code in a gist?

Regards (and you are welcome for the gem,) Noel

[jasonknebel]

As always, thanks for the quick response. I'm going to dig more into this next week and write some tests to explicitly look for these r:ids.

Here is the code I'm using to attach the excel file to an email.

# mailer
report = render_to_string(
  layout: false, template: 'residual_batches/residuals_export'
)
File.open(@batch.report_file, 'w') do |f|
  f.write(report)
end
attachments[@batch.report_filename] = {
  mime_type: Mime[:xlsx],
  content: report
}

In the controller, I'm using

...
format.xlsx {
  render xlsx: report_view, filename: report_filename
}

I know this isn't much to go on and I'll let you know what I uncover next week. I'm unsure if the controller rendering is having the same issues as when it's actually being saved to a file. As I said, more testing is required! Enjoy your weekend.

[straydogstudio]

@jasonknebel Did you ever get any conclusive results or fix?

[jasonknebel]

I did not, unfortunately. We forked the axlsx gem and are using an embarrassing hack. Thanks for following up.

[phuongnguyen2311]

@jasonknebel i have problem same you how to you resolve this issue? can you help me?