Error opening a spreadsheet created by this gem

[henriquesobral]

I'm using this gem to create and update spreadsheets, but when I try to open a spreadsheet created by this gem, I'm getting the following error:

/Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet/excel/workbook.rb:60:in `block in biff_version': Unkown BIFF_VERSION '' (RuntimeError)
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet/excel/workbook.rb:60:in `fetch'
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet/excel/workbook.rb:60:in `biff_version'
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet/excel/reader.rb:1293:in `setup'
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet/excel/reader.rb:140:in `read'
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet/excel/workbook.rb:32:in `open'
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet.rb:65:in `open'
    from /Users/******/.gem/ruby/2.2.0/gems/spreadsheet-1.0.3/lib/spreadsheet.rb:71:in `open'
    from conv.rb:75:in `block in <main>'
    from /Users/******/.gem/ruby/2.2.0/gems/dbf-2.0.10/lib/dbf/table.rb:109:in `block in each'
    from /Users/******/.gem/ruby/2.2.0/gems/dbf-2.0.10/lib/dbf/table.rb:109:in `times'
    from /Users/******/.gem/ruby/2.2.0/gems/dbf-2.0.10/lib/dbf/table.rb:109:in `each'
    from conv.rb:69:in `<main>'

How do I proceed?

Thanks.

[zdavatz]

Please upload the script to gist.github.com and link it here. Also upload your sample file somewhere and link it here.

[jlhonora]

I'm facing the same issue. I can replicate it with this test:

require 'rails_helper'

RSpec.describe "ExportExcel" do
  it "Reopens spreadsheet" do
    filename = 'test.xls'
    sheet_name = 'Test Sheet'

    # Create excel
    excel = Spreadsheet::Workbook.new(filename)
    sheet = excel.create_worksheet(name: sheet_name)
    sheet.row(1).replace ['Data']
    excel.write(filename)

    # Append something
    excel = Spreadsheet.open(filename, 'a+')
    sheet = excel.worksheet(sheet_name)
    sheet.row(2).replace ['Data2']
    excel.write(filename)

    # Reopen
    excel = Spreadsheet.open filename # Crashes here
    sheet = excel.worksheet sheet_name
  end
end

I see that you're using minitest on your test suite. Let me try to translate it and I'll post it here. Edit: porting to minitest should be trivial, let me know if you need more info.

[zdavatz]

I need a simpel test script with a test file so I can replicate your issue. The sample should not contain any dependencies on Rails. Link it all here please.

[jlhonora]

Hi,

Add this method to test/integration.rb:

def test_append_and_reopen
      puts "Testing append and reopen"
      filename = 'test.xls'
      sheet_name = 'Test Sheet'

      # Create excel
      excel = Spreadsheet::Workbook.new(filename)
      sheet = excel.create_worksheet(name: sheet_name)
      sheet.row(1).replace ['Data']
      excel.write(filename)

      # Append something
      excel = Spreadsheet.open(filename, 'a+')
      sheet = excel.worksheet(sheet_name)
      sheet.row(2).replace ['Data2']
      excel.write(filename)

      # Reopen
      excel = Spreadsheet.open filename # Crashes here
      sheet = excel.worksheet sheet_name
    end

or in patch format:

diff --git a/test/integration.rb b/test/integration.rb
index 906f604..481dd69 100644
--- a/test/integration.rb
+++ b/test/integration.rb
@@ -1402,6 +1402,27 @@ module Spreadsheet
         book.worksheet(0).row(0)
       end
     end
+    def test_append_and_reopen
+      puts "Testing append and reopen"
+      filename = 'test.xls'
+      sheet_name = 'Test Sheet'
+
+      # Create excel
+      excel = Spreadsheet::Workbook.new(filename)
+      sheet = excel.create_worksheet(name: sheet_name)
+      sheet.row(1).replace ['Data']
+      excel.write(filename)
+
+      # Append something
+      excel = Spreadsheet.open(filename, 'ab+')
+      sheet = excel.worksheet(sheet_name)
+      sheet.row(2).replace ['Data2']
+      excel.write(filename)
+
+      # Reopen
+      excel = Spreadsheet.open filename # Crashes here
+      sheet = excel.worksheet sheet_name
+    end
     private

     # Validates the workbook's SST

It doesn't need any dependencies or example .xls file. Thanks in advance.

[zdavatz]

Thank you! Please send me a pull request.

[jlhonora]

Just to be sure we're on the same page, should I make a PR of the test itself? That's the best I can do for now; I've been trying to find out where the problem is, but I haven't been able to fix it.

[zdavatz]

Ok, then lets wait some more time, until you have the solution and then send me the pull request.