caxlsx_rails: Excel 2016 unable to open generated document

I have been experiencing issues with opening generated documents with Excel 2016. Excel presents the following message: 2c127a76-27e4-11e6-8391-eb113ec8df7c If I “Open and Repair” I’m offered the chance to see the log file of what was removed, and the repaired sheet is opened. It’s not very helpful…

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to generated_report_30.xml</logFileName><summary>Errors were detected in file '/Users/martinpeck/Library/Containers/it.bloop.airmail2/Data/Library/Application Support/Airmail/General/Tmp/generated_report_3.xlsx'</summary><additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo></recoveryLog>

It appears OSX Numbers has an issue with fonts: 0868f612-27e5-11e6-8ea9-6ff90fa148c4

OSX OpenOffice opens it fine without issue.

The report is generated via a rake task. Here is the code that I am generating the doc:

view_assigns = {users: User.all, referrals: ReferralTracker.all, reporting_regions: ReportingRegion.all}
av = ActionView::Base.new(ActionController::Base.view_paths, view_assigns)

content = av.render template: 'dashboard/generate_report.xlsx.axlsx'

report = File.open("./tmp/codeclub-numbers-report-#{Date.today}.xlsx","w+b") {|f| f.puts content }

The template is fairly large so here is the main call:

wb = xlsx_package.workbook
wb.styles do |s|

header_title =  s.add_style  :bg_color => "ff9900", :fg_color => "FF", :sz => 11, :alignment => { :horizontal=> :left }
date_header =  s.add_style :sz => 18, :alignment => { :horizontal=> :left }
section =  s.add_style :sz => 10, :alignment => { :horizontal=> :left }


wb.add_worksheet(name: "Reporting Sheet") do |sheet|
    sheet.add_row ["Report ran for #{1.month.ago.beginning_of_month.strftime("%B")}"], :style => [date_header]
.....
....
    end
end

Anyone have any ideas what might be causing this?

About this issue

Original URL
State: open
Created 8 years ago
Comments: 31

Most upvoted comments

I just noticed that using a worksheet name seemed to make a difference and I was able to open the file in Excel.

p.workbook.add_worksheet(name: 'asdf') do |sheet|

jayshepherd on Sep 26, 2016

I just changed this:

file = File.open("#{Rails.root}/public/exports/#{fnm}", 'w') { |f| f.puts xlsx }

to:

file = File.open("#{Rails.root}/public/exports/#{fnm}", 'w') { |f| f.write xlsx }

and it’s working now 😉

msdundar on Jun 7, 2017

attachment = Base64.encode64(xlsx) When included, generates a file that is unreadable by MS Excel an Numbers.

The code that worked from me - xlsx = render_to_string layout: false, handlers: [:axlsx], formats: [:xlsx], template: "invoices/invoices_mailer" //#summary_file = Base64.encode64(xlsx) -- _Commented out_ attachments["invoices_summary.xlsx"] = {mime_type: Mime::XLSX, content: xlsx}

vsaroha on Sep 16, 2017

I was able to fix it by Extending to ActionController::Base on my controller. Is there a way where I don’t have to use the Base and still using API ? Thank you so much

aindong on Dec 11, 2017

I get the same error with:

driver = Driver.last
wb.add_worksheet(name: driver.fullname)

resolved with: wb.add_worksheet(name: "#{driver.fullname}")

pinzer on Sep 28, 2017

@msdundar Fantastic. I could see the extra new line causing an issue (although it really shouldn’t.) Thanks for posting. I’ll add a note on this to the troubleshooting section of the README.

straydogstudio on Jun 7, 2017

@asampatoor There are a number of issues that can come up. Axlsx and the xlsx format can be picky, and I’m not sure why. A few things to try:

Turn shared strings on (https://github.com/straydogstudio/axlsx_rails#axlsx-package-options)
Make sure you pass layout false when you render. Sometimes Rails decides to render a layout when it shouldn’t.
Make sure you aren’t adding worksheets with no name: p.workbook.add_worksheet(name: 'blah')

You can also make sure there isn’t a problem with axlsx_rails and the rendering context by moving your code into a script and using rails runner. That will confirm if Axlsx or axlsx_rails is the issue.

straydogstudio on Mar 3, 2017

@straydogstudio I’m also getting a similar issue. MS Excel 2016 cannot open the generated document, but Numbers can. The document was generated through a rails controller. Here’s my gem versions. rails: 4.2.6 axlsx_rails: 0.5.0 axlsx: 2.1.0.pre ruby_zip: 1.1.7

FlyingBlazer on Aug 3, 2016