javaapache-poihssf

HSSFCell setCellStyle apply style on all sheet's cell instead the selected cell


I transform a string called sCurrentLine to an Excel's row. Depending if the line is starting with X, Y or Z I color the row.

        row = sheet.createRow(lines);
        String[] parts = sCurrentLine.split("\\|");

        if (sCurrentLine.contains("X")) {
            bgColorIndex = HSSFColor.RED.index;
        } else if (sCurrentLine.contains("Y")){
            bgColorIndex = HSSFColor.LIGHT_BLUE.index;
        } else if (sCurrentLine.contains("Z")) {
            bgColorIndex = HSSFColor.YELLOW.index;
        } else {
            bgColorIndex = HSSFColor.BROWN.index;
        }

So I am having this variable called bgColorIndex and use it to set the color of all the cells of the row

        for (short i = 0; i < parts.length; i++) {
            row.createCell(i).setCellValue(parts[i]);

            HSSFCell curCell = row.getCell(i);
            HSSFCellStyle curStyle = curCell.getCellStyle();
            curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            curStyle.setFillForegroundColor(bgColorIndex);
            curStyle.setFillBackgroundColor(bgColorIndex);
            System.out.println("Color is: " + bgColorIndex);

            curCell.setCellStyle(curStyle);
        }
        lines++;
    }

The issue I am having is setCellStyle is applying the style for all the rows and sheets, so the last color I detect is applied to whole document.

How can I color independently every cell?

EDITED: Whole code:

private void generateCSVFile() {
    String filename = "excel.xls" ;
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("FirstSheet");  

    HSSFRow rowhead = sheet.createRow((short)0);

    // header
    rowhead.createCell((short)0).setCellValue("AXIS");
    rowhead.createCell((short)1).setCellValue("INIT");
    rowhead.createCell((short)2).setCellValue("MID");
    rowhead.createCell((short)3).setCellValue("END");     

    // Set columns width
    for (short i = 0; i < 3; i++)  {
        sheet.setColumnWidth(i, (short)(20*300));
    }

    BufferedReader br = null;
    FileReader fr = null;

    try {

        fr = new FileReader("data.txt");
        br = new BufferedReader(fr);

        String sCurrentLine;

        br = new BufferedReader(new FileReader("data.txt"));
        short lines = 1;
        HSSFRow row = null;

        while ((sCurrentLine = br.readLine()) != null) {
            if (sCurrentLine.contains("AXIS")) {
                lines = 0;
                sheet = workbook.createSheet("SecondSheet");  
                // Set columns width
                for (short i = 0; i < 7; i++)  {
                    sheet.setColumnWidth(i, (short)(20*256));
                }

                row = sheet.createRow(lines);
            } 

            row = sheet.createRow(lines);
            String[] parts = sCurrentLine.split("\\|");

            short bgColorIndex = 0;
            // Check the first cell to set color for X, Y or Z
            if (sCurrentLine.contains("X")) {
                bgColorIndex = HSSFColor.RED.index;
            } else if (sCurrentLine.contains("Y")){
                bgColorIndex = HSSFColor.LIGHT_BLUE.index;
            } else if (sCurrentLine.contains("Z")) {
                bgColorIndex = HSSFColor.YELLOW.index;
            } else {
                bgColorIndex = HSSFColor.BROWN.index;
            }

            for (short i = 0; i < parts.length; i++) {

                row.createCell(i).setCellValue(parts[i]);

                HSSFCell curCell = row.getCell(i);
                HSSFCellStyle curStyle = curCell.getCellStyle();
                curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                curStyle.setFillForegroundColor(bgColorIndex);
                curStyle.setFillBackgroundColor(bgColorIndex);
                System.out.println("Color is: " + bgColorIndex);

                curCell.setCellStyle(curStyle);
                curStyle = null;
            }
            lines++;
        }

    } catch (IOException e) {

        e.printStackTrace();

    } finally {

        try {

            if (br != null)
                br.close();

            if (fr != null)
                fr.close();

        } catch (IOException ex) {

            ex.printStackTrace();

        }

    }
}

Solution

  • You have to create four different CellStyle in the workbook:

    CellStyle styleX = wb.createCellStyle();
    styleX.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleX.setFillForegroundColor(HSSFColor.RED.index);
    styleX.setFillBackgroundColor(HSSFColor.RED.index);
    CellStyle styleY = wb.createCellStyle();
    styleY.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleY.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    styleY.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
    CellStyle styleZ = wb.createCellStyle();
    styleZ.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleZ.setFillForegroundColor(HSSFColor.YELLOW.index);
    styleZ.setFillBackgroundColor(HSSFColor.YELLOW.index);
    CellStyle styleOther = wb.createCellStyle();
    styleOther .setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    styleOther .setFillForegroundColor(HSSFColor.BROWN.index);
    styleOther .setFillBackgroundColor(HSSFColor.BROWN.index);
    

    And, for each row:

    CellStyle style = null;
    row = sheet.createRow(lines);
    String[] parts = sCurrentLine.split("\\|");
    
    if (sCurrentLine.contains("X")) {
        style = styleX;
    } else if (sCurrentLine.contains("Y")){
        style = styleY;
    } else if (sCurrentLine.contains("Z")) {
        style = styleZ;
    } else {
        style = styleOther;
    }
    for (short i = 0; i < parts.length; i++) {
        row.createCell(i).setCellValue(parts[i]);
        HSSFCell curCell = row.getCell(i);
        currCell.setCellStyle(style);
    }