Excel export in Rails

An export endpoint ships, somebody downloads it, and a week later the warehouse says the SKUs are wrong. 00000037 arrived as 37. The assumption in the room is that Excel did it, because Excel has done it before. On this machine it was not Excel. The zeros were already gone in the bytes Rails sent, and the line responsible is cell.rb:530 in caxlsx.

Everything below ran in a scratch Rails 8.1.3.1 application on Ruby 4.0.5 against PostgreSQL 17.7 on port 15432, holding 200000 rows in a 30 MB orders table. The gems are caxlsx 4.5.0, caxlsx_rails 0.7.2, xlsxwriter 0.2.3, roo 3.0.0, creek 2.6.3, rubyzip 3.7.0, nokogiri 1.19.4. The laptop is an Apple M2 Max, 12 cores, macOS 26.5.1, arm64-darwin25. Peak RSS is sampled every 50 ms with ps -o rss= from a process outside the one being measured, for a reason that gets its own section. There is no Excel and no LibreOffice on this machine, so nothing here is a claim about what a spreadsheet application displays. Every claim is about what is in the file and what Ruby reads back out of it.

The zeros are gone before any spreadsheet opens the file

Order#sku is a string column holding "00000037". Write that through caxlsx and unzip the result:

$ unzip -p tmp/caxlsx_200000.xlsx xl/worksheets/sheet1.xml | ruby -e 'b=STDIN.read; i=b.index(%{<row r="2"}); puts b[i,240]'
<row r="2" ><c r="A2" s="0" t="inlineStr"><is><t>ORD-2026-0000001</t></is></c><c r="B2" s="0" t="n"><v>0</v></c><c r="C2" s="0" t="inlineStr"><is><t>customer0@example.com</t></is></c><c r="D2" s="0" t="n"><v>199</v></c><c r="E2" s="0" t="in

Column A kept its type because ORD-2026-0000001 is not a number. Column B is t="n" with <v>0</v>, and the row it came from held "00000000". The whole decision is one method:

# caxlsx-4.5.0/lib/axlsx/workbook/worksheet/cell.rb:523
def cell_type_from_value(v)
  if v.is_a?(Date)
    :date
  elsif v.is_a?(Time)
    :time
  elsif v.is_a?(TrueClass) || v.is_a?(FalseClass)
    :boolean
  elsif v.respond_to?(:to_i) && Axlsx::NUMERIC_REGEX.match?(v.to_s)
    :integer

Axlsx::NUMERIC_REGEX is /\A[+-]?\d+?\Z/ at constants.rb:426. A String of digits matches it, so a String of digits becomes an integer cell, and the String is never consulted again. The same line takes out French postcodes, phone numbers stored with a leading zero, bank account fragments, and any identifier a previous generation of the schema zero padded.

The fix is types: on the row, and it is not a workaround, it is the documented way to turn the guess off:

COLS  = %i[reference sku customer_email amount_cents currency placed_at refunded]
TYPES = %i[string string string integer string time boolean]

sheet.add_row row, types: TYPES, style: [nil, nil, nil, nil, nil, stamp, nil]

That costs almost nothing. The same 200000 rows took 9.95s with declared types against 10.43s without, and the file grew from 8045345 to 8362728 bytes, because eight bytes of t="inlineStr" scaffolding per cell survives compression better than an integer does. Peak RSS did not move: 634 MB against 630 to 657 MB. So the reason to declare types is correctness, and there is no performance argument on either side of it.

What else the type guess changes

Two more conversions happen quietly on a plain add_row, and both of them survive a review because the export opens and looks right.

A datetime column comes back as a Date. caxlsx writes the value as a serial number, which is correct, and then applies Axlsx::STYLE_DATE to it. STYLE_DATE is 2, and cellXfs index 2 is built at styles.rb:575 as Xf.new(borderId: 0, xfId: 0, numFmtId: 14, fontId: 0, fillId: 0, applyNumberFormat: 1). Built-in number format 14 is a date with no time in it. So the serial keeps the clock and the format throws it away, and a reader that honours the format throws it away too:

# spec/xlsx_spec.rb, passing
t = Time.utc(2026, 9, 26, 15, 21, 48)
path = caxlsx("time.xlsx") { |s| s.add_row [t] }

expect(sheet_xml(path)).to include('<c r="A1" s="2" ><v>46291.64013888889</v></c>')
back = Roo::Excelx.new(path).cell(1, 1)
expect(back).to be_a(Date)
expect(back).not_to be_a(DateTime)
expect(back.to_s).to eq("2026-09-26")

The .64013888889 is 15:21:48 expressed as a fraction of a day. It is in the file. Nothing that reads the format will show it to you.

