CODEWITHSUNDEEP
c#excelopenxml
ConnorM
ConnorM

Reputation: 13

Can OpenXML return a result of a formula

I am using the following piece of code as part of a larger project - the program basically reads a spreadsheet based on the sheet selected from a drop down box - it then assigns the results to a datatable - I have a for loop that runs the following function to get the Cell content and add it to the datatable

 public string GetValue(Cell cell, SharedStringTablePart stringTablePart)
    {

        if (cell.ChildElements.Count == 0) return null;

        //get cell value

        string value = cell.ElementAt(0).InnerText;//CellValue.InnerText;


        //Look up real value from shared string table

        if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))

            value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

        return value;

    }

In the spreadsheet I am using however this returns a formula rather than the result. The Spreadsheet formula is =Sheet1!A12, essentially, whatever the user has entered into Sheet1 A12 should be displayed in the cell. Now the spreadsheet has the information already filled in but the program doesn't pull that result only the formula.

An example of this would be: Sheet 1 A12 contains the word "Dog" that means that in Sheet2 the Cell A4 has the formula =Sheet1A12 and should display "Dog" (which it does in excel but not in the program).

Is OpenXML capable of displaying the result considering it already exists?

Upvotes: 0

Views: 4910

Answers (4)

slipkorn
slipkorn

Reputation: 13

Use this to return a result of a formula:

string value = cell.CellValue.InnerText;

Upvotes: 1

LocEngineer
LocEngineer

Reputation: 2917

I do not quite agree with the two answers given. I have done a quick test, adding a few values and a sum formula to sheet1 of a fresh workbook, saved as xmlx, unzipped it and looked into the /xl/worksheets/sheet1.xml.

<row r="14" spans="1:1" x14ac:dyDescent="0.25">
    <c r="A14">
        <f>SUM(A1:A13)</f>
        <v>1295</v>
    </c>
</row>

As you can see, both the formula and its calculated value are visible. I then did a refined search and found this: OpenXML SDK: Make Excel recalculate formula

In short: it should absolutely be possible to access formula values and to even recalculate if required.

Upvotes: 1

Andrei15193
Andrei15193

Reputation: 705

Sorry but no. OpenXML SDK is just a set of classes for manipulating Word, Excel and PowerPoint files at the low level. Formulas are stored as plain text, you will need to implement a formula evaluator for this. See Interpreter design pattern if you want to engage in this endeavour.

Also use the provided properties by the SDK for accessing a cells data (CellValue, CellFormula). Also note that Excel does not always fill the DataType (only when it cannot be inferred correctly to be more precise, e.g.: shared strings, instead of reading the value as number it fills the data type to tell that that number is in fact an index in the shared string table like your code snippet illustrates).

I haven't used ClosedXML but I hear it is worth a try.

Upvotes: 0

Felice Pollano
Felice Pollano

Reputation: 33272

OpenXml is just the format in which the content of a SpreadSheet is saved. To have the result of a cell you need the SpreadSheet engine ( ie excel )

Upvotes: 0

Related Questions