excel匯入資料庫
使用者有時要大量資料資料要輸入時,不想一筆一筆key,為了結省他們的時間,只好幫他們做上傳excel後,再匯入….
一、Create The Below Table:
CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] NOT NULL,
[EmployeeName] [varchar](max) NOT NULL,
[Designation] [varchar](max) NOT NULL,
[Posting] [varchar](max) NOT NULL,
[Dept] [varchar](max) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
二、aspx:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
<style type="text/css">
.auto-style1 {
color: #FF0000;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
員工資料excel匯入(請用excel2007以上的版本附檔名為<span class="auto-style1">*.xlsx</span>,書籤只能用英文 <span class="auto-style1">Sheet1</span><br />
<br />
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="匯入" OnClick="btnUpload_Click" />
</div>
</form>
</body>
</html>
三、CS:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class test_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string sPath = Server.MapPath("~/excel/" + FileUpload1.FileName);
FileUpload1.SaveAs(sPath);
ImporttoSQL(sPath);
}
}
private void ImporttoSQL(string sPath)
{
// Connect to Excel 2007 earlier version
//string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
// Connect to Excel 2007 (and later) files with the Xlsx file extension
string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);
string sDestConstr = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("Select [Employee ID],[Employee Name],[Designation],[Posting],[Dept] FROM [{0}]", "Sheet1
quot;);
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "Employee";
//You can mannualy set the column mapping by the following way.
//bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code");
bulkCopy.WriteToServer(dr);
}
}
}
}
}
四、資料庫:
五、EXCEL格式:
1、第一列的標題要和資料庫的欄位一樣。
2、書籤只能用英文的 Sheet1
六、執行:
1、
2、選擇excel檔
3、點選匯入
4、
5、查詢資料庫
發佈留言