Publish / file · All operations

Write a spreadsheet

Write Excel, Google Sheets.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the excel extension.

Shape of the result
the table sent out, with nothing new to look at
Level
needs a DuckDB extension

The DuckDB step

Write an .xlsx

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
INSTALL excel; LOAD excel;
COPY (SELECT region, sum(amount) AS total FROM orders GROUP BY region) TO '<your_scratch_dir>/summary.xlsx' (FORMAT xlsx, HEADER true);
CREATE TEMP TABLE result AS
SELECT count(*) AS n, round(sum(TRY_CAST(total AS DOUBLE)), 2) AS total FROM read_xlsx('<your_scratch_dir>/summary.xlsx', all_varchar = true);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT n = 5 AND total = 1900.74 FROM result)) THEN 'as expected' ELSE error('the workbook written does not hold the five regional totals') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • The statement writes summary.xlsx itself, in <your_scratch_dir>.
  • It needs the excel extension.
  • The statement checks its own result and raises an error when it is wrong: “the workbook written does not hold the five regional totals”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Write sheets to a workbook

Write one or more tables to sheets of an Excel or OpenDocument workbook.

Update cells in an existing workbook

Overwrite chosen cells or ranges, or apply formatting, in a workbook that already exists.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
Apache Hop (2)
  • Excel writerread 2026-09-27

    Write or append rows to Excel or ODS workbooks, with templates and cell formatting

  • Google Sheets outputread 2026-09-27

    Write rows to a Google Sheets worksheet

Duckle (1)
  • Excel (XLSX)read 2026-09-25

    write .xlsx with COPY FORMAT xlsx, then a whitespace-preserving post-pass

EasyMorph (2)
KNIME Analytics Platform (6)
OpenRefine (3)
Pentaho Data Integration (2)
Polars (1)
RapidMiner Studio (4)
  • Delete Google Sheetsread 2026-09-27

    Delete a sheet from a Google Sheets spreadsheet

  • Update Excelread 2026-09-27

    Update individual cells or whole ranges in an existing Excel workbook

  • Write Excelread 2026-09-27

    Write one or more tables to an Excel workbook

  • Write Google Sheetsread 2026-09-27

    Write a table to a Google Sheets spreadsheet

Tableau Prep (1)
Talend (1)
VisiData (3)
  • gsheets (saver)read 2026-09-23

    Save as Google Sheets (experimental); also filetype g

  • xls (saver)read 2026-09-23

    Save as Excel .xls workbook

  • xlsx (saver)read 2026-09-23

    Save as Excel .xlsx workbook

Background

In Apache Hop, the Excel Writer transform writes incoming rows to spreadsheet files in the .xls, .xlsx or OpenDocument Spreadsheet (.ods) format.[1] In modern spreadsheet applications, sheets are gathered into a workbook, which is represented by a file holding all its sheets and cells.[2]

When to use it

A Microsoft Excel worksheet holds at most 1,048,576 rows by 16,384 columns.[3] DuckDB's excel extension throws an error when a sheet exceeds its sheet_row_limit option, which defaults to 1,048,576 rows.[4] Many tools only support a maximum of 1,048,576 rows in a sheet, so raising DuckDB's sheet_row_limit may make the written file unreadable by other software.[4] Count a table's rows before writing it to a sheet, and split it across sheets or choose another format when it will not fit. (Meridian's view)

DuckDB's excel extension casts numeric types to DOUBLE when writing an XLSX file, because XLSX files only really support storing numbers or strings.[4] Microsoft Excel keeps numbers to a precision of 15 digits.[3] When data is imported or entered, Microsoft Excel removes leading zeros and converts large numbers to scientific notation, such as 1.23E+15.[5] Write identifiers such as account numbers, product codes and postal codes as text, so the spreadsheet keeps their leading zeros and every digit. (Meridian's view)

DuckDB's excel extension casts TIMESTAMP_TZ and TIME_TZ values to TIMESTAMP and TIME in Coordinated Universal Time (UTC), and the time zone information is lost.[4] Convert zoned timestamps to the local time a reader expects, or keep the zone in a column of its own, before writing them to a sheet. (Meridian's view) Microsoft Excel supports two date systems, the 1900 and the 1904 date system, and converts each date into a serial number counting the days elapsed since its system's starting date.[6] The serial number of a date in Excel's 1900 date system is always 1,462 days greater than its serial number in the 1904 date system.[6] When dates in a written workbook read about four years off, check which date system the workbook uses. (Meridian's view)

Apache Hop's Excel Writer can write to a sheet that already exists in the output file, or replace it.[1] Apache Hop's Excel Writer can overwrite existing cells, which is fast, or shift existing cells down.[1] A workbook that people have already formatted and filled with formulas is safer updated in the cells that change than rewritten whole. (Meridian's view)

The method

Office Open XML (OOXML) is a zipped file format based on Extensible Markup Language (XML), developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents.[7] Microsoft submitted the initial material for OOXML to Ecma International's Technical Committee TC45, where it was standardised as ECMA-376, approved in December 2006.[7] An amended version of OOXML was approved as a standard of the International Organization for Standardization (ISO) and the International Electrotechnical Commission (IEC) in April 2008, and ISO/IEC 29500:2008 was published in November 2008.[7] Starting with Microsoft Office 2007, the Office Open XML file formats became the default file format of Microsoft Office.[7] In Apache Hop, .xlsx is the modern Excel Open XML format and .xls the legacy Excel binary format, and both are written through Apache POI.[1] For Apache Hop's Excel Writer, the binary .xls format is better suited to simple content, while .xlsx works well with templates because it can better preserve charts and other objects.[1]

OpenDocument Format (ODF), standardised as ISO 26300, is an open file format for word processing documents, spreadsheets, presentations and graphics using ZIP-compressed XML files.[8] ODF is developed and maintained by a technical committee of the Organization for the Advancement of Structured Information Standards (OASIS), and is the default format of OpenOffice.org and LibreOffice.[8] OpenDocument spreadsheets most commonly use the .ods and .fods filename extensions.[8]

DuckDB's excel extension writes dates and times as Excel serial numbers, the days since 1900-01-01 and the fraction of a day, styled with a number format so they appear as dates or times in Excel.[4] DuckDB's excel extension writes booleans as 1 and 0, with a number format that makes them appear as TRUE and FALSE in Excel.[4] DuckDB's excel extension casts every type other than numeric, temporal and boolean types to VARCHAR, its text type, and writes it as a text cell.[4]

Sources

  1. [1]Excel writer | Apache Hopdocumentation
  2. [2]Spreadsheet - Wikipediasecondary source
  3. [3]Excel specifications and limits | Microsoft Supportdocumentation
  4. [4]Excel Extension – DuckDBdocumentation
  5. [5]Keeping leading zeros and large numbers | Microsoft Supportdocumentation
  6. [6]Date systems in Excel | Microsoft Supportdocumentation
  7. [7]Office Open XML - Wikipediasecondary source
  8. [8]OpenDocument - Wikipediasecondary source