Import Excel Sheet Data To DataGridView Samples and examples

To import data from an excel sheet we use ASP.NET's DataGridView control. The code sample consists of an .aspx page followed by the code behind c-sharp file.
The web page gives users options to upload the excel sheet (.xls file). And the code behind file does the magic of loading the data into DataGridview.

HTML CODE
----------


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImportFromExcel.aspx.cs" Inherits="ImportFromExcel" %&RT;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&RT;

<html xmlns="http://www.w3.org/1999/xhtml" &RT;
<head runat="server"&RT;
    <title&RT;Untitled Page</title&RT;
</head&RT;
<body&RT;
    <form id="form1" runat="server"&RT;
    <div&RT;
        <table style="position: relative; left: 200px; top: 98px;"&RT;
            <tr&RT;
                <td colspan="3"&RT;
                    <asp:Label ID="lblError" runat="server" Visible="False" ForeColor="Red" Width="231px" style="position: static"&RT;</asp:Label&RT;</td&RT;
            </tr&RT;
            <tr&RT;
                <td colspan="2"&RT;
                    Select Your Excel File</td&RT;
                <td style="width: 100px"&RT;
                    <asp:FileUpload ID="FileUpload1" runat="server" /&RT;</td&RT;
            </tr&RT;
            <tr align="center"&RT;
                <td colspan="3"&RT;
                    <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="btnImport_Click" /&RT;
                    <asp:Button ID="btnCancel" runat="server" Text="Cancel" /&RT;</td&RT;
            </tr&RT;
            <tr&RT;
                <td colspan="3"&RT;
                    <asp:Label ID="lblMessage" runat="server" Text="" ForeColor="green"&RT;</asp:Label&RT;
                    <asp:GridView ID="MyGrid" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"
                        Style="left: 23px; position: relative; top: 13px" Width="204px" AllowPaging="True" OnPageIndexChanging="MyGrid_PageIndexChanging" PageSize="5"&RT;
                        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /&RT;
                        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /&RT;
                        <EditRowStyle BackColor="#999999" /&RT;
                        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /&RT;
                        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /&RT;
                        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /&RT;
                        <AlternatingRowStyle BackColor="White" ForeColor="#284775" /&RT;
                    </asp:GridView&RT;
                </td&RT;
            </tr&RT;
        </table&RT;            
    </div&RT;
    </form&RT;
</body&RT;
</html&RT;

Code Behind (.cs)


using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.SqlClient;
using System.Data.OleDb;


public partial class ImportFromExcel : System.Web.UI.Page
{   
    public SqlConnection sqlcon = new SqlConnection("Data Source = MINDS3; User ID = sa; Password = minds; Initial Catalog=simple");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            lblError.Visible = false;
        }

    }
    protected void btnImport_Click(object sender, EventArgs e)
    {
        string ExcelFilename = FileUpload1.PostedFile.FileName;
        string ExcelFileNameonly = ExcelFilename.Substring(ExcelFilename.LastIndexOf("\\") + 1);
        string FileExt = ExcelFilename.Substring(ExcelFilename.LastIndexOf(".") + 1);
        string Filenamewithoutextn = ExcelFileNameonly.Remove(ExcelFileNameonly.LastIndexOf("."));
        if(FileExt.ToLower() == "xls")
        {            
            try
            {
                FileUpload1.PostedFile.SaveAs(Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls");
                string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls;Extended Properties=Excel 8.0;";

                OleDbConnection olecon = new OleDbConnection(strConnectionString);

                int RecordCount = 0;
                lblMessage.Visible = false;
                lblError.Visible = false;
                olecon.Open();
                OleDbCommand cmd = olecon.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Import$]";
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                OleDbDataReader dr = cmd.ExecuteReader();

                sqlcon.Open();
                string deletestr = "delete from importdata";
                SqlCommand sqlcmd = new SqlCommand(deletestr, sqlcon);
                sqlcmd.ExecuteNonQuery();

                while (dr.Read())
                {
                    SqlCommand cmd1 = sqlcon.CreateCommand();
                    cmd1.CommandText = "INSERT INTO ImportData values ('" + dr[0] + "','" + dr[1] + "')";
                    cmd1.ExecuteNonQuery();
                    RecordCount++;
                    lblMessage.Visible = true;
                    lblMessage.Text = " Processed Record # " + RecordCount.ToString();
                }
                dr.Close();
                olecon.Close();
                BindGrid();
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                lblError.Visible = true;
                lblError.Text = ex.Message;
            }
        }
        
    }

    private void BindGrid()
    {
        try
        {
            lblError.Visible = false;
            SqlCommand cmd2 = sqlcon.CreateCommand();
            cmd2.CommandText = "SELECT * FROM ImportData order by UserID";

            DataSet sds = new DataSet();

            SqlDataAdapter sda = new SqlDataAdapter(cmd2);

            sda.Fill(sds, "ImportData");
            MyGrid.DataSource = sds.Tables["ImportData"].DefaultView;
            MyGrid.DataBind();
        }
        catch (Exception ex)
        {
            lblError.Visible = true;
            lblError.Text = ex.Message;
        }

    }
    protected void MyGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        MyGrid.PageIndex = e.NewPageIndex;
        BindGrid();
    }
}

Import Excel Sheet Data To DataGridView

Code Behind (.cs)

Post Comment: