Set cellStyle to cell or row in HSSFWorkbook

Question

I declared internal variables in this way:

FileOutputStream fos;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("analyze_result");
Row row = sheet.createRow(rowIndex);
Cell cell = row.createCell(cellIndex);
HSSFCellStyle cs = workbook.createCellStyle();

In the middle of the class, after I collect some importent data I send it to the next function, but in the result it seems that no change performed on my excel:

private void firstWriteToExcel(DefectFileReader curDF , HSSFSheet sheet , HSSFWorkbook workbook , FileOutputStream fos) throws Exception{

    cs.setWrapText(true);
    cs.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    if(recipeName.getState() == true){
        row = sheet.createRow(rowIndex - 1);
        row.createCell(cellIndex).setCellValue("Recipe Name: " + curDF.getRecipeName());
        cell.setCellStyle(cs);

    }
    row = sheet.createRow(rowIndex++);
    row.createCell(cellIndex++).setCellValue("inspection index");
    row.setRowStyle(cs);
    if(numberOfDefects.getState() == true){
        System.out.println("in printing to excel");
        row.createCell(cellIndex++).setCellValue("Total Defects");
    }
    if(numberOfDefectsPerDetector.getState() == true){
        ArrayList<String> toOtherFunc = new ArrayList<String>();
        toOtherFunc = curDF.getDetectorNames();
        for(int i=0; i < toOtherFunc.size() ; i++)
            row.createCell(cellIndex++).setCellValue(toOtherFunc.get(i));   
    }
    if(sensetivityName.getState() == true)
        row.createCell(cellIndex++).setCellValue("sensetivity Name");
    if(inspectionDuration.getState()==true)
        row.createCell(cellIndex++).setCellValue("Inspection Duration");

    row.setRowStyle(cs);

    System.out.println("row index is: " + rowIndex + "cell index is: " + cellIndex);
}

I can't see any code where you write your changed Excel file out - did you forget to call `write` to save it? — Gagravarr, May 14 '15 at 08:14
no... it's later in the code. the excel print's well just the style changes not seen. — Udi, May 14 '15 at 13:20
Why are you making the cell style grey inside the loop? Cell Styles are workbook scoped, so you should set them up only once — Gagravarr, May 14 '15 at 17:55
It looks like you are creating the cells and populating them with data first, then setting the row style afterwards. Try setting the row style on the row before you create any cells in that row. Also it would be better to create 2 different cell styles for the workbook before any data is added (one 'normal' and one 'grey' for example), and then apply the different styles where required, rather than changing the style properties on the fly which might lead to unexpected results. — Wee Shetland, May 15 '15 at 13:35
My problem aperenatly was connent to the issue that i declare cell style and tried to perform it on row. and some other tiny issues. adding some of the new code in the answer. @tony_h you help me a lot. thanks! — Udi, May 27 '15 at 12:11

score 0 · Answer 1 · answered May 27 '15 at 12:12

private void writeToExcel(DefectFileReader curDF , XSSFSheet sheet , XSSFWorkbook workbook , FileOutputStream fos) throws Exception{

        fontNotForBold.setBold(false);
        csForSecondWrite.setFont(fontNotForBold);
        csForSecondWrite.setFillForegroundColor(new XSSFColor(java.awt.Color.white));
        csForSecondWrite.setFillPattern(CellStyle.SOLID_FOREGROUND);
        csForSecondWrite.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        csForSecondWrite.setBorderTop(XSSFCellStyle.BORDER_THIN);
        csForSecondWrite.setBorderRight(XSSFCellStyle.BORDER_THIN);
        csForSecondWrite.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    cellIndex = 4;
    row = sheet.createRow(rowIndex++);
    row.createCell(cellIndex++).setCellValue("inspection_" + (rowIndex - 3));
    if(numberOfDefects.getState() == true)
        row.createCell(cellIndex++).setCellValue(curDF.getTotalDefects());
    if(numberOfDefectsPerDetector.getState() == true){
        ArrayList<String> toOtherFunc = new ArrayList<String>();
        int[] toPrintNumber = new int[toOtherFunc.size()];
        toOtherFunc = curDF.getDetectorNames();
        toPrintNumber = curDF.getDefectPerDetector(toOtherFunc);
        for(int i=0; i < toOtherFunc.size() ; i++)
            row.createCell(cellIndex++).setCellValue(toPrintNumber[i]); 
    }
    if(sensetivityName.getState() == true)
        row.createCell(cellIndex++).setCellValue(curDF.getSensetivityName());
    if(LOGloaded == true){
        LogFileReader logReader = new LogFileReader(LOG , curDF.getInspectionEndTime());
        ArrayList<String> toLog = curDF.getRunsIndex();
        String toPrint = "";
        for(int i=0 ; i < toLog.size() ; i = i+2)
            toPrint += toLog.get(i) + ": " + logReader.diagnozeLog(toLog.get(i+1)) + " ";
        if(toPrint.equals(""))
            row.createCell(cellIndex).setCellValue("The data of this run is not in this log!");
        else
            row.createCell(cellIndex).setCellValue(toPrint);
        sheet.autoSizeColumn(cellIndex);
        cellIndex++;

    }
    if(inspectionDuration.getState()==true)
        row.createCell(cellIndex++).setCellValue(curDF.inspectionDuration());


    for (Row row : sheet){
        if( row.getRowNum() > 2){
        for (Cell cell_local : row){
            cell_local.setCellStyle(csForSecondWrite);
        }
        }

Set cellStyle to cell or row in HSSFWorkbook

1 Answers1