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.
我在Ruby 2.0.0中使用了Spreadsheet gem。
When I write the results back to the same file, I am unable to open the xls any more. I get an error
当我将结果写回同一个文件时,我无法再打开xls。我收到一个错误
"File Format is not Valid"
“文件格式无效”
in MS Excel.
在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')
2 个解决方案
#1
3
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.
它不是有趣或高效,但它是一种解决问题的方法。希望这有帮助。
#2
0
check your file extension. spreadsheet, writeexcel..etc gems seem couldn't work with xlsx files. try .xls not .xlsx
检查文件扩展名。电子表格,writeexcel..etc gems似乎无法使用xlsx文件。试试.xls而不是.xlsx
#1
3
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.
它不是有趣或高效,但它是一种解决问题的方法。希望这有帮助。
#2
0
check your file extension. spreadsheet, writeexcel..etc gems seem couldn't work with xlsx files. try .xls not .xlsx
检查文件扩展名。电子表格,writeexcel..etc gems似乎无法使用xlsx文件。试试.xls而不是.xlsx