Formula in Excel using NPOI

Question

I am using NPOI dll for genrating excel sheet in C#. When I apply formula on some cell programmatically and export excel then in protected mode of excel sheet all the cells having formula show '0' value. but when i edit this excel all formulas work properly on those cell. 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);
}