Modifying existing xlsx table using apache poi without style/format lose

Question

I need to write data to existing template with formatting stored in it, but styles(font, color, borders) disappear. What I done? load table and get sheet in it:

Workbook xlsxWorkbook = WorkbookFactory.create(new File(filename));
xlsxWorkbook.getSheet(sheetname);

modify like this:

while (condition) {
    Row dataRow = xlsxSheet.getRow(rowIndex);
    if (dataRow == null) {
        dataRow = xlsxSheet.createRow(rowIndex);
    }
    rowIndex++;
    int colIndex = 0;

    for (String colName : colNames) {
        Cell tmp = dataRow.getCell(colIndex);
        if (tmp == null)
            tmp = dataRow.createCell(colIndex);

        tmp.setCellValue(value);

        colIndex++;
    }
}

write back to file:

write(params.getProperty("xlsx-filename"), xlsxSheet.getWorkbook());

What I know about this? Earlier I using same without null check, in that case all styles disappear. Now, if cell and row are not null(not recreated in java code), format saves, but cases when they pre initialized are very different. sometimes data initialize it, sometimes not, sometimes cells with specifically established style are pre initialized.

p.s. sorry for my eng

what is the value of rowIndex. Where is it defined in that code? you wanna add row existing sheet or change row? — krezus
row index is just row iterator, defined one line earlier "int rowIndex = 0;". i want to write data on clear template — V. Bobrikov
want to write data on clear template and also want modify? so where is the style? sorry I am confused what are you trying to achive based on your posted code? — krezus
template contains only style(all colored in yellow for ex), template only for styles that will be in result - styled data — V. Bobrikov
If cell style is set using Excel then this can either be a style for the one existing cell or a row style for all cells in that row or a column style for all cells in that column. In stackoverflow.com/questions/42081288/… I provided a method to get the preferred style for a cell in a sheet. This gets either the current style or the row style or the column style for a given cell. — Axel Richter

krezus krezus · Accepted Answer · 2018-01-26T08:47:35

As far as i understand, you would like to protect cell style whether you add new row or modify existing row. In order to implement both of them, you can get style of each cell or complete row and set new or modified one.

Row dataRow = xlsxSheet.getRow(rowIndex);
CellStyle  currentRowStyle = dataRow .getRowStyle()
Cell tmp = dataRow.getCell(colIndex);
CellStyle currentStyle = tmp.getCellStyle();

if you want to add new row, you can get the last row number with this instead of checking for null value:

int rowCount = sheet.getLastRowNum();

Modifying existing xlsx table using apache poi without style/format lose

1 Answers