private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { HSSFWorkbook hssfworkbook; FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); PDT_Product product; hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); int i = 0, count = getRowsCount() - 1; try { IRow headerRow = sheet.GetRow(0); if (headerRow.GetCell(0).ToString() != "营业部" || headerRow.GetCell(1).ToString() != "办事处ID" || headerRow.GetCell(2).ToString() != "办事处" || headerRow.GetCell(3).ToString() != "归属月份" || headerRow.GetCell(4).ToString() != "办事处月度费率目标" || headerRow.GetCell(5).ToString() != "办事处上月发生费用") { MessageBox.Show("工作表表头(1~6列)错误!\r\n"); return; } int month = 0; rows.MoveNext(); while (rows.MoveNext()) { i++; ((BackgroundWorker)sender).ReportProgress(i * 100 / count, i); HSSFRow row = (HSSFRow)rows.Current; if (row.GetCell(0) == null || row.GetCell(0).ToString() == "") { break; } int cloumn = 4; int cityid = 0; if (!int.TryParse(row.GetCell(1).ToString(), out cityid)) { errormessage += "办事处:" + row.GetCell(2).ToString() + "ID错误;\r\n"; row.GetCell(6).SetCellValue(errormessage); continue; } Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model; if (_city == null || _city.Name != row.GetCell(2).ToString()) { errormessage += "办事处ID号:" + cityid.ToString() + "与办事处名称不匹配!\r\n"; row.GetCell(6).SetCellValue(errormessage); continue; } ICell cell = row.GetCell(3); if (month == 0 && headerRow.GetCell(3).ToString() == "归属月份") { //ICellStyle cellStyle = cell.CellStyle; //IDataFormat format = hssfworkbook.CreateDataFormat(); //cellStyle.DataFormat = format.GetFormat("yyyy-MM"); //cell.CellStyle = cellStyle; IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + cell.ToString() + "'"); if (_monthlist.Count > 0) { month = _monthlist[0].ID; } else { errormessage += "会计月错误;\r\n"; row.GetCell(6).SetCellValue(errormessage); continue; } } SVM_OrganizeTargetBLL bll = null; IList <SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ToString()); if (_targetlist.Count > 0) { if (_targetlist.FirstOrDefault <SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null) { errormessage += "办事处:" + row.GetCell(2).ToString() + "当月的重点品项已审核,不可再次导入!\r\n"; row.GetCell(6).SetCellValue(errormessage); continue; } if (_targetlist.Count == 1) { bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID); } } if (bll == null) { bll = new SVM_OrganizeTargetBLL { Model = { OrganizeCity = cityid, AccountMonth = month, ApproveFlag = 2 } }; } decimal amount = 0M; if (row.GetCell(cloumn) != null && decimal.TryParse(row.GetCell(cloumn).ToString(), out amount)) { bll.Model.FeeRateTarget = amount; } else if (row.GetCell(cloumn) != null && row.GetCell(cloumn).CellType != CellType.BLANK) { errormessage += "ID号:" + cityid.ToString() + "," + _city.Name + "办事处月度费率目标:" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n"; row.GetCell(6).SetCellValue(errormessage); } amount = 0M; if (row.GetCell(++cloumn) != null && decimal.TryParse(row.GetCell(cloumn).ToString(), out amount)) { bll.Model["ActFee"] = amount.ToString(); decimal actSales = 0; if (decimal.TryParse(bll.Model["ActSales"], out actSales) && actSales != 0) { bll.Model.FeeYieldRate = amount * 100 / actSales; } else { bll.Model.FeeYieldRate = 0; } } else if (row.GetCell(cloumn) != null && row.GetCell(cloumn).CellType != CellType.BLANK) { errormessage += "ID号:" + cityid.ToString() + "," + _city.Name + "办事处上月发生费用:" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n"; row.GetCell(6).SetCellValue(errormessage); } #region 更新销量至数据库 if (bll.Model.ID > 0) { bll.Update(); improtmessage += "ID号:" + cityid.ToString() + "," + _city.Name + " 的办事处费率被成功更新!\r\n"; } else { bll.Add(); improtmessage += "ID号:" + cityid.ToString() + "," + _city.Name + " 的办事处费率已成功导入!\r\n"; } row.GetCell(6).SetCellValue("导入成功"); #endregion } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { FileStream writefile = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); hssfworkbook.Write(writefile); writefile.Close(); sheet = null; } }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { HSSFWorkbook hssfworkbook; FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); PDT_Product product; hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); int i = 0, count = getRowsCount() - 1; try { IRow headerRow = sheet.GetRow(0); if (headerRow.GetCell(0).ToString() != "营业部" || headerRow.GetCell(1).ToString() != "办事处ID" || headerRow.GetCell(2).ToString() != "办事处" || headerRow.GetCell(3).ToString() != "归属月份" || headerRow.GetCell(4).ToString() != "办事处业绩目标额") { MessageBox.Show("工作表表头(1~5列)错误!\r\n"); return; } int month = 0; rows.MoveNext(); ((BackgroundWorker)sender).ReportProgress(i * 100 / count, i); while (rows.MoveNext()) { i++; ((BackgroundWorker)sender).ReportProgress(i * 100 / count, i); HSSFRow row = (HSSFRow)rows.Current; if (row.GetCell(0).ToString() == "") { break; } int cloumn = 4; int cityid = 0; if (!int.TryParse(row.GetCell(1).ToString(), out cityid)) { errormessage += "办事处:" + row.GetCell(2).ToString() + "ID错误;\r\n"; row.GetCell(5).SetCellValue(errormessage); continue; } Addr_OrganizeCity _city = new Addr_OrganizeCityBLL(cityid).Model; if (_city == null || _city.Name != row.GetCell(2).ToString()) { errormessage += "办事处ID号:" + cityid.ToString() + "与办事处名称不匹配!\r\n"; row.GetCell(5).SetCellValue(errormessage); continue; } ICell cell = row.GetCell(3); if (month == 0 && headerRow.GetCell(3).ToString() == "归属月份") { //ICellStyle cellStyle = cell.CellStyle; //IDataFormat format = hssfworkbook.CreateDataFormat(); //cellStyle.DataFormat = format.GetFormat("yyyy-MM"); //cell.CellStyle = cellStyle; IList <AC_AccountMonth> _monthlist = AC_AccountMonthBLL.GetModelList("Name='" + cell.ToString() + "'"); if (_monthlist.Count > 0) { month = _monthlist[0].ID; } else { errormessage += "会计月错误;\r\n"; row.GetCell(5).SetCellValue(errormessage); continue; } } SVM_OrganizeTargetBLL bll = null; IList <SVM_OrganizeTarget> _targetlist = SVM_OrganizeTargetBLL.GetModelList("OrganizeCity=" + cityid.ToString() + "AND AccountMonth=" + month.ToString()); if (_targetlist.Count > 0) { if (_targetlist.FirstOrDefault <SVM_OrganizeTarget>(p => (p.ApproveFlag == 1)) != null) { errormessage += "办事处:" + row.GetCell(2).ToString() + "当月的办事处目标已审核,不可再次导入!\r\n"; row.GetCell(5).SetCellValue(errormessage); continue; } if (_targetlist.Count == 1) { bll = new SVM_OrganizeTargetBLL(_targetlist[0].ID); } } if (bll == null) { bll = new SVM_OrganizeTargetBLL { Model = { OrganizeCity = cityid, AccountMonth = month, ApproveFlag = 2 } }; } decimal amount = 0M; if (decimal.TryParse(row.GetCell(cloumn).ToString(), out amount)) { bll.Model.SalesTarget = amount; } else { errormessage += "办事处:" + row.GetCell(2).ToString() + "的办事处业绩目标额未能导入,办事处业绩目标额:" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n"; row.GetCell(5).SetCellValue(errormessage); continue; } ++cloumn; //IList<SVM_KeyProductTarget_Detail> details = new List<SVM_KeyProductTarget_Detail>(); //bool wrongflag = false;//判断导入数量是否正常(除空导致的异常) //while (true) //{ // product = null; // amount = 0M; // if (headerRow.GetCell(cloumn).CellType == CellType.BLANK || headerRow.GetCell(cloumn).ToString() == string.Empty) // { // break; // } // IList<PDT_Product> products = PDT_ProductBLL.GetModelList("ShortName='" + headerRow.GetCell(cloumn).ToString() + "' AND State=1"); // if (products.Count > 0) // { // product = products[0]; // } // else // { // errormessage += "产品名称:" + headerRow.GetCell(cloumn).ToString() + "在产品列表中不存在!\r\n"; // cloumn++; // continue; // } // if ((product != null) && row.GetCell(cloumn).CellType != CellType.BLANK) // { // decimal.TryParse(row.GetCell(cloumn).ToString(), out amount); // if (amount != 0M) // { // SVM_KeyProductTarget_Detail detail = new SVM_KeyProductTarget_Detail // { // Product = product.ID, // Amount = amount // }; // details.Add(detail); // } // else if (row.GetCell(cloumn).CellType != CellType.BLANK && (row.GetCell(cloumn).ToString() != "0")) // { // wrongflag = true; // break; // } // } // cloumn++; //} //if (wrongflag) //{ // errormessage += "办事处:" + row.GetCell(2).ToString() + "的重点品项未能导入,品项名称:" + headerRow.GetCell(cloumn).ToString() + "金额填写错误\r\n"; // continue; //} #region 更新销量至数据库 if (bll.Model.ID > 0) { //bll.DeleteDetail(); //先清除原先导入的数据 //bll.Items = details; //bll.AddDetail(); bll.Update(); improtmessage += "ID号:" + cityid.ToString() + "," + _city.Name + " 的办事处目标被成功更新!\r\n"; } else { //if (details.Count > 0) //{ //bll.Items = details; if (bll.Add() > 0) { //foreach (SVM_OrganizeTarget m in _targetlist) //{ // bll = new SVM_OrganizeTargetBLL(m.ID); // bll.DeleteDetail(); // bll.Delete(); //} improtmessage += "ID号:" + cityid.ToString() + "," + _city.Name + " 的重点品项已成功导入!\r\n"; } //} } row.GetCell(5).SetCellValue("导入成功"); #endregion } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { FileStream writefile = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); hssfworkbook.Write(writefile); writefile.Close(); sheet = null; } }