Xlsx2json is a Ruby gem that facilitates transformation of an Excel(xlsx and xlsm) into json without loading files into database or memory.
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"
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. 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 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.
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
Please use the Issues page to report bugs or suggest new enhancements.
Xlsx2json has been published under MIT License