5

The code below works as it should the FIRST time I run it:

require 'rubygems'
require 'spreadsheet'
book = Spreadsheet.open '/Users/me/myruby/Mywks.xls'
sheet = book.worksheet 0
row = sheet.row(1)
puts row[1]
book.write '/Users/me/myruby/Mywks.xls'

When I run it again I get more messages like:

/Library/Ruby/Gems/1.8/gems/spreadsheet-0.6.5.9/lib/spreadsheet/excel/reader.rb:1149:in `setup': undefined method `read' for false:FalseClass (NoMethodError)
    from /Library/Ruby/Gems/1.8/gems/spreadsheet-0.6.5.9/lib/spreadsheet/excel/reader.rb:121:in `read'

This suggest to me there is a problem with either: 1. The closing of the excel spreadsheet or 2. Writing back to the same spreadsheet I opened.

  1. There is nothing in the ruby gem spreadsheet documentation about closing spreadsheets. Examples end in the "book.write" statement as above, if anything. My search here and elsewhere turned up nothing re closing the xls file in spreadsheet.
  2. The spreadsheet documentation suggest you CAN write back to the same file but suggests maybe you shouldn't. Is that the problem here? If so do I writ to a temporary wks and then rename it?
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
Roy
  • 57
  • 1
  • 6
  • Which line triggers the exception? – Andrew Grimm Sep 13 '11 at 00:12
  • 1
    @AndrewGrimm Apparently it is line 3, beginning "book = spreadsheet.open". – Roy Sep 13 '11 at 12:13
  • I tried adding the code below but that didn't work either. Exact same results as above. afile = File.open("/Users/royclymer/myruby/Weekly Total.xls") afile.close I can't figure out how to format the code above in this comment. – Roy Sep 14 '11 at 00:36
  • Use backticks (eg ```foo```) within comments, but generally it's better to just edit your question. – Andrew Grimm Sep 14 '11 at 01:07

2 Answers2

9

Try using block syntax, it seems to work for me:

Spreadsheet.open '/Users/me/myruby/Mywks.xls' do |book|
  sheet = book.worksheet 0
  # book should be closed when the block exits
end
pguardiario
  • 53,827
  • 19
  • 119
  • 159
  • Why can't I vote more than once on this! For the purposes of getting people here with the same issue, let me throw some keywords around: Of those of you trying to use Carrierwave to upload files and Spreadsheet to read/write the uploaded excel file please note that for WIndows machines you MUST close the spreadsheet before trying to remove/copy the excel file as Windows locks the file and will give a "Errno::EACCES (Permission denied" error. The above fixes this! – S.Richmond Jan 20 '12 at 14:02
0

You can close file manualy: 1) add somewhere this code:

module Spreadsheet
  class Workbook
    attr_accessor :io
  end
end

2) simply call 

@book.io.close

to close file

Alexey Novikov
  • 97
  • 1