|
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 的摘要說(shuō)明。 /// </summary> public class 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(); private void Page_Load(object sender, System.EventArgs e) { // 在此處放置用戶代碼以初始化頁(yè)面 } #region Web 窗體設(shè)計(jì)器生成的代碼 override protected void OnInit(EventArgs e) { // // CODEGEN: 該調(diào)用是 ASP.NET Web 窗體設(shè)計(jì)器所必需的。 // InitializeComponent(); base.OnInit(e); } /// <summary> /// 設(shè)計(jì)器支持所需的方法 - 不要使用代碼編輯器修改 /// 此方法的內(nèi)容。 /// </summary> private void InitializeComponent() { this.BtnImport.Click += new System.EventHandler(this.BtnImport_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion //// <summary> /// 從Excel提取數(shù)據(jù)--》Dataset /// </summary> /// <param name="filename">Excel文件路徑名</param> private void ImportXlsToData(string fileName) { try { if (fileName == string.Empty) { throw new 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> private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile) { string orifilename = string.Empty; string uploadfilepath = string.Empty; string modifyfilename = string.Empty; string fileExtend = "" ;//文件擴(kuò)展名 int fileSize = 0;//文件大小 try { if(inputfile.Value != string.Empty) { //得到文件的大小 fileSize = inputfile.PostedFile.ContentLength; if(fileSize == 0 ) { throw new Exception("導(dǎo)入的Excel文件大小為0，請(qǐng)檢查是否正確！"); } //得到擴(kuò)展名 fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1); if(fileExtend.ToLower() != "xls") { throw new Exception("你選擇的文件格式不正確，只能導(dǎo)入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 { throw new Exception("請(qǐng)選擇要導(dǎo)入的Excel文件!"); } } catch(Exception ex) { throw ex; } return orifilename; } /// <summary> /// 將Dataset的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù) /// </summary> /// <param name="pds">數(shù)據(jù)集</param> /// <param name="Cols">數(shù)據(jù)集列數(shù)</param> /// <returns></returns> private bool AddDatasetToSQL(DataSet pds,int Cols) { int ic,ir; ic = pds.Tables[0].Columns.Count; if (pds.Tables[0].Columns.Count < Cols) { throw new Exception("導(dǎo)入Excel格式錯(cuò)誤！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 { throw new Exception("導(dǎo)入數(shù)據(jù)為空！"); } return true; } /// <summary> /// 插入數(shù)據(jù)到數(shù)據(jù)庫(kù) /// </summary> public void 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("'通過(guò)',"); 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("'通過(guò)',"); strSql.Append("'系統(tǒng)導(dǎo)入數(shù)據(jù)',"); strSql.Append("'"+DateTime.Now.ToString()+"'"); strSql.Append(")"); _da.ExecuteNonQuery(strSql.ToString()); } } private void BtnImport_Click(object sender, System.EventArgs e) { string filename = string.Empty; try { filename = UpLoadXls(FileExcel);//上傳XLS文件 ImportXlsToData(filename);//將XLS文件的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù) if (filename != string.Empty && System.IO.File.Exists(filename)) { System.IO.File.Delete(filename);//刪除上傳的XLS文件 } LblMessage.Text="數(shù)據(jù)導(dǎo)入成功！"; } catch(Exception ex) { LblMessage.Text=ex.Message; } } } } |
|
|
來(lái)自： 塵夢(mèng)123 > 《.net知識(shí)》
