Apache POI, creating new cells overrides the row style

16
votes

I'm using Apache POI to export data to a .xlsx file and I want to style some of the rows and cells contained in the file.

I'm using XSSF since the file is going to be read in Excel 2007+.

Basically, my problem is that I'm trying to set a row style like in the following example, which sets a black foreground color for the entire row at index 0. It works fine, but whenever I create a new cell, the newly created cell has no style, as if it's overriding the row style I specified.

Here's a code snippet to demonstrate what I'm doing:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("mySheet");
XSSFRow row = sheet.createRow(0);

XSSFCellStyle myStyle = wb.createCellStyle();           

myStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 255)));
myStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

row.setRowStyle(myStyle); //This works, the whole row is now black

row.createCell(0); // This cell doesn't have a style, the rest of the line stays stylized
row.getCell(0).setCellValue("Test");

I also tried *row.createCell(0, Cell.CELL_TYPE_STRING);*, but it didn't change anything.

What is the correct way of accomplishing what I want to do? I wanted to do it this way so I didn't have to set each cell's style after creating it since all cells on the same row have the same style.

3
javaexcelapache-poi
try calling row.createCell() before row.setRowStyle(myStyle); – Bhavik Shah
Yeah I already tried that and the order does't matter it seems – Adam Smith
row.setCellValue("Test"); gives me a compile time error check! – Bhavik Shah
@BhavikShah This is an error when I made this example for my post, see the updated question – Adam Smith
more than 7 years later the setRowStyle still doesn't work in an intuitive way :D I am having the same issue right now and it bothers me that setRowStyle doesn't provide a default value for the cells in that row (even if set after cell creation) – aBnormaLz

3 Answers

12
votes

Set the style into newly created cell as well e.g. below:

    XSSFCell newCell = row.createCell(0);
    newCell.setCellStyle(myStyle);
10
votes

Even you create a row with style, it will not effect to created cell of its. The create cell have their own cell style. The row style will not override to cell style automatically. If you would like use row style in cell, you have to set again.

Even if you set row style at end, it will not effect to cell.

Example

CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
Row r = sheet.createRow(0);
r.setRowStyle(rowStyle);

Cell c1 = r.createCell(0);
c1.setCellValue("Test 1");
c1.setCellStyle(rowStyle);
2
votes

I'm agree that "setRowStyle" doesn't work as it should be.

I created my own function to apply a style to a range ( could be a row or multiple row )

public void applyStyleToRange(Sheet sheet, CellStyle style, int rowStart, int colStart, int rowEnd, int colEnd) {
    for (int r = rowStart; r <= rowEnd; r++) {
        for (int c = colStart; c <= colEnd; c++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                Cell cell = row.getCell(c);

                if (cell != null) {
                    cell.setCellStyle(style);
                }
            }
        }
    }
}