.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Code Snippets >> ASP.NET Controls >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Show Excelsheet data into gridview and then insert to database

Posted By:satyapriyanayak       Posted Date: November 14, 2013    Points: 40    Category: ASP.NET Controls    URL: http://www.dotnetspark.com  

We will know how to Show Excelsheet data into gridview and then insert to database.


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Show_Excel_gridview_insert_to_db._Default" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
    <form id="form1" runat="server">
    <asp:Table ID="TableButtons" runat="server">
            <asp:TableRow ID="TableRow1" runat="server">
                <asp:TableCell ID="TableCell1" runat="server">
                    <asp:Button ID="ButtonUpload" runat="server"
                    Text="Upload Excel Files" OnClick="ButtonUpload_Click" />
                <asp:TableCell ID="TableCell2" runat="server">
                    <asp:Button ID="ButtonView" runat="server"
                    Text="View Excel Data"  OnClick="ButtonView_Click" />
        <asp:Panel ID="PanelUpload" runat="server" Visible="False">
            <asp:FileUpload ID="FileUploadExcel" runat="server" />
            <br />
            Please select an Excel file to import:<br />
            <asp:Button ID="ButtonUploadFile" runat="server"
                Text="Upload File" OnClick="ButtonUploadFile_Click" /><br />
            <asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
        <asp:Panel ID="PanelView" runat="server" Visible="False">
            <asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>
            <asp:GridView ID="GridViewExcel" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"
                BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
                <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
                <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
                <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
                <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
        <asp:Panel ID="PanelImport" runat="server" Visible="False">
            <asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>
        <asp:Label ID="lblError" runat="server" Text=""></asp:Label>
    <asp:Button ID="btn_insert" runat="server" onclick="btn_insert_Click"
        Text="Insert to Db" />
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace Show_Excel_gridview_insert_to_db
    public partial class _Default : System.Web.UI.Page
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlCommand com;

        protected void ButtonUploadFile_Click(object sender, EventArgs e)
            if (FileUploadExcel.HasFile)
                    LabelUpload.Text = "Upload File Name: " +
                        FileUploadExcel.PostedFile.FileName + "<br>" +
                        "Type: " + FileUploadExcel.PostedFile.ContentType +
                        " File Size: " + FileUploadExcel.PostedFile.ContentLength +
                        " kb<br>";
                catch (System.NullReferenceException ex)
                    LabelUpload.Text = "Error: " + ex.Message;
                LabelUpload.Text = "Please select a file to upload.";
        protected OleDbCommand ExcelConnection()

            string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection Con = new OleDbConnection(ConnStr);
            OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con);
            return com;

        protected void ButtonView_Click(object sender, System.EventArgs e)
            PanelUpload.Visible = false;
            PanelView.Visible = true;
            PanelImport.Visible = false;

            OleDbDataAdapter oledbda = new OleDbDataAdapter();
            oledbda.SelectCommand = ExcelConnection();
            DataSet ds = new DataSet();
            GridViewExcel.DataSource = ds.Tables[0].DefaultView;
        protected void ButtonUpload_Click(object sender, System.EventArgs e)
            PanelUpload.Visible = true;
            PanelView.Visible = false;
            PanelImport.Visible = false;

        protected void btn_insert_Click(object sender, EventArgs e)
            foreach (GridViewRow g1 in GridViewExcel.Rows)
                SqlConnection con = new SqlConnection(connStr);
                com = new SqlCommand("insert into student(sid,sname,smarks,saddress) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')", con);

            Label1.Text = "Records inserted successfully";


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend