pfx-excel | Pricefx Knowledge Base

Summary: Reference for the pfx-excel Camel component — reading and writing Excel (XLSX) files in integration routes.

Source: https://pricefx.atlassian.net/wiki/spaces/IMDEV/pages/2006155328/pfx-excel+Component
Space: IM Doc in Progress (IMDEV)
Author: Michal Štěpán
Since: IM 1.1.18 (March 2020)

This component is used for easy Excel file manipulation. It supports reading (unmarshalling) and writing (marshalling) of both XLS and XLSX formats.

URI format	`pfx-excel:<method>`

Available Methods

Method	Since	Description
`marshal`	IM 1.1.18	Converts internal representation (List of Maps) into an Excel file
`unmarshal`	IM 1.1.18	Converts an Excel file (XLS/XLSX) into internal representation (List of Maps)
`streamingUnmarshal`	IM 3.6 (Oct 2022)	Streaming variant of unmarshal for large XLSX files with lower memory footprint

pfx-excel:marshal

Source: pfx-excel:marshal
Last Modified: Oct 21, 2025

Transforms internal representation into the Excel format.

Input: List<Map<String, String>> — a list of rows, where each row is a map of column name to value.
Output: byte[] — the generated Excel file content. Returns null when using fileToAppend (output is written directly to file).

Properties

Option	Type	Default	Since	Description
`hasHeaderRecord`	Boolean	`true`	IM 1.1.18	Indicates whether the input contains a header record (must be on the first row).
`skipHeaderRecord`	Boolean	`false`	IM 1.1.18	Determines whether to skip the header record in the output.
`header`	String		IM 1.1.18	Comma-separated list of headers from the input which should be present in the output. When set, only the specified columns are included.
`format`	String	`xlsx`	IM 1.1.18	Sets the format of the output file. Options: `xlsx`, `xls`.
`sheetIndex`	Integer	`0`	IM 1.1.18	Index of the sheet to write data to.
`sheetName`	String		IM 1.1.18	Name of the sheet to write data to. If filled, takes precedence over `sheetIndex`.
`fileToAppend`	String		IM 5.3 (Feb 2024)	Full path to an existing Excel file to append data to. When set, data is appended to the target file instead of creating a new one. Uses `SXSSFWorkbook` for memory-efficient streaming writes. Supports split operations — state is preserved across split exchanges. Parent directories are created automatically if they don't exist.
`dataConversionMode`	Enum	`AUTO`	IM 6.0.13 (Oct 2025)	Controls automatic data type conversion for cell values. `AUTO` — attempts to parse values as integers and writes them as numeric cells (falls back to string on failure). `NONE` — writes all values as string cells without any conversion.

Data Type Handling

When writing cell values, the behavior depends on dataConversionMode:

Mode	Behavior
`AUTO` (default)	Attempts to parse each value as an integer. If successful, writes a numeric cell; otherwise writes a string cell.
`NONE`	All values are written as string cells regardless of content. Useful when leading zeros or specific formatting must be preserved (e.g., SKU codes like `00010`).

Examples

Marshal data into Excel format (default)

XML

<route>
  <from uri="direct:start"/>
  <setBody>
    <groovy>[[sku: 'sku', name: 'name'], [sku: 10, name: 'BMW'], [sku: 20, name: 'AUDI']]</groovy>
  </setBody>
  <to uri="pfx-excel:marshal"/>
</route>

sku	name
10	BMW
20	AUDI

Marshal data without header record

XML

<route>
  <from uri="direct:start"/>
  <setBody>
    <groovy>[[sku: 10, name: 'BMW'], [sku: 20, name: 'AUDI']]</groovy>
  </setBody>
  <to uri="pfx-excel:marshal?header=sku,name&amp;hasHeaderRecord=false"/>
</route>

sku	name
10	BMW
20	AUDI

Marshal with dataConversionMode=NONE (preserve leading zeros)

XML

<route>
  <from uri="direct:start"/>
  <setBody>
    <groovy>[[sku: 00010, name: 'BMW'], [sku: 020, name: 'AUDI']]</groovy>
  </setBody>
  <to uri="pfx-excel:marshal?header=sku,name&amp;hasHeaderRecord=false&amp;dataConversionMode=NONE"/>
</route>

All values are written as strings — no numeric conversion is applied.

Append data to an existing Excel file

XML

<route>
  <from uri="direct:start"/>
  <split>
    <simple>${body}</simple>
    <to uri="pfx-excel:marshal?fileToAppend=/tmp/output/result.xlsx"/>
  </split>
</route>

Each split chunk is appended to /tmp/output/result.xlsx. The workbook state is preserved across splits and finalized when the last split is processed (Exchange.SPLIT_COMPLETE).

pfx-excel:unmarshal

Source: pfx-excel:unmarshal
Last Modified: Aug 03, 2023

Transforms the given Excel file into internal structure.

Input: InputStream — the Excel file content (XLS or XLSX).
Output: List<Map<String, String>> — a list of rows, where each row is a map of column name to value.

The format of the input (XLS vs XLSX) is automatically detected using Apache POI's FileMagic (stream-safe detection via mark/reset since IM 7.3.0).

Properties

Option	Type	Default	Since	Description
`hasHeaderRecord`	Boolean	`true`	IM 1.1.18	Indicates whether the input contains a header record (must be on the first row).
`skipHeaderRecord`	Boolean	`false`	IM 1.1.18	Determines whether to skip the header record in the output.
`header`	String		IM 1.1.18	Comma-separated list of headers to use. When `hasHeaderRecord=false`, this defines the column names for the output maps. When `hasHeaderRecord=true`, this filters which columns appear in the output.
`sheetIndex`	Integer	`0`	IM 1.1.18	Index of the sheet with the required data.
`sheetName`	String		IM 1.1.18	Name of the sheet with the required data. If filled, takes precedence over `sheetIndex`.

