<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UploadExcel.aspx.cs" Inherits="ShowDataOnePage2010.UploadExcel" MasterPageFile="~/Site.Master" %>
<asp:Content ID="Content1" runat="server" contentplaceholderid="MainContent">
<table class="auto-style1">
<tr>
<td style="font-family: Georgia, "Times New Roman", Times, serif; font-size: medium; font-weight: bold; font-style: normal; font-variant: normal">Upload Excel Sheet :</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td>
<asp:Button ID="btnUpload" runat="server" Font-Bold="True" Text="Upload" OnClick="btnUpload_Click" />
</td>
</tr>
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td class="auto-style2" colspan="3">
<asp:Button ID="btnShow" runat="server" Font-Bold="True" Text="Show" OnClick="btnShow_Click" />
</td>
</tr>
<tr>
<td colspan="3">
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
</td>
</tr>
</table>
</asp:Content>
<asp:Content ID="Content2" runat="server" contentplaceholderid="HeadContent">
<style type="text/css">
.auto-style1 {
width: 100%;
}
.auto-style2 {
height: 30px;
}
</style>
</asp:Content>
UploadExcel.aspx.cs file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
namespace ShowDataOnePage2010
{
public partial class UploadExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string excelpath = Server.MapPath("~/ExcelSheets/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelpath);
string conString = string.Empty;
string extention = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extention)
{
case ".xls"://Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx"://Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelpath);
using (OleDbConnection excelCon = new OleDbConnection(conString))
{
excelCon.Open();
string sheet1 = excelCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["dbo.Student"].ToString();
DataTable dtexcelData = new DataTable();
dtexcelData.Columns.AddRange(new DataColumn[3]{
new DataColumn("Sno",typeof(string)),
new DataColumn("StName",typeof(string)),
new DataColumn("StFatherName",typeof(string))
});
using (OleDbDataAdapter da = new OleDbDataAdapter("Select * from [" + sheet1 + "]", excelCon))
{
da.Fill(dtexcelData);
}
excelCon.Open();
string conString1 = ConfigurationManager.ConnectionStrings["Decofloore2ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(conString1);
SqlBulkCopy BulkCopy = new SqlBulkCopy(con);
BulkCopy.DestinationTableName = "dbo.Student";
BulkCopy.ColumnMappings.Add("Sno", "STID");
BulkCopy.ColumnMappings.Add("StName", "StName");
BulkCopy.ColumnMappings.Add("StFatherName", "StFatherName");
con.Open();
BulkCopy.WriteToServer(dtexcelData);
con.Close();
}
}
protected void btnShow_Click(object sender, EventArgs e)
{
string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
SqlConnection SqlConn = new SqlConnection(StrCon);
string SqlQuery = "Select * from ShowOnePage";
SqlDataAdapter da = new SqlDataAdapter(SqlQuery, SqlConn);
DataTable dt = new DataTable("Student");
da.Fill(dt);
SqlConn.Open();
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
web.config File
<configuration>
<connectionStrings>
<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
providerName="System.Data.SqlClient" />
<add name="Decofloore2ConnectionString" connectionString="Data Source=PRADEEP-HP;Initial Catalog=Decofloore2;User ID=User_006;Password=user006"
providerName="System.Data.SqlClient" />
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>
</configuration>
https://www.youtube.com/channel/UCKLRUr6U5OFeu7FLOpQ-FSw/videos
0 comments
Post a Comment