How to get the value shown in a cell from .xlsx using open XML C #

Question

I am new to C # and open XML, so please be patient with my acquaintance.

I have this problem:

I need to get the cell value from a .xlsx file. I can do this using the XlGetCellValue method. But when one cell (e.g. A2 from sheet 1) gets its value from another cell (B2 sheet2)

XlGetCellValue("", "Sheet1", "A2") returns Sheet2!B2Joe .

Or, when the cell contains calculations (e.g. C2 + D2), XlGetCellValue(...) returns C2+D2120

Is there an easy way to get only the values ​​"Joe" and "120?

Answer 1

Here's a link to MSDN on how to get cell value using Open XML SDK 2.5. There is a sample code.

How to get cell values ​​in a spreadsheet document (Open XML SDK)

Answer 2

Working with openxmnl can be a pain in the ass if you haven't already downloaded OpenXMLSDKToolV25.msi (performance tool).

basically it is a reflection tool. you can open an excel document and the tool will create all the code you need to build from scratch.

CellValue is for value only. using a formula, you have to deal with a cell formula.

Eg. I created an excel file in A1 = 1256 in B1 = 2 in C1 "= A1 * B1" Opening the file with OpenXMLSDKTool, I got:

  public Row GenerateRow() { Row row1 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" } }; Cell cell1 = new Cell(){ CellReference = "A1" }; CellValue cellValue1 = new CellValue(); cellValue1.Text = "1256"; cell1.Append(cellValue1); Cell cell2 = new Cell(){ CellReference = "B1" }; CellValue cellValue2 = new CellValue(); cellValue2.Text = "2"; cell2.Append(cellValue2); Cell cell3 = new Cell(){ CellReference = "C1" }; CellFormula cellFormula1 = new CellFormula(); cellFormula1.Text = "A1*B1"; CellValue cellValue3 = new CellValue(); cellValue3.Text = "2512"; cell3.Append(cellFormula1); cell3.Append(cellValue3); row1.Append(cell1); row1.Append(cell2); row1.Append(cell3); return row1; } 

from this you can see that CellValue and CellFormula are children of Cell. So, assuming you can get your Row r, you can get the following:

 Cell c = r.Elements<Cell>().ElementAt(2); CellValue cv = c.CellValue; CellFormula cf = c.CellFormula;