頁面ＨＴＭＬ代碼：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Excel 資料匯入 資料庫</title> <script type="text/javascript" src="js/jquery-1.4.2.min.js"></script> <script type="text/javascript"> $(function(){ $("#"+"<%=btnImport.ClientID %>").click(function(){ var fileName = $("#"+"<%=excelFile.ClientID %>").val(); if(fileName==""){ alert("請選擇Excel檔案！"); return false; } else{ var extension = fileName.substring(fileName.lastIndexOf('.')+1); if(extension!="xlsx"&&extension!="xls"){ alert("上傳的檔案不是Excel檔案，請重試！"); return false; } } return true; }); }); </script></head><body> <form id="form1" runat="server"> <div> <fieldset> <legend>資料匯入：</legend> <table> <tr> <td style="width: 182px"> 資料Excel：</td> <td> <asp:FileUpload ID="excelFile" runat="server" /></td> </tr> <tr> <td colspan="2"> <asp:Button ID="btnImport" runat="server" Text="匯入" OnClick="btnImport_Click" /> <input id="btnCancel" type="button" value="取消" onclick='window.location.href="Default.aspx"' /></td> </tr> </table> </fieldset> </div> <div id="errorDiv" runat="server"> <fieldset> <legend>錯誤資訊:</legend> <textarea id="errorArea" runat="server" style="width: 722px; height: 88px"></textarea> </fieldset> </div> <div id="confrimDiv" runat="server"> <fieldset> <legend>匯入確認:</legend> <asp:GridView ID="GVConfirm" runat="server" CssClass="grid"> </asp:GridView> <div id="buttonDiv" runat="server" visible="false"> <asp:Button ID="btnConfirm" runat="server" Text="確定" OnClick="btnConfirm_Click" /> <input id="btnNotConfirm" type="button" value="取消" onclick='window.location.href="Default.aspx"' /> </div> </fieldset> </div> </form></body></html>
Ｃ# 代碼：
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Text;using System.Data.OleDb;using System.IO;public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { this.initPage(); } /// <summary> /// 通過ViewState儲存一個DataTable.用來在現實之後添加到資料庫 /// </summary> public DataTable SamplDataTable { get { if (ViewState["SamplDataTable"] == null) return new DataTable(); return (DataTable)ViewState["SamplDataTable"]; } set { ViewState["SamplDataTable"] = value; } } private void initPage() { this.errorDiv.Visible = false; this.confrimDiv.Visible = false; } protected void btnImport_Click(object sender, EventArgs e) { ///取得檔案名稱 string fileName = this.excelFile.FileName; //絕對路徑 string path = "~/Excel/" + fileName; path = Server.MapPath(path); try { //暫時儲存在服務上 this.excelFile.SaveAs(path); ///讀取指定路徑 Excel 檔案中的內容轉換成DataTable DataTable excelDT = ImportToDataSet(path); //聲明錯誤資訊字串 StringBuilder errorBuiler = new StringBuilder(); ///檢查 儲存在記憶體中的 Excel DataTable if (Validate(excelDT, errorBuiler)) { //如果檢查通過,使用ViewState儲存DataSet中資料,在儲存到資料庫的時候用到 SamplDataTable = excelDT; //頁面GridView資料繫結,用來顯示從Excel讀取出來的資料供使用者確認 this.GVConfirm.DataSource = SamplDataTable; this.GVConfirm.DataBind(); this.confrimDiv.Visible = true; this.buttonDiv.Visible = true; } else {//如果檢測沒有通過,輸出相關錯誤資訊 this.errorDiv.Visible = true; this.errorArea.Value = errorBuiler.ToString(); } } catch (Exception ex) { this.errorDiv.Visible = true; this.errorArea.Value = ex.Message; } finally { //關閉,刪除 檔案 if (File.Exists(path)) File.Delete(path); } } /// <summary> /// 驗證指定的Excel規則(列數) /// </summary> /// <param name="excelDT"></param> /// <param name="errorBuiler"></param> /// <returns></returns> public bool Validate(DataTable excelDT, StringBuilder errorBuiler) { bool result = true; if (excelDT.Columns.Count != 5)//假設是5列 { result = false; int difference = excelDT.Columns.Count - 5; if (difference > 0) errorBuiler.AppendLine("要匯入的Excel多" + difference.ToString() + "列"); else errorBuiler.AppendLine("要匯入的Excel少" + (-difference).ToString() + "列"); } else { //foreach (DataRow row in excelDT.Rows) //{ // if (Exists(row[0].ToString())) // { // result = false; // errorBuiler.AppendLine("內容 " + "'" + row[2].ToString() + "'" + "已存在！"); // } //} } return result; } protected void btnConfirm_Click(object sender, EventArgs e) { ///將GridView中顯示的資料(其實是儲存在VIewState中)寫入到資料庫中 if (AddDataTable(SamplDataTable, 0)) { Page.ClientScript.RegisterStartupScript(Page.GetType(), "insertSuccess", "<script>alert('匯入成功！');window.location.href='Default.aspx';</script>"); } else { Page.ClientScript.RegisterStartupScript(Page.GetType(), "insertFailure", "<script>alert('匯入失敗！請重試');</script>"); } } private bool AddDataTable(DataTable dt, int p) { bool result = true; string sql = BuilderInsertSql(dt, p); try { //執行sql語句 DbHelper.ExecuteSql(); return true; } catch { result = false; } return result; ; } private string BuilderInsertSql(DataTable dt, int p) { //遍曆DataTable拼接添加字串 return ""; } private bool AddDataTable(DataTable SamplDataTable, object p, int p_3) { throw new Exception("The method or operation is not implemented."); } /// <summary> /// 讀取指定路徑的Excel內容到DataTable中 /// </summary> /// <param name="path"></param> /// <returns></returns> public DataTable ImportToDataSet(string path) { string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"; OleDbConnection conn = new OleDbConnection(strConn); try { DataTable dt = new DataTable(); if (conn.State != ConnectionState.Open) conn.Open(); string strExcel = "select * from [Sheet1$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn); adapter.Fill(dt); return dt; } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (conn.State != ConnectionState.Closed) conn.Close(); } }}Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
