0

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?

ConnorM
  • 9
  • 1
  • 7

4 Answers4

1

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.

Community
  • 1
  • 1
LocEngineer
  • 2,847
  • 1
  • 16
  • 28
  • Is the value stored in the file if the user selected 'Calculation Options' > Manual? – bouvierr Aug 31 '15 at 16:27
  • @bouvierr I have just tried again with manual calc and it was stored. I can imagine that this is not always the case but then one can force calculation using the methods from the linked SO post. – LocEngineer Aug 31 '15 at 16:34
  • What about text values - I believe its the SharedStringTable that messes this up? – ConnorM Sep 01 '15 at 14:15
  • @Connor McCarthy Well, have you tried it? I have: `CONCATENATE(A4,A5,A6)svdfdgcsdsgd`. However without you trying it and disclosing *how* you tried it this is all useless speculation. – LocEngineer Sep 01 '15 at 14:22
  • @LocEngineer is right, then you should be able to get the value by saying cell.ElementAt(1).InnerText and you're done – J Sanchez May 17 '17 at 22:57
0

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 )

Felice Pollano
  • 32,832
  • 9
  • 75
  • 115
0

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.

Andrei15193
  • 655
  • 5
  • 8
0

Use this to return a result of a formula:

string value = cell.CellValue.InnerText;

slipkorn
  • 3
  • 2