Click here to Skip to main content
15,937,447 members
Articles / Web Development / ASP.NET
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
3.50/5 (4 votes)
12 Jul 2015CPOL1 min read 52.8K   9   5
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:

  1. XML
    <add name ="Excel03ConString" 
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source={FilePath};Extended Properties='Excel 8.0;HDR={Yes/No}'"/>
  2. 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:

C#
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:

  1. https://www.microsoft.com/en-in/download/details.aspx?id=13255
  2. https://www.microsoft.com/en-in/download/details.aspx?id=23734

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
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionvb code explain in c# plz.. Pin
Member 137717159-Apr-18 23:57
Member 137717159-Apr-18 23:57 
QuestionImport fromm Excel Dropdown value is not working sir (Other simple row value is inserted successfully) Pin
Anuj Mehta8-Sep-17 0:03
Anuj Mehta8-Sep-17 0:03 
GeneralNice one Pin
Member 1338498230-Aug-17 21:13
Member 1338498230-Aug-17 21:13 
QuestionAnother way to remove the error Pin
Joan Magnet12-Jul-15 23:55
Joan Magnet12-Jul-15 23:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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