Sunday, June 2, 2013

Read and Import Excel Sheet into ASP.Net GridView Control

Note: Add Folder Named (Files) in the project



<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="CS.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="" >
<head runat="server">
    <title>Import Excel Data into GridView</title>
    <form id="form1" runat="server">
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
        <br />
        <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label>
        <asp:RadioButtonList ID="rbHDR" runat="server">
            <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" ></asp:ListItem>
            <asp:ListItem Text = "No" Value = "No"></asp:ListItem>
        <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true"  >

using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration; 
public partial class _Default : System.Web.UI.Page 
    protected void Page_Load(object sender, EventArgs e)
    protected void btnUpload_Click(object sender, EventArgs e)
        if (FileUpload1.HasFile)
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
        string conStr="";
        switch (Extension)
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable(); 
        cmdExcel.Connection = connExcel;
        //Get the name of First Sheet
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        //Read Data from First Sheet
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath); 
        GridView1.DataSource = dt;
    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
        GridView1.PageIndex = e.NewPageIndex;

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

Read and Import Excel Sheet into ASP.Net GridView Control 2013-06-02 11-46-35

if you get the following Exception :

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.


Read this Thread:
trying to connect to an Access database in visual studio but keep getting this error?
try installing this first:
however if, like me, that doesnt work for you, try the following method:
NOTE: this DOES work for office 2010 even though it is for 2007 office, dont ask me why it just does :)
1. download and install this:
2. in VS click add data source, follow the wizard and enjoy! :)
P.S thanks to everyone that helped me at my original thread:


Post a Comment