Show excel data in gridview and insert it in database

Show excel data in gridview and insert it in database

Here we do upload excel using fileuploader   and then  display its data in gridview.

DataBase Tables

 Table creation



CREATE TABLE [dbo].[excel_test](
[cust_id] [varchar](50) NULL,
[city] [varchar](150) NULL,
[country] [varchar](150) NULL,
[post] [varchar](150) NULL
) ON [PRIMARY]

.Aspx Part

   <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:RadioButtonList>

        <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging = "PageIndexChanging"  AutoGenerateColumns="false" >

       

       <Columns>

       <asp:TemplateField HeaderText="CustomerID">

       <ItemTemplate>

       <asp:Label ID="CustomerID" runat="server" Text='<%#Eval("CustomerID") %>'></asp:Label>

       </ItemTemplate>

       

       </asp:TemplateField>

       

       <asp:TemplateField HeaderText="city">

       <ItemTemplate>

       <asp:Label ID="city" runat="server" Text='<%#Eval("city") %>'></asp:Label>

       </ItemTemplate>

       

       </asp:TemplateField>

       <asp:TemplateField HeaderText="Country">

       <ItemTemplate>

       <asp:Label ID="Country" runat="server" Text='<%#Eval("Country") %>'></asp:Label>

       </ItemTemplate>

       

       </asp:TemplateField>

       <asp:TemplateField HeaderText="PostalCode">

       <ItemTemplate>

       <asp:Label ID="PostalCode" runat="server" Text='<%#Eval("PostalCode") %>'></asp:Label>

       </ItemTemplate>

       

       </asp:TemplateField>

       </Columns>

        </asp:GridView>

    </div>

    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 

        Text="save to Db" />

It will look like 

.Cs Part

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCon"].ToString());

    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);

            FileUpload1.SaveAs(FilePath);

            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;

                break;

            case ".xlsx": //Excel 07

                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

                break;

        }

        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

        connExcel.Open();

        DataTable dtExcelSchema;

        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

        connExcel.Close();

        //Read Data from First Sheet

        connExcel.Open();

        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

        oda.SelectCommand = cmdExcel;

        oda.Fill(dt);

        connExcel.Close(); 

        //Bind Data to GridView

        GridView1.Caption = Path.GetFileName(FilePath); 

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

    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;

        GridView1.DataBind();  

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        con.Open();

        foreach (GridViewRow gvr in GridView1.Rows)

        {

            Label lb_c_id = gvr.Cells[0].FindControl("CustomerID") as Label;

            Label lb_city = gvr.Cells[1].FindControl("City") as Label;

             Label lb_country = gvr.Cells[2].FindControl("Country") as Label;

//            Label lb_p_code = gvr.Cells[3].FindControl("PostalCode") as Label;

            string qu = "insert into excel_test(cust_id,city,country)";

            qu += "values('" + lb_c_id.Text + "','" + lb_city.Text + "','" + lb_country.Text + "')";

            SqlCommand cmd = new SqlCommand(qu, con);

            cmd.ExecuteNonQuery();

        }

        }

In this artical i am using two button 

1 for uplaod excel and 2nd for insert excel data in to database.