Read Excel file using OpenXML

Question

I am writing a c# program to read excel file using OpenXML. I have a list of words added in a column in the excel file in a column.I want to read them and add to an array list. I am using the below code

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePatah + "\\" + fileName, false))
 {
       WorkbookPart workbookPart = doc.WorkbookPart;
       WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
       SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

       ArrayList data = new ArrayList();
       foreach (Row r in sheetData.Elements<Row>())
       {
          foreach (Cell c in r.Elements<Cell>())
          {
             data.Add(c.CellValue.Text);
          }
       }
 }

What I am seing is 1,2,3,4 etc with this while I am expecting the words to list. What should I need to do to get the words ?

Related post [here](https://stackoverflow.com/q/5115257/465053). — RBT, Jan 17 '18 at 07:40

score 7 · Accepted Answer · edited May 23 '17 at 11:52

The actual strings are stored in the SharedStringTable. What you are getting are only the references to the elements in that string table.

Here's your sample modified to retrieve the values from the string table:

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePatah + "\\" + fileName, false))
{
   WorkbookPart workbookPart = doc.WorkbookPart;
   WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
   SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

   ArrayList data = new ArrayList();
   foreach (Row r in sheetData.Elements<Row>())
   {
      foreach (Cell c in r.Elements<Cell>())
      {
        if (c.DataType != null && c.DataType == CellValues.SharedString)
        {
            var stringId = Convert.ToInt32(c.InnerText); // Do some error checking here
            data.Add(workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(stringId).InnerText);
        }
      }
   }
}

Please note that this is just a crude example. For a little more complete examples you can look here.

Also, depending on what you need, you might find using a library such as EPPlus much easier (you can read and write directly to cells without worrying about the actual document format) than OpenXML SDK.

Read Excel file using OpenXML

1 Answers1