Open Source Ruby Library to Create & Edit XLSX Spreadsheet
Free Ruby Library to Create & Protect Spreadsheet with Charts. It supports Inserting images, hyperlinks, applying styles via Ruby API.
Axlsx is a very useful and feature rich open source Ruby library that helps developers to generate powerful apps for creating and manipulation spreadsheet with ease. The spreadsheet generator library Axlsx helps developer to make stunning looking Open XML Spreadsheet documents without having the complete knowledge of ECMA specification.
Axlsx library is very powerful but yet very simple to configure. It has included several important features for Excel spreadsheet generation and processing such as adding new worksheet, renaming a worksheet, add charts to worksheet, inserting images and links, manage column & rows widths, customized styles, tables handling, printing support, merging cells, apply filters, add comments and much more.
The library also included some advanced features while handling spreadsheet files, such as adding headers and footers, handling PivotTables, protecting your sheets using password, Auto filtering tables and many more.
At A Glance
An overview of Axlsx features.
- Make XLSX File
- Custom data XLSX
- Access Excel row
- 4Read spreadsheet
- Manag Cells
- Add rows
- Insert Columns
- Add worksheets
- Rename Sheet
- Style support
- Enhanced borders
- Merging cells
- Delete rows/Columns
- Access a Row
The Axlsx library only requires Ruby runtime.
Getting Started with Axlsx
The recommend way to install Axlsx library is by using RubyGems. Please use the following command for smooth installation.
Install Axlsx via RubyGems
$ gem install axlsx
Create Spreadsheet with Charts via Ruby
The Axlsx library makes it easy for software programmers to create Excel spreadsheets inside their own Ruby applications. The library supports adding charts to their spreadsheet with just couple of lines of code. It enables developers to generate 3D pie, line, scatter and bar charts easily. The library allows users to build charts based on the custom data as well as generate charts without any data in their worksheet. You can easily customize the gridlines, label rotation and much more.
Protect Spreadsheet via Ruby API
The open source Axlsx library gives Ruby programmers the ability to protect the spreadsheet by applying passwords inside their own applications. It is a very useful technique that prevents other people from making any changes or viewing, moving, or deleting important data from a worksheet. The library has included support for creating password protected as well as non-password worksheet with just couple lines of code.
Apply Sheet Protection & Excluding Cells from Locking via Ruby API
# Sheet Protection and excluding cells from locking. if examples.include? :sheet_protection unlocked = wb.styles.add_style :locked => false wb.add_worksheet(:name => 'Sheet Protection') do |sheet| sheet.sheet_protection do |protection| protection.password = 'fish' protection.auto_filter = false end sheet.add_row [1, 2 ,3], :style => unlocked # These cells will be locked sheet.add_row [4, 5, 6] sheet.add_row [7, 8, 9] sheet.auto_filter = "A1:C3" end end
Apply Custom Styles to Worksheets
Custom styles are a very convenient way to save users time when formatting their worksheets. The Axlsx library has provided complete functionality for applying custom style and formatting to Excel Spreadsheet using Ruby code. The library has provided consistent formatting across a range of cells. Developers can easily style borders, use alignment, apply fills, select fonts, and number formats in a single line of Ruby code.
How to Apply Custom Formatting & Date via Ruby API?
#```ruby if examples.include? :format_dates require 'date' wb.styles do |s| date = s.add_style(:format_code => "yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER) padded = s.add_style(:format_code => "00#", :border => Axlsx::STYLE_THIN_BORDER) percent = s.add_style(:format_code => "0000%", :border => Axlsx::STYLE_THIN_BORDER) # wb.date1904 = true # Use the 1904 date system (Used by Excel for Mac < 2011) wb.add_worksheet(:name => "Formatting Data") do |sheet| sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER sheet.add_row [Date::strptime('2012-01-19','%Y-%m-%d'), 0.2, 32], :style => [date, percent, padded] end end end #```
Inserting Images and Hyperlinks to Spreadsheets
The free Axlsx library has provided complete support for adding and modifying images inside their worksheets with just couple Ruby commands. You can use popular image formats such as JPG, GIF and PNG images inside a worksheet. You can also insert images with hyperlinks. The library also allows replacing an existing image or modifying its size and place.
Add Image with Hyperlink via Ruby API
##Add an Image with a hyperlink #```ruby if examples.include? :images wb.add_worksheet(:name => "Image with Hyperlink") do |sheet| img = File.expand_path('../image1.jpeg', __FILE__) # specifying the :hyperlink option will add a hyper link to your image. # # @note - Numbers does not support this part of the specification. sheet.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| image.width=720 image.height=666 image.hyperlink.tooltip = "Labeled Link" image.start_at 0, 0 end