Showing #value! before enable editing on excel if I write formula using epplus

Question

Using C# .net core I am updating existing excel template with Data and formulas using EPPlus lib 4.5.3.3.

If you see the below screen shots all formula cells has '#value!' even after using calculate method in C# code (Just for reference attached xml screen short just after downloading excel before opening it). Auto calculation is also enabled in Excel.

In one of the blog mentioned to check the xml info,

My requirement is to upload this excel through code to sharepoint site and read the excel formula cells for other operations with out opening the excel manually.

is there any other way to calculate the formula cells form code and update the cell values?

I went through the Why won't this formula calculate unless i double click a cell? as well, but no luck.

using (ExcelPackage p = new ExcelPackage())
            {
                MemoryStream stream = new MemoryStream(byteArray);
                p.Load(stream);
                ExcelWorksheet worksheet = p.Workbook.Worksheets.FirstOrDefault(a => a.Name == "InputTemplate");
                worksheet.Calculate();
                if (worksheet != null)
                {
                    worksheet.Cells["A3"].Value = company.CompanyName;//// Company Name
                    worksheet.Cells["B3"].Value = product.Name;////peoduct name 
                    worksheet.Cells["C3"].Value = product.NetWeight;
                    worksheet.Cells["D3"].Value = product.ServingSize;
                    worksheet.Cells["E3"].Value = 0;

                    var produceAndIngredientDetailsForExcelList = await GetProduceAndIngredientDetails(companyId, productId);

                    ////rowIndex will be 3
                    WriteProduceAndIngredientDetailsInExcel(worksheet, produceAndIngredientDetailsForExcelList);

                    ///rowIndex will update based on no. of produce and then Agregates.
                    StageWiseAggregate(worksheet, produceAndIngredientDetailsForExcelList);

                    ////Write Total Impacts Row
                    TotalImpactsFormulaSection(worksheet);

                    worksheet.Calculate();
                }
                Byte[] bin = p.GetAsByteArray();
                return bin;
            }

Formula Code

 var columnIndex = 22;///"V" Column

        for (; columnIndex <= 27; columnIndex++)
        {
            var columnName = GetExcelColumnName(columnIndex);
            worksheet.Cells[currentRowIndex, columnIndex].Formula = $"=SUBTOTAL(109,{columnName}{firstRowIndex}:{columnName}{currentRowIndex - 1})";
        }

Please add code and data as text ([using code formatting](/editing-help#code)), not images. Images: A) don't allow us to copy-&-paste the code/errors/data for testing; B) don't permit searching based on the code/error/data contents; and [many more reasons](//meta.stackoverflow.com/a/285557). Images should only be used, in addition to text in code format, if having the image adds something significant that is not conveyed by just the text code/error/data. — gunr2171, May 11 '22 at 17:55

score 0 · Answer 1 · edited May 13 '22 at 04:21

Found the solution for this issue from my Architect (kudos to him). I was writing formulas in wrong way by blindly fallowing tutorials like https://riptutorial.com/epplus/example/26433/add-formulas-to-a-cell

Note: don't follow link shown above.

We should not use "=" for formulas. I just removed it worked like charm

 var columnIndex = 22;///"V" Column

        for (; columnIndex <= 27; columnIndex++)
        {
            var columnName = GetExcelColumnName(columnIndex);
            worksheet.Cells[currentRowIndex, columnIndex].Formula = $"SUBTOTAL(109,{columnName}{firstRowIndex}:{columnName}{currentRowIndex - 1})";
        }

Here is the official tutorial which mentioned correctly. https://www.epplussoftware.com/en/Developers/ (check the second slide)

Working result:

Showing #value! before enable editing on excel if I write formula using epplus

1 Answers1