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