注明:必須按照數據庫字段的“順序”,“字段格式 ”來寫EXCEL;自動編號不要寫入Excel。
我這裡用的是Access
前台代碼:
後台代碼:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Security;
using System.Configuration;
namespace TestAll
...{
/**//// <summary>
/// Excel 的摘要說明。
/// </summary>
public class Excel : System.Web.UI.Page
...{protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.HtmlControls.HtmlInputFile File1;
protected System.Web.UI.WebControls.TextBox txt_tbname;
protected System.Web.UI.HtmlControls.HtmlInputFile file;
protected System.Web.UI.WebControls.TextBox txtTableName;
public string ST_ConnectionString;
private void Page_Load(object sender, System.EventArgs e)
...{
}
Web 窗體設計器生成的代碼#region Web 窗體設計器生成的代碼
override protected void OnInit(EventArgs e)
...{
//
// CODEGEN: 該調用是 ASP.Net Web 窗體設計器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/**//// <summary>
/// 設計器支持所需的方法 - 不要使用代碼編輯器修改
/// 此方法的內容。
/// </summary>
private void InitializeComponent()
...{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
...{
if (!file.Value.ToLower().EndsWith("xls"))
...{
Response.Write("<script>alert(''只能導入Excel類型文件'');</script>"); return;
}
if (txtTableName.Text.Trim() == "")
...{
Response.Write("<script>alert(''請填寫Excel表單名'');</script>");
return;
}
//獲取上傳文件的路徑
string myFile=file.PostedFile.FileName;
string fileName=myFile.Substring(myFile.LastIndexOf("\")+1);
file.PostedFile.SaveAs(Server.MapPath("../xls")+"\"+fileName);
string tbname = txtTableName.Text.Trim(); //表名
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+Server.MapPath("../xls")+"\"+fileName+";"+"Extended PropertIEs=''Excel 8.0;hdr=yes;imex=1'';";
DataSet myDataSet = new DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+tbname+"$]", strConn);
try
...{
myCommand.Fill(myDataSet);
}
catch(Exception ex)
...{
string aa = ex.Message;
Response.Write("<script>alert(''表單名不正確'');</script>");
return;
}
// //復制
ST_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(ConfigurationSettings.APPSettings["ConnectionString1"]);
OleDbConnection con=new OleDbConnection(ST_ConnectionString);
OleDbDataAdapter da=new OleDbDataAdapter("select * from SW_Product order by id desc",con);
DataSet ds1=new DataSet();
da.Fill(ds1);//原有
for(int i=0;i<myDataSet.Tables[0].Rows.Count;i++)
...{
DataRow dr=ds1.Tables[0].NewRow();
for (int j=0; j<ds1.Tables[0].Columns.Count;j++)
...{
dr[j]=myDataSet.Tables[0].Rows[i][j];
}
ds1.Tables[0].Rows.Add(dr);
}
try
...{
OleDbCommandBuilder cb=new OleDbCommandBuilder(da);
cb.QuotePrefix="[";
cb.QuoteSuffix="]";
da.Update(ds1);
Response.Write("<script>alert(''添加成功!'');</script>");
}
catch(Exception ex1)
...{
Response.Write("<script>alert(''"+ex1.Message.ToString()+"'');</script>");
}
}
}
}
具體路徑自行左修改就OK了。