.net实现将Excel中的数据导入数据库
在近期的项目中需要实现将Excel中的数据导入到数据库的功能，相信各位博友也会经常有这样的需求，现把我的实现方式与大家分享一下，如果哪位博友有更好的实现方式，欢迎补充！ 实现思路：先上传Excel文件，然后用OleDB连接Excel文件，将Excel数据导入Dataset，再将Dataset数据导入数据库，最后再将上传的Excel文件删除。 前台代码：
<table class="Text" cellSpacing="1" cellPadding="0" width="100%" bgColor="#1d82d0" border="0"> <tr bgColor="#ffffff"> <td vAlign="top"> <table class="Text" cellSpacing="0" cellPadding="0" width="100%" border="0"> <tr> <td width="15"> </td> <td vAlign="top" width="100%"> <table class="Text" cellSpacing="1" cellPadding="0" width="100%" border="0"> <tr height="30"> <td style="WIDTH: 120px" width="120"><FONT face="宋体">请选择要导入的文件</FONT></td> <td style="WIDTH: 350px" align="left" width="350"><INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server"><FONT color="red"></FONT></td> <td class="hint"><FONT face="宋体"><asp:button id="BtnImport" Text="导 入" CssClass="button" Runat="server"></asp:button></FONT></td> </tr> </table> </td> </tr> </table> <asp:label id="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label> </td> </tr></table>
后台代码：
using System; using System.Collections; using System.ComponentModel; using System.Data; 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.IO; using System.Data.SqlClient; using System.Data.OleDb; using System.Text;
namespace GZPI.Service.AgenciesChannel { ///<summary> /// ImportXlsToData 的摘要说明。 ///</summary> publicclass ImportXlsToDataBase : GZPI.Service.AgenciesChannel.AgenciesBasePage { protected System.Web.UI.HtmlControls.HtmlInputFile FileExcel; protected System.Web.UI.WebControls.Button BtnImport; protected System.Web.UI.WebControls.Label LblMessage; protected ZsoftDataAccess.DataAccess _da =new ZsoftDataAccess.DataAccess(); privatevoid Page_Load(object sender, System.EventArgs e) { // 在此处放置用户代码以初始化页面 }
#region Web 窗体设计器生成的代码 overrideprotectedvoid OnInit(EventArgs e) { // // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。 // InitializeComponent(); base.OnInit(e); } ///<summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 ///</summary> privatevoid InitializeComponent() { this.BtnImport.Click +=new System.EventHandler(this.BtnImport_Click); this.Load +=new System.EventHandler(this.Page_Load);
} #endregion
//// <summary> /// 从Excel提取数据--》Dataset ///</summary> ///<param name="filename">Excel文件路径名</param> privatevoid ImportXlsToData(string fileName) { try { if (fileName ==string.Empty) { thrownew ArgumentNullException("Excel文件上传失败！"); } string oleDBConnString = String.Empty; oleDBConnString ="Provider=Microsoft.Jet.OLEDB.4.0;"; oleDBConnString +="Data Source="; oleDBConnString += fileName; oleDBConnString +=";Extended Properties=Excel 8.0;"; OleDbConnection oleDBConn =null; OleDbDataAdapter oleAdMaster =null; DataTable m_tableName=new DataTable(); DataSet ds=new DataSet();
oleDBConn =new OleDbConnection(oleDBConnString); oleDBConn.Open(); m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
if (m_tableName !=null&& m_tableName.Rows.Count >0) {
m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();
} string sqlMaster; sqlMaster=" SELECT * FROM ["+m_tableName.TableName+"]"; oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn); oleAdMaster.Fill(ds,"m_tableName"); oleAdMaster.Dispose(); oleDBConn.Close(); oleDBConn.Dispose(); AddDatasetToSQL(ds,14); } catch(Exception ex) { throw ex; } } ///<summary> /// 上传Excel文件 ///</summary> ///<param name="inputfile">上传的控件名</param> ///<returns></returns> privatestring UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile) { string orifilename =string.Empty; string uploadfilepath =string.Empty; string modifyfilename =string.Empty; string fileExtend ="" ;//文件扩展名 int fileSize =0;//文件大小 try { if(inputfile.Value !=string.Empty) { //得到文件的大小 fileSize = inputfile.PostedFile.ContentLength; if(fileSize ==0 ) { thrownew Exception("导入的Excel文件大小为0，请检查是否正确！"); } //得到扩展名 fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1); if(fileExtend.ToLower() !="xls") { thrownew Exception("你选择的文件格式不正确，只能导入EXCEL文件！"); } //路径 uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads"); //新文件名 modifyfilename = System.Guid.NewGuid().ToString(); modifyfilename +="."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1); //判断是否有该目录 System.IO.DirectoryInfo dir =new System.IO.DirectoryInfo(uploadfilepath); if (!dir.Exists) { dir.Create(); } orifilename = uploadfilepath+"\\"+modifyfilename; //如果存在,删除文件 if(File.Exists(orifilename)) { File.Delete(orifilename); } // 上传文件 inputfile.PostedFile.SaveAs(orifilename); } else { thrownew Exception("请选择要导入的Excel文件!"); } } catch(Exception ex) { throw ex; } return orifilename; } ///<summary> /// 将Dataset的数据导入数据库 ///</summary> ///<param name="pds">数据集</param> ///<param name="Cols">数据集列数</param> ///<returns></returns> privatebool AddDatasetToSQL(DataSet pds,int Cols) { int ic,ir; ic = pds.Tables[0].Columns.Count; if (pds.Tables[0].Columns.Count < Cols) { thrownew Exception("导入Excel格式错误！Excel只有"+ ic.ToString() +"列"); } ir = pds.Tables[0].Rows.Count; if (pds !=null&& pds.Tables[0].Rows.Count >0) { for (int i =1;i < pds.Tables[0].Rows.Count;i++) { Add(pds.Tables[0].Rows[i][1].ToString(), pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(), pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(), pds.Tables[0].Rows[i][6].ToString(),pds.Tables[0].Rows[i][7].ToString(), pds.Tables[0].Rows[i][8].ToString(),pds.Tables[0].Rows[i][9].ToString(), pds.Tables[0].Rows[i][10].ToString(),pds.Tables[0].Rows[i][11].ToString(), pds.Tables[0].Rows[i][12].ToString(),pds.Tables[0].Rows[i][13].ToString()); } } else { thrownew Exception("导入数据为空！"); } returntrue; }
///<summary> /// 插入数据到数据库 ///</summary> publicvoid Add(string B0105,string SequenceNumber,string A0101,string OldGuid,string RecordType,string BirthDay,string A0177,string MobliePhone,string TelePhone,string ContractBeginDate,string ContractEndDate,string ContractPayCharge,string TransactDate) { string sql="select * from PersonRecord where A0177='"+A0177+"'"; DataTable dt=_da.ExecuteDataTable(sql); if(dt.Rows.Count==0) { //insert into PersonRecord StringBuilder strSql=new StringBuilder(); strSql.Append("insert into PersonRecord("); strSql.Append("ID,B0105,SequenceNumber,A0101,OldGuid,RecordType,BirthDay,A0177,MobliePhone,TelePhone,ContractBeginDate,ContractEndDate,ContractPayCharge,TransactDate"); strSql.Append(")"); strSql.Append(" values ("); strSql.Append("'"+System.Guid.NewGuid().ToString()+"',"); strSql.Append("'"+B0105+"',"); strSql.Append("'"+SequenceNumber+"',"); strSql.Append("'"+A0101+"',"); strSql.Append("'"+OldGuid+"',"); strSql.Append("'"+RecordType+"',"); strSql.Append("'"+BirthDay+"',"); strSql.Append("'"+A0177+"',"); strSql.Append("'"+MobliePhone+"',"); strSql.Append("'"+TelePhone+"',"); strSql.Append("'"+ContractBeginDate+"',"); strSql.Append("'"+ContractEndDate+"',"); strSql.Append("'"+ContractPayCharge+"',"); strSql.Append("'"+TransactDate+"'"); strSql.Append(")");
//insert into PersonnelAgencyInfo string GUID=System.Guid.NewGuid().ToString(); strSql.Append(" insert into PersonnelAgencyInfo("); strSql.Append("PersonnelAgencyID, A0101, A0177, PersonnelAgencyState, PersonnelAgencyStateCode, Checker, CheckTime"); strSql.Append(")"); strSql.Append(" values ("); strSql.Append("'"+GUID+"',"); strSql.Append("'"+A0101+"',"); strSql.Append("'"+A0177+"',"); strSql.Append("'通过',"); strSql.Append("'1',"); strSql.Append("'"+GZPI.Service.Common.AuthManager.CurrentUserNameCN.ToString()+"',"); strSql.Append("'"+DateTime.Now.ToString()+"'"); strSql.Append(")");
//insert into PersonnelAgencyRecord strSql.Append(" insert into PersonnelAgencyRecord("); strSql.Append("PersonnelAgencyRecordID, PersonnelAgencyID, PersonnelAgencyState, Checker, CheckTime"); strSql.Append(")"); strSql.Append(" values ("); strSql.Append("'"+System.Guid.NewGuid().ToString()+"',"); strSql.Append("'"+GUID+"',"); strSql.Append("'通过',"); strSql.Append("'系统导入数据',"); strSql.Append("'"+DateTime.Now.ToString()+"'"); strSql.Append(")"); _da.ExecuteNonQuery(strSql.ToString()); } }
privatevoid BtnImport_Click(object sender, System.EventArgs e) { string filename =string.Empty; try { filename = UpLoadXls(FileExcel);//上传XLS文件 ImportXlsToData(filename);//将XLS文件的数据导入数据库 if (filename !=string.Empty && System.IO.File.Exists(filename)) { System.IO.File.Delete(filename);//删除上传的XLS文件 } LblMessage.Text="数据导入成功！"; } catch(Exception ex) { LblMessage.Text=ex.Message; } } } }
namespace GZPI.Service.AgenciesChannel { ///<summary> /// ImportXlsToData 的摘要说明。 ///</summary> publicclass ImportXlsToDataBase : GZPI.Service.AgenciesChannel.AgenciesBasePage { protected System.Web.UI.HtmlControls.HtmlInputFile FileExcel; protected System.Web.UI.WebControls.Button BtnImport; protected System.Web.UI.WebControls.Label LblMessage; protected ZsoftDataAccess.DataAccess _da =new ZsoftDataAccess.DataAccess(); privatevoid Page_Load(object sender, System.EventArgs e) { // 在此处放置用户代码以初始化页面 }
#region Web 窗体设计器生成的代码 overrideprotectedvoid OnInit(EventArgs e) { // // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。 // InitializeComponent(); base.OnInit(e); } ///<summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 ///</summary> privatevoid InitializeComponent() { this.BtnImport.Click +=new System.EventHandler(this.BtnImport_Click); this.Load +=new System.EventHandler(this.Page_Load);
} #endregion
//// <summary> /// 从Excel提取数据--》Dataset ///</summary> ///<param name="filename">Excel文件路径名</param> privatevoid ImportXlsToData(string fileName) { try { if (fileName ==string.Empty) { thrownew ArgumentNullException("Excel文件上传失败！"); } string oleDBConnString = String.Empty; oleDBConnString ="Provider=Microsoft.Jet.OLEDB.4.0;"; oleDBConnString +="Data Source="; oleDBConnString += fileName; oleDBConnString +=";Extended Properties=Excel 8.0;"; OleDbConnection oleDBConn =null; OleDbDataAdapter oleAdMaster =null; DataTable m_tableName=new DataTable(); DataSet ds=new DataSet();
oleDBConn =new OleDbConnection(oleDBConnString); oleDBConn.Open(); m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
if (m_tableName !=null&& m_tableName.Rows.Count >0) {
m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();
} string sqlMaster; sqlMaster=" SELECT * FROM ["+m_tableName.TableName+"]"; oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn); oleAdMaster.Fill(ds,"m_tableName"); oleAdMaster.Dispose(); oleDBConn.Close(); oleDBConn.Dispose(); AddDatasetToSQL(ds,14); } catch(Exception ex) { throw ex; } } ///<summary> /// 上传Excel文件 ///</summary> ///<param name="inputfile">上传的控件名</param> ///<returns></returns> privatestring UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile) { string orifilename =string.Empty; string uploadfilepath =string.Empty; string modifyfilename =string.Empty; string fileExtend ="" ;//文件扩展名 int fileSize =0;//文件大小 try { if(inputfile.Value !=string.Empty) { //得到文件的大小 fileSize = inputfile.PostedFile.ContentLength; if(fileSize ==0 ) { thrownew Exception("导入的Excel文件大小为0，请检查是否正确！"); } //得到扩展名 fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1); if(fileExtend.ToLower() !="xls") { thrownew Exception("你选择的文件格式不正确，只能导入EXCEL文件！"); } //路径 uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads"); //新文件名 modifyfilename = System.Guid.NewGuid().ToString(); modifyfilename +="."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1); //判断是否有该目录 System.IO.DirectoryInfo dir =new System.IO.DirectoryInfo(uploadfilepath); if (!dir.Exists) { dir.Create(); } orifilename = uploadfilepath+"\\"+modifyfilename; //如果存在,删除文件 if(File.Exists(orifilename)) { File.Delete(orifilename); } // 上传文件 inputfile.PostedFile.SaveAs(orifilename); } else { thrownew Exception("请选择要导入的Excel文件!"); } } catch(Exception ex) { throw ex; } return orifilename; } ///<summary> /// 将Dataset的数据导入数据库 ///</summary> ///<param name="pds">数据集</param> ///<param name="Cols">数据集列数</param> ///<returns></returns> privatebool AddDatasetToSQL(DataSet pds,int Cols) { int ic,ir; ic = pds.Tables[0].Columns.Count; if (pds.Tables[0].Columns.Count < Cols) { thrownew Exception("导入Excel格式错误！Excel只有"+ ic.ToString() +"列"); } ir = pds.Tables[0].Rows.Count; if (pds !=null&& pds.Tables[0].Rows.Count >0) { for (int i =1;i < pds.Tables[0].Rows.Count;i++) { Add(pds.Tables[0].Rows[i][1].ToString(), pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(), pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(), pds.Tables[0].Rows[i][6].ToString(),pds.Tables[0].Rows[i][7].ToString(), pds.Tables[0].Rows[i][8].ToString(),pds.Tables[0].Rows[i][9].ToString(), pds.Tables[0].Rows[i][10].ToString(),pds.Tables[0].Rows[i][11].ToString(), pds.Tables[0].Rows[i][12].ToString(),pds.Tables[0].Rows[i][13].ToString()); } } else { thrownew Exception("导入数据为空！"); } returntrue; }
///<summary> /// 插入数据到数据库 ///</summary> publicvoid Add(string B0105,string SequenceNumber,string A0101,string OldGuid,string RecordType,string BirthDay,string A0177,string MobliePhone,string TelePhone,string ContractBeginDate,string ContractEndDate,string ContractPayCharge,string TransactDate) { string sql="select * from PersonRecord where A0177='"+A0177+"'"; DataTable dt=_da.ExecuteDataTable(sql); if(dt.Rows.Count==0) { //insert into PersonRecord StringBuilder strSql=new StringBuilder(); strSql.Append("insert into PersonRecord("); strSql.Append("ID,B0105,SequenceNumber,A0101,OldGuid,RecordType,BirthDay,A0177,MobliePhone,TelePhone,ContractBeginDate,ContractEndDate,ContractPayCharge,TransactDate"); strSql.Append(")"); strSql.Append(" values ("); strSql.Append("'"+System.Guid.NewGuid().ToString()+"',"); strSql.Append("'"+B0105+"',"); strSql.Append("'"+SequenceNumber+"',"); strSql.Append("'"+A0101+"',"); strSql.Append("'"+OldGuid+"',"); strSql.Append("'"+RecordType+"',"); strSql.Append("'"+BirthDay+"',"); strSql.Append("'"+A0177+"',"); strSql.Append("'"+MobliePhone+"',"); strSql.Append("'"+TelePhone+"',"); strSql.Append("'"+ContractBeginDate+"',"); strSql.Append("'"+ContractEndDate+"',"); strSql.Append("'"+ContractPayCharge+"',"); strSql.Append("'"+TransactDate+"'"); strSql.Append(")");
//insert into PersonnelAgencyInfo string GUID=System.Guid.NewGuid().ToString(); strSql.Append(" insert into PersonnelAgencyInfo("); strSql.Append("PersonnelAgencyID, A0101, A0177, PersonnelAgencyState, PersonnelAgencyStateCode, Checker, CheckTime"); strSql.Append(")"); strSql.Append(" values ("); strSql.Append("'"+GUID+"',"); strSql.Append("'"+A0101+"',"); strSql.Append("'"+A0177+"',"); strSql.Append("'通过',"); strSql.Append("'1',"); strSql.Append("'"+GZPI.Service.Common.AuthManager.CurrentUserNameCN.ToString()+"',"); strSql.Append("'"+DateTime.Now.ToString()+"'"); strSql.Append(")");
//insert into PersonnelAgencyRecord strSql.Append(" insert into PersonnelAgencyRecord("); strSql.Append("PersonnelAgencyRecordID, PersonnelAgencyID, PersonnelAgencyState, Checker, CheckTime"); strSql.Append(")"); strSql.Append(" values ("); strSql.Append("'"+System.Guid.NewGuid().ToString()+"',"); strSql.Append("'"+GUID+"',"); strSql.Append("'通过',"); strSql.Append("'系统导入数据',"); strSql.Append("'"+DateTime.Now.ToString()+"'"); strSql.Append(")"); _da.ExecuteNonQuery(strSql.ToString()); } }
privatevoid BtnImport_Click(object sender, System.EventArgs e) { string filename =string.Empty; try { filename = UpLoadXls(FileExcel);//上传XLS文件 ImportXlsToData(filename);//将XLS文件的数据导入数据库 if (filename !=string.Empty && System.IO.File.Exists(filename)) { System.IO.File.Delete(filename);//删除上传的XLS文件 } LblMessage.Text="数据导入成功！"; } catch(Exception ex) { LblMessage.Text=ex.Message; } } } }
