"OLE2 signature is invalid" when trying to parse XLS file

Question

I'm trying to upload and parse an .XLS file. I've tried with roo-xls and Spreadsheet, but with both, I'm getting this error:

Ole::Storage::FormatError in UploadController#upload
OLE2 signature is invalid

I found a few resources about this error, and the only answer ever given is to re-save the document as .XLS, because although the original is labeled as .XLS, it actually isn't.

Unfortunately this isn't really an option, because I have users uploading files, so it's important that it work without requiring a re-save.

For the record, I tried re-saving the file and now it works, but I'm at a loss as to why it works, because the file's format before and after the re-save looks to be the exact same. This is what was listed under "type of file" in Excel, before and after:

Microsoft Excel 97-2003 Worksheet (.xls)"

And this is what is listed under "Type" in Libreoffice, before and after:

Microsoft Excel Worksheet (application/vnd.ms-excel)

What's going on?

Also, here's my simple upload code:

form

<%= form_tag(upload_path, multipart: true) do %>
  <%= file_field_tag :file %>
<% end %>

controller

file = params[:file].path

#Roo Attempt
doc = Roo::Excel.new(file)

#Spreadsheet Attempt
require 'spreadsheet'
Spreadsheet.client_encoding = 'UTF-8'
doc = Spreadsheet.open(file).worksheets

Answer 1

The issue is that roo reads only:

• Excel 2007 - 2013 formats (xlsx, xlsm)
• LibreOffice / OpenOffice.org formats (ods)
• CSV

For xls only you need to use roo-xls gem.

If you need any details further, I would need a copy of the excel file.

you should have:

require 'roo'
require 'roo-xls'

Then it will work.

Answer 2

Here's a suggestion. Might not be pretty, but should work, assuming the differences are the same for any file with the problem.

1. Manually perform a binary compare of the file before and after it's 're-saved'. Note the differences.
2. Read the uploaded file from the user. See if it's missing the noted difference from earlier, and modify the file accordingly.
3. Send the 'modified' file to roo-xls