Read Excel file

The “Read Excel file (xls/xlsx)” service task reads one sheet of an Excel file and returns its rows as a list of records (records). Supported formats are .xls (Excel 97–2003), .xlsx and .xlsm. The format is detected from the file content, not from the file extension.

The task covers two cases:

Clean lists with a header row, e.g. article or price lists. header_row is all you need.
Excel in print layout, e.g. invoices or bills of quantities with page headers, subtotals, titles and texts spanning several rows. Rules describe what a row is.

Values come out the way Excel displays them: formulas return the result stored in the file, and numbers can be rounded according to their number format (113.9396 with format 0.00 becomes 113.94).

Input parameters

{
  "document": {
    "referenceId": "string",
    "filename": "invoice.xls"
  },
  "options": {
    "sheet": 0,
    "header_row": 1
  }
}

document: Reference to the uploaded Excel file (fileReference). Required.
options: Optional. Describes which sheet and which rows are read, and how. May also be passed as a JSON string. Without options, the first sheet is read; every non-empty row becomes a record with the fields A, B, C, …

Simple list

{
  "document": { "referenceId": "..." },
  "options": { "sheet": "Articles", "header_row": 1 }
}

Result: one record per row from row 2 onwards, field names taken from the header row, e.g. { "Article": "A-1", "Description": "Screw", "Price": 0.15, "Date": "2026-02-15" }.

Excel in print layout

Example: invoice items from the header row “Bezeichnung” down to “Nettosumme”, skipping page headers and subtotals, attaching titles (“3.0 Concrete work”) and intermediate texts (“Daywork report of …”) to the following items.

{
  "sheet": 0,
  "start_after": { "column": "B", "matches": "^(Artikel)?bezeichnung$", "flags": "i" },
  "stop_at": { "any_column": "^Nettosumme$" },
  "scan_columns": "A:F",
  "columns": {
    "oz":       { "col": "A", "as": "text" },
    "text":     { "col": "B", "as": "text" },
    "quantity": { "col": "C", "as": "number", "round": "display" },
    "unit":     { "col": "D", "as": "text" },
    "price":    { "col": "E", "as": "number" }
  },
  "rules": [
    { "name": "Page header", "type": "skip", "if": [{ "any_column": "Seite\\s*\\d+" }] },
    { "name": "Subtotal",    "type": "skip", "if": [{ "any_column": "^(Summe|Übertrag)\\b", "flags": "i" }] },
    { "name": "Item",        "type": "position",
      "if": [{ "column": "quantity", "is": "numeric" }, { "column": "unit", "is": "filled" }] },
    { "name": "Title",       "type": "title", "if": [{ "column": "oz", "matches": "^\\d+\\.0$" }] },
    { "name": "Text",        "type": "text",  "if": [{ "column": "text", "is": "filled" }] }
  ],
  "carry": [
    { "name": "title",   "from_type": "title", "value": "{oz} {text}" },
    { "name": "context", "from_type": "text", "accumulate": true, "reset_on": ["empty_row", "type:title"] }
  ]
}

How it works

Read: For every cell, type, value, number format and displayed text are determined.
Range: Where the table starts and ends (start_row, start_after, end_row, stop_at). Repeating blocks such as page headers can be skipped with skip_blocks.
Columns: columns assigns names and sets type, rounding and replacements.
Rules: For each row the rules are checked in order; the first match determines the row type. Types can be named freely. Rows of type skip are dropped; if no rule matches, the type is unclear.
Carry: carry attaches values to the following rows (e.g. the current title).
Merge: merge attaches text rows to the next or previous row of a given type (e.g. long texts spanning several rows).
Result: records with all records (without skip).

Empty rows never become records. They can, however, reset carried values and end merges (empty_row). Empty rows directly after skipped rows don't count, so a page break in the middle of an item doesn't interrupt anything.

Options

Option	Meaning
`sheet`	Sheet as index (`0` = first), name (`"Invoice"`) or pattern (`{"matches": "^Offer"}`). Default `0`.
`header_row`	Row number of the header row. Column names become field names; reading starts in the row below.
`start_row` / `end_row`	Fixed first / last row (1-based as in Excel).
`start_after`	Condition: reading starts in the row after the first match. No match: no records, warning.
`stop_at`	Condition: reading stops before the first match.
`scan_columns`	Column range for `any_column` and empty-row detection, e.g. `"A:F"`. Default: all.
`columns`	Field name → column. Short form `"B"` or object `{ "col": "B", "as": …, "round": …, "replace": … }`.
`rules`	List of rules `{ "name", "type", "if": [conditions], "any": [conditions], "extract": [...] }`.
`default_type`	Type if no rule matches. Default `"unclear"`; these rows are also listed in `unclear`.
`skip_types`	Types that are dropped. Default `["skip"]`.
`skip_blocks`	Skip blocks: `{ "start": condition, "rows": 4 }` or `{ "start": condition, "end": condition }`.
`carry`	Attach values to following rows, see below.
`lookups`	Lookup tables built from a row range, e.g. titles from a cover sheet.
`merge`	Merge text rows, see below.
`output_types`	Only these types in `records`, e.g. `["position"]`.
`locale`	`"de"` (default) or `"en"`: decimal and thousands separators for displayed texts.
`include_rows`	`true`: additionally `rows` with every row read and its classification (for checking).
`detect_missing_formula_values`	Default `true`: detect formulas without a stored result (.xlsx only).
`strict`	`true`: the task fails if there are unclear rows or formulas without a stored result.