The second conversion is worse, because it produces a file roo cannot open at all. Hand caxlsx a String that looks like an ISO 8601 timestamp and cell_type_from_value returns :iso_8601, which serialises as t="d" with the text left as it was:

<c r="A1" s="0" t="d"><v>2026-09-26T08:30:00</v></c>

roo 3.0.0 reads t="d" as a number cell and calls Integer() on the text:

ArgumentError: invalid value for Integer(): "2026-09-26T08:30:00"
  from roo-3.0.0/lib/roo/excelx/cell/number.rb:27:in 'Roo::Excelx::Cell::Number#create_numeric'
  from roo-3.0.0/lib/roo/excelx/sheet_doc.rb:172:in 'Roo::Excelx::SheetDoc#create_cell_from_value'

Which means an application that writes with caxlsx and reads with roo can produce a file its own importer rejects, from a column whose values are perfectly ordinary strings. The trigger is narrow: ISO_8601_REGEX at constants.rb:419 requires the full T form down to seconds, so "2026-09-26" is safe and "2026-09-26T08:30:00" is not. Serialised timestamps from a JSON payload land in the unsafe half.

Two writers, and only one of them guesses

xlsxwriter 0.2.3 is a Ruby binding over libxlsxwriter, a C library. Its add_row does its own type dispatch, in lib/xlsxwriter/worksheet.rb, and the when String branch calls write_string. Given the same "00000000" it writes what it was given:

<row r="2"><c r="A2" t="inlineStr"><is><t>ORD-2026-0000001</t></is></c><c r="B2" t="inlineStr"><is><t>00000000</t></is></c>

The interesting difference is not the guess, it is the memory. caxlsx builds an Axlsx::Cell object per cell and holds the whole workbook until you ask for bytes. libxlsxwriter has a constant_memory mode that flushes each row to a temporary file as it is written. Same query, same batching, best of five runs:

200000 rows by 7 columns	time	peak RSS	file bytes
pluck in batches of 5000, no writer at all	2.85s	216 MB
CSV, one reused writer	3.17s	217 MB	18502613
xlsxwriter, write_* calls, constant_memory: true	5.24s	223 MB	8085308
xlsxwriter, add_row, constant_memory: true	6.27s	218 MB	8049546
xlsxwriter, add_row, default	6.28s	439 MB	8657832
caxlsx, Package#to_stream	10.43s	630 MB	8045345

Read the first row of that table before the rest of it. Fetching the rows is 216 MB and 2.85s on its own, so the CSV writer costs 1 MB and 0.3s and the streaming xlsx writer costs 2 MB and 2.4s. caxlsx costs 414 MB. The gem is not slow at writing XML, it is expensive at holding it.

Two smaller results in that table are worth naming. The .xlsx is less than half the size of the same data as CSV, 8.0 MB against 18.5 MB, because the sheet inside it is a zip entry: unzip -l reports xl/worksheets/sheet1.xml at 80787728 bytes uncompressed. And bypassing the gem's add_row for direct write_string and write_number calls saved a second, which is the cost of XlsxWriter::Worksheet#update_col_auto_width running val.count(THIN_CHARS) over every string cell.

The other row of the table, add_row with the default options at 439 MB, is libxlsxwriter building a shared string table. Shared strings dedupe repeated text into xl/sharedStrings.xml and leave <v> indices in the sheet. On this data the trade is bad in both directions: the archive got bigger (8657832 against 8049546) because the references are nearly all distinct, and the table cost 221 MB to hold. caxlsx makes the opposite default choice and writes inline strings unless you set workbook.use_shared_strings = true, which is the right default for exported rows and the wrong one for a sheet of repeated labels.

What one export costs a Puma worker

The table above is a bin/rails runner. The number that matters is the one a web process reaches, so the same two writers went behind real routes: GET /orders rendering an .xlsx.axlsx template through caxlsx_rails, and GET /orders/stream writing with xlsxwriter into a Tempfile and answering with send_file. Fresh server each time, one 1-row request to warm it, then one request for all 200000 rows with the worker's RSS sampled throughout.

fresh worker RSS 132 MB (after one 1-row request)
GET /orders?limit=200000 -> 200 8045344 bytes ttfb=10.496432s total=10.502809s
peak worker RSS 650 MB

fresh worker RSS 132 MB (after one 1-row request)
GET /orders/stream?limit=200000 -> 200 8049546 bytes ttfb=5.909722s total=5.913263s
peak worker RSS 281 MB

518 MB added to a worker against 149 MB, for one request. On a dyno with two or three workers and a table that anybody can click Export All on, the first number is the one that gets the process killed, and it gets killed at a memory ceiling rather than a timeout, which is why the incident looks like a mystery restart instead of a slow endpoint.

