I want to use Spreadsheet to collate a bunch of sheets, so that I copy rows from x number of sheets and dump them into newly created sheets based on certain criteria.
Opening, reading, extracting the rows, and the initial creation of the ‘dump’ sheets is not a problem (all nice and easy), but when Spreadsheet opens a sheet created with Spreadsheet and inserts a row, then writes the file, I am no longer able to open the spreadsheet with either excel or open office – when I try I get an invalid file format message.
I don’t get any errors when running my cukes (apart from trying to open the files manually later), but when I run the program from the cmd line, I get the following runtime error:
/.rvm/gems/ruby-1.9.2-p290@account_manager/gems/spreadsheet-0.6.5.7/lib/spreadsheet/excel/workbook.rb:60:in `block in biff_version’: Unkown BIFF_VERSION ” (RuntimeError)
If I start an IRB session and try to open the sheet with Spreadsheet, I get the same error.
I presume I am missing something when it comes to writing the file a second time?
Comment by Paul Hollyer — September 4, 2011 @ 7:59 pm
Did you search the mailing list http://groups.google.com/group/rubyspreadsheet? This is a known “feature”. Modifying an XLS with several worksheets will result in not being able to open the modified file if you only modify one worksheet but do not rewrite the untouched worksheets (the ones that you did not modify). All worksheets have to be rewritten (or deleted if empty) if you modify a file with the spreadsheet gem.
Just in case anyone else comes across this, I found my problem – I was writing to the same file because I need to maintain the file names and locations, then I read in the help files that it wasn’t a good idea to do that!
The fix was to delete the file prior to writing.
I have added a Gist here  for anyone else to have a look at for similar issues.
I’m trying to use spreadsheet to read from an xls doc and write the contents to an html file. The writing to html file part, I’ve got, but I’m having trouble getting the exact row and columns I need stored into a variable so I can write with it. Any help getting rows and columns would be appreciated.
book = Spreadsheet.open ‘\some\path\to\file.xls’
current = book.worksheet 0
Or if you want
current = book.worksheet ‘SheetName’
rowC = current.row(2)
Now you have an array to work with. Column 1 is then rowC, column 2 is rowC, etc
Convert as necessary.