Formula in Excel using NPOI

Question

I am using NPOI for generating Excel spreadsheet in C#. When I apply formula on some cell programmatically and export to Excel then in protected mode of excel sheet all the cells having formula show '0' value. But when I edit this spreadsheet all formulas work properly on those cells.

Is there any solution from which applied formula can work in protected mode also?

Answer 1

You have to evaluate formulas after setting them:

cell = row.CreateCell(j++);
cell.SetCellType(CellType.FORMULA);
cell.SetCellFormula(String.Format("$B$1*B{0}/$B$2*C{0}", i));
cell.CellStyle = styleCell;

if(wb is XSSFWorkbook) {
    XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
} else {
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}