Share via

Is there a way using .NET and Open-xml to read the output value of a formula in a cell of an excel file?

Yazan Ghafir 1 Reputation point
2021-02-16T10:42:58.327+00:00

Summary of the problem

How to read the output value of a formula in a cell, using .NET and Openxml.

Goal

Using .NET open-xml, to read a value of a cell that contains a formula and that is not calculated at compile-time, but at run time.
For example if we have a cell with the formula: =SUM(O32:O80). I need to read the output value after evaluating this formula, using .NET.

In my case the formulas is getting calculated only when you open the excel file and press enable editing button, only then, the formulas get calculated.

So in other words, is there a way to force calculating the formulas of the cells to then read the output cell values from .NET.

Not relevant answers

As the value of the formula is not getting calculated at compile-time, I would say a not relevant answer would be in the form of:

  • Getting the Cell using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(tmpTempletePath, true)){
    ...
    Cell cell = row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).FirstOrDefault();
  • Getting the cellValue using the normal way or getting it as a SharedString for example as follows: string value = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText;
  • Force calculation by setting calculation properties

I tried:

Report.Workbook.CalculationProperties.CalculationOnSave = true;
Report.Workbook.CalculationProperties.ForceFullCalculation = true;
Report.Workbook.CalculationProperties.FullCalculationOnLoad = true;

Why the answers above are not relevant

Because those answers give the value of the cell, but not the calculated output value of the formula included in the cell. In my case, the excel document that I'm working on is a very complex price calculation document with more than 70 defined names that point to ranges and cells, and a large number of formulas that include these defined names in their calculations to calculate a total price value. Meaning that to make the calculation in C# based on the data I have, is a big project compared with just reading the value after it gets calculated by excel at run-time.

Idea

I got an idea to include a visual-basic based button in a sheet that will read the value after calculation and persist it to a file that I read from .NET? Any ideas?

Many thanks in advance

Best Regards

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,317 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yijing Sun-MSFT 7,071 Reputation points
    2021-02-18T07:41:13.483+00:00

    Hi @Yazan Ghafir ,
    I have find your another case in SO.But you say you care cost of the EPPLUS. As far as I think,if you provide a tool to open excel file and translate it's content to html you must deal with calculation.Actually you have to build a complex web based spreadsheet viewer/editor.Just like this article.

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,
    Yijing Sun

    0 comments