CODEWITHSUNDEEP
c#excelopenxml
rednerus
rednerus

Reputation: 401

Read Excel file using OpenXML

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 ?

Upvotes: 4

Views: 12130

Answers (1)

Darko Kenda
Darko Kenda

Reputation: 4960

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.

Upvotes: 7

Related Questions