Open Source Ruby Library to Process Excel Spreadsheet
Ruby API to work with Microsoft Excel compatible spreadsheets. It allows to create a new spreadsheet, modify existing documents, group or ungroup Excel spreadsheet cells & more.
The Ruby Spreadsheet Library helps software developers to work with Microsoft Excel compatible spreadsheets inside their own Ruby applications. The library is very stable and is available under the GPL-3.0 License for the general public. The library is very user-friendly as well as very secure. It has included support for various encoding features. By default, UTF-8 is used for spreadsheet encoding.
There are several important features supported by the library related to Excel spreadsheet creation as well as manipulation such as creating new spreadsheets from the scratch, reading existing Excel files, modifying the existing spreadsheet, using page settings, adding new rows and columns, hide existing rows or columns, grouping rows and columns, printing setting support, spreadsheet encoding support, backward compatibility and many more. Moreover, the library has significantly improved memory-efficiency while reading large Excel Files.
At A Glance
An overview of Spreadsheet features.
- Read spreadsheet
- Grouping support
- Modify Existing File
- Formula support
- Add new sheet
- Spreadsheets Creation
- Style support
- String Encodings
- Outline support
- Hide rows/columns
- Unhide rows/columns
The spreadsheet library only requires Ruby runtime.
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.
Install xlsx-populate via npm
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.
Create New Excel Spreadsheet via Ruby Library
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.
Read Existing Spreadsheets via Ruby Library
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.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.
Hide Rows in Spreadsheets via Ruby API
require ‘spreadsheet’ file = ARGV book = Spreadsheet.open(file, ‘rb’) sheet= book.worksheet(0) 26.upto(30) do |i| sheet.row(i).hidden = true end book.write “out.xls”