Railscasts Episode #362 - Exporting to Excel: How to avoid the warning message given by Excel 2010 when opening the file?
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.
Warning message when opening a generated excel file in office 07
Try calling the file blah.xlsx
instead of blah.xls
. Excel apparently wants XML files to have an "xlsx" extension. The "xls" extension is for the binary format files.
Edit in response to your comment:
I was wrong: xlsx
files aren't just the XML files, they're zip archives containing the XML format plus other metadata. All in all, it's a little complex to set up. I'd try renaming the file to blah.xml
, and see if that works. Otherwise I'm afraid you might have to look at how to make these zip files. There are two options:
- Use Microsoft's OOXML SDK: see http://msdn.microsoft.com/en-us/library/bb448854.aspx.
- Do it yourself (much harder). You'll have to look at the OOXML standard. Part 1 contains an overview, and a description of each XML file. Part 2 describes the package format (i.e. the zip archive format). There are additional requirements on
xlsx
files above what part 2 says you need, so read part 2 first, then part 1.
Exporting silverlight datagrid to excel file without warning message
found the answer same day, forgot to post the answer as soon as I got it working.
Here's a handy library to help create and read excel files properly. Works on SL4 but you could just tweak the source code to make it usable in SL3
http://excellite.codeplex.com/
Roo deprecated roo method
The correct fix would be to not use the deprecated method and instead use the recommended one. However, in this case, it's not you who is using the deprecated method, it's the author of roo-xls
:
make_tmpdir do |tmpdir| # …
So, the only things you can do are:
- file a bug report against
roo-xls
, preferably with a pull request fixing the issue (actually, the former was already done for you) for the time being, suppress deprecation warnings until the upstream issue is resolved; Roo uses
Kernel#warn
, so you can unfortunately only turn off all warnings, but something like this should work:original_warning_level = $VERBOSE
$VERBOSE = nil
workbook = Roo::Excel.new 'test.xls'
$VERBOSE = original_warning_level
Export to CSV File
Controller:
respond_to do |format|
format.csv { send_data @report.to_csv, filename: @report.name + ".csv"} #to export csv
end
In the view
<%= link_to 'CSV', report_path(report, format: :csv)%>
How to suppress the file corrupt warning at Excel download?
This problem results from a feature called Extension Hardening, and you can find more information about it here
I've run into this problem a lot in my projects, and like Jon said, turning off Extension Hardening is something that will have to be done by each client side user.
Unfortunately, the link above also states that there are no expected changes to this code until at least Office 14.
Related Topics
Splitting a String into Words and Punctuation with Ruby
How to Serialize a Ruby Digest::Sha1 Instance Object
Rvm Isnt Setting Environment with Cron
Ruby: Automatically Wrapping Methods in Event Triggers
Convert Spreadsheet Column Index into Character Sequence
Rails: Chartkick Cummulative User Graph
404 Resource Not Found: Domain with Google Directory API
Mini_Magick Gem Doesn't Work with My Imagemagick Install
Problems with Jslint-V8 Ruby Gem Installation on Windows7 64-Bit
Integrate Shoes into Aptana Studio Radrails
Ruby: Why Are All Coordinates Getting Updated in an Array of Arrays
How to Reset Boolean to "Default: False" at End of Day
How to Create Two Routes in One Block in Grape
Mechanize and Ntlm Authentication
Use [].Replace to Make a Copy of an Array