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

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.

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.

This entry was posted in Allgemein, coding, english, howto, Java and tagged , , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">