Xlsxtream

gem 'xlsxtream'

# Creates a new workbook and closes 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.

gem 'xlsxtream', '< 2'

# Pre 2.x code with :io_wrapper option
Xlsxtream::Workbook.new(io, io_wrapper: MyCustomIOWrapper)
# New code with IO wrapper instance
io_wrapper = MyCustomIOWrapper.new(io)
Xlsxtream::Workbook.new(io_wrapper)