CODEWITHSUNDEEP
c#excel-formulaopenxmlopenxml-sdk
dfmetro
dfmetro

Reputation: 4592

How to get the openxml.spreadsheet.cell screen display value

I have a C# application that reads an uploaded excel file (.xslx) created in Excel 2013

I read each cell putting each cell into a collection of cells per collection of rows using C# openxml library.

The code works correctly except it doesn't read a formatted excel cell value correctly.

Here is how the cell shows in excel:

enter image description here

and the fromatting: enter image description here

I have examined the DocumentFormat.OpenXml.Spreadsheet.Cell cell properties for that cell when the C# application reads it but none of the cell properties equals the value of May-15 I want.

I noticed the cell.DataType and cell.CellFormula are both null so they cannot be used to determine the format.

The cell.InnerText value is "42125". If I change that cells format in Excel to General then it displays as 42125.

I am using the code from this article

Here is the code snippet I am using

for (var i = 0; i < rows.Count; i++)
{
    var dataRow = new List<string>();
    data.DataRows.Add(dataRow);
    var row = rows[i];
    var cellEnumerator = GetExcelCellEnumerator(row);
    while (cellEnumerator.MoveNext())
    {
        var cell = cellEnumerator.Current; //return the current DocumentFormat.OpenXml.Spreadsheet.Cell
        var text = ReadExcelCell(cell, workbookPart).Trim();
        dataRow.Add(text);
    }
}

.

private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
    var cellValue = cell.CellValue;
    var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
    if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
    {
        text = workbookPart.SharedStringTablePart.SharedStringTable
            .Elements<SharedStringItem>().ElementAt(
                Convert.ToInt32(cell.CellValue.Text)).InnerText;
    }

    return (text ?? string.Empty).Trim();
}

Question:

  1. How can I read the display value of cell.InnerText of May-15?

  2. Is it possible to convert "May-15" to "42125" or "42125" to "May-15" using standard C# string/date formatting if it given that the format is mmm-y?

Upvotes: 1

Views: 1114

Answers (1)

devfric
devfric

Reputation: 7542

The answer to your 2nd question:

How to convert 42125 to May-15

var myval = String.Format("{0:MMM-yy}", DateTime.FromOADate(42125));

Convert May-15 to 42125

var myval =  DateTime.ParseExact("May-15", "MMM-yy", System.Globalization.CultureInfo.InvariantCulture).ToOADate();

Upvotes: 1

Related Questions