Xlsxtream

Xlsxtream is a streaming writer for XLSX spreadsheets. It supports multiple worksheets and optional string deduplication via a shared string table (SST). Its purpose is to replace CSV for large exports, because using CSV in Excel is very buggy and error prone. It's very efficient and can quickly write millions of rows with low memory usage.

Xlsxtream does not support formatting, charts, comments and a myriad of other OOXML features. If you are looking for a fully featured solution take a look at caxslx.

Xlsxtream supports writing to files or IO-like objects, data is flushed as the ZIP compressor sees fit.

Installation

Add this line to your application's Gemfile:

gem 'xlsxtream'

And then execute:

$ bundle

Or install it yourself as:

$ gem install xlsxtream

Usage

# Creates a new workbook file, write and close it at the end of the block
Xlsxtream::Workbook.open('my_data.xlsx') do |xlsx|
  xlsx.write_worksheet 'Sheet1' do |sheet|
    # Boolean, Date, Time, DateTime and Numeric are properly mapped
    sheet << [true, Date.today, 'hello', 'world', 42, 3.14159265359, 42**13]
  end
end

io = StringIO.new
xlsx = Xlsxtream::Workbook.new(io)

# Number of columns doesn't have to match
xlsx.write_worksheet 'Sheet1' do |sheet|
  sheet << ['first', 'row']
  sheet << ['second', 'row', 'with', 'more colums']
end

# Write multiple worksheets with custom names
xlsx.write_worksheet 'AppendixSheet' do |sheet|
  sheet.add_row ['Timestamp', 'Comment']
  sheet.add_row [Time.now, 'Good times']
  sheet.add_row [Time.now, 'Time-machine']
end

# If you have highly repetitive data, you can enable Shared String Tables (SST)
# for the workbook or a single worksheet. The SST has to be kept in memory,
# so do not use it if you have a huge amount of rows or a little duplication
# of content across cells. A single SST is used for the whole workbook.
xlsx.write_worksheet(name: 'SheetWithSST', use_shared_strings: true) do |sheet|
  sheet << ['the', 'same', 'old', 'story']
  sheet << ['the', 'old', 'same', 'story']
  sheet << ['old', 'the', 'same', 'story']
end

# Strings in numeric or date/time format can be auto-detected and formatted
# appropriately. This is a convenient way to avoid an Excel-warning about
# "Number stored as text". Dates and times must be in the ISO-8601 format and
# numeric values must contain only numbers and an optional decimal separator.
# The strings true and false are detected as boolean values.
xlsx.write_worksheet(name: 'SheetWithAutoFormat', auto_format: true) do |sheet|
  # these two rows will be identical in the xlsx-output
  sheet << [true, 11.85, DateTime.parse('2050-01-01T12:00'), Date.parse('1984-01-01')]
  sheet << ['true', '11.85', '2050-01-01T12:00', '1984-01-01']
end

# You can also create worksheet without a block, using the `add_worksheet` method.
# It can be only used sequentially, so remember to manually close the worksheet
# when you are done (before opening a new one).
worksheet = xls.add_worksheet(name: 'SheetWithoutBlock')
worksheet << ['some', 'data']
worksheet.close

# Writes metadata and ZIP archive central directory
xlsx.close
# Close IO object
io.close

# Changing the default font from Calibri, 12pt, Swiss
Xlsxtream::Workbook.new(io, font: {
  name: 'Times New Roman',
  size: 10, # size in pt
  family: 'Roman' # Swiss, Modern, Script, Decorative
})

# Specifying column widths in pixels or characters; 3 column example;
# "pixel" widths appear to be *relative* to an assumed 11pt Calibri
# font, so if selecting a different font or size (see above), do not
# adjust widths to match. Calculate pixel widths for 11pt Calibri.
Xlsxtream::Workbook.new(io, columns: [
  { width_pixels: 33 },
  { width_chars: 7 },
  { width_chars: 24 }
])
# The :columns option can also be given to write_worksheet, so it's
# possible to have multiple worksheets with different column widths.


# Output from Rails (will stream without buffering)
class ReportsController < ApplicationController
  include ZipKit::RailsStreaming
  EXCEL_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

  def download
    zip_kit_stream(filename: "report.xlsx", type: EXCEL_CONTENT_TYPE) do |zip_kit_streamer|
      Xlsxtream::Workbook.open(zip_kit_streamer) do |xlsx|
        xlsx.write_worksheet 'Sheet1' do |sheet|
          # Boolean, Date, Time, DateTime and Numeric are properly mapped
          sheet << [true, Date.today, 'hello', 'world', 42, 3.14159265359, 42**13]
        end
      end
    end
  end
end

Compatibility

The current version of Xlsxtream requires at least Ruby 2.6

If you are using an older Ruby version you can use the following in your Gemfile:

gem 'xlsxtream', '< 3'

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake test to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/felixbuenemann/xlsxtream.

License

The gem is available as open source under the terms of the MIT License.