Xlsx2json – Converts Excel to JSON in Ruby and Ruby on Rails.¶ ↑

Xlsx2json is a Ruby gem that facilitates transformation of an Excel(xlsx and xlsm) into json without loading files into database or memory.

Installation¶ ↑

Xlsx2json can be used from the command line or as part of a Ruby web framework. To install the gem using terminal, run the following command:

gem install xlsx2json

To use it in Rails, add this line to your Gemfile:

gem "xlsx2json"

Basic Usage¶ ↑

Xlsx2json can convert a tabular structure of an Excel worksheet into a json object. . Xlsx2json will do the conversion withoutloading the Excel file into memory or database.

require 'xlsx2json'
require 'json'

json_path = '/Users/developer/information.json'
excel_path = 'spec/fixtures/fixture_2.xlsx'
sheet_number = 0 # sheet number start from 0
header_row_number = 1 # row number of the header row which contains column names.
                      # Rows before this number get ignored.
                      # Row numbers start from 1 based on Excel conventions.
header_row_translations = {"from"=>"to"} # If the header row should be changed on the fly

Xlsx2json::Transformer.execute excel_path, sheet_number, json_path, header_row_number: header_row_number

JSON.parse(File.open(json_path).read) # => [{"sku"=>"P07", "bu"=>"Paper", "sales"=>"200", "year"=>"2008"}, {"sku"=>"P17", "bu"=>"Paper", "sales"=>"200", "year"=>"2014"}, {"sku"=>"P19", "bu"=>"Paper", "sales"=>"200", "year"=>"2008"}]

Xlsx2json::Transformer.execute excel_path, sheet_number, json_path, :header_row_translations => {"sku" => "ski"}

JSON.parse(File.open(json_path).read) # => [{"ski"=>"P07", "bu"=>"Paper", "sales"=>"200", "year"=>"2008"}, {"ski"=>"P17", "bu"=>"Paper", "sales"=>"200", "year"=>"2014"}, {"ski"=>"P19", "bu"=>"Paper", "sales"=>"200", "year"=>"2008"}]

Excel Support¶ ↑

• Xlsx2json only supports XLSX and XLSM files.

• Data structure on the Excel worksheet should be tabular.

• By default is assumed that the first row of the Excel worksheet contains column names. If the header starts at a different row, you should pass that by header_row_number option. All rows before this number will get ignored.

• Sheet numbers start from 0.

• Use header_row_translations if there is need to obtain a different json key than the header given in the Excel worksheet. Sometimes handy not needing to modify the sheet manually.

Contributing¶ ↑

Contributions are welcomed. You can fork a repository, add your code changes to the forked branch, ensure all existing unit tests pass, create new unit tests cover your new changes and finally create a pull request.

After forking and then cloning the repository locally, install Bundler and then use it to install the development gem dependencies:

gem install bundler
bundle install

Once this is complete, you should be able to run the test suite:

rake

Bug Reporting¶ ↑

Please use the Issues page to report bugs or suggest new enhancements.

License¶ ↑

Xlsx2json has been published under MIT License