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:
<%@ 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:
-
<add name ="Excel03ConString"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={FilePath};Extended Properties='Excel 8.0;HDR={Yes/No}'"/>
-
<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")
{ connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
}
else if (ext == ".xlsx")
{ 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;
conn.Open();
DataTable dtSchema;
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
conn.Open();
cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dt);
conn.Close();
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:
- https://www.microsoft.com/en-in/download/details.aspx?id=13255
- https://www.microsoft.com/en-in/download/details.aspx?id=23734
Install both of them & restart the machine once.
Whoo... Error gone... :)
Enjoy!