The renderer is doing exactly what its source says. caxlsx_rails ends in send_data render_to_string(options), and its template handler appends xlsx_package.to_stream.string, so the whole workbook exists twice at the moment of the response: once as cell objects and once as a String. Worth knowing before you reach for the runner: Mime[:xlsx] is nil in bin/rails runner, because the registration sits inside ActiveSupport.on_load(:action_controller) in axlsx_rails/railtie.rb and a runner never loads Action Controller.

There is no streaming .xlsx

Time to first byte equals total time in both responses above, to four decimal places, and no amount of rewriting the controller changes that. A CSV export can hand the browser its first line while the query is still running, which is what an Enumerator assigned to response_body buys in CSV import and export in Rails. An .xlsx cannot: it is a zip, and the two paths available both need the archive finished. caxlsx builds it in memory and gives you a StringIO; libxlsxwriter writes it to a path on disk and closes it. Neither hands out a prefix.

So the answer for an export that takes ten seconds is not a clever controller, it is a job. Write the file in Solid Queue or Sidekiq, attach it with Active Storage, and email or Turbo Stream the link. The cost of that is honest and it is not small: you have added a background worker, a storage bucket, a retention policy for files that contain customer data, and a second place where the export can fail silently. For 5000 rows none of that is worth building, and send_file from the request is the right answer. The line sits wherever the response starts outliving your proxy's request timeout, and that number is in your own configuration rather than in this article.

Reading one back

An importer is where the memory numbers get strange. Both gems in common use claim to stream, and one of them costs nine times what the other does. Reading column D out of the same 200000 row file, summing it to prove every row was seen:

read of 200000 rows	time	peak RSS
Roo::Excelx#each_row_streaming	17.32s	202 MB
Creek::Book#sheets[0].rows	10.56s	1801 MB
Roo::Excelx#cell(i, j) in a loop	26.01s	2623 MB

One run each, and the reading numbers are noisier than the writing ones: a second each_row_streaming run over the same file came back at 16.20s and 234 MB. All three returned 48501500000. The third row is the one most importers are written as, and it holds the entire parsed sheet; each_row_streaming is the same gem with one method changed. creek is faster than streaming roo and holds 1.8 GB while doing it, which makes it the wrong default despite the name on the tin.

One thing that changes these numbers is a property of the file rather than the reader. Read the shared-strings version of the same data with each_row_streaming and it goes from 202 MB to 747 MB, because the dictionary is loaded before the first row is yielded. If you control both ends, inline strings on the way out are cheaper on the way back in.

A guess I had about creek turned out to be wrong, and it is the kind of wrong worth writing down because the internet repeats it. Creek's row hashes do not skip empty cells. Given a sheet whose XML genuinely has no <c r="B1"> element at all, written by xlsxwriter with skip_empty: true:

<row r="1" spans="1:3"><c r="A1" t="s"><v>0</v></c><c r="C1" t="s"><v>1</v></c></row>

creek 2.6.3 returns {"A1" => "left", "B1" => nil, "C1" => "right"}, because Creek::Sheet#fill_in_empty_cells pads the gap. Positional indexing into a creek row is safe.

The measurement that broke the thing it was measuring

The first caxlsx run in this article crashed, and it crashed because of the instrument:

rubyzip-3.7.0/lib/zip/deflater.rb:20:in 'Zlib::Deflate#deflate': buffer error (Zlib::BufError)
    from rubyzip-3.7.0/lib/zip/output_stream.rb:209:in 'Zip::OutputStream#<<'
    from caxlsx-4.5.0/lib/axlsx/util/buffered_zip_output_stream.rb:60:in 'Axlsx::BufferedZipOutputStream#flush'
    from caxlsx-4.5.0/lib/axlsx/workbook/worksheet/cell_serializer.rb:164:in 'Axlsx::CellSerializer.value_serialization'

It failed 2 runs in 5 and only inside the benchmark harness, never in a plain script, which is the signature of a race rather than a bug in the export. The harness sampled RSS from a thread, and the sampler shelled out to ps. Four variants of that thread, 10 runs of a 10000 row caxlsx export each:

mode=none     runs=10 Zlib::BufError=0
mode=sleep    runs=10 Zlib::BufError=0
mode=spin     runs=10 Zlib::BufError=0
mode=backtick runs=10 Zlib::BufError=6

A thread burning CPU is harmless. A thread that starts a subprocess is not. Strip caxlsx out and the cause is plainer: any signal delivered while Zlib::Deflate#deflate is running is enough, with no subprocess anywhere.

thread: system("true")                               BufError 10/10
thread: spawn + detach (no wait)                     BufError 10/10
thread: Process.kill('URG', self)                    BufError 10/10
main thread only, self-signalling                    BufError  0/10

