Excel

DataPipeline reads and writes Microsoft Excel workbooks through the classes in its excel package. ExcelReader turns the rows of a worksheet into records and ExcelWriter turns records into rows, so spreadsheets take part in everything else in this guide: filtering, transformation, lookups, aggregation, and conversion to and from the other data formats. Excel support is part of the core DataPipeline jar; no add-on dependency is needed.

Excel Documents and Providers

Readers and writers never touch files directly. They work through an ExcelDocument: open an existing workbook with open(File) or open(InputStream), or start from an empty one, then save(File) or save(OutputStream) whatever was written to it. One document can hold several worksheets (getSheetNames() lists them), can feed several readers or writers, and should be closed once you are done with it.

The document's ProviderType selects the workbook format and the library used behind the scenes (Apache POI or JExcelApi). new ExcelDocument() uses POI_XSSF; pass a different type to the constructor for other formats or for the streaming providers.

Provider	Format	Reads	Writes	Notes
POI_XSSF (default)	.xlsx (Excel 2007 and later)	Yes	Yes	Holds the whole workbook in memory.
POI_XSSF_SAX	.xlsx	Yes, streaming	Falls back to POI_XSSF	Reads row by row with little memory and opens the file read-only. Requires open(File).
POI_SXSSF	.xlsx	Falls back to POI_XSSF	Yes, streaming	Flushes rows to temporary files while writing, so memory stays flat.
POI	.xls (Excel 97 to 2003)	Yes	Yes	Apache POI's HSSF implementation.
JXL	.xls (Excel 97 to 2003)	Yes	Yes	JExcelApi. Does not support the data formats and cell styles described below.
POI_XSSFB	.xlsb (binary workbook)	Yes	No	Requires open(File).

Reading Excel Files

Open the workbook, give it to an ExcelReader, and run the reader like any other. Each row becomes a record. With setFieldNamesInFirstRow(true) the first row supplies the field names; otherwise the fields get the default names A, B, C, and so on, or the names you pass to setFieldNames(...).

Options on the reader control what is read and how:

• setSheetName(String) or setSheetIndex(int): the worksheet to read; the first sheet by default. To read a whole workbook, loop over document.getSheetNames() with a new reader per sheet.
• setStartingRow(int), setLastRow(int), and setStartingColumn(int): the range of cells to read; setSkipEmptyRows(true) drops blank rows.
• setEvaluateExpressions(boolean): formulas are evaluated and their results returned by default; turn this off to receive the formula text instead.
• setFailedExpressionStrategy(...): what happens when a formula cannot be evaluated: FAIL (the default, which aborts the job), SET_CACHED_VALUE (use the value Excel last calculated), SET_EXPRESSION, SET_NULL, or SET_EXCEPTION_MESSAGE.
• setUseSheetColumnCount(boolean): each record holds only the cells present on its row by default; enable this to give every record the sheet's full column count.
• setReadMetadata(true): also capture each cell's style and hyperlink as field session properties, readable through ExcelFieldMetadata.
• setAutoCloseDocument(true): close the document when the reader closes. Leave it off while other readers still need the document, and close it yourself.

Writing Excel Files

Writing is the mirror image: create a document, run a job into an ExcelWriter, then save the document. The writer adds a worksheet with the name given to setSheetName(String) and writes the field names as the first row unless you tell it not to.

Saving is separate from the job, so several jobs can write into one document, one writer per worksheet, before it is saved once.

Options on the writer:

• setFieldNamesInFirstRow(boolean): include (the default) or omit the header row.
• setFirstRowIndex(int) and setFirstColumnIndex(int): where on the sheet the output starts.
• setAutofitColumns(true) and setAutoFilterColumns(true): size columns to their content and add Excel's filter drop-downs to the header row.
• setFreezeRows(int) and setFreezeColumns(int): freeze panes; setFreezeRows(1) keeps the header visible while scrolling (POI providers only).
• setDataFormat(FieldType, String): the Excel number or date format for every cell of a field type, such as setDataFormat(FieldType.DATE, "yyyy-mm-dd") or setDataFormat(FieldType.DOUBLE, "#,##0.00"). Dates and decimals get sensible formats by default (POI providers only).
• setLargeCellHandler(LargeCellHandler): Excel cells hold at most 32,767 characters. A longer value fails the job by default (FAIL); choose TRUNCATE to cut it down or SKIP to leave the cell empty.

Streaming Large Workbooks

The default provider keeps the entire workbook on the heap, which is fine for everyday spreadsheets but not for hundreds of thousands of rows. Switch providers to stream instead: POI_XSSF_SAX reads rows as it parses them and POI_SXSSF writes rows out to temporary files as they arrive, so memory use stays flat whatever the size. The reader and writer code does not change.

Each streaming provider works in one direction only: POI_XSSF_SAX falls back to the in-memory provider when asked to write, and POI_SXSSF does the same when asked to read, so match the provider to the direction of each document.

Formatting Styles and Hyperlinks

With the POI providers, ExcelWriter can style cells and add hyperlinks without any POI code of your own. Register an ExcelCellStyleDecorator for header cells, for data cells, or both. Each one is paired with a FieldLocationPredicate that decides which cells it applies to: all of them, named fields, odd or even rows, negative numbers, a field type, and so on. Decorators combine with and(...).

Hyperlinks follow the same pattern through addHeaderHyperlink(...) and addDataHyperlink(...). Each takes a predicate and a function that builds an ExcelHyperlink for a cell from its FieldLocation: ExcelHyperlink.forUrl(...), forEmail(...), forFile(...), or forCell(sheetName, columnIndex, rowIndex) to jump to another part of the workbook.

For complete control, attach an ExcelFieldMetadata style or hyperlink to individual fields in a ProxyWriter and enable setWriteMetadata(true) on the writer. On the reading side, setReadMetadata(true) exposes the same information for the cells of an existing workbook.

Excel Examples

• Read from an Excel File and Write to Excel
• Use Streaming Excel for Reading and Use Streaming Excel for Writing
• Read a Binary Excel File (.xlsb)
• Write Excel Formatting and Styles and Read Excel Formatting and Styles
• Create Hyperlink in Excel and Read Hyperlink from Excel
• Freeze Panes in Excel
• Truncate Large Excel Cell Values
• Read BigDecimal and BigInteger from an Excel File
• Use Data Lineage with ExcelReader
• Write Excel to Amazon S3

See all Excel examples and the Excel Javadocs.