Excel 匯入資料庫與備份
看完http://www.dotblogs.com.tw/dislin/archive/2010/07/02/16331.aspx
稍微進行了修改，因之前有慘痛的經驗，所以就改了下
把匯入前加了一個備份動作。
首先設計要匯入的資料表
第二準備要匯入的EXCEL檔案名稱為test.xls
後面的查會依工作表來當table查詢
第三就是開始設計頁面了
前台設計
1: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="exceltosql.aspx.cs" Inherits="exceltosql" %>
2:
3: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4:
5: <html xmlns="http://www.w3.org/1999/xhtml">
6: <head runat="server">
7: <title></title>
8: </head>
9: <body>
10: <form id="form1" runat="server">
11: <div>
12:
13: </div>
14: <asp:FileUpload ID="FileUpload1" runat="server" />
15: <p>
16: <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
17: </p>
18: <asp:GridView ID="GridView1" runat="server">
19: </asp:GridView>
20: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
21: ConnectionString="<%$ ConnectionStrings:DA_DATABASEConnectionString2 %>"
22: SelectCommand="SELECT * FROM [book]"></asp:SqlDataSource>
23: </form>
24: </body>
25: </html>
後台
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Web;
5: using System.Web.UI;
6: using System.Web.UI.WebControls;
7: using System.Data.OleDb;
8: using System.Data.SqlClient;
9: using System.Web.Configuration;
10: using System.Data.Common;
11: using System.Data;
12: using System.Configuration;
13: public partial class exceltosql : System.Web.UI.Page
14: {
15: protected void Page_Load(object sender, EventArgs e)
16: {
17:
18: }
19: protected void Button1_Click(object sender, EventArgs e)
20: {
21: try
22: {
23: //建立目錄
24: System.IO.Directory.CreateDirectory(@"c:\SQLBackup\");
25:
26: SqlConnection connect;
27: //建立連線字串
28: string con = ConfigurationManager.ConnectionStrings["DA_DatabaseConnectionString"].ToString();
29: connect = new SqlConnection(con);
30: connect.Open();
31: SqlCommand command;
32: command = new SqlCommand(@"backup database DA_DATABASE to disk ='c:\SQLBackup\" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss") + ".bak' with init,stats=10", connect);
33: //執行
34: command.ExecuteNonQuery();
35: connect.Close();
36: }
37:
38: catch
39: {
40: Response.Write("<script>parent.location.href='ErrorMessage.aspx'</script>");
41: }
42:
43:
44: //Excel檔案的實體路徑
45: string ExcelPath = Request.Files[0].FileName.ToString();
46:
47: //Excel的OLEDB ConnectionString
48: string ExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
49:
50: OleDbConnection ExcelCn = new OleDbConnection(ExcelConnectionString);
51: OleDbCommand ExcelCmd = new OleDbCommand();
52: DbDataReader ExcelDr = null;
53:
54: ExcelCmd.CommandText = "Select bookid,book_name,book_author from [bookone$]";
55: //抓取Excel資料的SQL指令
56: ExcelCmd.CommandType = CommandType.Text;
57: ExcelCmd.Connection = ExcelCn;
58:
59: ExcelCmd.Connection.Open();
60: ExcelDr = ExcelCmd.ExecuteReader(CommandBehavior.CloseConnection);
61:
62: SqlConnection cn = new SqlConnection();
63: //設定資料庫Connect物件
64:
65: //設定資料庫Connection連接
66: cn.ConnectionString = ConfigurationManager.ConnectionStrings["DA_DatabaseConnectionString"].ToString();
67: cn.Open();
68:
69: SqlBulkCopy BulkCopy = new SqlBulkCopy(cn);
70: //宣告SqlBulkCopy物件
71: BulkCopy.DestinationTableName = "book";
72: //定義要匯入的資料庫Table
73: BulkCopy.WriteToServer(ExcelDr);
74: //寫入資料
75:
76: ExcelDr.Close();
77: ExcelCmd.Dispose();
78: ExcelCn.Dispose();
79: cn.Dispose();
80: GridView1.DataSource = SqlDataSource1;
81: GridView1.DataBind();
82:
83:
84: }
85: }
86:
87:
88:
開始測試!!!!!!
匯入前IE fileupload 路徑是完整的
成功匯入後
資料庫備份
不過用chrome 去瀏覽時會發生路徑不完整的問題，解決方法在下面
按照上面後台寫法 改寫44~48行程式碼如下
1: //指定上傳路徑
2: string target = Server.MapPath(@"~/excel_temp\");
3: //取的檔案名稱
4: string filename = FileUpload1.FileName.ToString();
5: //Excel檔案的實體路徑
6: string path = target + filename;
7: //檔上傳回指定目錄
8: FileUpload1.SaveAs(path);
9: //Excel的OLEDB ConnectionString
10: string ExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
11: .
12: ..//在87 行GridView1.DataBind();下面補上 System.IO.File.Delete(path); 即可
13: System.IO.File.Delete(path);
範例下載
大家一起加入blogads 賺零用錢!!
