Trying to read an Excel file file stream in C#, I get numbers instead of text

Question

I'm trying to use DocumentFormat.OpenXml to read an uploaded Excel file. When I get the file (HttpPosteFileWrapper) I'm simply trying to read the cells and write them to a text string. (Later I will do more, but I'm just trying to get used to OpenXml right now.)

My data in Excel looks something like this:

Field1 - Field2 - Phone - City
IT Department - Emp - 7175551234 - Springfield
HR - Emp - 7175556543 - W Springfield

Code looks like this:

var doc = SpreadsheetDocument.Open(file.InputStream, false);
WorkbookPart workbookPart = doc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData thisSheet = worksheetPart.Worksheet.Elements().First();
System.Text.StringBuilder text = new System.Text.StringBuilder();
foreach (Row r in thisSheet.Elements())
{
    foreach (Cell c in r.Elements())
    {
        text.Append(c.CellValue.Text + ",");
    }
    text.AppendLine();
}

And the string it creates looks like this:

49,51,50,0,1,2,3,4,5,6,7,8,9,10,11,12,13,16,14,15,17,18,19,20,21,22,40,41,42,43,44,45,54,
52,24,23,25,26,27,7306,33,28,29,30,31,17033,32,34,7175555555,7175551234,7175554321,7175550000,35,36,37,36526,40179,38,39,30,31,17033,32,55,
53,46,47,48,555,

It seems like the numeric values come through. Is it because I'm using the wrong stream type?

Edit: I've updated my code to now look like this, but it still doesn't work right. There seems to be no way for me to see the text data.

public ActionResult ProfileImport(IEnumerable files)
{
    // Build file list
    int i = 1;
    foreach (var file in files)
    {
        if (file.ContentLength > 0)
        {

var doc = SpreadsheetDocument.Open(file.InputStream, false);
WorkbookPart workbookPart = doc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData thisSheet = worksheetPart.Worksheet.Elements().First();
System.Text.StringBuilder text = new System.Text.StringBuilder();
foreach (Row r in thisSheet.Elements())
{
    foreach (Cell c in r.Elements())
    {
        string value = c.InnerText;
        if (c.DataType != null && c.DataType.Value == CellValues.SharedString) // Check DataType exists
        {
            var stringTable = workbookPart.GetPartsOfType()
                .FirstOrDefault(); // Get Table parts from workbookPart
            if (stringTable != null)
                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
            text.Append(value + ",");
        }
        else
            text.Append(value + ",");
     }
     text.AppendLine();
 }
 var outText = text.ToString();
}
}
}

Actual data from 1st row of the file:
AddressDescription, Address1, Address2, City, State, PostalCode, CountryCode, Email, CellPhone, HomePhone, WorkPhone, Fax, OrganizationName, Department, Position, StartDate, EndDate, OrganizationAddress1, OrganizationAddress2 OrganizationCity, OrganizationState, OrganizationPostalCode, OrganizationCountryCode, Keywords

Row.InnerText of that row:
"49515001234567891011121316141517181920212240414243444554"

Row.OuterXml:

maniak1982 · Accepted Answer

It looks like these are indices to the strings in the Shared Strings table. In Excel file formats, string data is stored in a shared strings table, which is then references on the cell level. Per the documentation, CellValue returns an index to the StringTable if the data type is text.

https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalue%28v=office.14%29.aspx?f=255&MSPPError=-2147217396

Not knowing more about the type of data that's in your cells (there are different ways to retrieve it based on the data type). If it's what I think it is, it'll be a SharedString, which you will need to retrieve as a SharedStringTablePart, as shown in this MSDN page:

https://msdn.microsoft.com/en-us/library/hh298534%28v=office.14%29.aspx?f=255&MSPPError=-2147217396

Your code would look something like this:

foreach (Cell c in r.Elements())
{
    string value = c.InnerText;
    if (c.DataType.Value == CellValues.SharedString)
    {
        var stringTable = workbookPart.GetPartsOfType()
            .FirstOrDefault();
        if (stringTable != null)
            value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
        text.Append(value + ",");
    }
    else
        text.Append(value + ",");
    text.AppendLine();
}

Trying to read an Excel file file stream in C#, I get numbers instead of text

Answers (2)

Related Questions