Saturday, 11 November 2017

Upload Excel File in sqlserver database in asp.net C#

UploadExcel1.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UploadExcel1.aspx.cs" Inherits="ShowDataOnePage2010.UploadExcel1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Panel ID="Panel1" runat="server">
                <asp:FileUpload ID="FileUpload1" runat="server" />
                <asp:Button ID="btnUpload" runat="server" Text="Upload"
                    OnClick="btnUpload_Click" />
                <br />
                <asp:Label ID="lblMessage" runat="server" Text="" />
            </asp:Panel>
            <asp:Panel ID="Panel2" runat="server" Visible="false">
                <asp:Label ID="Label5" runat="server" Text="File Name" />
                <asp:Label ID="lblFileName" runat="server" Text="" />
                <br />
                <asp:Label ID="Label2" runat="server" Text="Select Sheet" />
                <asp:DropDownList ID="ddlSheets" runat="server" AppendDataBoundItems="true">
                </asp:DropDownList>
                <br />
                <asp:Label ID="Label3" runat="server" Text="Enter Source Table Name" />
                <asp:TextBox ID="txtTable" runat="server"></asp:TextBox>
    <br />
    <asp:Label ID="Label1" runat="server" Text="Has Header Row?" />
    <br />
    <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>
    <br />
    <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
    <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />
            </asp:Panel>
        </div>
    </form>
</body>
</html>

UploadExcel1.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using System.Data.SqlClient;

namespace ShowDataOnePage2010
{
    public partial class UploadExcel1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        private void GetExcelSheets(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;
            }

            //Get the Sheets in Excel WorkBoo
            conStr = String.Format(conStr, FilePath, isHDR);
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            cmdExcel.Connection = connExcel;
            connExcel.Open();

            //Bind the Sheets to DropDownList
            ddlSheets.Items.Clear();
            ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
            ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            ddlSheets.DataTextField = "TABLE_NAME";
            ddlSheets.DataValueField = "TABLE_NAME";
            ddlSheets.DataBind();
            connExcel.Close();
            txtTable.Text = "";
            lblFileName.Text = Path.GetFileName(FilePath);
            Panel2.Visible = true;
            Panel1.Visible = false;
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            string FileName = lblFileName.Text;
            string Extension = Path.GetExtension(FileName);
            string FolderPath = Server.MapPath(ConfigurationManager
                               .AppSettings["FolderPath"]);
            string CommandText = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    CommandText = "spx_ImportFromExcel03";
                    break;
                case ".xlsx": //Excel 07
                    CommandText = "spx_ImportFromExcel07";
                    break;
            }
            //Read Excel Sheet using Stored Procedure
            //And import the data into Database Table
            String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CommandText;
            cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value =ddlSheets.SelectedItem.Text;
            cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value =FolderPath + FileName;
            cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value =rbHDR.SelectedItem.Text;
            cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value =txtTable.Text;
            cmd.Connection = con;
            try
            {
                con.Open();
                object count = cmd.ExecuteNonQuery();
                lblMessage.ForeColor = System.Drawing.Color.Green;
                lblMessage.Text = count.ToString() + " records inserted.";
            }
            catch (Exception ex)
            {
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Text = ex.Message;
            }
            finally
            {
                con.Close();
                con.Dispose();
                Panel1.Visible = true;
                Panel2.Visible = false;
            }
        }
        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);
                GetExcelSheets(FilePath, Extension, "Yes");
            }
        }
    }
}

0 comments

Post a Comment