Excel 2016 unable to open generated document

[damienh]

I have been experiencing issues with opening generated documents with Excel 2016. Excel presents the following message: 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:

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?

[straydogstudio]

That's a tough one. It looks like it is Axlsx, but we better be sure. Can you put it entirely in a script (outside of Rake and just using Axlsx) and just use dummy data? Does it work any better without the styles?

[straydogstudio]

Also, I am curious if you get any different results using render_to_string.

[damienh]

@straydogstudio sorry for late reply on this. When I run it outside of bundle e.g via console the file appears fine.

Removing the styles has no effect.

tried looking at render_to_string but experiencing NoMethodError: undefined methodrender_to_string' for #<ActionView::Base:` errors so havent got further with that.

Its strange that bundler is appearing to be the issue.

[damienh]

Ok so I have now managed to get a report to generate without any unable to open errors.

Prior I was generating the document in the rake task via

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

I have now moved this into the _reports/codeclub_numbers.xlsx.axlsx itself and added to the bottom:

s = p.to_stream()
File.open("./tmp/codeclub-numbers-report-#{Date.today}.xlsx", 'w') { |f| f.write(s.read) }

I also instead of wb = xlsx_package.workbook I use:

p = Axlsx::Package.new
wb = p.workbook

[straydogstudio]

@damienh Thanks for getting back to me. That's an interesting result. It makes me wonder if it's a character set problem. Glad you got it working.

FYI, I just published version 0.5.0 with tested support for Rails 5. Version 0.4 works also, but it throws deprecations.

[straydogstudio]

I'll leave this open. Some time I will get to this and try to replicate the problem.

[straydogstudio]

Ok, given your fix, I have a guess. The render call is supposed to be using the xlsx template engine, which wraps the template with xlsx_package = Axlsx::Package.new and ends it with xlsx_package.to_stream.string. It looks like there is a problem with that code somehow. And you are replacing it and manually exporting the contents to a file. Which is fine.

[straydogstudio]

Sometime let me know what Rails, Axlsx, axlsx_rails, and rubyzip versions you're using.

[damienh]

rails (~> 3.2.22.2)
axlsx (2.0.1)
axlsx_rails (0.4.0)
rubyzip (1.0.0)

[tp-clickapps]

@straydogstudio I'm getting a similar issue that my exported xlsx file is not opening in MS Office and Google Drive.

[straydogstudio]

@tp-clickapps what Rails, Axlsx, axlsx_rails, and rubyzip versions are you using? Is it in the context of a mailer, or rake task, or...? Can you post a gist of your relevant code?

[FlyingBlazer]

@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

[jayshepherd]

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|

[straydogstudio]

@jayshepherd Was that with 0.5.0?

[straydogstudio]

If anyone in this thread is using a special encoding or language let me know.

[jayshepherd]

@straydogstudio Yes, 0.5.0

rails: 4.2.6
axlsx_rails: 0.5.0
axlsx: 2.1.0.pre
rubyzip: 1.1.7

[asampatoor]

hi @straydogstudio

I am using rubyzip (1.2.0, 1.1.7, 1.0.0) axlsx (2.0.1) axlsx_rails (0.5.0) axlsx_styler (0.1.7)

With this, I am generating an Excel spreadsheet and I am still encountering this issue. Anyone here has any clue regarding how to fix this issue.

[rylanb]

I had an issue with this and fixed it by making sure I wasn't writing past the rows/columns I had generated, which I was doing, I believe.

So I had 75 records, but was only writing 26 columns due to a faulty numeric limit I had set, but then I started writing past that in my iterator over the 75 records, this caused me to write into non-existent columns.

[straydogstudio]

@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:

1. Turn shared strings on (https://github.com/straydogstudio/axlsx_rails#axlsx-package-options)
2. Make sure you pass layout false when you render. Sometimes Rails decides to render a layout when it shouldn't.
3. 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.

[asampatoor]

@straydogstudio I have turned the shared_strings on in my .axlsx template and also I have passed the layout: false option while rendering. Also, I have the name for the worksheet but still I am getting the same error.

[straydogstudio]

@asampatoor Can you post your controller code, and template, in a gist?

[asampatoor]

@ all,

For this issue in my case, The reason I am receiving this error is because of the DateTime format in my data. The DateTime is in is06081 format which excel didn't like it.

The Fix I have done: I have added a style format in the styling section date_style = styles.add_style format_code: 'mm-dd-yyyy hh:mm:ss'

And format the iso6081 date as follows DateTime.strptime(#{your_date}, '%Y-%m-%dT%H:%M:%S%z').in_time_zone

[msdundar]

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 ;)

[straydogstudio]

@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.

[vsaroha]

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}

[pinzer]

I get the same error with:

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

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

[aindong]

I am currently trying to create an excel report on my rails app, generated using "--api" and is also experiencing this error right now on the generated excel.

Here's the gem versions gem 'rubyzip', '= 1.0.0' gem 'axlsx', '= 2.0.1' gem 'axlsx_rails'

Here's my controller

module V1
  class ExcelController < ApplicationController
    skip_before_action :authorize_request

    def index
      @cams = Cam.all
      filename = 'test'

      render xlsx: filename, template: 'excel/index', layout: false
    end
  end
end

here's my views/excel/index.xlsx.axlsx

wb = xlsx_package.workbook

wb.add_worksheet(name: "Sheet 1") do |sheet|
  sheet.add_row ['VILLANUEVA GABIONZA & DY LAW OFFICE']
  sheet.add_row ['Schedule of CAM Earned / Received (Partners, Special Attorneys\' & Associates']
  sheet.add_row ['Peso & Dollar Commission']
  sheet.add_row ['As of ']
  sheet.add_row ['', '', '', '', '', '', '', '', '', '', '', '', '', '', 'COMMISSION EARNED']

  sheet.add_row %w(Partners JANUARY FEBRUARY MARCH APRIL MAY JUNE JULY AUGUST SEPTEMBER OCTOBER NOVEMBER DECEMBER 2017 2016)
end

Thank you so much for the help and this wonderful gem

[aindong]

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

[jhugon61]

Just wanted to chime in that I ran into the same issue of "Excel file needs to be repaired", and the solution proposed by @msdundar above of using f.write instead of f.puts when writing out the rendered view did solve the issue for me.

I am using delayed_job to generate these files in the background, and initially followed the "user rails runner" code found here: https://gist.github.com/straydogstudio/323139591f2cc5d48fbc Very helpful to learn how to render the view from a model - just needed to change the f.puts to f.write.

rails (4.2.10) rubyzip (1.2.1) axlsx_rails (0.5.2)

[de-farias]

I ran into this issue today, and in the end the cause of my problem was an accented character in the filename. "Relatório", which means "report" in portuguese, caused the file to be unreadable (because of the "ó" thing).