The Ruby Spreadsheet

October 20, 2011

Modifying an exiting Workbook with several Worksheets with the Ruby spreadsheet library

Filed under: Workbook, Worksheet — Tags: , , — zdavatz @ 6:22 am

This question has been asked many times on the mailing list:

I have an Excel file (workbook) with many worksheets in it. Now I modify one worksheet but leave the other worksheets untouched. When I write the file and then try to open it with OpenOffice or Microsoft Office the file hangs. What do I have to do?

1. You need to write the modified file to a new file name.

2. If the Workbook contains empty sheets, delete the empty sheets from the source file. There is no point in carrying empty sheets around.

3. If the Workbook contains 1 sheet that you want to modify but you want to leave the other worksheets untouched because they contain data, then read the following post from John Smith (very smart guy).

http://groups.google.com/group/rubyspreadsheet/browse_thread/thread/f5bc157b06b66604

4. If you find a better solution, let me know via the list – and do not forget to post your sample code to github (please do not post it in the mail).

March 23, 2011

Ruby Spreadsheet 0.6.5.3 has been released

Filed under: Uncategorized — Tags: , , — zdavatz @ 3:48 pm

Thanks to Alexandre Bini for this update. See: http://url.ba/ery3

PS: A lot of questions have already been answered here:

http://rubyforge.org/forum/forum.php?set=custom&forum_id=2920&style=nested&max_rows=50&submit=Change+View

December 7, 2010

Hiding and Grouping of columns and lines now possible with Ruby Spreadsheet

Filed under: New Features, Outline — Tags: , , , , , — zdavatz @ 4:10 pm

If you are using Ruby-Spreadsheet to edit existing Excel Files then we have a new feature for you: Hiding, Unhiding, and Outlining of columns and lines is now possible with Ruby-Spreadsheet 0.6.5.

You can create a new file with outline and hiding rows and columns as
follows:

require ‘spreadsheet’

# create a new book and sheet
book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
5.times {|j| 5.times {|i| sheet[j,i] = (i+1)*10**j}}

# column
sheet.column(2).hidden = true
sheet.column(3).hidden = true
sheet.column(2).outline_level = 1
sheet.column(3).outline_level = 1

# row
sheet.row(2).hidden = true
sheet.row(3).hidden = true
sheet.row(2).outline_level = 1
sheet.row(3).outline_level = 1

# save file
book.write ‘out.xls’

gem and gemspec for ruby spreadsheet 0.6.5 can be found here. Hit the “Blob” link to download the gem.

# This deletes the highest outline level

require ‘spreadsheet’

file = ARGV[0]
book = Spreadsheet.open(file, ‘rb’)
sheet= book.worksheet(0)

26.upto(30) do |i|
sheet.row(i).outline_level = 4
end

book.write “out.xls”

# This hides the rows in the highest level of outline

require ‘spreadsheet’

file = ARGV[0]
book = Spreadsheet.open(file, ‘rb’)
sheet= book.worksheet(0)

26.upto(30) do |i|
sheet.row(i).hidden = true
end

book.write “out.xls”

See the sample file. Sample Nested Outline for Ruby-Spreadsheet 0.6.5.

December 10, 2008

The Ruby Spreadsheet

Filed under: Ruby, Spreadsheet — Tags: , , — zdavatz @ 4:15 pm

http://spreadsheet.rubyforge.org

http://scm.ywesee.com/?p=spreadsheet;a=summary

The Spreadsheet Library is designed to read and write Spreadsheet
Documents. As of version 0.6.0, only Microsoft Excel compatible
spreadsheets are supported. Spreadsheet is a combination/complete
rewrite of the Spreadsheet::Excel Library by Daniel J. Berger and the
ParseExcel Library by Hannes Wyss. Spreadsheet can read, write and
modify Spreadsheet Documents.

Changes:

### 0.6.0 / 2008-10-13
Initial upload of the shiny new Spreadsheet Gem after three weeks of
grueling labor in the dark binary mines of Little-Endian Biff and long
hours spent polishing the surfaces of documentation:

* Significantly improved memory-efficiency when reading large Excel Files
* Limited Spreadsheet modification support
* Improved handling of String Encodings
* Runs on top of the ruby-ole Library

Roadmap:

0.7.0: Improved Format support/Styles
0.7.1: Document Modification: Formats/Styles
0.8.0: Formula Support
0.8.1: Document Modification: Formulas
0.9.0: Write-Support: BIFF5
1.0.0: Ruby 1.9 Support;
Remove backward compatibility code

Backward Compatibility:

Spreadsheet is designed to be a drop-in replacement for both
ParseExcel and Spreadsheet::Excel. It provides a number of
require-paths for backward compatibility with its predecessors. If you
have been working with ParseExcel, you have probably used one or more
of the following:

require ‘parseexcel’
require ‘parseexcel/parseexcel’
require ‘parseexcel/parser’

Either of the above will define the ParseExcel.parse method as a
facade to Spreadsheet.open. Additionally, this will alter Spreadsheets
behavior to define the ParseExcel::Worksheet::Cell class and fill each
parsed Row with instances thereof, which in turn provide ParseExcel’s
Cell#to_s(encoding) and Cell#date methods.
You will have to manually uninstall the parseexcel library.

If you are upgrading from Spreadsheet::Excel, you were probably using
Workbook#add_worksheet and Worksheet#write, write_row or write_column.
Use the following to load the code which provides them:

require ‘spreadsheet/excel’

Again, you will have to manually uninstall the spreadsheet-excel library.

If you perform fancy formatting, you may run into trouble as the
Format implementation has changed considerably. If that is the case,
please drop me a line at hannes.wyss@gmail.com and I will try to help
you. Don’t forget to include the offending code-snippet!

Blog at WordPress.com.