.net excel导入mysql_.net实现将Excel中的数据导入数据库

usingSystem;usingSystem.Collections;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Web;usingSystem.Web.SessionState;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Web.UI.HtmlControls;usingSystem.IO;usingSystem.Data.SqlClient;usingSystem.Data.OleDb;usingSystem.Text;namespaceGZPI.Service.AgenciesChannel

{//ImportXlsToData 的摘要说明。///publicclassImportXlsToDataBase : GZPI.Service.AgenciesChannel.AgenciesBasePage

{protectedSystem.Web.UI.HtmlControls.HtmlInputFile FileExcel;protectedSystem.Web.UI.WebControls.Button BtnImport;protectedSystem.Web.UI.WebControls.Label LblMessage;protectedZsoftDataAccess.DataAccess _da=newZsoftDataAccess.DataAccess();privatevoidPage_Load(objectsender, System.EventArgs e)

{//在此处放置用户代码以初始化页面}#regionWeb 窗体设计器生成的代码overrideprotectedvoidOnInit(EventArgs e)

{CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。//InitializeComponent();base.OnInit(e);

}//设计器支持所需的方法 - 不要使用代码编辑器修改///此方法的内容。///privatevoidInitializeComponent()

{this.BtnImport.Click+=newSystem.EventHandler(this.BtnImport_Click);this.Load+=newSystem.EventHandler(this.Page_Load);

}#endregion///从Excel提取数据--》Dataset//Excel文件路径名privatevoidImportXlsToData(stringfileName)

{try{if(fileName==string.Empty)

{thrownewArgumentNullException("Excel文件上传失败！");

}stringoleDBConnString=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=newDataTable();

DataSet ds=newDataSet();

oleDBConn=newOleDbConnection(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();

}stringsqlMaster;

sqlMaster="SELECT *  FROM ["+m_tableName.TableName+"]";

oleAdMaster=newOleDbDataAdapter(sqlMaster,oleDBConn);

oleAdMaster.Fill(ds,"m_tableName");

oleAdMaster.Dispose();

oleDBConn.Close();

oleDBConn.Dispose();

AddDatasetToSQL(ds,14);

}catch(Exception ex)

{throwex;

}

}//上传Excel文件//上传的控件名///privatestringUpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)

{stringorifilename=string.Empty;stringuploadfilepath=string.Empty;stringmodifyfilename=string.Empty;stringfileExtend="";//文件扩展名intfileSize=0;//文件大小try{if(inputfile.Value!=string.Empty)

{//得到文件的大小fileSize=inputfile.PostedFile.ContentLength;if(fileSize==0)

{thrownewException("导入的Excel文件大小为0，请检查是否正确！");

}//得到扩展名fileExtend=inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);if(fileExtend.ToLower()!="xls")

{thrownewException("你选择的文件格式不正确，只能导入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=newSystem.IO.DirectoryInfo(uploadfilepath);if(!dir.Exists)

{

dir.Create();

}

orifilename=uploadfilepath+"\\"+modifyfilename;//如果存在,删除文件if(File.Exists(orifilename))

{

File.Delete(orifilename);

}//上传文件inputfile.PostedFile.SaveAs(orifilename);

}else{thrownewException("请选择要导入的Excel文件!");

}

}catch(Exception ex)

{throwex;

}returnorifilename;

}//将Dataset的数据导入数据库//数据集///数据集列数///privateboolAddDatasetToSQL(DataSet pds,intCols)

{intic,ir;

ic=pds.Tables[0].Columns.Count;if(pds.Tables[0].Columns.Count

{thrownewException("导入Excel格式错误！Excel只有"+ic.ToString()+"列");

}

ir=pds.Tables[0].Rows.Count;if(pds!=null&&pds.Tables[0].Rows.Count>0)

{for(inti=1;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{thrownewException("导入数据为空！");

}returntrue;

}//插入数据到数据库///publicvoidAdd(stringB0105,stringSequenceNumber,stringA0101,stringOldGuid,stringRecordType,stringBirthDay,stringA0177,stringMobliePhone,stringTelePhone,stringContractBeginDate,stringContractEndDate,stringContractPayCharge,stringTransactDate)

{stringsql="select * from PersonRecord where A0177='"+A0177+"'";

DataTable dt=_da.ExecuteDataTable(sql);if(dt.Rows.Count==0)

{//insert into PersonRecordStringBuilder strSql=newStringBuilder();

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 PersonnelAgencyInfostringGUID=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 PersonnelAgencyRecordstrSql.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());

}

}privatevoidBtnImport_Click(objectsender, System.EventArgs e)

{stringfilename=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;

}

}

}

}