Railscasts Episode #362 - Exporting to Excel: How to avoid the warning message given by Excel 2010 when opening the file?

Question

When using the example app from Ryan Bates' Railscasts Episode #362 about exporting to Excel (https://github.com/railscasts/362-exporting-csv-and-excel), I've noticed that Excel 2010 (which is on Windows) gives me a warning message when opening up the .xls file I've downloaded using the "Download as Excel" link.

The warning reads:

"The file you are trying to open ... is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

I can open the file just fine when I click 'Yes.' And I don't even get the warning message when using Excel 2011 (on the Mac). But I'd like to be able to provide an Excel file that won't prompt that warning when a user downloads such a file from my site.

Note: I've also tried replacing all the references in the app from .xls to .xlsx, but then Excel can't open the file at all. It complains: "Excel cannot open this file. The file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

I am aware of gems such as AXLSX (https://github.com/randym/axlsx), but was hoping I could just use the Railscasts method and make a quick fix to eliminate the warning message from Excel 2010.

Thank you so much for your help!

Answer 1

The xls file that is generated by the Railscasts application is actually an XML file in the old Excel 2003 XML format.

Recent versions of Excel have a feature called Extension Hardening that generate this warning when the file format doesn't match the file extension:

The alert is a new security feature in Excel 2007 called Extension Hardening, which ensures that the file content being opened matches the extension type specified in the shell command that is attempting to open the file. Because the MIME types listed above are associated with the .XLS extension, the file must be in XLS (BIFF8) file format to open without this warning prompt.

In order to avoid this warning you will have to generate an Excel file in a format that matches the file extension. Editing the registry as suggested as a workaround in the above link probably isn't workable in practice. Changing the extension to xml might also work.

As alternatives writeexcel for xls, write_xlsx for xlsx and AXLSX (that you mention above) are good options.

Answer 2

In fact, the code generated:

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
    <% @products.each do |product| %>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

is XML (XLS is a binary format, XLST a zipped format)

Renaming the file to .xml should work