Formula not identify cell values

Question

I'm generating an excel using the following code in my ASP.Net MVC Application

 var fileName =  DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
                var outputDir = ConfigurationManager.AppSettings["ExcelUploadPath"];

                //  var fileName = "ExcellData.xlsx";
                var file = new FileInfo(outputDir + fileName);

                var fDate = JsonConvert.DeserializeObject<DateTime>(fromDate);
            var tDate = JsonConvert.DeserializeObject<DateTime>(toDate);
                using (var package = new OfficeOpenXml.ExcelPackage(file))
                {
                    // add a new worksheet to the empty workbook
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Plan " + DateTime.Now.ToShortDateString());

                    // --------- Data and styling goes here -------------- //

                    DataTable dt = planService.GetFlow(fDate, tDate, customerId, ordertypeId, suppliers, items);
                    if (dt != null)
                    {
                        int iCol = 1;
                        // Add column headings...
                        for (int i = 9; i < dt.Columns.Count; i++)
                        {
                            dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.MultiInsert("/", 1, 3);
                        }




                        foreach (DataColumn c in dt.Columns)
                        {
                            worksheet.Cells[1, iCol].Value = c.ColumnName;
                            worksheet.Cells[1, iCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
                            worksheet.Cells[1, iCol].Style.Font.Bold = true;
                            worksheet.Cells[1, iCol].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                            iCol++;
                        }




                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            for (int k = 0; k < dt.Columns.Count; k++)
                            {

                                worksheet.Cells[j + 2, k + 1].Value = dt.Rows[j].ItemArray[k].ToString();



                                if (int.Parse(dt.Rows[j].ItemArray[7].ToString()) == 6)
                                {
                                    worksheet.Cells[j + 2, k + 1].Style.Locked = false;
                                    worksheet.Cells[j + 2, k + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                    worksheet.Cells[j + 2, k + 1].Style.Fill.BackgroundColor.SetColor(Color.Cyan);
                                }
                                if (int.Parse(dt.Rows[j].ItemArray[7].ToString()) == 7)
                                {
                                    worksheet.Cells[j + 2, k + 1].Style.Locked = false;
                                    worksheet.Cells[j + 2, k + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                    worksheet.Cells[j + 2, k + 1].Style.Fill.BackgroundColor.SetColor(Color.Magenta);
                                     //worksheet.Cells[j + 2, k + 1].Formula = 

                                    if((k+1) > 10){

                                        var addressList = new List<string>();
                                        for (int i = 11; i <= k+1; i++)
                                        {
                                            addressList.Add(worksheet.Cells[((j + 2) -1) , i].Address);
                                        }
                                        var lstAdress = String.Join(",", addressList);
                                        worksheet.Cells[j + 2, k + 1].Formula = "SUM(" + lstAdress + ")";
                                    }

                                }
                                if (int.Parse(dt.Rows[j].ItemArray[7].ToString()) == 8)
                                {
                                    //worksheet.Cells[j + 2, k + 1].Style.Locked = false;
                                    worksheet.Cells[j + 2, k + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                    worksheet.Cells[j + 2, k + 1].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                                }
                            }

                            var colCount = dt.Columns.Count;
                            // worksheet.Cells[j+2, 8, j+2, colCount- 1].Style.Numberformat.Format = "0.000";
                            var range = worksheet.Cells[j + 2, 9, j + 2, colCount - 1];
                            var r = range.ToString();
                            var decimalValidation = worksheet.DataValidations.AddDecimalValidation(range.ToString());
                            decimalValidation.ShowErrorMessage = true;
                            decimalValidation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
                            decimalValidation.ErrorTitle = "The value you entered is not valid";
                            decimalValidation.Error = "This cell must be a valid positive number.";
                            decimalValidation.Operator = ExcelDataValidationOperator.greaterThanOrEqual;
                            decimalValidation.Formula.Value = 0D;

                        }


                        worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
                        worksheet.Column(1).Hidden = true;
                        worksheet.Column(2).Hidden = true;
                        worksheet.Column(3).Hidden = true;
                        worksheet.Column(4).Hidden = true;
                        worksheet.Column(5).Hidden = true;
                        worksheet.Column(8).Hidden = true;
                        worksheet.Column(9).Hidden = true;
                        worksheet.Column(10).Hidden = true;
                        worksheet.Protection.IsProtected = true;
                        // save our new workbook and we are done!


                        worksheet.Calculate();
                        package.Save();
                        return Json(fileName, JsonRequestBehavior.AllowGet);
                    }
                    else
                    {
                        return Json("NoData", JsonRequestBehavior.AllowGet);
                    }
                }
                return Json("", JsonRequestBehavior.AllowGet);

Here I'm setting my formula with comma separated cell names eg:

SUM(A1,A2,A3.. etc)

The excel file is generating correctly. But the problem is the formula calculation is not happen when I open my excel file.

The formula works when I manually change a cell value in Column Type Agreed Flow.

And it can only identify values of manually edited cells.

How can I resolve this?

Answer 1

It worked when I change my formula as follows..

    var addressList = new List<string>();
  for (int i = 11; i <= k+1; i++)
  {
      addressList.Add(worksheet.Cells[((j + 2) -1) , i].Address);
  }
  var lstAdress = String.Join("+", addressList);
   worksheet.Cells[j + 2, k + 1].Formula = "(" + lstAdress + ")";

I think there is an issue in my excel sheet when I use the SUM function So I write the formula without using it. Then it worked

(A1+B1+C1+D1+ ..... etc)

Answer 2

Formula recalculation is both an Excel and a workbook setting.

You could set it with at the workbook level with

workbook.CalcMode = ExcelCalcMode.Automatic;

If the user has set it to manual though, the formulas won't be recalculated.

If you want to ensure the saved values are correct you can force the calculation by calling

worksheet.Calculate();

You can also calculate the formulas at the workbook or range level, eg :

worksheet.Cells[j + 2, k + 1].Calculate();

or

package.Workbook.Calculate();

This is explained in the documentation. Keep in mind that EPPlus doesn't contain Excel's formula engine. It uses its own engine to parse and calculate formulas. Some things aren't supported