CODEWITHSUNDEEP
rubyrubygemsspreadsheet
user2330585
user2330585

Reputation: 11

Editing a spreadsheet using SPREADSHEET ruby gem

I have to read data from a spread sheet modify some rows and then write the updated rows / cells into the same file.

I have used Spreadsheet gem with Ruby 2.0.0.

When I write the results back to the same file, I am unable to open the xls any more. I get an error

"File Format is not Valid"

in MS Excel.

When the updates are written onto a different file, I am able to open the file but it is in protected view. Is there a solution to this issue?

Below is the sample code:

require 'rubygems'
require 'spreadsheet'

 book = Spreadsheet::open('filePath')
 sheet = book.worksheet 0

 ## have application logic in here

 book.write('filePath')

Upvotes: 1

Views: 2308

Answers (2)

jargalan
jargalan

Reputation: 5194

check your file extension. spreadsheet, writeexcel..etc gems seem couldn't work with xlsx files. try .xls not .xlsx

Upvotes: 0

airazor
airazor

Reputation: 51

I've worked with this problem a few times and they've had the issue on log for around a year now.

The first problem is that it locks the file when spreadsheet loads it and there is no clear way to close it the only way I've been able to get it to not lock is with this code block. It opens it and stores the first worksheet off into its own variable then closes the file.

worksheet = nil
    Spreadsheet.open workbook_name do |inner_book|
      worksheet = inner_book.worksheet 0
    end
worksheet

If you want all the worksheets you could do something similar. In addition to the file opening closing/problem you have the issue around capturing the content of the worksheet depending on the format. I know for my purposes I end up doing the following to capture the content. This sadly loses any formatting you might have had in the source spreadsheet.

rows = []
worksheet.each do |row|
  rows << row
end

You can then make your own workbook/sheet and iterate through the rows and add them to the new sheet/book. Then save the new book with the same file name.

Its not fun or efficient, but it is a way to go about solving the problem. Hope this helped.

Upvotes: 3

Related Questions