/// <summary> /// 判断Products_Cost 原始数据中,具体工序是否为空! /// </summary> /// <param name="xlsPath"></param> /// <returns></returns> public bool check_P_C_Template(string xlsPath, out string msg) { MyExcel myExcel = new MyExcel(xlsPath); myExcel.open(); Worksheet wS = myExcel.getFirstWorkSheetAfterOpen(); Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS); int rowMaxIndex = wS.UsedRange.Rows.Count; int colMaxIndex = wS.UsedRange.Columns.Count; //具体 工序在第四列 string specificProcess = string.Empty; for (int rowIndex = rowMaxIndex; rowIndex >= 1; rowIndex--) { specificProcess = uEHelper.getSpecificCellValue("D" + rowIndex); if (string.IsNullOrEmpty(specificProcess)) { myExcel.close(); msg = "第" + rowIndex + "行,具体工序不能为空!"; return(false); } } myExcel.close(); msg = ""; return(true); }
/// <summary> /// /// </summary> public static void clearSheet(Worksheet firstWS) { Queue <Range> rangeToDelQueue = new Queue <Range>(); int rowsMaxCount; rowsMaxCount = firstWS.UsedRange.Rows.Count; Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS); //获取最大列 int maxColIndex = getMaxColIndexOfThe4thRowOfAR(firstWS); //判断是否有空行。 for (int i = 5; i <= rowsMaxCount; i++) { if (uEHelper.isBlankRangeTheSpecificRow(i, 1, maxColIndex)) { //只要上一列不是 //删除掉此行。 //判断上一行中的A列是否为工号。 string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString()); if ("工号:".Equals(temp)) { continue; } //获取该行。 Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing]; //不为工号 rangeToDelQueue.Enqueue(rangeToDel); } } Range rangeToDelete; //开始删除空行。 while (rangeToDelQueue.Count > 0) { rangeToDelete = rangeToDelQueue.Dequeue(); rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp); } }
/// <summary> /// 将考勤记录导入数据库. /// </summary> /// <param name="xlsFilePath"></param> /// <param name="randomStr"></param> /// <param name="pb"></param> /// <returns></returns> public static MSG ImportAttendanceRecordToDB(string xlsFilePath, string randomStr, BackgroundWorker bgWork) { string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath); bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0},准备读取:", excelName)); int pbLength = 0; bgWork.ReportProgress(pbLength, "pb.Maximum"); int pbValue = 0; bgWork.ReportProgress(pbValue, "pb.Value"); MSG msg = new MSG(); //用于确定本月最后一天. Stack <int> sDate = new Stack <int>(); //Queue<AttendanceR> qAttendanceR = new Queue<AttendanceR>(); Queue <AttendanceRecordDetail> qARDetail = new Queue <AttendanceRecordDetail>(); AttendanceRecordDetail._random_str = randomStr; //按指纹日期 string fingerPrintDate = String.Empty; //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; Usual_Excel_Helper uEHelper = null; MyExcel myExcel = new MyExcel(xlsFilePath); //打开该文档。 myExcel.openWithoutAlerts(); //只获取第一个表格。 Worksheet ws = myExcel.getFirstWorkSheetAfterOpen(); bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0},正在读取:", excelName)); AttendanceRecordDetail._file_path = xlsFilePath; //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; AttendanceRecordDetail._sheet_name = ws.Name; //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表. string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", ""); if (String.IsNullOrEmpty(A1Str)) { msg.Msg = "工作表的A1单元格不能为空!"; msg.Flag = false; myExcel.close(); return(msg); } //如果A1Str的内容不包含"考勤记录表"5个字。 if (!A1Str.Contains("考勤记录表")) { msg.Msg = "A1内容未包含'考勤记录表'"; msg.Flag = false; myExcel.close(); return(msg); } #region 判断名称中是否区分了考勤记录。 string Seq_Attendance_Record = string.Empty; int indexOfFullStop = xlsFilePath.LastIndexOf("."); Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1); if (!CheckPattern.CheckNumber(Seq_Attendance_Record)) { msg.Msg = "考勤记录表名称请以数字结尾!"; msg.Flag = false; myExcel.close(); return(msg); } #endregion AttendanceRecordDetail._prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0]; string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim(); // \0: 表空字符. if (String.IsNullOrEmpty(C3Str)) { msg.Msg = "异常: 考勤时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } // string[] ArrayC3 = C3Str.Split('~'); if (ArrayC3.Length == 0) { msg.Msg = "异常: 考勤时间格式变更!"; msg.Flag = false; myExcel.close(); return(msg); } AttendanceRecordDetail._start_date = ArrayC3[0].ToString().Trim().Replace('/', '-'); AttendanceRecordDetail._end_date = ArrayC3[1].ToString().Trim().Replace('/', '-'); //制表时间: L3 3行12列. string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim().Replace('/', '-'); if (String.IsNullOrEmpty(L3Str)) { msg.Msg = "异常: 制表时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } //制表时间. AttendanceRecordDetail._tabulation_time = L3Str; //检查第4行是否为;考勤时间: string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim(); if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase)) { msg.Msg = "异常: 第四行已变更!"; msg.Flag = false; myExcel.close(); return(msg); } uEHelper = new Usual_Excel_Helper(ws); //此刻不能删除,只是获取行号。 Queue <Range> rangeToDelQueue = new Queue <Range>(); //判断是否有空行。 for (int i = 5; i <= rowsMaxCount; i++) { if (uEHelper.isBlankRow(i)) { //只要上一列不是 //删除掉此行。 //判断上一行中的A列是否为工号。 string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString()); if ("工号:".Equals(temp)) { //本行为空,上一行为工号行,则也统计。 continue; } //本行,为空,上一行非工号行。则删除本行。 Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing]; //不为工号 rangeToDelQueue.Enqueue(rangeToDel); } ; } Range rangeToDelete; //开始删除空行。 while (rangeToDelQueue.Count > 0) { rangeToDelete = rangeToDelQueue.Dequeue(); rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp); } ; rowsMaxCount = ws.UsedRange.Rows.Count; //进度条长度增加。 pbLength += colsMaxCount; pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1)); bgWork.ReportProgress(pbLength, "pb.Maximum"); //入队列值0 sDate.Push(0); //显示进度条。 //考勤表中第4行,某月的最大考勤天数。 //lblPrompt.Text = excelName + ",正在读取:"; int actualMaxDay = 0; //开始循环 for (int i = 1; i <= colsMaxCount; i++) { A4Str = ((Range)ws.Cells[4, i]).Text.ToString(); //碰到第4行某列为空,退出循环。 if (String.IsNullOrEmpty(A4Str)) { break; } int aDate = 0; //对A4Str进行分析. if (!Int32.TryParse(A4Str, out aDate)) { msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate); msg.Flag = false; myExcel.close(); return(msg); } //判断新增的日期是否大于上一个. if (aDate <= sDate.Peek()) { //跳出循环. break; } actualMaxDay++; sDate.Push(aDate); //pb.Value++; bgWork.ReportProgress(pbValue++, "pb.Value"); } //取其中的最小值。 colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay); //考勤日期 fingerPrintDate = AttendanceRecordDetail._start_date.Substring(0, 7).Replace('/', '-'); string tempStr = string.Empty; //开始循环 for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++) { //从第5行开始. //奇数;偶数行共用一个对象. AttendanceRecordDetail ARDetail = null; //设定用于填充的对象 AttendanceRecordDetail._prefix_Job_Number = Seq_Attendance_Record.ToCharArray()[0]; for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++) { //如果行数为奇数则为工号行. if (rowIndex % 2 == 1) { //工号行. //取工号 ARDetail = new AttendanceRecordDetail(); ARDetail.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim(); //自行拼凑AR. ARDetail.combine_Job_Number(); //取姓名: K5 ARDetail.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim(); //取部门: U5 ARDetail.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim(); //部门为空,则填充为NULL; ARDetail.Dept = !String.IsNullOrEmpty(ARDetail.Dept) ? ARDetail.Dept : "NULL"; //取日期.填充0; ARDetail.Fingerprint_date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0'); } else { //偶数行取考勤结果. //上班时间. 如B10; tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim(); string tempFirstTime = String.Empty; string tempLastTime = String.Empty; List <string> strTimeList = null; msg = getFPTimeReturnMSG(tempStr, out strTimeList); if (!msg.Flag) { msg.Msg = string.Format(@"导入失败,提交数据尚未开始:第{0}行{1}列,{1}!", rowIndex, colIndex, msg.Msg); myExcel.close(); return(msg); } ; //无打卡记录,不提交 if (strTimeList.Count == 0) { qARDetail.Enqueue(ARDetail); } //有打卡记录 for (int i = 0; i < strTimeList.Count; i++) { AttendanceRecordDetail ARDetailTemp = (AttendanceRecordDetail)CloneObject.Clone(ARDetail); ARDetailTemp.Finger_print_time = ARDetailTemp.Fingerprint_date + " " + strTimeList[i].ToString(); qARDetail.Enqueue(ARDetailTemp); } } //pb.Value++; bgWork.ReportProgress(pbValue++, "pb.Value"); } } //释放对象 myExcel.close(); System.Threading.Thread.Sleep(2000); GC.Collect(); GC.WaitForPendingFinalizers(); //lblResult.Text = ""; bgWork.ReportProgress(0, "lblResult.Text = ''"); //lblPrompt.Text = "提交数据: "; bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}, 提交数据:", excelName)); // bgWork.ReportProgress(qARDetail.Count, "pb.Maximum"); //*******/ pbValue = 0; bgWork.ReportProgress(pbValue, "pb.Value"); #region //OracleDaoHelper.noLogging("Attendance_Record"); OracleDaoHelper.noLogging("Attendance_Record_Detail"); OracleConnection conn = OracleConnHelper.getConn(); OracleTransaction tran = conn.BeginTransaction(); //保存对象 while (qARDetail.Count > 0) { try { AttendanceRecordDetail aRDetail = qARDetail.Dequeue(); affectedCount += aRDetail.saveBySpecificConn(conn); //pb.Value++; bgWork.ReportProgress(pbValue++, "pb.Value"); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error); msg.Msg = DirectoryHelper.getFileName(xlsFilePath) + ":导入失败; " + ex.ToString(); msg.Flag = false; tran.Rollback(); conn.Close(); conn.Dispose(); return(msg); throw; } } tran.Commit(); conn.Close(); conn.Dispose(); #endregion //OracleDaoHelper.logging("Attendance_Record"); OracleDaoHelper.logging("Attendance_Record_Detail"); msg.Flag = true; msg.Msg = String.Format(@"导入完成;总计{0}条.", affectedCount.ToString()); return(msg); }
/// <summary> /// 将考勤记录导入数据库. /// </summary> /// <param name="xlsFilePath"></param> /// <param name="randomStr"></param> /// <param name="pb"></param> /// <returns></returns> public static MSG ImportARToDB(string xlsFilePath) { int pbLength = 0; MSG msg = new MSG(); ProgressInfo pI = new ProgressInfo("ConsoleCollectAR"); if (pI.ifExists()) { pI.delete(); } pI.add(); //用于确定本月最后一天. Stack <int> sDate = new Stack <int>(); Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>(); //按指纹日期 string fingerPrintDate = String.Empty; //导入数据的行数. int affectedCount = 0; //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; MyExcel myExcel = new MyExcel(xlsFilePath); //打开该文档。 myExcel.open(); #region 获取该工作簿中可视的工作表列表 List <Worksheet> listVisualWS = myExcel.getVisualWS(); List <AR_Sheet_Info> list_AR_Sheet_Info = new List <AR_Sheet_Info>(); Usual_Excel_Helper uEHelper = null; foreach (Worksheet wS in listVisualWS) { int sheet_index = wS.Index; int max_rows = wS.UsedRange.Rows.Count; uEHelper = new Usual_Excel_Helper(wS); //获取A1内容 string contentOfA3 = uEHelper.getSpecificCellValue("A3"); //考勤时间为 空,忽略 if (!"考勤时间".Equals(contentOfA3)) { continue; } int maxColumns = wS.UsedRange.Columns.Count; if (maxColumns <= 27 || maxColumns >= 32) { continue; } //列的宽度28-31. AR_Sheet_Info ar_sheet_info = new AR_Sheet_Info(sheet_index, max_rows); list_AR_Sheet_Info.Add(ar_sheet_info); } #endregion //获取list_AR_Sheet_Info 中 rows 最少的那个Sheet //先对list_AR_Sheet_Info进行排序。 list_AR_Sheet_Info.Sort((a, b) => a.Max_rows.CompareTo(b.Max_rows)); int sheetIndex = list_AR_Sheet_Info[0].SheetIndex; Worksheet ws = listVisualWS[sheetIndex - 1]; //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; AttendanceR.Sheet_name = ws.Name; //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表. string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", ""); if (String.IsNullOrEmpty(A1Str)) { msg.Msg = "工作表的A1单元格不能为空!"; msg.Flag = false; myExcel.close(); return(msg); } //如果A1Str的内容不包含"考勤记录表"5个字。 if (!A1Str.Contains("考勤记录表")) { msg.Msg = "A1内容未包含'考勤记录表'"; msg.Flag = false; myExcel.close(); return(msg); } #region 判断名称中是否区分了考勤记录。 string Seq_Attendance_Record = string.Empty; int indexOfFullStop = xlsFilePath.LastIndexOf("."); Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1); if (!CheckPattern.CheckNumber(Seq_Attendance_Record)) { msg.Msg = "考勤记录表名称请以数字结尾!"; msg.Flag = false; myExcel.close(); return(msg); } #endregion string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath); AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0]; string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim(); // \0: 表空字符. if (String.IsNullOrEmpty(C3Str)) { msg.Msg = "异常: 考勤时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } // string[] ArrayC3 = C3Str.Split('~'); if (ArrayC3.Length == 0) { msg.Msg = "异常: 考勤时间格式变更!"; msg.Flag = false; myExcel.close(); return(msg); } AttendanceR.Start_date = ArrayC3[0].ToString().Trim(); AttendanceR.End_date = ArrayC3[1].ToString().Trim(); //制表时间: L3 3行12列. string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim(); if (String.IsNullOrEmpty(L3Str)) { msg.Msg = "异常: 制表时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } //制表时间. AttendanceR.Tabulation_time = L3Str; //检查第4行是否为;考勤时间: string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim(); if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase)) { msg.Msg = "异常: 第四行已变更!"; msg.Flag = false; myExcel.close(); return(msg); } uEHelper = new Usual_Excel_Helper(ws); //此刻不能删除,只是获取行号。 Queue <Range> rangeToDelQueue = new Queue <Range>(); //判断是否有空行。 for (int i = 5; i <= rowsMaxCount; i++) { if (uEHelper.isBlankRow(i)) { //只要上一列不是 //删除掉此行。 //判断上一行中的A列是否为工号。 string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString()); if ("工号:".Equals(temp)) { continue; } //获取该行。 Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing]; //不为工号 rangeToDelQueue.Enqueue(rangeToDel); } ; } Range rangeToDelete; //开始删除空行。 while (rangeToDelQueue.Count > 0) { rangeToDelete = rangeToDelQueue.Dequeue(); rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp); } ; rowsMaxCount = ws.UsedRange.Rows.Count; //进度条长度增加。 pbLength += colsMaxCount; pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1)); pI.Max_value = pbLength; pI.Current_value = 0; pI.Msg = excelName + ",正在读取:"; pI.update(); Console.WriteLine(excelName + ",正在读取:"); //入队列值0 sDate.Push(0); int actualMaxDay = 0; //开始循环 for (int i = 1; i <= colsMaxCount; i++) { A4Str = ((Range)ws.Cells[4, i]).Text.ToString(); //碰到第4行某列为空,退出循环。 if (String.IsNullOrEmpty(A4Str)) { break; } int aDate = 0; //对A4Str进行分析. if (!Int32.TryParse(A4Str, out aDate)) { msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate); msg.Flag = false; myExcel.close(); return(msg); } pI.Current_value++; pI.update(); //判断新增的日期是否大于上一个. if (aDate <= sDate.Peek()) { //跳出循环. break; } actualMaxDay++; sDate.Push(aDate); } //取其中的最小值。 colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay); //考勤日期 fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-'); string tempStr = string.Empty; //开始循环 for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++) { //从第5行开始. //奇数;偶数行共用一个对象. AttendanceR AR = null; for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++) { //如果行数为奇数则为工号行. if (rowIndex % 2 == 1) { //工号行. //取工号 AR = new AttendanceR(); AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim(); //自行拼凑AR. AR.combine_Job_Number(); //取姓名: K5 AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim(); //取部门: U5 AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim(); //部门为空,则填充为NULL; AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL"; //取日期.填充0; AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0'); } else { //偶数行取考勤结果. //上班时间. 如B10; tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim(); string tempFirstTime = String.Empty; string tempLastTime = String.Empty; if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime)) { msg.Msg = string.Format(@"导入失败:表中第{0}行{1}列的按指纹时间格式不对!", rowIndex, colIndex); msg.Flag = false; return(msg); } ; AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime; AR.FPT_Last_Time = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime; qAttendanceR.Enqueue(AR); } pI.Current_value++; pI.update(); } } Console.WriteLine("提交数据: "); pI.Max_value = qAttendanceR.Count; pI.Current_value = 0; pI.Msg = "提交数据: "; pI.update(); System.Threading.Thread.Sleep(2000); #region OracleDaoHelper.noLogging("Attendance_Record"); //保存对象 while (qAttendanceR.Count > 0) { try { affectedCount += qAttendanceR.Dequeue().saveDataToDB(); pI.Current_value++; pI.update(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error); msg.Msg = ex.ToString(); msg.Flag = false; OracleConnHelper.closeConn(); return(msg); } } #endregion OracleDaoHelper.logging("Attendance_Record"); //释放对象 myExcel.close(); Console.WriteLine(String.Format(@"导入完成;计{0}条.", affectedCount.ToString())); //隐藏进度条。 pI.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString()); pI.Finish_flag = true; pI.update(); return(msg); }
/// <summary> /// 将考勤记录导入预备表中. /// </summary> /// <param name="xlsFilePath"></param> /// <param name="randomStr"></param> /// <param name="pb"></param> /// <returns></returns> public static MSG Import_Record_To_Preparative_Table(string xlsFilePath, string randomStr, System.Windows.Forms.Label lblPrompt, ProgressBar pb, System.Windows.Forms.Label lblResult) { int pbLength = 0; MSG msg = new MSG(); //用于确定本月最后一天. Stack <int> sDate = new Stack <int>(); Queue <AttendanceR> qAttendanceR = new Queue <AttendanceR>(); AttendanceR.Random_Str = randomStr; //按指纹日期 string fingerPrintDate = String.Empty; //导入数据的行数. int affectedCount = 0; //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; Usual_Excel_Helper uEHelper = null; MyExcel myExcel = new MyExcel(xlsFilePath); //打开该文档。 myExcel.open(); //只获取第一个表格。 Worksheet ws = myExcel.getFirstWorkSheetAfterOpen(); AttendanceR.File_path = xlsFilePath; //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; pb.Value = 0; pb.Visible = true; lblPrompt.Visible = true; AttendanceR.Sheet_name = ws.Name; //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表. string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", ""); if (String.IsNullOrEmpty(A1Str)) { msg.Msg = "工作表的A1单元格不能为空!"; msg.Flag = false; myExcel.close(); return(msg); } //如果A1Str的内容不包含"考勤记录表"5个字。 if (!A1Str.Contains("考勤记录表")) { msg.Msg = "A1内容未包含'考勤记录表'"; msg.Flag = false; myExcel.close(); return(msg); } #region 判断名称中是否区分了考勤记录。 string Seq_Attendance_Record = string.Empty; int indexOfFullStop = xlsFilePath.LastIndexOf("."); Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1); if (!CheckPattern.CheckNumber(Seq_Attendance_Record)) { msg.Msg = "考勤记录表名称请以数字结尾!"; msg.Flag = false; myExcel.close(); return(msg); } #endregion string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath); AttendanceR.Prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0]; string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim(); // \0: 表空字符. if (String.IsNullOrEmpty(C3Str)) { msg.Msg = "异常: 考勤时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } // string[] ArrayC3 = C3Str.Split('~'); if (ArrayC3.Length == 0) { msg.Msg = "异常: 考勤时间格式变更!"; msg.Flag = false; myExcel.close(); return(msg); } AttendanceR.Start_date = ArrayC3[0].ToString().Trim(); AttendanceR.End_date = ArrayC3[1].ToString().Trim(); //制表时间: L3 3行12列. string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim(); if (String.IsNullOrEmpty(L3Str)) { msg.Msg = "异常: 制表时间为空!"; msg.Flag = false; myExcel.close(); return(msg); } //制表时间. AttendanceR.Tabulation_time = L3Str; //检查第4行是否为;考勤时间: string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim(); if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase)) { msg.Msg = "异常: 第四行已变更!"; msg.Flag = false; myExcel.close(); return(msg); } uEHelper = new Usual_Excel_Helper(ws); //此刻不能删除,只是获取行号。 Queue <Range> rangeToDelQueue = new Queue <Range>(); //判断是否有空行。 for (int i = 5; i <= rowsMaxCount; i++) { if (uEHelper.isBlankRow(i)) { //只要上一列不是 //删除掉此行。 //判断上一行中的A列是否为工号。 string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString()); if ("工号:".Equals(temp)) { //本行为空,上一行为工号行,则也统计。 continue; } //本行,为空,上一行非工号行。则删除本行。 Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing]; //不为工号 rangeToDelQueue.Enqueue(rangeToDel); } ; } Range rangeToDelete; //开始删除空行。 while (rangeToDelQueue.Count > 0) { rangeToDelete = rangeToDelQueue.Dequeue(); rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp); } ; rowsMaxCount = ws.UsedRange.Rows.Count; //进度条长度增加。 pbLength += colsMaxCount; pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1)); pb.Maximum = pbLength; //入队列值0 sDate.Push(0); //显示进度条。 //考勤表中第4行,某月的最大考勤天数。 lblPrompt.Text = excelName + ",正在读取:"; int actualMaxDay = 0; //开始循环 for (int i = 1; i <= colsMaxCount; i++) { A4Str = ((Range)ws.Cells[4, i]).Text.ToString(); //碰到第4行某列为空,退出循环。 if (String.IsNullOrEmpty(A4Str)) { break; } int aDate = 0; //对A4Str进行分析. if (!Int32.TryParse(A4Str, out aDate)) { msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate); msg.Flag = false; myExcel.close(); return(msg); } pb.Value++; //判断新增的日期是否大于上一个. if (aDate <= sDate.Peek()) { //跳出循环. break; } actualMaxDay++; sDate.Push(aDate); } //取其中的最小值。 colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay); //考勤日期 fingerPrintDate = AttendanceR.Start_date.Substring(0, 7).Replace('/', '-'); string tempStr = string.Empty; //开始循环 for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++) { //从第5行开始. //奇数;偶数行共用一个对象. AttendanceR AR = null; for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++) { //如果行数为奇数则为工号行. if (rowIndex % 2 == 1) { //工号行. //取工号 AR = new AttendanceR(); AR.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim(); //自行拼凑AR. AR.combine_Job_Number(); //取姓名: K5 AR.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim(); //取部门: U5 AR.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim(); //部门为空,则填充为NULL; AR.Dept = !String.IsNullOrEmpty(AR.Dept) ? AR.Dept : "NULL"; //取日期.填充0; AR.Fingerprint_Date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0'); } else { //偶数行取考勤结果. //上班时间. 如B10; tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim(); string tempFirstTime = String.Empty; string tempLastTime = String.Empty; if (!getFPTime(tempStr, out tempFirstTime, out tempLastTime)) { msg.Msg = string.Format(@"导入失败:表中第{0}行{1}列的按指纹时间格式不对!", rowIndex, colIndex); msg.Flag = false; myExcel.close(); return(msg); } ; AR.FPT_Fisrt_Time = String.IsNullOrEmpty(tempFirstTime) ? String.Empty : AR.Fingerprint_Date + " " + tempFirstTime; AR.FPT_Last_Time = String.IsNullOrEmpty(tempLastTime) ? String.Empty : AR.Fingerprint_Date + " " + tempLastTime; qAttendanceR.Enqueue(AR); } pb.Value++; } } //释放对象 myExcel.close(); System.Threading.Thread.Sleep(2000); GC.Collect(); GC.WaitForPendingFinalizers(); lblResult.Text = ""; lblResult.Visible = false; lblPrompt.Visible = false; lblPrompt.Text = ""; lblPrompt.Text = "提交数据: "; lblPrompt.Focus(); lblPrompt.Visible = true; pb.Maximum = qAttendanceR.Count; pb.Value = 0; #region //保存对象 while (qAttendanceR.Count > 0) { try { AttendanceR aR = qAttendanceR.Dequeue(); affectedCount += aR.import_AR_To_Preparative_Table(); pb.Value++; } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error); msg.Msg = DirectoryHelper.getFileName(xlsFilePath) + ":导入失败; " + ex.ToString(); msg.Flag = false; return(msg); } } #endregion lblPrompt.Visible = false; //隐藏进度条。 pb.Visible = false; msg.Flag = true; msg.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString()); return(msg); }
/// <summary> /// 导入 /// </summary> public void import_P_C_Record_Template() { string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录:", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir); if (string.IsNullOrEmpty(xlsFilePath)) { return; } if (!File.Exists(xlsFilePath)) { return; } tbPath.Text = xlsFilePath; MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.open(); Tools.AppManagement.add(myExcel.HwndOfApp); Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen(); int notProductsCostFile_Count = 0; //非成本文件,则忽略。 if (!isProductsCostRecordOfGroup1(firstWS)) { notProductsCostFile_Count++; lblPromptForPB.Visible = false; ShowResult.show(lblResult, " 第1个表格非成衣成本文件!依据:第4行3列和第4行4列均非数字!", false); myExcel.close(); return; } Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS); string PN = uEHelper.getSpecificCellValue("A1"); if ("请在此填写成品名称".Equals(PN)) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (string.IsNullOrEmpty(PN)) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //判断是否存在该成品的记录 string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN); System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr); if (dt.Rows.Count > 0) { //删除文档 sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN); OracleDaoHelper.executeSQL(sqlStr); } //先导入概要工序。 int rowMaxIndexOfGroup1 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(1); int rowMaxIndexOfGroup2 = 0; //判断是否存在= bool ifExistsGroup2 = isProductsCostRecordOfGroup2(firstWS); if (ifExistsGroup2) { rowMaxIndexOfGroup2 = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(6); } //获取第3行 //int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count; pb.Maximum = rowMaxIndexOfGroup1 - 2 + rowMaxIndexOfGroup2 - 2; pb.Value = 0; pb.Visible = true; lblResult.Visible = false; OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str); theConnForTransaction.Open(); OracleTransaction tran = theConnForTransaction.BeginTransaction(); string prepared_Summary_process = string.Empty; //先读取第4列的内容。 for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup1; rowIndex++) { string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex); //序号为空跳出循环。 if (string.IsNullOrEmpty(seq_p_c_record)) { break; } string content_B_Col = uEHelper.getSpecificCellValue("B" + rowIndex).Trim(); //1.读取C列内容,如果为空,说明是主工序。 string content_C_Col = uEHelper.getSpecificCellValue("C" + rowIndex); if (string.IsNullOrEmpty(content_C_Col)) { //执行下一次循环。 prepared_Summary_process = content_B_Col; continue; } string specific_process = content_B_Col; //2. C列不为空。 string man_hours = content_C_Col; string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex); if (string.IsNullOrEmpty(specific_process)) { break; } ; if (string.IsNullOrEmpty(man_hours)) { break; } ; if (string.IsNullOrEmpty(labour_cost)) { break; } ; //插入该大类中的第一行数据。 sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier) VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')", seq_p_c_record, PN, prepared_Summary_process, specific_process, man_hours, labour_cost, Program._userInfo.User_Name); try { OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction); } catch (Exception ex) { if (ex.ToString().Contains("ORA-00001")) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); return; } MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); return; } lblPromptForPB.Visible = true; lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + ": "; pb.Value++; continue; } //先读取第9列的内容。 for (int rowIndex = 3; rowIndex <= rowMaxIndexOfGroup2; rowIndex++) { string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex); //序号为空跳出循环。 if (string.IsNullOrEmpty(seq_p_c_record)) { break; } string content_G_Col = uEHelper.getSpecificCellValue("G" + rowIndex).Trim(); //1.读取C列内容,如果为空,说明是主工序。 string content_H_Col = uEHelper.getSpecificCellValue("H" + rowIndex); if (string.IsNullOrEmpty(content_H_Col)) { //执行下一次循环。 prepared_Summary_process = content_G_Col; continue; } string specific_process = content_G_Col; //2. C列不为空。 string man_hours = content_H_Col; string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex); if (string.IsNullOrEmpty(specific_process)) { break; } ; if (string.IsNullOrEmpty(man_hours)) { break; } ; if (string.IsNullOrEmpty(labour_cost)) { break; } ; //插入该大类中的第一行数据。 sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier) VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')", seq_p_c_record, PN, prepared_Summary_process, specific_process, man_hours, labour_cost, Program._userInfo.User_Name); try { OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction); } catch (Exception ex) { if (ex.ToString().Contains("ORA-00001")) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); return; } MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); return; } lblPromptForPB.Visible = true; lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + ": "; pb.Value++; continue; } tran.Commit(); theConnForTransaction.Close(); pb.Visible = false; lblPromptForPB.Visible = false; lblPromptForPB.Text = ""; lblResult.Visible = true; IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl(); this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN); DGVHelper.AutoSizeForDGV(dgv); myExcel.close(); //ShowResult.show(lblResult, " 第1个表格非成衣成本文件!依据:第3行4列和第3行5列均非数字!", false); ShowResult.show(lblResult, " 导入完成!", true); timerRestoreLblResult.Enabled = true; }
/// <summary> /// 导入成本记录。 /// </summary> private void importProductsCostRecord() { string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录:", "*.xls|*.xls", defaultDir); if (string.IsNullOrEmpty(xlsFilePath)) { return; } if (!File.Exists(xlsFilePath)) { return; } tbPath.Text = xlsFilePath; MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.open(); Tools.AppManagement.add(myExcel.HwndOfApp); Worksheet wS = myExcel.getFirstWorkSheetAfterOpen(); Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS); string PN = uEHelper.getSpecificCellValue("A1"); if ("请在此填写成品名称".Equals(PN)) { MessageBox.Show("请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN); System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr); if (dt.Rows.Count > 0) { sqlStr = string.Format(@"DELETE FROM Products_Cost_Header WHERE Product_Name = '{0}'", PN); OracleDaoHelper.executeSQL(sqlStr); } //判断是否存在该成品的记录 sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN); dt = OracleDaoHelper.getDTBySql(sqlStr); if (dt.Rows.Count > 0) { //删除文档 sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN); OracleDaoHelper.executeSQL(sqlStr); } //先导入概要工序。 int rowStartIndex = 3; int rowMaxIndex = wS.UsedRange.Rows.Count; string summary_process = string.Empty; string specific_process = string.Empty; //summary_process不允许空开。 //先检查第1至4列. pb.Visible = true; lblResult.Visible = false; pb.Maximum = 4 * (rowMaxIndex - rowStartIndex + 1); pb.Value = 0; for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++) { string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex); string man_hour = uEHelper.getSpecificCellValue("C" + rowIndex); string temp = uEHelper.getSpecificCellValue("B" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(temp)) { //左边到头。 break; } if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour)) { //为该要工序. //开始保存 //则process为概要工序 summary_process = uEHelper.getSpecificCellValue("B" + rowIndex); pb.Value++; continue; } //具体工序时先保存概要工序。 sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process) values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process); OracleDaoHelper.executeSQL(sqlStr); pb.Value++; } //再检查第6至第9列 F,G,H,I for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++) { string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex); string man_hour = uEHelper.getSpecificCellValue("H" + rowIndex); string temp = uEHelper.getSpecificCellValue("G" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(temp)) { //右边到头 break; } if (string.IsNullOrEmpty(man_hour) || "0".Equals(man_hour)) { //为该要工序. //开始保存 //则process为概要工序 summary_process = uEHelper.getSpecificCellValue("G" + rowIndex); pb.Value++; continue; } //具体工序时先保存概要工序。 sqlStr = string.Format(@"insert into Products_Cost_Header(SEQ,Product_Name,Summary_Process) values('{0}','{1}','{2}')", seq_p_c_record, PN, summary_process); OracleDaoHelper.executeSQL(sqlStr); pb.Value++; } #region 开始保存 第1至第4列的数据。 for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++) { string seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex); string temp = uEHelper.getSpecificCellValue("B" + rowIndex); string man_hours = uEHelper.getSpecificCellValue("C" + rowIndex); string labour_cost = uEHelper.getSpecificCellValue("D" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(temp)) { //右边到头 break; } if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours)) { //为该要工序. //开始保存 //则temp为具体工序。 specific_process = temp; sqlStr = string.Format(@"INSERT INTO PRODUCTS_Cost( SEQ, SEQ_P_C_Record, Product_Name, Summary_Process, Specific_Process, Man_Hours, Labour_Cost, Supplier, UPDATE_Time ) SELECT SEQ_PRODUCTS_COST.Nextval, '{1}', --- Seq_P_C_Record '{0}', Summary_Process, '{2}', --- specific_process '{3}', --- man_hours '{4}', --- labour_cost '{5}', --- supplier SYSDATE FROM Products_Cost_Header WHERE Product_Name = '{0}' AND SEQ = '{1}'", PN, seq_p_c_record, specific_process, man_hours, labour_cost, Program._userInfo.User_Name ); OracleDaoHelper.executeSQL(sqlStr); pb.Value++; } } #endregion #region 开始保存 第6至第9列的数据。 for (int rowIndex = rowStartIndex; rowIndex < rowMaxIndex; rowIndex++) { string seq_p_c_record = uEHelper.getSpecificCellValue("F" + rowIndex); string temp = uEHelper.getSpecificCellValue("G" + rowIndex); string man_hours = uEHelper.getSpecificCellValue("H" + rowIndex); string labour_cost = uEHelper.getSpecificCellValue("I" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(temp)) { //右边到头 break; } if (!string.IsNullOrEmpty(man_hours) && !"0".Equals(man_hours)) { //为该要工序. //开始保存 //则temp为具体工序。 specific_process = temp; sqlStr = string.Format(@"INSERT INTO PRODUCTS_Cost( SEQ, SEQ_P_C_Record, Product_Name, Summary_Process, Specific_Process, Man_Hours, Labour_Cost, Supplier, UPDATE_Time ) SELECT SEQ_PRODUCTS_COST.Nextval, '{1}', --- SEQ_P_C_Record '{0}', Summary_Process, '{2}', --- specific_process '{3}', --- man_hours '{4}', --- labour_cost '{5}', --- supplier SYSDATE FROM Products_Cost_Header WHERE Product_Name = '{0}' AND SEQ = '{1}'", PN, seq_p_c_record, specific_process, man_hours, labour_cost, Program._userInfo.User_Name); OracleDaoHelper.executeSQL(sqlStr); pb.Value++; } } #endregion myExcel.close(); IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl(); this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN); DGVHelper.AutoSizeForDGV(dgv); pb.Visible = false; lblResult.Visible = true; }
/// <summary> /// 导入 /// </summary> public void import_P_C_Record_Template_previous() { string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录:", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir); if (string.IsNullOrEmpty(xlsFilePath)) { return; } if (!File.Exists(xlsFilePath)) { return; } tbPath.Text = xlsFilePath; MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.open(); Tools.AppManagement.add(myExcel.HwndOfApp); List <Worksheet> wSList = myExcel.getVisualWS(); int notProductsCostFile_Count = 0; for (int i = 0; i <= wSList.Count - 1; i++) { Worksheet wS = wSList[i]; MSG msg = null; //非成本文件,则忽略。 if (!(msg = isProductsCostRecord(wS)).Flag) { notProductsCostFile_Count++; continue; } Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS); string PN = uEHelper.getSpecificCellValue("A1"); if ("请在此填写成品名称".Equals(PN)) { MessageBox.Show(wS.Index + "." + wS.Name + ": 请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); continue; } if (string.IsNullOrEmpty(PN)) { MessageBox.Show(wS.Index + "." + wS.Name + ": 产品名称不能为空!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); continue; } //判断是否存在该成品的记录 string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN); System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr); if (dt.Rows.Count > 0) { //删除文档 sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN); OracleDaoHelper.executeSQL(sqlStr); } //先导入概要工序。 int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankRow(); //int rowMaxIndex = wS.UsedRange.Rows.Count; string seq_p_c_record = string.Empty; string summary_process = string.Empty; string specific_process = string.Empty; string man_hours = string.Empty; string labour_cost = string.Empty; pb.Maximum = rowMaxIndex - 2; pb.Value = 0; pb.Visible = true; lblResult.Visible = false; OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str); theConnForTransaction.Open(); OracleTransaction tran = theConnForTransaction.BeginTransaction(); for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++) { seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex); string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim(); specific_process = uEHelper.getSpecificCellValue("C" + rowIndex); man_hours = uEHelper.getSpecificCellValue("D" + rowIndex); labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(specific_process)) { break; } ; if (string.IsNullOrEmpty(man_hours)) { break; } ; if (string.IsNullOrEmpty(labour_cost)) { break; } ; //若主工序不为空 if (!"".Equals(prepared_Summary_process)) { summary_process = prepared_Summary_process; } //插入该大类中的第一行数据。 sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier) VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')", seq_p_c_record, PN, summary_process, specific_process, man_hours, labour_cost, Program._userInfo.User_Name); try { OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction); } catch (Exception ex) { if (ex.ToString().Contains("ORA-00001")) { MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); break; } MessageBox.Show(wS.Index + "." + wS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); break; } lblPromptForPB.Visible = true; lblPromptForPB.Text = wS.Index + "." + wS.Name + ": "; pb.Value++; continue; } tran.Commit(); theConnForTransaction.Close(); pb.Visible = false; lblPromptForPB.Visible = false; lblPromptForPB.Text = ""; lblResult.Visible = true; IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl(); this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN); DGVHelper.AutoSizeForDGV(dgv); } myExcel.close(); if (notProductsCostFile_Count == wSList.Count) { ShowResult.show(lblResult, " 共" + wSList.Count + "个表格均非成衣成本文件!依据:第3行4列和第3行5列均非数字!", false); timerRestoreLblResult.Enabled = true; lblPromptForPB.Visible = false; } }
/// <summary> /// 导入 /// </summary> public void import_P_C_Record_Of_First_Sheet() { //确认关闭已经打开的Excel if (CmdHelper.ifExistsTheProcessByName("EXCEL")) { FrmCloseExcel frmCloseExcel = new FrmCloseExcel(); frmCloseExcel.ShowDialog(); } string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择成衣成本记录:", "*.xls,*.xlsx|*.xls;*.xlsx", defaultDir); if (string.IsNullOrEmpty(xlsFilePath)) { return; } if (!File.Exists(xlsFilePath)) { return; } tbPath.Text = xlsFilePath; MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.open(); Tools.AppManagement.add(myExcel.HwndOfApp); Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen(); MSG msg = null; //非成本文件,则忽略。 if (!(msg = isProductsCostRecord(firstWS)).Flag) { lblPromptForPB.Visible = false; ShowResult.show(lblResult, msg.Msg, false); myExcel.close(); return; } Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS); string PN = uEHelper.getSpecificCellValue("A1"); if ("请在此填写成品名称".Equals(PN)) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 请填写成品名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); myExcel.close(); return; } if (string.IsNullOrEmpty(PN)) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 产品名称不能为空!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); myExcel.close(); return; } //判断是否存在该成品的记录 string sqlStr = string.Format(@"SELECT 1 FROM Products_Cost WHERE Product_Name = '{0}'", PN); System.Data.DataTable dt = OracleDaoHelper.getDTBySql(sqlStr); if (dt.Rows.Count > 0) { //删除文档 sqlStr = string.Format(@"DELETE FROM Products_Cost where Product_Name = '{0}'", PN); OracleDaoHelper.executeSQL(sqlStr); } //先导入概要工序。 //最大行,取 //从第二行开始,取第三列连续出现工序的最大行 int rowMaxIndex = uEHelper.getTheMaxRowIndexUntilBlankCellOfTheSpecificCol(2, 3); //int rowMaxIndex = firstWS.UsedRange.RofirstWS.Count; string seq_p_c_record = string.Empty; string summary_process = string.Empty; string specific_process = string.Empty; string man_hours = string.Empty; string labour_cost = string.Empty; pb.Maximum = rowMaxIndex - 2; pb.Value = 0; pb.Visible = true; lblResult.Visible = false; OracleConnection theConnForTransaction = new OracleConnection(OracleDaoHelper.conn_str); theConnForTransaction.Open(); OracleTransaction tran = theConnForTransaction.BeginTransaction(); //先删除Check_Products_Cost_Doc中的所有记录。 CheckProductsCostDoc.deleteAllRecord(); //检查工时数据,成本数据是否正确。 for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++) { seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex); string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim(); specific_process = uEHelper.getSpecificCellValue("C" + rowIndex); man_hours = uEHelper.getSpecificCellValue("D" + rowIndex); labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(specific_process)) { break; } ; if (string.IsNullOrEmpty(man_hours)) { break; } ; if (string.IsNullOrEmpty(labour_cost)) { break; } //判断man_hours是否为decimal; decimal _man_hours_d; decimal _labour_cost_d; if (!decimal.TryParse(man_hours, out _man_hours_d)) { lblPromptForPB.Visible = false; pb.Visible = false; ShowResult.show(lblResult, string.Format(@"第{0}行第4列,非工时数据,请检查!", rowIndex), false); timerRestoreLblResult.Enabled = true; myExcel.close(); return; } if (!decimal.TryParse(labour_cost, out _labour_cost_d)) { lblPromptForPB.Visible = false; pb.Visible = false; ShowResult.show(lblResult, string.Format(@"第{0}行第5列,非单价数据,请检查!", rowIndex), false); timerRestoreLblResult.Enabled = true; myExcel.close(); return; } //若主工序不为空 if (!"".Equals(prepared_Summary_process)) { summary_process = prepared_Summary_process; } CheckProductsCostDoc checkProductsCostDoc = new CheckProductsCostDoc(rowIndex, summary_process, specific_process); msg = checkProductsCostDoc.ifExistsSameProcess(); if (msg.Flag) { lblPromptForPB.Visible = false; pb.Visible = false; ShowResult.show(lblResult, string.Format(@"序号:{0}行与{1},工序相同,请检查。", seq_p_c_record, msg.Msg), false); timerRestoreLblResult.Enabled = true; myExcel.close(); return; } checkProductsCostDoc.addProcess(); lblPromptForPB.Visible = true; lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + " 检查中: "; pb.Value++; } pb.Value = 0; //保存工序数据到后台。 for (int rowIndex = 3; rowIndex <= rowMaxIndex; rowIndex++) { seq_p_c_record = uEHelper.getSpecificCellValue("A" + rowIndex); string prepared_Summary_process = uEHelper.getSpecificCellValue("B" + rowIndex).Trim(); specific_process = uEHelper.getSpecificCellValue("C" + rowIndex); man_hours = uEHelper.getSpecificCellValue("D" + rowIndex); labour_cost = uEHelper.getSpecificCellValue("E" + rowIndex); if (string.IsNullOrEmpty(seq_p_c_record)) { break; } if (string.IsNullOrEmpty(specific_process)) { break; } ; if (string.IsNullOrEmpty(man_hours)) { break; } ; if (string.IsNullOrEmpty(labour_cost)) { break; } //若主工序不为空 if (!"".Equals(prepared_Summary_process)) { summary_process = prepared_Summary_process; } //插入该大类中的第一行数据。 sqlStr = string.Format(@"INSERT INTO Products_Cost(SEQ,SEQ_P_C_RECORD,Product_Name,Summary_Process,Specific_Process,Man_hours,Labour_cost,Supplier) VALUES(SEQ_Products_Cost.nextVal,'{0}','{1}','{2}','{3}','{4}','{5}','{6}')", seq_p_c_record, PN, summary_process, specific_process, man_hours, labour_cost, Program._userInfo.User_Name); try { OracleDaoHelper.executeSQLThrowExceptioin(sqlStr, theConnForTransaction); } catch (Exception ex) { if (ex.ToString().Contains("ORA-00001")) { MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行为重复工序,请检查成本表格,请重新导入 . ", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); return; } MessageBox.Show(firstWS.Index + "." + firstWS.Name + ": 第" + rowIndex + "行,数据导入时发生异常,请检查成本表格,重新导入。" + ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); tran.Rollback(); theConnForTransaction.Close(); lblPromptForPB.Visible = false; pb.Visible = false; myExcel.close(); return; } lblPromptForPB.Visible = true; lblPromptForPB.Text = firstWS.Index + "." + firstWS.Name + " 导入中: "; pb.Value++; continue; } tran.Commit(); theConnForTransaction.Close(); pb.Visible = false; lblPromptForPB.Visible = false; lblPromptForPB.Text = ""; lblResult.Visible = true; IShowProductsCostDetail showProductsCostDetailImpl = new ShowProductsCostDetailImpl(); this.dgv.DataSource = showProductsCostDetailImpl.getProductsDetail(PN); DGVHelper.AutoSizeForDGV(dgv); myExcel.close(); ShowResult.show(lblResult, "已经提交!", true); timerRestoreLblResult.Enabled = true; lblPromptForPB.Visible = false; //默认提交 pictures下的 waiting.jpg图片。 //保存或更新其对应的图片。 if (!FrmProductsCostSummary.ifExistsRecordOfProducts_Picture(PN)) { FrmProductsCostSummary.addPictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg"); } else { FrmProductsCostSummary.updatePictureOfProduct(PN, System.Windows.Forms.Application.StartupPath + "\\pictures\\waiting.jpg"); } }