0

The problem I'm facing is when I'm trying to introduce any styling to any particular cell, the same styling gets applied to entire workbook, even to the sheets which I'm not accessing at all(the additional sheets present at the end).

e.g., when I'm using setFillForegroundColor() method for headerCell, it puts the same color into all cells after the one I'm trying to change. Same issue happened to dataCell as well when I tried to set borders to them(code commented now)

These affect only the cells which have no data previously. Cells having data before my program runs(I'm trying to add/modify sheets with pre-existing data), remain unaffected. I'm still not able to find the issue. Have given my entire method's code below.

private void prepareDataForExcel() {
try {
    System.out.println(dataMap);
    String sheetNameMapArr[] = props.getProperty("sheetNameMap").split(",");
    XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(new File("mydata.xlsx").getAbsolutePath()));

    for (String sheetMap : sheetNameMapArr) {
        String table = sheetMap.split("~")[0];
        String sheetNm = sheetMap.split("~")[1];
        int rowNum = 0;
        String headerArr[] = null;

        String opDataArr[] = dataMap.get(table).toString().split(",");

        XSSFSheet sheet = workbook.getSheet(sheetNm);
        XSSFRow row = sheet.getRow(rowNum);
        int lstCol = row.getLastCellNum();

        // Setting Headers
        String headerMapArr[] = props.getProperty("tableHeaderMap").split(";");
        for (String header : headerMapArr) {
            if (header.split("~")[0].equalsIgnoreCase(table)) {
                headerArr = header.split("~")[1].split(",");
            }
        }

        int headerIndx = lstCol;
        if (headerArr != null) {
            ++rowNum;
            for (String header : headerArr) {
                XSSFCell headerCell = row.createCell(headerIndx, Cell.CELL_TYPE_STRING);
                ++headerIndx;
                headerCell.setCellValue(header);
                headerCell.getCellStyle()
                        .setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 255, 204)));
                headerCell.getCellStyle().setFillPattern(FillPatternType.SOLID_FOREGROUND);
            }
        }

        // Setting Date
        String dt = new SimpleDateFormat("dd/MM/yyyy").format(new Date());
        row = sheet.getRow(rowNum);
        if (headerIndx > lstCol)
            sheet.addMergedRegion(
                    new CellRangeAddress(rowNum, rowNum, lstCol, lstCol + (headerIndx - lstCol - 1)));
        XSSFCell dtCell = row.createCell(lstCol, Cell.CELL_TYPE_STRING);
        dtCell.setCellValue(dt);

        // Populating Data
        int dataRowIndx = ++rowNum;
        int dataColIndx = lstCol;
        for (String tableData : opDataArr) {
            String dataArr[] = tableData.split("~");
            for (String data : dataArr) {
                row = sheet.getRow(dataRowIndx);

                if (row == null)
                    row = sheet.createRow(dataRowIndx);

                XSSFCell dataCell = row.createCell(dataColIndx, Cell.CELL_TYPE_STRING);
                ++dataColIndx;
                if (isNumeric(data))
                    dataCell.setCellValue(Double.valueOf(data));
                else
                    dataCell.setCellValue(data);

                // dataCell.getCellStyle().setBorderBottom(XSSFCellStyle.BORDER_THIN);
                // dataCell.getCellStyle().setBorderLeft(XSSFCellStyle.BORDER_THIN);
                // dataCell.getCellStyle().setBorderRight(XSSFCellStyle.BORDER_THIN);
                // dataCell.getCellStyle().setBorderTop(XSSFCellStyle.BORDER_THIN);
            }
            ++dataRowIndx;
            dataColIndx = lstCol;
        }

    }

    FileOutputStream fileOut = new FileOutputStream(new File("mydata_res.xlsx"));
    workbook.write(fileOut);
    fileOut.close();
    workbook.close();

} catch (Exception e) {
    e.printStackTrace();
    System.exit(1);
}
}
Snehansu
  • 13
  • 3
  • This is by design of `Excel` since cell styles **are** stored on workbook level. So either you needs creating each needed cell style on workbook level before applying it to a cell. [Workbook.createCellStyle](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Workbook.html#createCellStyle--) is doing this. But do not creating the same cell styles multiple times as there are limits of cell styles count. – Axel Richter Aug 31 '18 at 03:31
  • Or you need using methods of [CellUtil](https://poi.apache.org/apidocs/org/apache/poi/ss/util/CellUtil.html) and/or [PropertyTemplate](https://poi.apache.org/apidocs/org/apache/poi/ss/util/PropertyTemplate.html). Those methods "...allow you to create your CellStyles as you need them. When you apply a style change to a cell, the code will attempt to see if a style already exists that meets your needs. If not, then it will create a new style." I have shown this in https://stackoverflow.com/questions/50712340/apply-fill-colors-and-borders-to-excel-range-using-apache-poi/50762030#50762030. – Axel Richter Aug 31 '18 at 03:33

0 Answers0