open xml sdk 2.0 read excel cell value with formula

Question

I am reading excel file using openXML sdk. Some excel cells contains formula, I want to read the value of the cell but when I use the below code it is fetching me the formula. How to get the value.

WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
  WorkbookPart wbPart = document.WorkbookPart;
  Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
  WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
  Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D16").FirstOrDefault();
  value = theCell.InnerText;
}

Here I am reading particular cell "D16" which contains formula.

Need one more help, not able to read Date for the cell. I am getting some number. For Date cell Datatype is Null so below code not working. HOw do I get date vale from the cell

  if (theCell != null)
                {
                    value = theCell.CellValue.InnerText;
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:
                                var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                                if (stringTable != null)
                                {
                                    value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                                }
                                break;

                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }

score 3 · Accepted Answer · answered Jun 04 '18 at 19:08

3

try CellValue proerty of the cell.

WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
  WorkbookPart wbPart = document.WorkbookPart;
  Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
  WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
  Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D16").FirstOrDefault();
  value = theCell.CellValue;
}

I am assuming you are hardcoding the cell reference for debugging purposes. otherwise i would strongly advise against that.

answered Jun 04 '18 at 19:08

shamanth Gowdra Shankaramurthy

925
7
17

what do you mean by 'unable to fetch Date'? you are not getting it in the format that you are expecting? which cell property are you using? you may want to put it as a different question with code sample which you have tried. – shamanth Gowdra Shankaramurthy Jun 05 '18 at 16:47
Hi I have updated the question with code sample. Please check. I am getting value as 4233 – Sunil Jun 05 '18 at 16:56
1

check this - https://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value?noredirect=1&lq=1 – shamanth Gowdra Shankaramurthy Jun 05 '18 at 17:05

score 2 · Answer 2 · answered Jun 04 '18 at 19:10

Try this:

WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
    WorkbookPart wbPart = document.WorkbookPart;
    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
    WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "D16").FirstOrDefault();
    var value = theCell.CellValue.InnerText;
}

open xml sdk 2.0 read excel cell value with formula

2 Answers2