How To Read Data From An Excel Sheet

In this article, I will guide you on how to read data from an excel file using OpenXML.

I have used the Console App as a demo.

Step 1

Open Visual studio -> New Project -> Console App.

Project Name/App Name.

Step 2 - Add open XML package.

We can add the OpenXML package using the Package Manager Console. 

Install-Package DocumentFormat.OpenXml -Version 2.17.1

Manage Nuget Packages.

Step 3

I have created one method, in which we open and read the data from excel, and load the data in DataTable.

Note: My file path is C:\WorkingProjects\Practice\circuit-import-template.xlsx 

var table = new DataTable();
using(SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@ "C:\WorkingProjects\Practice\circuit-import-template.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)) {
        table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
    }
    //this will also include your header row...
    foreach(Row row in rows) {
        DataRow tempRow = table.NewRow();
        for (int i = 0; i < row.Descendants < Cell > ().Count(); i++) {
            tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants < Cell > ().ElementAt(i));
        }
        table.Rows.Add(tempRow);
    }
}
table.Rows.RemoveAt(0);
return table;

"Get cell value" will read the cell data.

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;
    }
}

Step 5

My File Path:

Step 6

File Data.

Step 7

Execute code and check data is loaded in Datatable.

Once our data is loaded, we can add data to the database.

I have added the full code as an attachment.