CODEWITHSUNDEEP
c#openxml
Apurva Deshmukh
Apurva Deshmukh

Reputation: 3

Getting incorrect cell value while parsing excel with OpenXML

I am trying to parse an excel and get the result in datatable using C# and openxml. Below is my code snippet.

   value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
        }
        return value;

But if the cell value is 80.3600 then it is getting parsed as 80.36. Also if the value is 03-Jan-2018 then it is getting parsed as 43103. The problem is, the excel which I am trying to parse is dynamically generated and at run time I won't know which column is date and which column is numeric. Is there any way to get the value as it is or get every value as a string i.e. no formatting?

Upvotes: 0

Views: 1685

Answers (1)

Z.R.T.
Z.R.T.

Reputation: 1603

i've noticed , numeric and date time cell's value have different styleIndex value. you can get cell format by styleIndex from doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.

        var doc = SpreadsheetDocument.Open(File.Open("D:\\123.xlsx", FileMode.Open), false);
        var sheet = doc.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
        WorksheetPart wsPart = (WorksheetPart)(doc.WorkbookPart.GetPartById(sheet.Id));
        var cells = wsPart.Worksheet.Descendants<Cell>().ToList();


        var numberingFormats = doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.ToList();

        var stringTable = doc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
        foreach (var cell in cells)
        {
            if (cell.DataType == null)
            {
                //DateTime 
                if (cell.StyleIndex != null)
                {
                    var numerFormat = numberingFormats.ElementAt((int) cell.StyleIndex.Value - 1) as NumberingFormat;
                    if (numerFormat.FormatCode.Value == "[$-409]mmmm\\ d\\,\\ yyyy;@")
                    {
                        Console.WriteLine(DateTime.FromOADate(double.Parse(cell.InnerText)).ToString("MMMM dd,yyyy"));
                    }
                    else if (numerFormat.FormatCode.Value == "[$-409]dd\\-mmm\\-yy;@")
                    {
                        Console.WriteLine(DateTime.FromOADate(double.Parse(cell.InnerText)).ToString("dd-MMM-yy"));
                    }
                }
                else
                {
                    //Numeric
                    Console.WriteLine(int.Parse(cell.InnerText));
                }

            }
            else if (cell.DataType.Value == CellValues.SharedString)
            {
                Console.WriteLine(stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText);
            }
        }

also can read this one:Excel Interop cell formatting of Dates

Upvotes: 1

Related Questions