Apache POI, creating new cells overrides the row style

Question

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.

try calling _row.createCell()_ before _row.setRowStyle(myStyle);_ — Bhavik Shah, Oct 31 '12 at 05:01
Yeah I already tried that and the order does't matter it seems — Jumbala, Oct 31 '12 at 05:02
_row.setCellValue("Test");_ gives me a compile time error check! — Bhavik Shah, Oct 31 '12 at 05:13
@BhavikShah This is an error when I made this example for my post, see the updated question — Jumbala, Oct 31 '12 at 05:17
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, Jul 27 '20 at 12:22

score 12 · Accepted Answer · answered Oct 31 '12 at 05:11

12

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

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

answered Oct 31 '12 at 05:11

Yogendra Singh

33,927
6
63
73

12

I know I can do that as I mentioned in my question, but I don't really see what the point of setting the row style is if I have to manually set each cell's style afterwards anyway. – Jumbala Oct 31 '12 at 05:19
1

@AdamSmith: I doubt if the newly created cell inherits the properties from parent. – Yogendra Singh Oct 31 '12 at 05:28
I guess I'll have to do it this way then. I thought it worked like when I do it manually in Excel (set a color for the whole row, then when you put data in a cell the style stays). Thanks for your answer! I guess what I'm asking is just not possible. – Jumbala Oct 31 '12 at 11:24
@adam-smith Assuming `setRowStyle` works the same as `setDefaultColumnStyle`: This actually sets the (default) style for cells that are added manually *after the workbook has been exported already* (i.e. by a human). – Auke Jan 25 '17 at 09:53

score 10 · Answer 2 · answered Oct 31 '12 at 05:33

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);

@mateus-viccari Assuming `setRowStyle` works the same as `setDefaultColumnStyle`: This actually sets the (default) style for cells that are added manually *after the workbook has been exported already* (i.e. by a human). — Auke, Jan 25 '17 at 09:51

amdev · Answer 3 · 2019-06-18T07:05:59.043

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);
                }
            }
        }
    }
}

Apache POI, creating new cells overrides the row style

3 Answers3