Read Excel as DataTable using OpenXML and C#

July 11, 2014 by Anuraj

.Net .Net 3.0 / 3.5 .Net 4.0 ASP.Net ASP.Net MVC

In the current project we were using OpenXML extensively for reading Excel files. Here is the code snippet, which will help you to read / convert Excel files to DataTable.

using System;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static DataTable ReadAsDataTable(string fileName)
    DataTable dataTable = new DataTable();
    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, 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))
            dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));

        foreach (Row row in rows)
            DataRow dataRow = dataTable.NewRow();
            for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));



    return dataTable;

private 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;
        return value;

Happy Programming :)

Update : Previous code snippet had some problem, now I fixed it.

Copyright © 2024 Anuraj. Blog content licensed under the Creative Commons CC BY 2.5 | Unless otherwise stated or granted, code samples licensed under the MIT license. This is a personal blog. The opinions expressed here represent my own and not those of my employer. Powered by Jekyll. Hosted with ❤ by GitHub