From Excel to DataTable in C# with Open XML

Question

I'm using Visual Studio 2008 and I need create a DataTable from a Excel Sheet using the Open XML SDK 2.0. I need to create it with the DataTable columns with the first row of the sheet and complete it with the rest of values.

Does anyone have a example code or a link that can help me to do this?

Answer 1

I think this should do what you're asking. The other function is there just to deal with if you have shared strings, which I assume you do in your column headers. Not sure this is perfect, but I hope it helps.

static void Main(string[] args) {     DataTable dt = new DataTable();      using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"..\..\example.xlsx", false))     {          WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;         IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();         string relationshipId = sheets.First().Id.Value;         WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);         Worksheet workSheet = worksheetPart.Worksheet;         SheetData sheetData = workSheet.GetFirstChild<SheetData>();         IEnumerable<Row> rows = sheetData.Descendants<Row>();          foreach (Cell cell in rows.ElementAt(0))         {             dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));         }          foreach (Row row in rows) //this will also include your header row...         {             DataRow tempRow = dt.NewRow();              for (int i = 0; i < row.Descendants<Cell>().Count(); i++)             {                 tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));             }              dt.Rows.Add(tempRow);         }      }     dt.Rows.RemoveAt(0); //...so i'm taking it out here.  }   public static string GetCellValue(SpreadsheetDocument document, Cell cell) {     SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;     string value = cell.CellValue.InnerXml;      if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)     {         return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;     }     else     {         return value;     } } 

Answer 2

Hi The above code is working fine except one change

replace the below line of code

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1)); 

with

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i)); 

If you use (i-1) it will throw an exception:

specified argument was out of the range of valid values. parameter name index.