Formula in Excel using NPOI

7
votes

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?

1
visual-studio-2010sql-server-2008c#-4.0npoi
This is going to sound silly, but have you tried pressing F9 when you have the exported file open in Excel? - Ann L.
Ann, I know its silly but its happing with each exported file, user are not able to view formula data until they convert excel from protected view to edit mode. - Tarun Mathur

1 Answers

10
votes

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);
}