CODEWITHSUNDEEP
javaexcelapache-poi
hotmeatballsoup
hotmeatballsoup

Reputation: 595

Apache POI DataFormat limitations vs method invocations

Java 8 and Apache POI 4.x here. I've found evidence that you can only get 64K different data formats via dataFormat.getFormat(...). But does that mean you can call dataFormat.getFormat(...) any number of times, so long as your app is using 64K (or less) different data formats? Or does it mean you can only apply formatting (and thus call dataFormat.getFormat(...) 64K times before you start getting errors?

Upvotes: 0

Views: 385

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

The "Too many different cell formats" error results because of a Excel limitation on different cell formats. This only has indirectly to do with different data formats since a cell may have not only data formats set in it's cell format but font formats, border formats and interior formats (colors) too. The limitation says: You cannot have more than 64,000 different cell formats in one Excel workbook in current Excelversions. Different cell formats mean different in either data format, font format, border format, interior format, ...

So theoretically you could have more than 64,000 different data formats but you cannot use more than 64,000 of them in different cell styles of one workbook.

The class DataFormat of apache poi uses a short to index different data formats. Since a short has a max value of 32,767 and the index starts with 0, you cannot create more than 32,768 different data formats in one Workbook using apache poi.

Note: All this is about different formats. So you can call DataFormat.getFormat as often as needed without getting an error, as long as there are no more than 32,768 different data formats needed.

For example you can call CellUtil.setCellStyleProperty:

...
Workbook workbook = ...
DataFormat dataFormat = workbook.createDataFormat();
...
... {
 Cell cell = ...
 ...
 CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat("#,##0.00"));
 ...
}
...

as often as needed, even in a loop for much more than 64,000 cells, as there dataFormat.getFormat("#,##0.00") always gets only the same data format index multiple times.

Upvotes: 2

Related Questions