Design Page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<asp:Label ID="lblMsg" runat="server" Text="Label" Visible="false" ForeColor="Red"></asp:Label>
</div>
</form>
</body>
</html>
.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.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
lblMsg.Text = "";
String Path = Server.MapPath("~/FolderName/");
var directory = new DirectoryInfo(Path);
string[] name = new string[] { "Employee", "Emp"};//Import Files With Specific
Name
var fileName = (from f in directory.GetFiles()
from n in name
where f.Name.Contains(n)
orderby f.LastWriteTime descending //Last
Modified Date
select f).First();
string excelPath = Server.MapPath("~/FolderName/") + fileName;
if (fileName.Extension == ".xls" || fileName.Extension == ".xlsx")
{
string conString = string.Empty;
switch (fileName.Extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString,
excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 =
excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
using (OleDbDataAdapter
oda = new
OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["COMMONConnectionString"].ConnectionString))
{
for (int i = 0; i <
dtExcelData.Rows.Count; i++)
{
DataRow row =
dtExcelData.Rows[i];
int columnCount =
dtExcelData.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount;
j++)
{
columns[j]
= row[j].ToString();
}
string sql = "Insert Into
TableName(Empid,Name,Email,Salary)";
sql += "VALUES('" +
columns[0] + "','" + columns[1] + "','" + columns[2] + "','" + columns[3] + "')";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
}
lblMsg.Visible = true;
lblMsg.Text = "Excel Data Imported To SQL Server Successfully";
}
}
}
else
{
lblMsg.Visible = true;
lblMsg.Text = "File Format is Not Supported. Try Again With Excel
File Format";
}
}
catch (Exception ex)
{
lblMsg.Visible = true;
lblMsg.Text = ex.Message;
}
}
}
<connectionStrings>
<add name="COMMONConnectionString" connectionString="Data
Source=....;Initial Catalog=.....;User ID=sa;Password=123" 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 = "Excel07+ConString" connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>
No comments:
Post a Comment