Excel (xlsx)

Excel workbooks, including templates: the xlsx format, from the xlsx package. Declare the package once in dependencies.yml; DRE installs it on demand:

dependencies.yml

plugins:
  - xlsx

Options

Set in a report’s output: (or format_options.xlsx in dre_project.yml).

Option	Type	Default	Notes
header	boolean	true	Write column names above each result set.
max_rows_per_sheet	integer	1000000	Rows per sheet before continuing on Name (2); Excel’s limit less a header.
columns	map		Per column name, on any sheet: {format: <Excel number format>, formula: "={a}*{b}", total: sum}; a query entry’s columns wins.
autofit	boolean	true	Size each column from its content (at most 60 characters); a query entry’s autofit and a column’s width win.
style	map		How sheets look: font, header, totals, banded_rows, borders, and cell keys (bold, fill, …) for every data cell; a query entry’s style and a column’s win.
totals_label	string	Total	Text in the first column of a totals row, when that column has no total.
date_format	string	yyyy-mm-dd	Excel number format for date columns.
datetime_format	string	yyyy-mm-dd hh:mm:ss	Excel number format for timestamp columns.
time_format	string	hh:mm:ss	Excel number format for time columns.

Values

• xlsx keeps every value exact. What Excel can’t store as a number or date is written as text, with one warning per column: numbers with more than 15 significant digits (large integers, wide decimals), numbers beyond Excel’s range, and dates or timestamps before 1900-03-01 or after 9999-12-31 (as ISO text). Those values get no number format, and the warning says so.

Column widths

Columns are sized from their content: each gets the width of its longest value as Excel shows it (number formats and dates applied), across the header, the data rows (the first 10,000 of each sheet) and the totals row, plus two characters; at least Excel’s default (8.43) and at most 60 characters, so a long text stops there and is cut off by the next cell, as in Excel. Numbers never show #####.

queries:
  - query: sales
    autofit: false                 # this tab keeps Excel's default widths...
    columns:
      region: {width: auto}        # ...except region, sized from its content
      comment: {width: 40}         # a fixed width in characters (above 60 too)
output:
  format: xlsx
  autofit: true                    # the default

A column’s width wins over its tab’s autofit, which wins over the output’s. width takes auto or a number of characters from 1 to 255; a fixed width is used as it is. In a template, sheets keep the template’s own widths unless the tab sets autofit: true or a column sets a width.

Styles

style sets how sheets look, at three levels: the output (every sheet), a query entry (that tab) and a column (its data cells). A tab’s style merges over the output’s, key by key, and a column’s over both. Without any, sheets look as they always have: a bold header, a bold totals row with a thin top border, nothing else.

queries:
  - query: sales
    style: {banded_rows: false}          # this tab without bands
    columns:
      net: {format: "#,##0.00", style: {bold: true, font_color: "#C00000", align: right}}
output:
  format: xlsx
  style:
    font: {name: Calibri, size: 11}
    header: {bold: true, fill: "#1F4E78", font_color: "#FFFFFF"}
    banded_rows: "#F2F2F2"               # every other data row; false turns it off
    borders: thin                        # around every cell of the table
    totals: {fill: "#DDEBF7"}

Key	Where	Value
bold, italic, underline	any	true or false
font	any	{name: Calibri, size: 11} (either key)
font_color, fill	any	a colour, "#RRGGBB"
align	any	left, center, right
border	any	none, thin, medium, around the cell
header, totals	output, tab	the cell keys above, for the header and totals rows
banded_rows	output, tab	the fill of every other data row, or false
borders	output, tab	none, thin, medium, around every cell of the table

Cell keys on an output or tab apply to every data cell. A column’s fill wins over the band. bold, italic and underline toggle emphasis; font.name and font.size choose a face and point size. font_color colors text, while fill colors its background. align sets horizontal placement, border frames data cells and borders frames every table cell. header and totals style those rows independently; banded_rows makes wide tables easier to scan. Styled cells are written even when empty, so a band or border has no gaps. In a template, the template’s formatting stays, and only a column’s style is applied over it, on the cells DRE fills. dre validate rejects an unknown key, a colour that isn’t "#RRGGBB" and any other bad value.

Column formats

Every setting here is optional; a report without them gets numbers in General and dates, timestamps and times as yyyy-mm-dd, yyyy-mm-dd hh:mm:ss and hh:mm:ss. A format changes only how a cell displays: the value stays a real number or date, so Excel can still sum, sort and filter it.

queries:
  - query: sales
    columns:
      amount: {format: "#,##0.00"}
      share:  {format: "0.0%"}
  - query: refunds
output:
  format: xlsx
  date_format: "dd/mm/yyyy"
  columns:
    amount: {format: "[$€-x-euro2] #,##0.00"}   # default for `amount` on every sheet

Formats are Excel format codes, the text of Excel’s Format Cells → Custom dialog. The format a cell gets, highest first:

1. its query entry’s columns.<name>.format;
2. the output-level columns.<name>.format;
3. in a template, the template cell’s own number format, if it isn’t General;
4. date_format, datetime_format or time_format, for date, timestamp and time columns;
5. none (General) for numbers, booleans and text.

