1. Products
  2.   Spreadsheet
  3.   Ruby
  4.   Axlsx
 
  

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.

Previous Next

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
 English