simple-spreadsheet not properly loading an xls file on ruby, but working properly on irb

Question

I'm currently trying to open and parse this xls file using ruby 2.1.1. The straightforward way would be to use the simple-spreadsheet gem, which doesn't seem to work properly for this particular spreadsheet (and a couple others):

require 'simple-spreadsheet'
s=SimpleSpreadsheet::Workbook.read('151.xls')
puts s.last_row #prints 5

However, when I copy and paste this exact code into irb, I get the right answer

2.1.1 :001 > require 'simple-spreadsheet'
 => true 
2.1.1 :002 > s=SimpleSpreadsheet::Workbook.read('151.xls')
 => #prints the entire contents of the spreadsheet
2.1.1 :003 > s.last_row
 => 154

The same behaviour occurs when using Roo (which is to be expected, as simple-spreadsheet uses Roo to open .xls files):

require 'roo'
s=Roo::Excel.new('151.xls')
puts s.last_row #prints 5, should print 154

While on irb

2.1.1 :001 > require 'simple-spreadsheet'
 => true 
2.1.1 :002 > s=Roo::Excel.new('151.xls')
 => #prints the entire contents of the spreadsheet
2.1.1 :003 > s.last_row
 => 154

Digging further, I tried using spreadsheet, as it is required on Roo's excel.rb file:

require 'spreadsheet'    
Spreadsheet.open('151.xls') do |book|
  rows=0
  book.worksheet(0).each do |row|
    rows+=1
  end
  puts rows#prints 5
end

However, this is where it gets weird(er); when I copy and paste this last code into irb, I get

2.1.1 :001 > require 'spreadsheet'    
 => true 
2.1.1 :002 > Spreadsheet.open('152.xls') do |book|
2.1.1 :003 >       rows=0
2.1.1 :004?>     book.worksheet(0).each do |row|
2.1.1 :005 >           rows+=1
2.1.1 :006?>       end
2.1.1 :007?>     puts rows
2.1.1 :008?>   end
5
 => nil 

I should probably add that the gems aren't just printing 5; every cell below this row returns nil; they actually stop parsing the file after this particular row.

So here are my questions: fist, why is irb behaving differently than ruby? Secondly, why isn't any of those gems loading the entire spreadsheet? Third, what can I do to fix this?

Thanks for your help

Answer 1

This is one of the strangest issues I've ever seen. To answer your questions:

First, why is irb behaving differently than ruby?

IRB is a tool that is written in ruby, and is different than executing ruby directly. One of the main differences is that IRB has different options that can be set on startup. You can see the different flags in the source, and this page has some good descriptions of what those flags mean.

The culprit for the inconsistencies you're seeing is the --noinspect option, run irb --noinspect and executing your code should give you the same strange behavior as running the ruby program (where you only see 5 rows). This is because irb defaults to calling inspect on every line that's executed, so when you call s=SimpleSpreadsheet::Workbook.read('151.xls') it's actually like calling this in a ruby program:

s = SimpleSpreadsheet::Workbook.read('151.xls')
s.inspect

Why does calling s.inspect matter? Keep reading...

Secondly, why isn't any of those gems loading the entire spreadsheet?

This is the hardest question to answer, I still haven't pinpointed it 100%, but hopefully the 90% explanation is good enough. In short, all of the gems you mention rely on the spreadsheet gem. One of the quirks/bugs of that gem in this particular setup (I don't think it's designed to always work like this) seems to be its reliance on the inspect method. If you call inspect on the worksheet it is persisting more values than if you don't call it. That is to say, if you don't call inspect on it's only reading part of the file (or reading some of it and disregarding the rest, I'm not sure) but if you do call inspect it reads the file entirely.

Third, what can I do to fix this?

As I alluded to before, manually calling inspect should read the entire file:

s = SimpleSpreadsheet::Workbook.read('151.xls')
s.inspect