CODEWITHSUNDEEP
javaexcelapache-poi
Jumbala
Jumbala

Reputation: 4944

Apache POI, creating new cells overrides the row style

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.

Upvotes: 17

Views: 33190

Answers (3)

amdev
amdev

Reputation: 3249

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

Upvotes: 2

Zaw Than oo
Zaw Than oo

Reputation: 9935

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

Upvotes: 10

Yogendra Singh
Yogendra Singh

Reputation: 34367

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

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

Upvotes: 12

Related Questions