Xlsxtream

If your dataset contains highly repetitive strings, you can enable Shared String Tables (SST) to reduce file size. This can be enabled for the entire workbook or for a specific worksheet using the use_shared_strings: true option.

Warning: The SST is kept in memory. Do not enable this if you have a massive number of rows or very little duplication, as it may lead to high memory usage.

xlsx.write_worksheet(name: 'SheetWithSST', use_shared_strings: true) do |sheet|
  sheet << ['the', 'same', 'old', 'story']
  sheet << ['the', 'old', 'same', 'story']
end

You can create a new XLSX file using Xlsxtream::Workbook.open, which automatically handles opening and closing the file. Inside the block, use write_worksheet to define sheets and the << operator to append rows of data. Xlsxtream automatically maps Ruby types like Boolean, Date, Time, DateTime, and Numeric to their appropriate Excel formats.

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

To use Xlsxtream in your Ruby application, add it to your Gemfile:

gem 'xlsxtream'

Then run bundle. Alternatively, install it directly via the command line:

gem install xlsxtream

Note: Xlsxtream requires Ruby 2.6 or higher. For older Ruby versions, you may need to specify an older version in your Gemfile (e.g., gem 'xlsxtream', '< 3').

To reduce file size when dealing with highly repetitive string data, you can enable the Shared String Table (SST).

Warning: The SST is kept in memory. Do not enable it if you have a massive number of rows or very little duplication, as it may lead to high memory consumption. A single SST is shared across the entire workbook.

Enable it via the :use_shared_strings option in the workbook or worksheet configuration.

# Enable for the whole workbook
Xlsxtream.open("output.xlsx", use_shared_strings: true) do |workbook|
  # ...
end

# Or enable for a specific worksheet
workbook.write_worksheet("Sheet1", use_shared_strings: true) do |worksheet|
  # ...
end

To use Xlsxtream in your Ruby project, install the gem via RubyGems.

gem install xlsxtream

To avoid the Excel warning "Number stored as text", you can use auto_format: true on a worksheet. This tells Xlsxtream to attempt to detect and format strings that match numeric, date, or time patterns.

• Dates/Times: Must be in ISO-8601 format.
• Numeric: Must contain only numbers and an optional decimal separator.
• Booleans: The strings 'true' and 'false' are detected as boolean values.

xlsx.write_worksheet(name: 'SheetWithAutoFormat', auto_format: true) do |sheet|
  # These strings will be output as actual numbers/dates in Excel
  sheet << ['true', '11.85', '2050-01-01T12:00', '1984-01-01']
end

When initializing a Xlsxtream::Workbook, you can pass several configuration options:

• font: A hash to change the default font. Supports :name, :size (in pt), and :family (e.g., 'Swiss', 'Modern', 'Script', 'Decorative').
• has_header_row: A boolean. If true, the first output row is treated as a header (bold and centered).
• columns: An array of hashes defining column dimensions. Supports :width_pixels or :width_chars.

These options can be applied to the entire workbook or passed to write_worksheet for worksheet-specific settings.

# Custom font
Xlsxtream::Workbook.new(io, font: { name: 'Times New Roman', size: 10, family: 'Roman' })

# Header row
Xlsxtream::Workbook.new(io, has_header_row: true)

# Column widths
Xlsxtream::Workbook.new(io, columns: [
  { width_pixels: 33 },
  { width_chars: 7 },
  { width_chars: 24 }
])

If you prefer not to use a block, you can use the add_worksheet method. Note that worksheets created this way must be used sequentially, and you must manually call worksheet.close when finished before opening a new one.

worksheet = xlsx.add_worksheet(name: 'SheetWithoutBlock')
worksheet << ['some', 'data']
worksheet.close

You can set global font styles for the workbook via the options hash passed to Xlsxtream.open or Workbook.new.

Supported font family keys are:

• '' (default)
• 'roman'
• 'swiss'
• 'modern'
• 'script'
• 'decorative'

Available options:

• :font (Hash):
  • :size (Integer/String, default: 12)
  • :name (String, default: 'Calibri')
  • :family (String, default: 'Swiss')

Xlsxtream.open("output.xlsx", font: { name: 'Arial', size: 14, family: 'modern' }) do |workbook|
  # ...
end

The Xlsxtream::Worksheet class is initialized with an IO object and an optional options hash.

Key options include:

• :has_header_row: A boolean. If true, the first row added via << or add_row is treated as a header row.
• :columns: An array of column definitions. If provided, these are written to the worksheet XML immediately upon initialization.
• :name: The name of the worksheet.
• :id: An identifier for the worksheet.
• :is_header: (Internal/Implicit) Used to mark a row as a header.

# Example initialization with columns and header row support
worksheet = Xlsxtream::Worksheet.new(io, {
  name: "My Sheet",
  columns: ["Name", "Age"],
  has_header_row: true
})

You can add rows to a worksheet using the << operator or the add_row method. If the worksheet was initialized with the :has_header_row option, the first row added will be automatically treated as a header row (using the :is_header => true option internally).

Each row is processed and converted to XML before being written to the underlying IO object.

worksheet << ["Column 1", "Column 2"]
# OR
worksheet.add_row(["Value 1", "Value 2"])

Use Xlsxtream.open to create a new Excel workbook. When passed a block, it automatically handles closing the workbook once the block execution completes, ensuring the file is properly written and finalized.

If no block is provided, you are responsible for calling #close on the returned workbook instance.

Xlsxtream.open("output.xlsx") do |workbook|
  # write data here
end