Columns

Property	Meaning
`col`	Column letter, e.g. `"C"`.
`as`	`text`: displayed text (e.g. item number “3.091”, even if Excel stored it as a number). `number`: number, also from text such as “1.234,56”. `date`: date as `YYYY-MM-DD`, also from text such as “15.02.2026”. `auto` (default): the value as stored in the cell.
`round`	`"display"`: round as displayed (default for `number`). Number: fixed decimal places. The unrounded value is then in `_raw`.
`replace`	List `[[regex, replacement], …]` for texts, e.g. `[["^-$", ""]]` so a bullet dash isn't taken as an item number.
`trim`	Default `true`: strip surrounding whitespace and collapse repeated whitespace.

Conditions

Conditions are used in if (all must match), any (one must match), start_after, stop_at, skip_blocks, only_if and only_if_target. They can be nested with all, any and not.

Form	checks
`{ "column": "quantity", "is": "numeric" }`	Type: `empty`, `filled`, `number`, `numeric` (number or number as text), `text`, `date`, `error`. Also as a list.
`{ "column": "oz", "matches": "^\\d+\\.0$" }`	Regular expression on the displayed text. `flags: "i"` ignores case. Likewise `not_matches`, `equals`.
`{ "column": "C", "gt": 0 }`	Numeric comparison `gt` / `lt`.
`{ "any_column": "Seite\\s*\\d+" }`	Any column matches (optionally limited with `columns: "A:F"`).
`{ "row": "114-200" }`	Row number or range.
`{ "not": { … } }`	Negation.

column takes a field name from columns or a column letter.

extract splits a value into fields using a regex with named groups. For example, { "field": "oz", "pattern": "^(?P<oz>A[\\d.]*\\d)(?P<text>.*)$" } turns “A4.01Alternativposition zu 4.01” into the item number A4.01 and the text “Alternativposition zu 4.01”.

Carry

{ "name": "context", "from_type": "text", "value": "{text}", "accumulate": true, "reset_on": ["empty_row", "type:title"] }

from_type: rows of this type set the value.
value: template from fields of the row, e.g. "{oz} {text}".
accumulate: true collects all values in a list; otherwise the last one wins.
reset_on: empty_row and/or type:<type> reset the value.
fallback_lookup / fallback_key: if the value is missing, it is looked up in a table from lookups.

Every record receives the current value under name.

Merge

{ "from_type": "text", "into_type": "position", "direction": "next", "take_first": ["oz"], "break_on": ["empty_row", "type:title"], "dehyphenate": true }

direction: "next": text rows are held back and prepended to the next row of type into_type (long text above the quantity row).
direction: "previous": text rows directly after a row of type into_type are appended to it.
fields: which fields are joined. Default ["text"]. separator: default space.
take_first: fields taken from the first held-back row if the target row doesn't have them.
only_if (condition on the text row), only_if_target (condition on the target row).
break_on: empty_row and/or type:<type> end holding back.
dehyphenate: true joins hyphenated words (“Hoch-” + “lochziegeln” → “Hochlochziegeln”) but leaves “Beton- und” as is.

Held-back rows that are not merged remain records of their own.

Output

{
  "status": 200,
  "format": "xls",
  "sheet": "Rechnung",
  "sheets": ["Rechnung", "Aufmaß"],
  "start_row": 117,
  "end_row": 325,
  "stopped_at": null,
  "records": [
    {
      "_row": 125, "_rows": [125], "_type": "position", "_rule": "Item",
      "oz": "3.01", "text": "Frostriegel", "quantity": 3.97, "unit": "m³", "price": 85.5,
      "title": "3.0 Beton- und Stahlbetonarbeiten", "context": [],
      "_raw": { "quantity": 3.9699999999999998 }
    }
  ],
  "total": 1,
  "counts": { "position": 1, "skip": 10 },
  "unclear": [],
  "warnings": [],
  "metadata": { "filename": "invoice.xls", "inputBytes": 173056, "processingTimeMs": 120, "engine": "xlrd" }
}

records: the records. _row is the row in Excel, _rows all rows the record is made of, _type and _rule show which rule matched. _raw holds unrounded values, _notes notes on the row.
counts: number of rows per type; merged rows appear as “… (angehängt)”.
unclear: rows no rule matched, with their cell contents.
warnings: e.g. “start marker not found” or formulas without a stored result.

Map to invoice items (JSONata)

{
  "app_invoice_items_custom": [records[_type = "position"].{
    "app_article_no": oz,
    "app_description": text,
    "app_amount": quantity,
    "app_unit": unit,
    "app_item_price_net": price,
    "app_vat_percentage": 19
  }]
}

Notes

Formula results: The task doesn't recalculate formulas; it reads the result Excel stores in the file when saving. .xls files always contain it. An .xlsx that wasn't saved with Excel (e.g. exported from another program) can contain formulas without a result: the value is then null and a warning is added. Fix: open the file in Excel once and save it.
Rounding: With round: "display", the quantity matches what is printed. If the Excel file internally calculates with the unrounded value, an invoice created from it can differ from the Excel total by a few cents.
Not supported: .xlsb, .ods and password-protected files (error message “passwortgeschützt”). CSV files are read by Read CSV file into structured data.
If the options contain an error (unknown sheet, unknown column, invalid condition), the task fails with a message and is not retried.
When setting up a new template, set include_rows: true: rows shows every row with its classification.
Messages in warnings, _notes and errors are in German.