在項目中經常有客戶要求用EXCEL匯入到資料庫，提高效率。
注意：EXCEL中的第一行不能匯入。
下面是源碼；
IntoExcel.aspx：
<mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"></mce:script>><%@ Page Language="C#" AutoEventWireup="true" CodeBehind="IntoExcel.aspx.cs" Inherits="DEMO.IntoExcel" %><br /><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><br /><html xmlns="http://www.w3.org/1999/xhtml" ><br /><head runat="server"><br /> <title>無標題頁</title><br /><mce:script language="javascript" type="text/javascript"><!--<br />// <!CDATA[<br />function check() {<br />var k=//S+/.[xls]/;<br />if(!k.test(document.getElementById("fileId").value))<br />{<br /> alert("只能上次xls格式的檔案");<br /> return false;<br />}<br />return true;<br />}<br />// --></mce:script><br /></head><br /><body><br /> <form id="form1" runat="server"><br /> <div><br /> <p><br /> <asp:FileUpload ID="fileId" runat="server" /><asp:Button ID="Button1" runat="server" Text="上傳" OnClientClick="return check()" onclick="Button1_Click" /></p><br /> </div><br /> </form><br /></body><br /></html><br />
IntoExcel.aspx.cs
using System;<br />using System.Collections;<br />using System.Configuration;<br />using System.Data;<br />using System.Web;<br />using System.Web.Security;<br />using System.Web.UI;<br />using System.Web.UI.HtmlControls;<br />using System.Web.UI.WebControls;<br />using System.Web.UI.WebControls.WebParts;<br />using System.IO;<br />using System.Data.OleDb;<br />using System.Data.SqlClient;<br />namespace DEMO<br />{<br /> public partial class IntoExcel : System.Web.UI.Page<br /> {<br /> protected void Page_Load(object sender, EventArgs e)<br /> {<br /> }<br /> /// <summary><br /> /// 上傳檔案<br /> /// </summary><br /> /// <param name="sender"></param><br /> /// <param name="e"></param><br /> protected void Button1_Click(object sender, EventArgs e)<br /> {<br /> string fileName = fileId.FileName;<br /> string savePath = Server.MapPath("~/file/");<br /> FileOperatpr(fileName, savePath);<br /> fileId.SaveAs(savePath + fileName);<br /> DataOperator(fileName, savePath);<br /> }<br /> /// <summary><br /> /// 資料操作<br /> /// </summary><br /> /// <param name="fileName"></param><br /> /// <param name="savePath"></param><br /> private void DataOperator(string fileName, string savePath)<br /> {<br /> string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + savePath + fileName + ";Extended Properties=Excel 8.0";<br /> OleDbConnection oconn = new OleDbConnection(myString);<br /> oconn.Open();<br /> DataSet ds = new DataSet();<br /> OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn);<br /> oda.Fill(ds);<br /> oconn.Close();<br /> DataSetOperator(ds,savePath+fileName);<br /> }<br /> /// <summary><br /> /// 資料集操作<br /> /// </summary><br /> /// <param name="ds"></param><br /> private void DataSetOperator(DataSet ds,string filePath)<br /> {<br /> SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TestExcel;Integrated Security=True");<br /> conn.Open();<br /> SqlTransaction str = conn.BeginTransaction();//利用交易處理 防止中斷<br /> int k = 0;<br /> if (ds.Tables[0].Rows.Count < 1)<br /> {<br /> Response.Write("<mce:script type="text/javascript"><!--<br />alert('沒有資料！')<br />// --></mce:script>");<br /> return;<br /> }<br /> try<br /> {<br /> for (int i = 0; i < ds.Tables[0].Rows.Count; i++)<br /> {<br /> string sqlStr = "insert into IntoExcel(Tname,Tage,Taddress)values";<br /> sqlStr +="('"+ ds.Tables[0].Rows[i][0].ToString()+"',";<br /> sqlStr += ds.Tables[0].Rows[i][1].ToString()+",";<br /> sqlStr +="'" +ds.Tables[0].Rows[i][2].ToString()+"')";<br /> SqlCommand cmd = new SqlCommand(sqlStr, conn, str);<br /> cmd.Transaction = str;<br /> k += cmd.ExecuteNonQuery();<br /> }<br /> str.Commit();<br /> }<br /> catch (Exception ex)<br /> {<br /> Response.Write("發生異常,資料已復原/n資訊/n" + ex.Message);<br /> str.Rollback();<br /> }<br /> finally<br /> {<br /> Response.Write("上傳成功" + k + "條");<br /> File.Delete(filePath);<br /> }<br /> }<br /> /// <summary><br /> /// 檔案操作<br /> /// </summary><br /> /// <param name="fileName"></param><br /> /// <param name="savePath"></param><br /> private void FileOperatpr(string fileName, string savePath)<br /> {<br /> if (!Directory.Exists(savePath))<br /> {<br /> Directory.CreateDirectory(savePath);<br /> }<br /> if (File.Exists(savePath + fileName))<br /> {<br /> File.Delete(savePath + fileName);<br /> }<br /> }<br /> }<br />}<br />
資料庫中的欄位：
create table IntoExcel<br />(<br />Tid int identity(1,1) primary key,<br />Tname varchar(50),<br />Tage int,<br />Taddress varchar(200),</p><p>)
EXCEL資料
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
