The Ruby Spreadsheet

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.

About these ads

6 Comments »

  1. Does this support Rails 3? Thanks.

    Comment by Raghu — December 10, 2010 @ 7:35 pm

  2. It was tested with Rails 2.3.4. on Windows and on Linux. We also removed the iconv dependency and replaced it with the ole dependency. See this commit: http://scm.ywesee.com/?p=spreadsheet/.git;a=commit;h=cc3eaaa581714b225b32813756b1e6bd049ea7e9

    If possible let me know but my guess is that it will run with Rails 3.

    Comment by zdavatz — December 11, 2010 @ 10:43 am

  3. How do I install the latest version 0.6.5, when I do gem install spreadsheet, I still get the 0.6.4.1 version which is not supported in Rails 3.

    Appreciate your help.

    Comment by Raghu — December 13, 2010 @ 3:50 am

  4. Download the latest gem from here: http://rubyforge.org/frs/?group_id=678

    Comment by zdavatz — December 13, 2010 @ 7:20 am

  5. What error do you get when you try to load spreadsheet 0.6.4.1 in Rails 3? Can you please post the error here?

    Comment by zdavatz — December 13, 2010 @ 7:21 am

  6. is it possible to detect which rows/columns are hidden when opening and excel file?

    Comment by Peretz Partensky — December 8, 2013 @ 10:33 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 304 other followers

%d bloggers like this: