When generating Excel Sheets it is sometimes necessary to remove rows which match a given criteria afterwards. The first idea for this would be to use removeRow(Row row) but this has the problem that it deletes all the row contents and leaves the empty row in your sheet which is probably not what you want. To remove the rows as a whole the shiftRows(int startRow, int endRow, int n) is needed.
Lets say we have a xls file with column A being the title which is always filled but we want to remove every row in which the data column B is not filled. For this case the following snippet would be appropriate …
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
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.
