Import Excel Data to DataTable and Display in Grid in C#, ASP.NET

Tushar Parmar

Rate me:

3.50/5 (4 votes)

12 Jul 2015CPOL1 min read

52.6K

Here is an example of how to import full excel file into a DataTable and bind it with a data grid. also the solution for error "The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine" is included.

Introduction

The example shows how you can directly import full Excel sheet into a datatable in C#. I have created an example of the same with importing both *.xls & *.xlsx file. The common problem when using this method will be "The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine". I have also added a solution for the same.

Using the Code

Let us create GUI for the same as below on an aspx page:

XML

<%@ Page Language="C#" AutoEventWireup="true" 
CodeBehind="WebForm1.aspx.cs" Inherits="Excel_Import.WebForm1" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <div style="float: left;">
                <asp:FileUpload ID="FileUploadControl" runat="server" />
        <asp:Button ID="btnUpload" runat="server" 
        Text="Upload" OnClick="btnUpload_Click" />            
        </div>
            <div style="float: left;">
                <asp:Label ID="Label" runat="server" 
                Text="Is Header Exists?"></asp:Label>
                <asp:DropDownList ID="ddlIsHeaderExists" runat="server">
                    <asp:ListItem Value="Yes">Yes</asp:ListItem>
                    <asp:ListItem Value="No">No</asp:ListItem>
                </asp:DropDownList>
                <label runat="server" style="color: red;" 
                id="lblErrorMessage" visible="false"></label>
            </div>
            <div style="clear: both;padding-top:20px;"></div>
            <div>
                <asp:GridView ID="ExcelGridView" 
                runat="server" AllowPaging="false">
                </asp:GridView>
            </div>
        </div>
    </form>
</body>
</html>

This will allow you to upload Excel file for import.

Now for importing Excel file, we will use "Microsoft.Jet.OLEDB.4.0" (for Excel fiel 97-03 format) & "Microsoft.ACE.OLEDB.12.0". The core part of importing Excel file is to use the below two connections strings:

XML

<add name ="Excel03ConString" 
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={FilePath};Extended Properties='Excel 8.0;HDR={Yes/No}'"/>

XML

<add name ="Excel03ConString" 
connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

Now our C# code behind file will be as shown below:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Excel_Import
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (FileUploadControl.HasFile)
            {
                string Ext = Path.GetExtension(FileUploadControl.PostedFile.FileName);
                if (Ext == ".xls" || Ext == ".xlsx")
                {
                    lblErrorMessage.Visible = false;
                    string Name = Path.GetFileName(FileUploadControl.PostedFile.FileName);
                    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                    string FilePath = Server.MapPath(FolderPath + Name);
                    FileUploadControl.SaveAs(FilePath);
                    FillGridFromExcelSheet(FilePath, Ext, ddlIsHeaderExists.SelectedValue);
                }
                else
                {
                    lblErrorMessage.Visible = true;
                    lblErrorMessage.InnerText = "Please upload valid Excel File";                    
                    ExcelGridView.DataSource = null;
                    ExcelGridView.DataBind();
                }
            }
        }
        private void FillGridFromExcelSheet(string FilePath, string ext, string isHader)
        {
            string connectionString = "";
            if (ext == ".xls")
            {   //For Excel 97-03
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
                Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
            }
            else if (ext == ".xlsx")
            {    //For Excel 07 and greater
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
            }
            connectionString = String.Format(connectionString, FilePath, isHader);
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            cmd.Connection = conn;
            //Fetch 1st Sheet Name
            conn.Open();
            DataTable dtSchema;
            dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
            conn.Close();
            //Read all data of fetched Sheet to a Data Table
            conn.Open();
            cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(dt);
            conn.Close();
            //Bind Sheet Data to GridView
            ExcelGridView.Caption = Path.GetFileName(FilePath);
            ExcelGridView.DataSource = dt;
            ExcelGridView.DataBind();
        }
    }
}

This is how we can add Excel import with C#.

There will be a common error which I faced after using "Microsoft.ACE.OLEDB.12.0" is "The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine". I have a clear solution for that.

Download the below two EXE files:

Install both of them & restart the machine once.

Whoo... Error gone... :)

Enjoy!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Tushar Parmar

United States

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Import Excel Data to DataTable and Display in Grid in C#, ASP.NET

Introduction

Using the Code

License

Comments and Discussions