Limits and performance

This section lists every known limit and behaviour that may surprise, including those that were deliberately left as they are and documented instead of being turned into errors.

Excel limits

Case	What happens	What to do
Date or date-time before 1900-01-01	Excel cannot represent it: the cell contains -1 and is displayed as #####. No error is raised.	Convert such values to text with a converter.
NaN or infinite number	The cell becomes an Excel error: #NUM! for NaN, #DIV/0! for infinity. No error is raised. A BigInteger or BigDecimal too large for a double becomes infinite.	Map them, for example to CellValue.blank().
Number with more than 15 significant digits	Precision is lost: Excel stores 64-bit floating point numbers. A long above 2^53 or a large or very precise BigDecimal is rounded. No error is raised.	Write identifiers and exact decimals as text.
Text longer than 32,767 characters	Excel cannot store it: SheetsmithGenerationException.	Shorten the text before writing it.
More than 1,048,576 rows in a sheet, title and header included	SheetsmithGenerationException before the sheet is written.	Split the data across several sheets.
Sheet named History	Excel reserves the name for change tracking and may refuse or repair the file. It is not rejected by sheetsmith.	Choose another sheet name.
Sheet name rules	Enforced by V-19 and V-20.	Clean names built from data.
More than 16,384 columns	Beyond the Excel limit; not checked by sheetsmith and not realistic for an annotated class.	None.
Cell styles (about 64,000 per file)	Not a practical limit: equal styles are shared, so the number of styles depends on the number of distinct styles, not on the number of cells.	None.

Value conversion

Behaviour	Explanation	What to do
float values show extra digits	Numbers are written through doubleValue(); a float such as 0.1f becomes 0.10000000149011612, visible with the General format.	Use double or BigDecimal, or set a number format such as 0.00.
BigDecimal scale is not preserved	2.50 is written as the number 2.5; trailing zeros are a display matter.	Set a format, for example 0.00.
Enums are written with name()	toString() overrides are ignored.	Register a converter (section 9.9).
Fractions of a second	Excel stores times with a precision of about a millisecond; finer precision of LocalDateTime is lost.	None, or write as text.
Text that looks like a number or a formula	Text values are always written as text cells: "00123" keeps its zeros, "=SUM(A1:A2)" is not a formula.	None: this is intended.
A getter with a non-matching return type	The getter is ignored and the field is read directly (section 4.2.1).	Align the getter return type with the field type.
Default formats for blank cells	Empty cells (null values or CellValue.blank()) receive no default format, only the formats set by styles.	None.
numberFormat applies to integers too	A default #,##0.00 shows decimals on integer columns.	Give integer columns format = "0" or "#,##0".

Very large exports

The whole workbook is built in memory before it is written, so memory grows with the number of cells. The following measurements come from the acceptance tests of version 1.0.0, with a 4 GB heap and 10 columns. They are indicative and depend on the data, the JVM and the hardware.

Measure	Value
Heap needed	about 1.4 GB per million cells
Generation time	about 10 seconds every 100,000 rows
Effect of automatic column sizing	multiplies the time by about 2.4
Largest successful export	350,000 rows (3.5 million cells)
Failed export	500,000 rows, OutOfMemoryError
byte[] vs OutputStream	no measurable difference

Recommendations:

1. Measure with realistic volumes and size the heap accordingly.
2. For large sheets, set autoSizeColumns = false and give each column an explicit width.
3. Writing to a stream instead of returning a byte[] does not reduce memory significantly: the workbook, not the file, takes most of it.
4. Concurrent large exports add up: limit their concurrency (for example with a bounded executor or a semaphore) on memory-constrained services.
5. Split very large datasets across several workbooks rather than several sheets of one workbook, since all the sheets of a workbook are in memory together.

A streaming mode for very large volumes is not part of version 1.0.0: see section 15.

Automatic column sizing

Behaviour	Explanation
Depends on installed fonts	Apache POI measures text with Java AWT fonts. On servers or containers without fonts or without the AWT native libraries, measuring fails and sheetsmith falls back to an estimate based on the number of characters of the displayed values, plus 2, capped at 255. The estimate does not account for proportional fonts, bold text or font sizes, so columns may be slightly wider or narrower than an exact measure.
Title excluded, with one exception	The merged title never widens its columns. With a single column and a title, however, there is nothing to merge: the exact measurement of Apache POI then includes the title text, and the column becomes as wide as the title, while the fallback estimate ignores the title. Set an explicit width on the column when this matters.
Cost	Sizing reads every cell of the column; on large sheets it dominates the generation time.
Width cap	The maximum width is 255 characters.

To make the exact measurement available on Linux containers, install a font package and fontconfig in the image (for example fontconfig and a DejaVu or Liberation font package), and run the JVM with -Djava.awt.headless=true.

Layout and styles

Behaviour	Explanation
Row heights are not set	Rows with wrapped text keep the default height until the spreadsheet application adjusts them.
locked and hidden have no visible effect	They apply only to protected sheets, and sheetsmith does not protect sheets.
Indexed colours vary	They depend on the palette of the application that opens the file. Prefer hexadecimal colours.
Font availability	A font name not installed on the reader’s machine is substituted by the spreadsheet application.
Regional display	Thousand and decimal separators, and month and day names, follow the regional settings of the reader.
Data formats are not validated	An invalid format code is written as it is; Excel may ignore it or report the file as needing repair.
Empty data list	The sheet has the title (if any) and the header; the auto-filter covers the header only; the outer frame closes below the header.

Validation and caching

Behaviour	Explanation
Invalid classes are re-validated at every call	They are not cached, so the cost of validation is paid again until the class is fixed.
A failing field converter creation is retried	It is not remembered as failed; it is attempted again at each validation or generation.
Startup validation uses the context bean	A sheet class valid only with a converter registered elsewhere (for example on a different generator) is reported.
Metadata cache is shared	All generators of the same class loader share the metadata cache; converter bindings are per generator.