In this article I will explain with an example, how to read and import Excel file (Excel sheet) data to DataTable using OpenXml in ASP.Net with C# and VB.Net.
 
 
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Note: You will need to install the OpenXml SDK 2.0 in your Windows Operating System.
 
 
HTML Markup
The following HTML Markup consists of an ASP.Net FileUpload control, a Button and a GridView.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
 
VB.Net
Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
 
 
Importing Excel data to DataTable using OpenXml in ASP.Net
When the Import Button is clicked, the following event handler is clicked. First the uploaded Excel file is saved to a folder named Files and then it is opened and read using OpenXml SpreadSheetDocument class object.
Then the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Finally a loop is executed over the fetched rows and a DataTable is populated which is then bound to the GridView.
C#
protected void ImportExcel(object sender, EventArgs e)
{
    //Save the uploaded Excel file.
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);
 
    //Open the Excel file in Read Mode using OpenXml.
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        //Read the first Sheet from Excel file.
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
 
        //Get the Worksheet instance.
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
 
        //Fetch all the rows present in the Worksheet.
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
 
        //Create a new DataTable.
        DataTable dt = new DataTable();
 
        //Loop through the Worksheet rows.
        foreach (Row row in rows)
        {
            //Use the first row to add columns to DataTable.
            if (row.RowIndex.Value == 1)
            {
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Columns.Add(GetValue(doc, cell));
                }
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                    i++;
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}
 
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
    string value = cell.CellValue.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
    }
    return value;
}
 
VB.Net
Protected Sub ImportExcel(sender As Object, e As EventArgs)
    'Save the uploaded Excel file.
    Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)
 
    'Open the Excel file in Read Mode using OpenXml.
    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
        'Read the first Sheet from Excel file.
        Dim sheet As Sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild(Of Sheet)()
 
        'Get the Worksheet instance.
        Dim worksheet As Worksheet = TryCast(doc.WorkbookPart.GetPartById(sheet.Id.Value), WorksheetPart).Worksheet
 
        'Fetch all the rows present in the Worksheet.
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Descendants(Of Row)()
 
        'Create a new DataTable.
        Dim dt As New DataTable()
 
        'Loop through the Worksheet rows.
        For Each row As Row In rows
            'Use the first row to add columns to DataTable.
            If row.RowIndex.Value = 1 Then
                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Columns.Add(GetValue(doc, cell))
                Next
            Else
                'Add rows to DataTable.
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Rows(dt.Rows.Count - 1)(i) = GetValue(doc, cell)
                    i += 1
                Next
            End If
        Next
        GridView1.DataSource = dt
        GridView1.DataBind()
    End Using
End Sub
 
Private Function GetValue(doc As SpreadsheetDocument, cell As Cell) As String
    Dim value As String = cell.CellValue.InnerText
    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
        Return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(Integer.Parse(value)).InnerText
    End If
    Return value
End Function
 
 
Screenshots
The Excel File
Read and Import Excel data to DataTable using OpenXml in ASP.Net with C# and VB.Net
 
GridView displaying Excel data
Read and Import Excel data to DataTable using OpenXml in ASP.Net with C# and VB.Net
 
 
Note: While importing Excel using OpenXml library you might get the following error.
CS0012: The type 'System.IO.Packaging.Package' is defined in an assembly that is not referenced. You must add a reference to assembly 'WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.
 
 
Downloads