CODEWITHSUNDEEP
excelapache-poi
naga headhunter
naga headhunter

Reputation: 216

Apache POI: cell.setCellFormula not giving the desired value when excel is generated

I am able to generate the excel file but the cell which contains the formula(suppose to show the total sum of the integer values of the column) is showing value 0 initially. Then, when i edit some of the column cells it gets updated with the sum of the values of ONLY the edited cells and not adding up those of the unedited cells.

My piece of code is:

Cell cell = row.createCell(size - 2);
cell.setCellValue("Total");
cell = row.createCell(size - 1);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(D3:D4)");

Also, i tried by adding further these codes below the above code.

FormulaEvaluator evaluator =  wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);

But it still gave the same result. Can anyone please tell me what is going wrong? Thanks in advance.

Upvotes: 1

Views: 6795

Answers (3)

Vadi raja
Vadi raja

Reputation: 11

Use wb.setForceFormulaRecalculation(true); before closing file

it will calculate the formula

Upvotes: 1

ArtiBucco
ArtiBucco

Reputation: 2239

I do not know why it doesn't work for you, try this code, it works for me.

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    Row row = sheet.createRow(3);// Row 4
    Cell cell = row.createCell(3); // Cell 'D'
    cell.setCellValue(10);

    row=sheet.createRow(2);// Row 3
    cell=row.createCell(3);// Cell 'D' 
    cell.setCellValue(2);

    cell=row.createCell(4);// Cell 'E'
    cell.setCellValue("Total");
    cell = row.createCell(5);// Cell 'F'
    cell.setCellType(Cell.CELL_TYPE_FORMULA);
    cell.setCellFormula("SUM(D3:D4)");

The only difference is that I use Cell.CELL_TYPE_FORMULA instead of HSSFCell.CELL_TYPE_FORMULA, maybe that is the problem.

Upvotes: 1

user1912404
user1912404

Reputation: 396

Maybe your changing the style of the cell later on in the code

Upvotes: 0

Related Questions