Using CellUtil with POI to avoid too many cell styles in Excel

Using CellUtil with POI to avoid too many cell styles in Excel

https://poi.apache.org
https://poi.apache.org

When you create Excel documents with the popular Apache POI library it is possible that you encounter a problem where Excel tells you there are “Too many different cell formats”. A possible source for this is that you probably have a loop structure in which you define a new cell style for every cell you create.
poi-cellstyle-loop

Excel is limited in the amount of cell styles it can handle in a single document (4000 per document). Therefore the above code would break. The easy fix would be to define the cell style outside of your loop and reuse it.
But sometimes you want to create different cell styles depending on a line number for example. In this case POI comes with a handy utility class named CellUtil that allows you to modify existing cell styles without creating a new one. Internally it has to create a new cell style and saves it in a map. If you make the same modification at any other place in your code again it will iterate that map, find the previously created style and reuses it. Therefore the same style is only created once. The usage is quite simple and would adapt to the sample above as follows

poi cellUtil

The code above will add a border to the bottom of every second cell created but because it is every time the same modification CellUtil will only create one additional style and apply this every time again.

One thought on “Using CellUtil with POI to avoid too many cell styles in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.