The Ruby Spreadsheet

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.