Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Spreadsheet Gem - updating an existing sheet without changing the output location or filename
#!/usr/bin/env ruby
require 'spreadsheet'
# Begin Test
print "Spreadsheet Test\n"
# Create the rows to be inserted
row_1 = ['A1', 'B1']
row_2 = ['A2', 'B2']
# Create a new Workbook
new_book = Spreadsheet::Workbook.new
# Create the worksheet
new_book.create_worksheet :name => 'Sheet Name'
# Add row_1
new_book.worksheet(0).insert_row(0, row_1)
# Write the file
new_book.write('test.xls')
# Wait for the user to inspect the file manually
print "Sheet Created, press Enter to continue...."
gets
# Open the previously created Workbook
open_book = Spreadsheet.open('test.xls')
# Get the row index in order to append a new row
# after any exisitng rows with data
new_row_index = open_book.worksheet(0).last_row_index + 1
# Indicate the row index to the user
print "Inserting new row at index: #{new_row_index}\n"
# Insert row_2
open_book.worksheet(0).insert_row(new_row_index, row_2)
# Delete the file so that it can be re-written
File.delete('test.xls')
# Write out the Workbook again
open_book.write('test.xls')
# End Test
print "Test Complete.\n"
@zdavatz

This comment has been minimized.

Copy link

@zdavatz zdavatz commented Sep 13, 2011

@analyticsPierce

This comment has been minimized.

Copy link

@analyticsPierce analyticsPierce commented Jan 24, 2014

Can this approach be used to edit only the worksheet and not delete the file? I am trying to update a data tab in an excel doc where other people will be editing content on other tabs. Is it possible to open the excel doc and save only the changes to worksheet(0)?

@FUT

This comment has been minimized.

Copy link

@FUT FUT commented Feb 11, 2014

Thanks!

@evmorov

This comment has been minimized.

Copy link

@evmorov evmorov commented Apr 25, 2014

For Windows users

book = Spreadsheet.open("test.xls")
# edit file
book.write("temp_test.xls")
book = open_book("temp_test.xls")
FileUtils.rm("test.xls", :force => true)
book.write("test.xls")
@cheenwe

This comment has been minimized.

Copy link

@cheenwe cheenwe commented Jun 26, 2014

不懂

@gorn

This comment has been minimized.

Copy link

@gorn gorn commented Nov 23, 2014

I am trying to set up something very similar to spreadshhet for LibreOffice. Currently it can do most of the basic things, read, modify and write spreadsheets back. I would appreciate feedback from anyone interested, just have a look at gitbub page of the rspreadsheet project and file an issue.

@rajiv-shrivastava

This comment has been minimized.

Copy link

@rajiv-shrivastava rajiv-shrivastava commented May 12, 2016

The File.delete('test.xls') line in the above code is to be written even when we are updating same file.For eg. We have 30 rows already and now will add new 10 rows.
In that case we read the file

book = Spreadsheet.open('test.xls')
@xml_records = [ ] #a blank array
sheet1 = book.worksheet(0)
sheet1.each_with_index do |row,index|
if index != 0
@xml_records << row[0]
end
end

In this way. The @xml_records is array contains all rows. Then will check the new row is present in it or not. If not enter it in file

How we check that row present in xls and if not enter the row. It can be done like this

Like we have a new row row2 = ['data1','data2','data3']

    if !@xml_records.include? @row2[0]     #or row2[1] or row[2] whichever you think is unique column
                    new_row_index = book.worksheet(0).last_row_index + 1
         book.worksheet(0).insert_row(new_row_index, @row_2) 
            end
            book.write('test.xls')

The File.delete() deletes the file but store the previous data of file in a buffer.
Use the above code and these stuff.This will update your excel file.

@sachin-metacube

This comment has been minimized.

Copy link

@sachin-metacube sachin-metacube commented Aug 24, 2016

does this gem supports xlsx format?

@sumantacaptain

This comment has been minimized.

Copy link

@sumantacaptain sumantacaptain commented Sep 14, 2016

is there any way to 1. wrap/justify text? 2. fix width and height of row/cell? any example will be awesome!

@niyando

This comment has been minimized.

Copy link

@niyando niyando commented Apr 26, 2017

It works! Could you please explain this? How is deleting the file helping solve the issue?

@paragmangal

This comment has been minimized.

Copy link

@paragmangal paragmangal commented May 16, 2017

Hi !!
How to access a drop down list in .xls file using this ruby spreadsheet gem?

@veekram

This comment has been minimized.

Copy link

@veekram veekram commented May 3, 2019

@ALL Have not gone through full api documentation. But, could not figure out how to write .XLS headers. Do anybody know how to? Thanks in advance.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.