An explicit format (1 or 2) replaces a template cell’s number format and keeps its font, fill and border. date_format and the other defaults are output options, so format_options.xlsx in dre_project.yml sets them for the project and a report’s own value wins. Rows inserted into a template’s table block, and continuation sheets past max_rows_per_sheet, are formatted too. Header cells stay bold text, and nulls stay empty.

dre validate rejects a malformed code (unbalanced quotes or brackets, more than four ; sections, text that needs quoting) and a date default that doesn’t show a date. A run fails, naming the sheet and column, when a query entry formats a column the query doesn’t return, when an output-level name is on no sheet (no workbook is written), or when a code doesn’t fit its column: a date code on a number, a number code on a date, or either on text or booleans. @ (text) fits any column. columns: on a query entry of another format is an error.

Code	Shows 1234.5 / 0.125 / 2026-01-25 as
#,##0.00	1,234.50
0.0%	12.5%
[$€-x-euro2] #,##0.00	€ 1,234.50
#,##0.00;[Red](#,##0.00)	negatives in red, in parentheses
dd/mm/yyyy	25/01/2026
mmm yyyy	Jan 2026
h:mm AM/PM	a time as 3:05 PM

Formulas and totals rows

The columns: map also takes formula (a formula on every row) and total (a totals row under the data), on a query entry or at output level, like format. A query entry’s setting wins.

queries:
  - query: sales          # select region, qty, price, qty * price as line_total from sales
    columns:
      line_total: {formula: "={qty}*{price}", format: "#,##0.00", total: sum}
      qty:        {total: sum}
      price:      {total: "=SUM({line_total:*})/SUM({qty:*})"}   # average price per unit
output:
  format: xlsx
  totals_label: Total     # the default; "" for none

Row formulas. The SQL selects a placeholder column where the formula goes, so it decides the column’s position and header. {name} is another result column’s cell on the same row: with the header in row 1, ={qty}*{price} becomes =B2*C2, =B3*C3, and so on, following anchor and header. Text outside braces is written as it is (={qty}*$H$1); {{ and }} are literal braces. The placeholder’s value becomes the formula’s cached result, so pandas, DuckDB and other readers that don’t recalculate still see a value; a null placeholder leaves Excel to work it out when the file is opened. A formula must start with =. Only YAML makes formulas: text from a query, even =SUM(A1:A2), is always written as text.

Totals rows. A total writes a bold row with a top border straight under the data: sum, average, count (non-empty cells, Excel’s COUNTA), min or max, or a formula whose {name:*} references stand for a column’s data range (B2:B10). DRE works out the functions’ results as cached values; a totals formula is left for Excel to calculate. The row’s first cell shows totals_label when that column has no total of its own. sum and average need a number column; min and max a number, date or time column; count takes any. Each continuation sheet past max_rows_per_sheet gets a totals row over its own rows, and a sheet with no rows gets none.

dre validate rejects a formula not starting with =, unbalanced braces, an unknown total, a {name:*} in a row formula, and a {name} in a totals formula. A run fails, naming the sheet and column, when a reference names a column the query doesn’t return or a total doesn’t fit its column. In a template, row formulas work in table blocks and use the block’s columns (a reference to a column the block doesn’t place is an error); a single-cell binding takes the value, not the formula. Templates don’t take total: put the totals row under the block in the template, and DRE extends a SUM ending on the block’s row over every inserted row. Formulas written in the block’s row itself, in columns the block doesn’t fill (e.g. D5: =B5*C5 beside a block in A5:C5), are filled down to every inserted row the way Excel’s fill-down does: relative row references move (=B6*C6, and a running total =SUM(C$5:C5) becomes =SUM(C$5:C6)), absolute rows ($B$5, B$5) stay. Excel works out their values when the file is opened.

Start from a complete workbook project

Use xlsx for reports people inspect, filter and share. The monthly finance example includes SQL, lookup data, formatting and a workbook template. The minimum output is output: {format: xlsx}; declare xlsx in dependencies and provide one named result query. max_rows_per_sheet splits large results; set it below Excel’s limit when a recipient needs smaller sheets. totals_label labels the first unaggregated column of a totals row.

To fill an existing branded workbook, keep templates/monthly.xlsx in the project and map query results to sheet locations:

output:
  format: xlsx
  template:
    file: templates/monthly.xlsx
    bindings:
      - {sheet: Summary, cell: B2, value: "{{ run.date.prev_month }}"}
      - {sheet: Summary, cell: B3, query: summary, column: revenue}
      - sheet: Detail
        query: detail
        result_index: 1
        anchor: A5
        header: true
        columns: [region, revenue]

file is relative to the project root; sheet must exist in the template. A table uses anchor and optionally selected columns; a scalar uses cell with either value, or query and column. result_index is one-based and defaults to the last result set. Do not put both value and query on a scalar binding. The placeholder cells keep their template formatting unless explicitly overridden.

missing-template means the file path does not resolve from the project root. invalid-cell means a cell is not an Excel address such as B3. invalid-output-option covers malformed styles or formulas; format-failed reports a runtime mismatch such as a column absent from the query. Fix SQL aliases to match columns and formula references, then preview again. See template binding keys, error codes, format and Binding.