Apache POI - Writing to Excel template without overwriting existing cell formatting

Question

I am using Apache POI to write data to an Excel template. The template only contains headers on the first row, but I also applied specific styles to the entire column (e.g. Accounting and Percentage).

It would be nice to use these formats when I write data to the new cells. But if I use the createRow and createCell methods, the cell format is overwritten and I get General for all the cells. If I try to use getRow and getCell instead, I run into NullPointerExceptions retrieving the blank cell.

Is there a way to use the pre-existing cell formatting saved in the template? Or am I stuck setting the data format myself through the API?

Thanks for your help.

Answer 1

If you have applied specific styles to an entire column, then you can retrieve that CellStyle with Sheet's getColumnStyle method, passing it the 0-based column index. It retrieves a normal CellStyle object that can be used anywhere else CellStyles are accepted, such as in Cell's setCellStyle method.

For avoiding the NullPointerException, both getRow and getCell may return null if the row or cell doesn't exist, respectively. You will need to call createRow and/or createCell to create the Cell, on which you can always call setCellStyle.