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

Question

I am using Apache POI to write data to an Excel template. The template contains only the headings in the first row, but I also applied specific styles to the entire column (for example, accounting and percentage).

It would be nice to use these formats when I write data to new cells. But if I use createRowand methods createCell, the cell format is overwritten, and I get General for all cells. If I try to use getRowand getCellinstead, I throw a NullPointerException, getting an empty cell.

Is there a way to use the previously saved cell formatting saved in the template? Or am I stuck in setting data format via API?

Thank you for your help.

Answer 1

If you apply specific styles to the entire column, you can get this CellStyleusing Sheet getColumnStylemethod , passing it the index of the column based on 0. It retrieves a regular object CellStylethat can be used anywhere else CellStyle, for example, in method . Cell setCellStyle

To avoid NullPointerExceptionboth getRowand getCellmay return null, if the string or the cell does not exist, respectively. You will need to call createRowand / or createCellto create Cellwhich you can always call setCellStyle.

Answer 2

cell.setCellStyle(sheet.getColumnStyle(index) works good.