ESV

Ruby library/gem for Excel parsing and generation with the ease of CSV.

Exporting CSVs because Excel generation is too complex? No more! CSVs can also be difficult to open correctly, e.g. in Excel on Mac.

ESV will read and generate XLS files. There is currently no XLSX support, but Pull Requests are welcome.

By design, ESV only reads and writes simple values like integers, floats, strings and dates/datetimes. When parsing a spreadsheet with formulas, you will get their last value, if known.

require "esv"

data = ESV.generate do |esv|
  esv << [ "Name", "Dogs", "Cats" ]
  esv << [ "Victor", 1, 4 ]
end

File.write("/tmp/test.xls", data)

require "esv"

ESV.generate_file("/tmp/test.xls") do |esv|
  esv << [ "Name", "Dogs", "Cats" ]
  esv << [ "Victor", 1, 4 ]
end

require "esv"

data = File.read("/tmp/test.xls")
output = ESV.parse(data)
# => [ [ "Name", "Dogs", … ], … ]

This will raise for a file with multiple worksheets unless you explicitly specify the one you want (the first worksheet is index 0):

ESV.parse(data, worksheet_index: 0)

.parse supports the header_converters: keyword argument, which takes the same arguments as CSV.parse does:

• a Symbol name for a registered header converter
• a Proc which takes the value and returns the converted value
• an Array of Symbol names for registered header converters

require "esv"

data = File.read("/tmp/test.xls")
output = ESV.parse(data, header_converters: :symbol)
# => [ [ :name, :dogs, … ], … ]

Registering a new converter:

ESV::HEADER_CONVERTERS[:upcase] = ->(value) { value.upcase }

require "esv"

output = ESV.parse_file("/tmp/test.xls")
# => [ [ "Name", "Dogs", … ], … ]

This is a thin wrapper around ESV.parse and takes the same arguments.

In config/initializers/mime_types.rb:

Mime::Type.register ESV::MIME_TYPE, "xls"

As a model or whatever you prefer:

class MyExcelDocument
  def self.generate(name)
    ESV.generate { |esv| esv << [ "Hello #{name}" ] }
  end
end

Controller:

class MyController < ApplicationController
  include ESV::RailsController  # for send_excel

  def show
    data = MyExcelDocument.generate("Rails")
    send_excel(data)
    # or: send_excel(data, filename: "myfilename.xls")
  end

  def another_example
    respond_to do |format|
      format.html { … }
      format.xls { send_excel(…) }
    end
  end
end

Add this line to your application's Gemfile:

gem 'excel-esv'

And then execute:

$ bundle

Or install it yourself as:

$ gem install excel-esv

By Henrik Nyh for Auctionet.com under the MIT license.

Using a lot of code from LivingSocial's Excelinator, also under the MIT license.

This library is a thin wrapper around Spreadsheet which does the heavy lifting.