rubyzip reaches that call with Zip::ZLIB_FLUSHING_STRATEGY, defined at zip.rb:68 as defined?(JRUBY_VERSION) ? Zlib::SYNC_FLUSH : Zlib::NO_FLUSH. I expected the flush mode to be the whole story and wrote a test asserting SYNC_FLUSH was safe. The test failed. SYNC_FLUSH raises too, just less often, and how often depends on the payload: 0 in 10 on varied cell XML, 10 in 10 on a repeating chunk. The flush mode is not the fix.

What does move is the Ruby version. The same script, same payload, same thread:

ruby-3.2.2 zlib 3.0.0/1.3.1   NO_FLUSH + system()   BufError  0/10
ruby-3.4.1 zlib 3.2.1/1.3.1   NO_FLUSH + system()   BufError 10/10
ruby-3.4.8 zlib 3.2.1/1.3.1   NO_FLUSH + system()   BufError 10/10
ruby-4.0.1 zlib 3.2.2/1.3.1   NO_FLUSH + system()   BufError 10/10
ruby-4.0.5 zlib 3.2.3/1.3.2   NO_FLUSH + system()   BufError 10/10
ruby-4.0.6 zlib 3.2.3/1.3.2   NO_FLUSH + system()   BufError 10/10

Activating zlib 3.0.1, 3.1.0, 3.1.1, 3.1.2 and 3.2.0 under Ruby 4.0.5 changed nothing, so the variable is the interpreter and not the extension. I have not read the commit that did it and I am not going to guess at one.

Whether this reaches your application depends on whether anything in the process starts a subprocess while an export is deflating. In a Puma worker running five threads, that is one ImageProcessing call, one Open3.capture2, one system("pdftotext ..."), on any other request. The export raises Zlib::BufError from a stack trace that names neither of them, and it does it often enough to notice and rarely enough to be filed as flaky. Building the file in a job in a process that does nothing else is not only a memory argument.

A million rows, and the four it dropped

xlsxwriter enforces the format's row limit by ignoring what is over it. libxlsxwriter/include/xlsxwriter/worksheet.h:59 defines LXW_ROW_MAX as 1048576 and LXW_COL_MAX as 16384. Ask for 1048580 rows:

# spec/xlsx_spec.rb, passing
expect(sheet_xml(path)).to include('<dimension ref="A1:A1048576"/>')
expect(sheet_xml(path)).to include('<row r="1048576"><c r="A1048576"><v>1048575</v></c></row>')
expect(sheet_xml(path)).not_to include('<row r="1048577"')

No exception, no warning, 1.35s, and four rows that are not in the file. caxlsx has no equivalent constant anywhere in lib, so nothing there is checking. An export that can exceed a million rows needs its own guard, and a count before the write is the cheap one.

What I would ship

Declare the types. Whichever writer you are on, types: on add_row is the difference between an export that is correct and one that is correct for the columns nobody zero pads, and it costs nothing measurable. That conclusion is independent of everything else here.

Then leave caxlsx where it is unless memory is the problem you have. It is the gem with the templates, the styling API, the Rails integration and the answers on the internet, and at 634 MB for 200000 rows the ceiling is real but a long way from most exports. Below 20000 rows none of this section applies to you and the whole decision is the previous paragraph.

What would change that: if exports run inside the web process and the worker has a hard memory ceiling, xlsxwriter with constant_memory: true is 218 MB for the same file and the migration is mechanical, add_row to add_row with a types: array and a named format for timestamps. The cost is a native extension in the build, a smaller community, and no template handler, so the view becomes a plain object that writes rows.

And if the thing being asked for is "a spreadsheet" rather than "an .xlsx", ship CSV. It was 3.17s and 217 MB here against 10.43s and 630 MB, it streams, and the two reasons to prefer the binary format are typed cells and multiple sheets. Only one of those is usually being asked for.

What this post does not cover

No spreadsheet application was involved in any measurement here. There is no Excel and no LibreOffice on this laptop, so every statement above is about bytes in a file and what roo, creek and rubyzip do with them. What Excel displays for numFmtId="14", whether it accepts t="d" at all, and what it does with a text cell full of digits are all outside what I can check, and they are the questions most worth checking before you trust a file to a finance team.

Also absent: .xls, the pre-2007 BIFF format, which roo reads through the spreadsheet gem and neither writer here produces; formulas, charts, pivot tables, conditional formatting and merged cells, all of which caxlsx supports and none of which were measured; rubyXL and write_xlsx, the two other writers on RubyGems, which were not installed; Google Sheets, which is an API problem and not a file problem; Windows COM automation, which needs Windows; and the import half of the job past parsing, meaning validation, insert_all and what to do with 20000 bad rows, which is the same problem in both file formats and is written up for CSV.