Formula evaluation does not work in NPOI 2.1.3.0

I am creating an Excel workbook in the code behind and I save it as both XLSX and PDF. I am using a template book for this, which has formatting and formulas that will be evaluated after the generation completes. When I open an Excel file, formulas are evaluated only when I set ForceFormulaRecalculation

to true. When I do the same with the PDF file, I get #VALUE!

where the results should be. My relevant code:

ReportGenerator generator = new ReportGenerator();
List<Activity> activities = GetActivitiesForItemCollection(items);

generator.CreateWorkbook(templatePath);

generator.Year = int.Parse(year);
generator.Month = int.Parse(month);

activities = generator.SortActivitiesByDateTime(activities);

activities = generator.GenerateBreaksForProject(activities);
bool isExternalReport = false;

if (project == "Intern")
    isExternalReport = true;

generator.GenerateReports(activities, isExternalReport);

if (pdf && !xlsx)
    generator.SaveReportToList(OutputFileType.PDF, generator.AssembleFileName(UserProperties.FullName, project, month, year, OutputFileType.PDF));
else if (xlsx && !pdf)
    generator.SaveReportToList(OutputFileType.XLSX, generator.AssembleFileName(UserProperties.FullName, project, month, year, OutputFileType.XLSX));
else
{                            
    generator.SaveReportToList(OutputFileType.XLSX, generator.AssembleFileName(UserProperties.FullName, project, month, year, OutputFileType.XLSX));
    generator.SaveReportToList(OutputFileType.PDF, generator.AssembleFileName(UserProperties.FullName, project, month, year, OutputFileType.PDF));
}

      

        
        
        
      

    

Here is my code where I am doing the evaluation:

public void SaveReportToList(OutputFileType outputType, string filename)
{
      string siteUrl = SPContext.Current.Web.Url;
      SPSecurity.RunWithElevatedPrivileges(delegate()
      {
           using (MemoryStream mStream = new MemoryStream())
           {
                sheet.PrintSetup.Landscape = true;
                sheet.PrintSetup.FitWidth = 1;
                sheet.PrintSetup.FitHeight = 1;

                XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
                workbook.Write(mStream);

                using (MemoryStream crutchStream = new MemoryStream(mStream.ToArray()))
                {
                    using (SPSite spsite = new SPSite(siteUrl))
                    {
                        using (SPWeb spweb = spsite.OpenWeb())
                        {
                           ....

      

        
        
        
      

    

I've also tried it this way:

private void ForceCalculateSheet()
{
      XSSFFormulaEvaluator helper = (XSSFFormulaEvaluator)workbook.GetCreationHelper().CreateFormulaEvaluator();

      for(int i = 0; i<sheet.LastRowNum; i++)
      {
          XSSFRow row = (XSSFRow)sheet.GetRow(i);

          for(int j = 0; j< row.LastCellNum; j++)
          {
               XSSFCell cell = (XSSFCell)row.GetCell(j);

               if(cell != null && cell.CellType == CellType.Formula)
               {
                   helper.EvaluateFormulaCell(cell);
               }
          }
     }
 }

      

        
        
        
      

    

Here are some formulas I'm trying to evaluate (some of the cell values ​​are 24 hour time values):

=IF(D37-C37-E37>0;D37-C37-E37;0)
=INT(J39/60)
=J39-C39*60

      

        
        
        
      

    

The weird thing is when I trace the score in Excel, it goes to the last step. Then the value becomes #VALUE!

.

Anyone have any idea what is going on here?

To be careful, I updated NPOI to the latest .NET version (2.1.3.1). This hasn't fixed the problem yet.

When I click on a cell that is in a formula, change its value, then confirm it with Enter, the formula will evaluate correctly. This means that there should be nothing wrong with the formulas themselves.

I solved this by doing the calculations manually. This is not my long term solution as users should be able to do their own calculations, but this is the only solution I can think of right now, other than using a different Excel framework (poor planning;)).