# Excel (xlsx)

> The xlsx format: column formats, formulas, totals rows and templates.

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

```yaml
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 `#####`.

```yaml
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.

```yaml
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.

```yaml
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.

```yaml
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](https://github.com/get-dre/dre/blob/v0.5.1/examples/monthly-finance/) 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:

```yaml
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](https://getdre.com/docs/reference-report/#outputtemplatebindings), [error codes](https://getdre.com/docs/reference-error-codes/), [format](https://getdre.com/docs/glossary/#format) and [Binding](https://getdre.com/docs/glossary/#binding).

[Edit page](https://github.com/get-dre/dre/edit/master/docs/plugin-xlsx.md)

[Previous  
Parquet](https://getdre.com/docs/plugin-parquet/)[Next  
Amazon S3](https://getdre.com/docs/plugin-s3/)