In this article I will explain how to read and import Excel file (Excel sheet) data to GridView without using OLEDB in ASP.Net using C# and VB.Net.
Since OLEDB is not used, the Excel file (Excel sheet) data will be read and imported to GridView using OpenXml library in
ASP.Net using C# and VB.Net.
 
 

Install DocumentFormat.OpenXml package

In order to install DocumentFormat.OpenXml library using Nuget, please refer my article Install DocumentFormat.OpenXml package using NuGet.
 
 

HTML Markup

The HTML Markup consists of following controls:
FileUpload – For selecting the file.
Button – For uploading the selected file.
GridView – For displaying the data.
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="gvCustomers" 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 without using OELDB in ASP.Net using C# and VB.Net

When the ImportExcel button is clicked, the uploaded file is saved to a Files Folder (Directory) using SaveAs method of FileUpload control.
Then, then it is opened and read using OpenXml SpreadSheetDocument class object.
After that, the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, using nested FOR EACH loop is executed over the fetched rows and GetValue method (explained later) is called and columns are added to the DataTable.
Finally, the DataTable is assigned to the DataSource property of GridView.
 

GetValue

The GetValue function accepts SpreadsheetDocument and Cell class objects as a parameter where a cell value is determined and initialized.
Then, a check is performed if cell datatype NOT equal to NULL and cell value is string then, it returns the cell value.
C#
protected void ImportExcel(object sender, EventArgs e)
{
    //Save the uploaded Excel file.
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
    fuUpload.SaveAs(filePath);
 
    //Open the Excel file in Read Mode using OpenXml.
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        //Read the first Sheets 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++;
                }
            }
        }
        gvCustomers.DataSource = dt;
        gvCustomers.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(fuUpload.PostedFile.FileName)
    fuUpload.SaveAs(filePath)
 
    'Open the Excel file in Read Mode using OpenXml.
    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
        'Read the first Sheets 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
        gvCustomers.DataSource = dt
        gvCustomers.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

Uploading Excel File

Read and Import Excel without using OELDB in ASP.Net using C# and VB.Net
 

Imported file

Read and Import Excel without using OELDB in ASP.Net using C# and VB.Net
 
 

Downloads

Download Code