1.导入数据思路：
C#winform程序中，要导入数据，给一个EXCEL模板，让用户按照给的模板填好数据，然后在程序中一条一条的进行导入数据。
比如
foreach (DataRow dr in dt.Rows) { //校区 XX0103BLL bll_xx0103 = new XX0103BLL(); XX0103Model.Base model_xx0103 = new XX0103Model.Base(); if (bll_xx0103.Exists("", dr["校区编号"].ToString(), dr["校区名称"].ToString()) == false) { //没有校区添加校区 model_xx0103.XQBH = dr["校区编号"].ToString(); model_xx0103.XQMC = dr["校区名称"].ToString(); bll_xx0103.Add(model_xx0103); } }
注意：这里要判段每一条数据是否符合规范，符合的才往数据库里面插入数据。不符合的就continue。
2.进度条
2.1导入按钮的单击事件
/// <summary> /// 导入数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_Import_Click(object sender, EventArgs e) { ProcessThread = new Thread(new ThreadStart(LoadBasicData)); ProcessThread.IsBackground = true; ProcessThread.Start(); }
2.2 LoadBasicData方法
/// <summary> /// 基础数据导入 /// </summary> private void LoadBasicData() { this.NowCount = 0; this.AllCount = 0; this.hasResult = false; try { DelegateShowProssBar mi = new DelegateShowProssBar(ShowProcessBar); this.BeginInvoke(mi, new object[] { "导入基础数据", "导入数据", true }); Thread.Sleep(1000);//等待进度条弹出 object[] objPar = new object[] { 0, "导入数据... " }; object objReturn; this.Invoke(this.myIncrease, objPar); this.AllCount = dt.Rows.Count; this.wpf.SetMaxValue(this.AllCount); objPar = new object[] { this.NowCount, "开始导入数据... " }; this.Invoke(this.myIncrease, objPar); foreach (DataRow dr in dt.Rows) { this.NowCount++; //校区 XX0103BLL bll_xx0103 = new XX0103BLL(); XX0103Model.Base model_xx0103 = new XX0103Model.Base(); if (bll_xx0103.Exists("", dr["校区编号"].ToString(), dr["校区名称"].ToString()) == false) { //没有校区添加校区 model_xx0103.XQBH = dr["校区编号"].ToString(); model_xx0103.XQMC = dr["校区名称"].ToString(); bll_xx0103.Add(model_xx0103); } //部门 XX0301BLL bll_xx0301 = new XX0301BLL(); XX0301Model.Base model_xx0301 = new XX0301Model.Base(); if (bll_xx0301.Exists("", dr["校区编号"].ToString(), dr["部门编号"].ToString(), dr["部门名称"].ToString()) == false) { model_xx0301.BMBH = dr["部门编号"].ToString(); model_xx0301.BMMC = dr["部门名称"].ToString(); model_xx0301.XQBH = dr["校区编号"].ToString(); bll_xx0301.Add(model_xx0301); } //专业 JX01BLL bll_jx01 = new JX01BLL(); JX01Model.Base model_jx01 = new JX01Model.Base(); if (bll_jx01.Exists("", dr["专业编号"].ToString()) == false) { model_jx01.ZYBH = dr["专业编号"].ToString(); model_jx01.ZYMC = dr["专业名称"].ToString(); model_jx01.BMBH = dr["部门编号"].ToString(); model_jx01.CC = dr["层次"].ToString(); bll_jx01.Add(model_jx01); } //用户 CustomerBLL bll_customer = new CustomerBLL(); CustomerModel.Base model_customer = new CustomerModel.Base(); if (bll_customer.Exists("", dr["考生号"].ToString()) == false) { model_customer.OutID = dr["考生号"].ToString(); model_customer.CardNo = int.Parse(dr["卡号"].ToString()); model_customer.Name = dr["姓名"].ToString(); model_customer.CardSN = int.Parse(dr["换卡序号"].ToString()); model_customer.Sex = dr["性别"].ToString(); model_customer.CustomerTypeID = int.Parse(dr["客户类别"].ToString()); model_customer.NJ = dr["年级"].ToString(); model_customer.ZYBH = dr["专业编号"].ToString(); model_customer.CardStatusID = int.Parse(dr["卡状态码"].ToString()); model_customer.IDCardNo = dr["身份证号"].ToString(); model_customer.CardTypeID = int.Parse(dr["卡类别码"].ToString()); model_customer.AddTime = DateTime.Now; bll_customer.Add(model_customer); } if (!this.hasResult) { objPar = new object[] { this.NowCount, "" }; objReturn = this.Invoke(this.myIncrease, objPar); hasResult = ((WaitProcessFormEx.ResStruct)objReturn).HasResult; bool canceled = ((WaitProcessFormEx.ResStruct)objReturn).Canceled; if (hasResult) { if (this.NowCount == this.AllCount) { // showResult(true, "完成! "); } else { showResult(false, canceled ? "操作被取消! " : "导入未完成! "); return; } } } else { } } objPar = new object[] { this.NowCount, "数据导入完成！" }; this.Invoke(this.myIncrease, objPar); } catch { Error("错误", "数据导入错误"); } }
3.提供一个读EXCEL，转DataTable的方法，注意：只能是excel2003或者excel2007的EXCEL表
/// <summary> /// 读excel /// </summary> /// <param name="fileFullPath">excel路径</param> /// <returns></returns> public DataTable ReadExcel(string fileFullPath) { OleDbConnection myConn = null; OleDbDataAdapter myCommand = null; DataSet dst = null; string StrCon = ""; if (fileFullPath.Substring(fileFullPath.LastIndexOf(".")).ToLower() == ".xls") { StrCon = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" + fileFullPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; } else if (fileFullPath.Substring(fileFullPath.LastIndexOf(".")).ToLower() == ".xlsx") { StrCon = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; } else if (fileFullPath.Substring(fileFullPath.LastIndexOf(".")).ToLower() == ".mdb") { StrCon = " Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileFullPath + "; User Id=; Password="; } else { this.Error("错误", "文件格式不正确"); } myConn = new OleDbConnection(StrCon); myConn.Open();//打开Excel连接 //查询excel文件中有多少张表 DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//查询所有表 if (dt.Rows.Count == 0) { this.Error("错误", "文件中没有可用的表"); } string sql = "select * from [{0}]"; bool flag = false; foreach (DataRow d in dt.Rows)//列出所有表以供选择 { if (d["TABLE_TYPE"].ToString().ToUpper() == "TABLE") { //sql += d[2].ToString().Replace("'", ""); sql = string.Format(sql, d[2].ToString().Replace("'", "")); flag = true; break; } } if (flag == false) { this.Error("错误", "没有可用的表"); } myCommand = new OleDbDataAdapter(sql, myConn); dst = new DataSet(); myCommand.Fill(dst); return dst.Tables[0]; }