Open Source Ruby Library to Process Excel Spreadsheet  

Getting Started with Spreadsheet

The recommended way to install Spreadsheet into your project is by using RubyGems. Please use the following command for smooth installation.

udo gem install spreadsheet 

Generate New Excel Spreadsheet using Ruby

The open source library Ruby Spreadsheet has provided complete support for generating Microsoft Excel compatible spreadsheets using Ruby code. You can easily create a new workbook and add sheets to it with just a couple of lines of Ruby code. Once created you can insert contents to it and apply formatting to it. You can also insert new rows or columns, insert text or images, and so on.

book = Spreadsheet::Workbook.new

sheet = book.create_worksheet(name: 'First sheet') # We are creating new sheet in the Spreadsheet(We can create multiple sheets in one Spreadsheet book)

# Let's create first row as the following.
sheet.row(0).push('Test Name', 'Test country', 'Test city', 'Test profession') # Number of arguments will be number of columns

# We can create many rows same as the mentioned above.
sheet.row(1).push('Bobby', 'US', 'New York', 'Doctor')
sheet.row(2).push('John', 'England', 'Manchester', 'Engineer')
sheet.row(3).push('Rahul', 'India', 'Mumbai', 'Teacher')

# Write this sheet's contain to the test.xls file.
book.write 'test.xls'
 

Read & Edit Existing Spreadsheet via Ruby

The Ruby Spreadsheet library enables software programmers to access and open an existing spreadsheet inside their own application. The library provides only write support for BIFF8 (Excel97 and higher versions). You can also modify your existing spreadsheet documents with just a couple of lines of code. The library has provided limited support.  You can easily add, modify, or delete Excel cells as well as fill in Data to be evaluated by predefined Formulas.

require 'spreadsheet'    
book = Spreadsheet.open('myexcel.xls')
sheet1 = book.worksheet('Sheet1') # can use an index or worksheet name
sheet1.each do |row|
  break if row[0].nil? # if first cell empty
  puts row.join(',') # looks like it calls "to_s" on each cell's Value
end
 

Group or Hide Rows and Columns

The open source Ruby Spreadsheet library allows computer programmers to group or ungroup Excel spreadsheet cells using Ruby commands. The library also provided supports for creating a new spreadsheet file with an outline. You can also hide or unhide rows or columns of your choice with ease. While reading a spreadsheet file you can easily change the hidden and outline properties. Please remember that the outline_level must be below 8, which is due to the Excel data format.

    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”