7

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?

Tarun Mathur
  • 865
  • 1
  • 8
  • 25
  • This is going to sound silly, but have you tried pressing F9 when you have the exported file open in Excel? – Ann L. Jun 15 '14 at 19:21
  • 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 Jun 25 '14 at 12:23

1 Answers1

11

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);
}
Evalds Urtans
  • 6,436
  • 1
  • 41
  • 31