How to edit tab in .xls with Spreadsheet gem?

Question

I am trying to open an existing .xls file and overwrite the contents in one spreadsheet (tab).There are many tabs on the file and many have pivottables and other visual presentations.

I have tried Spreadsheet and axlsx. Axlsx has great controls but overwrites the entire file including any other created tabs. Spreadsheet will open and edit a file but you have to copy the other tabs which will remove the excel formatting.

Is there a way to use Ruby to add data to only one tab in a spreadsheet without changing content in the other tabs?

Update:

Here is what I am testing now using the Spreadsheet gem. I can open a spreadsheet that has multiple tabs where one tab contains a pivottable, another contains a chart, and another the raw data. They have to be saved out as a new doc otherwise you get a File Format is not Valid error.

   open_book = Spreadsheet.open('../data/exports/test_output_dashboard.xls') 
    puts "#{open_book.worksheet(0)}"
    puts "#{open_book.worksheet(1)}"
    puts "#{open_book.worksheet(2)}"
    open_book.write('../data/exports/test_output_dashboard_2.xls')

If I just open and resave the new document is fine, a working copy of the original. However, if I edit the tab with the raw data as in this code then when I open the file it is shown as needs to be 'repaired' and none of the tabs show the correct information.

open_book = Spreadsheet.open('../data/exports/test_output_dashboard.xls') 
puts "#{open_book.worksheet(0)}"
puts "#{open_book.worksheet(1)}"
puts "#{open_book.worksheet(2)}"
new_row_index = open_book.worksheet(1).last_row_index + 1
open_book.worksheet(1).insert_row(new_row_index, row_2)
open_book.write('../data/exports/test_output_dashboard_4.xls')

Any suggestions for adding data to one tab of an excel doc while keeping the other tabs intact would be greatly appreciated. The solution can be any gem or could be any language or automatable tool.

UPDATE:

Here is an example Excel dashboard that I am using for testing. I am writing rows into the data tab. https://dl.dropboxusercontent.com/u/23226147/test_output_dashboard.xlsx

UPDATE:

With RubyXL I can open and inspect the content of each tab but the saved doc cannot be opened by Excel.

workbook = RubyXL::Parser.parse("../data/exports/test_output_dashboard.xlsx") puts "#{workbook.worksheets[0].inspect}" puts "#{workbook.worksheets[1].inspect}" puts "#{workbook.worksheets[2].inspect}" workbook.write("../data/exports/test_output_dashboard_5.xlsx")

score 2 · Answer 1 · answered Jan 29 '14 at 20:16

2

If you're just looking for a quick tool, RubyXL might do the trick for you:

https://github.com/weshatheleopard/rubyXL

It parses existing .xlsx .xlsm files and has a decent set of documentation.

answered Jan 29 '14 at 20:16

Sady

412
4
10

yes I tried RubyXL also. I added my code to my original question. When I tried it, the saved file was not able to be opened by Excel. I tried with the example excel file I added to my question. Is there something I should do differently with how I am using RubyXL? – analyticsPierce Jan 31 '14 at 07:45
It's possible that this is the issue that is causing it to not save: http://stackoverflow.com/questions/14174451/rubyxl-errnoenoent I didn't dig into your file; but perhaps your sheets underlying numbering system doesn't match the inherent single iteration that the gem assumes. I'll look around for another option. – Sady Jan 31 '14 at 22:31

doc75 · Answer 2 · 2019-05-12T11:13:20.457

0

Solution for Windows users only. I use to modify Excel spreadhseet with the gem win32ole and it works fine.

In case it is interesting for you here is a short sample to open a file and activate a given tab:

excel = WIN32OLE.new('Excel.Application')
excel.visible = true
filepath = 'e:\tmp\file.xlsx'
cur_book = excel.workbooks.Open(filepath)
sheet_name = 'sheet1'
cur_sheet = cur_book.Worksheets(sheet_name)
# put value 10 in Cell(2,2)
cur_sheet.Cells(2,2).Value = 10

Official documentation: http://ruby-doc.org/stdlib-1.9.3/libdoc/win32ole/rdoc/WIN32OLE.html

edited May 12 '19 at 11:13

answered Jan 29 '14 at 12:39

doc75

251
2
4

is that gem for windows only? I am on a mac. what system do you use it on? – analyticsPierce Jan 29 '14 at 16:36
Sorry, my solutions might be windows only. – doc75 Feb 01 '14 at 12:50

score 0 · Answer 3 · answered Feb 07 '14 at 08:47

0

You could try cloudxls.com API. You can merge data into existing xls and xlsx files using its API. If that is not an option, you most likely have to use some java libraries like Apache POI.

answered Feb 07 '14 at 08:47

seb

3,274
4
22
16

How to edit tab in .xls with Spreadsheet gem?

3 Answers3