In one of my presvious article, I have explained how you can read excel in C# using OLEDB in Console application, now in this article, I have how you can read excel file using C# and import data into GridView by binding datatable in ASP.NET web-forms.

sample-excel-file-to-read-using-csharp-min.png

Sample Excel file to read/ upload in GridView using C# / Datatable

Step 1: Add the below connection strings in your Project's Web.Config file

<configuration>
  <connectionStrings>
       <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
     <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
    </connectionStrings>  
</configuration>

Step 2: Create a new Default.aspx file, with a GridView, FileUpload control in it

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ReadExcelInDatatable.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Read Excel and import it into GridView using Datatable C#</title>
</head>
<body>
    <form id="form1" runat="server">
       
   <asp:FileUpload id="FileUpload1" runat="server">
   </asp:FileUpload>
 <br />  

   <asp:Button id="UploadButton" 
       Text="Upload file"
       OnClick="UploadButton_Click"
       runat="server">
   </asp:Button>    
      
    <br />  
      
      
      
    <asp:GridView ID="GridView1" runat="server">  
      
    </asp:GridView>  

    </form>
</body>
</html>

Step 3: Once you have added GridView and FileUpload control, let's create the C# code to Get file, upload it on server inside "Uploads" folder and fetch all records of excel file in datatable, then bind datatable with GridView and show it in the View to user.

You will need to refer these namespace

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;

Now, create the method to call on Upload Button Click in C# as show below

protected void UploadButton_Click(Object sender, EventArgs e)
        {
           //check if file exists
            if (FileUpload1.HasFile)
            {
                string filePath = string.Empty;
                
                string path = Server.MapPath("~/Uploads/");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                //get file path
                filePath = path + Path.GetFileName(FileUpload1.FileName);
                //get file extenstion
                string extension = Path.GetExtension(FileUpload1.FileName);
                //save file on "Uploads" folder of project
                FileUpload1.SaveAs(filePath);

                string conString = string.Empty;
                //check file extension
                switch (extension)
                {
                    case ".xls": //Excel 97-03.
                        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                        break;
                    case ".xlsx": //Excel 07 and above.
                        conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                        break;
                }

                //create datatable object
                DataTable dt = new DataTable();
                conString = string.Format(conString, filePath);

                //Use OldDb to read excel
                using (OleDbConnection connExcel = new OleDbConnection(conString))
                {
                    using (OleDbCommand cmdExcel = new OleDbCommand())
                    {
                        using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                        {
                            cmdExcel.Connection = connExcel;

                            //Get the name of First Sheet.
                            connExcel.Open();
                            DataTable dtExcelSchema;
                            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            connExcel.Close();

                            //Read Data from First Sheet.
                            connExcel.Open();
                            cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                            odaExcel.SelectCommand = cmdExcel;
                            odaExcel.Fill(dt);
                            connExcel.Close();
                        }
                    }
                }
                
                //bind datatable with GridView
                GridView1.DataSource = dt;
                GridView1.DataBind();

            }
            else
            {
               //You did not specify a file to upload
            }
        }

As you can see in the above code I have commented out important lines of code to understand it, basically we are using OleDB connection to read excel in C# and then binding data to gridview.

Here, we are first checking the extension of excel file, then we are using OleDB connection to read uploaded excel file, using which we are filling all the values in datatable and then binding datatable with GridView.

Once, you have done all the above steps build and run your project in browser, you will see output as below

read-excel-file-in-c-sharp-upload-into-gridview-using-datatable-min.gif

You can download project files from here : ReadExcelFileInDatatable.

You may also like to read:

Import data from Excel to SQL Server

Save Selected Dropdown and Text value in database in ASP.NET Web-Forms

Using CKEditor (Rich-Text Editor) in ASP.NET Core

Large file upload with Progress bar in ASP.NET Core (using Tus)

jQuery Datatable Server side processing in ASP.NET Core