Numeric Value Handling (since IM 7.2.0, March 2026)

Cell values are extracted with the following rules:

Cell Type	Handling
STRING	Returned as-is
BOOLEAN	Converted to `"true"` / `"false"`
NUMERIC (whole number)	Converted to long if within `Long.MIN_VALUE` to `Long.MAX_VALUE` range
NUMERIC (decimal)	Formatted using `BigDecimal.toPlainString()` to avoid scientific notation (e.g., `0.000178495` instead of `1.78495E-4`)
NUMERIC (NaN / Infinity)	Returned as `"NaN"` / `"Infinity"`
NUMERIC (date)	Formatted as date string
BLANK / OTHER	Empty string `""`

Examples

Transform Excel file into internal representation (default)

The default configuration expects that the data resides in the first sheet (index 0) and contains a header row. Both XLS and XLSX formats are supported — the format is auto-detected.

XML

<to uri="pfx-excel:unmarshal"/>

Given an Excel file with:

sku	name
10	BMW
20	AUDI

Output:

Groovy

[[sku: 'sku', name: 'name'], [sku: '10', name: 'BMW'], [sku: '20', name: 'AUDI']]

Transform Excel file without header

When the Excel file has no header row, provide column names via the header parameter:

XML

<to uri="pfx-excel:unmarshal?header=sku,name&amp;hasHeaderRecord=false"/>

Output:

Groovy

[[sku: '10', name: 'BMW'], [sku: '20', name: 'AUDI']]

Read from a specific sheet by name

XML

<to uri="pfx-excel:unmarshal?sheetName=Products"/>

pfx-excel:streamingUnmarshal

Source: pfx-excel:streamingUnmarshal
Last Modified: Aug 03, 2023
Since: IM 3.6 (Oct 2022)

Transforms the given Excel file into internal structure in a fully streaming way.

Input: InputStream — the Excel file content (XLSX only).
Output: Stream<Map<String, String>> — a lazy stream of rows for memory-efficient processing.

Key differences from unmarshal:

	`unmarshal`	`streamingUnmarshal`
Format	XLS and XLSX	XLSX only
Return type	`List<Map>` (all rows in memory)	`Stream<Map>` (lazy, on-demand)
Memory footprint	Full file loaded	Substantially lower
Use case	Small to medium files	Large files (thousands of rows)

Properties

Option	Type	Default	Since	Description
`hasHeaderRecord`	Boolean	`true`	IM 3.6	Indicates whether the input contains a header record (must be on the first row).
`skipHeaderRecord`	Boolean	`false`	IM 3.6	Determines whether to skip the header record in the output.
`header`	String		IM 3.6	Comma-separated list of headers to use. Same behavior as in `unmarshal`.
`sheetIndex`	Integer	`0`	IM 3.6	Index of the sheet with the required data.
`sheetName`	String		IM 3.6	Name of the sheet with the required data. If filled, takes precedence over `sheetIndex`.

Limitations

XLSX only — XLS files cannot be streamed. Use unmarshal for XLS files.
Streaming resources must be properly closed. The component handles cleanup automatically via CleanupFunction callbacks in PfxExcelProducer.doStop().

Examples

Transform Excel file into internal representation streaming (default)

The default configuration expects that the data resides in the first sheet (index 0) and contains a header row. The input must be an XLSX file.

XML

<to uri="pfx-excel:streamingUnmarshal"/>

Output:

Groovy

[[sku: 'sku', name: 'name'], [sku: '10', name: 'BMW'], [sku: '20', name: 'AUDI']]

Stream large file and process in batches

XML

<route>
  <from uri="file:inbox?fileName=large-products.xlsx"/>
  <to uri="pfx-excel:streamingUnmarshal"/>
  <!-- body is now a Stream<Map<String, String>> — process lazily -->
  <split streaming="true">
    <simple>${body}</simple>
    <to uri="direct:processRow"/>
  </split>
</route>

Technical Notes

Supported Formats

Format	Extension	unmarshal	streamingUnmarshal	marshal
OOXML (Office Open XML)	`.xlsx`	Yes	Yes	Yes (default)
OLE2 (Binary Excel)	`.xls`	Yes	No	Yes (`format=xls`)

Format auto-detection for unmarshal uses Apache POI's FileMagic — it peeks at the stream's first bytes via mark/reset without buffering the entire file (since IM 7.3.0, March 2026).

Streaming Library

The streaming reader library was updated from xlsx-streamer (monitorjbl) v2.2.0 to excel-streaming-reader (github.pjfanning) for Apache POI 5.x compatibility (IM 7.3.0, March 2026).

Version History

Version	Date	Change
IM 1.1.18	March 2020	Initial component with `marshal` and `unmarshal` methods
IM 1.2	2020	Package refactoring
IM 3.6	October 2022	Added `streamingUnmarshal` method for large XLSX files
IM 5.3	February 2024	Added `fileToAppend` property for append-to-file support in marshal
IM 6.0.13	October 2025	Added `dataConversionMode` property (AUTO/NONE) for marshal
IM 7.3.0	March 2026	Migrated to POI 5.x, replaced streaming library, improved format auto-detection via FileMagic
IM 7.2.0	March 2026	Fixed scientific notation and numeric edge cases (NaN, Infinity, long overflow) in cell value extraction