/// <summary> /// 获取考勤记录。 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnGetARResult_Click(object sender, EventArgs e) { V_Work_Schedule v_W_S = new V_Work_Schedule(); v_W_S.WORK_AND_REST_DATE = YearAndMonthStr; /* * if (!v_W_S.ifExistsWS()) { * return; * } */ ARResult aRResult = new ARResult(YearAndMonthStr); string randomStr = RandomStr.GetRandomString(40, true, true, true, false, ""); if (aRResult.updateARResult(randomStr) == 0) { this.dgv.DataSource = MESSAGES.getMSG(randomStr); DGVHelper.AutoSizeForDGV(dgv); ShowResult.show(lblResult, "异常!", false); timerRestoreTheLblResult.Enabled = true; return; } ShowResult.show(lblResult, "完成!", true); //显示结果 AttendanceR aR = new AttendanceR(); this.dgv.DataSource = aR.getARByYearAndMonth(aRResult.Year_And_Month_str); DGVHelper.AutoSizeForDGV(dgv); }
/// <summary> /// 分析考勤数据。 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnAnalyze_Click(object sender, EventArgs e) { int V_FLAG = 0; //分析刚刚导入的数据。 OracleParameter parma_RandomStr = new OracleParameter("v_RandomStr", OracleDbType.NVarchar2, ParameterDirection.Input); OracleParameter param_V_FLAG = new OracleParameter("v_FLAG", OracleDbType.Int32, ParameterDirection.Output); parma_RandomStr.Value = randomStr; param_V_FLAG.Value = V_FLAG; OracleParameter[] parameters = new OracleParameter[2] { parma_RandomStr, param_V_FLAG }; int j = oH.ExecuteNonQuery(procedureName, parameters); V_FLAG = Int32.Parse(parameters[1].Value.ToString()); if (V_FLAG == 1) { //获取导入的信息 ShowResult.show(lblResult, "分析结束", true); timerRestoreTheLblResult.Enabled = true; } else { /* * //未能导入,展示查询结果 * DataTable dt1 = OracleDaoHelper.getDTBySql(String.Format(@"SELECT * FROM MESSAGE WHERE IPADDR = '{0}' AND UPPER(Subject) = UPPER('{1}') AND Random_Str = '{2}' ORDER BY OPERATE_TIME DESC;PROMPT DESC"; Program.IPADDR; subject; _staffTemp.Random_Str)); * this.dgv.DataSource = null; * Messages.convertColumnsName(dt1); * dgv.DataSource = dt1; * DGVHelper.AutoSizeForDGV(dgv); * ShowResult.prompt(lblResult; "存在同名员工,确定导入,请点击\"确定导入\"按钮!"; false); * timerRestoreTheLblResult.Enabled = true; */ } }
/// <summary> /// 补卡 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnFillUpRecord_Click(object sender, EventArgs e) { v_fillUp.Day = day; v_fillUp.Time = time; int fillUpRecordTimes = v_fillUp.getFillUpRecordTimes(); if (fillUpRecordTimes >= 3) { ShowResult.show(lblResult, v_fillUp.Day.Substring(0, 7) + " " + v_fillUp.Name + " 已经补卡3次了。", false); timerRestoreTheLblResult.Start(); return; } string temp = string.Empty; if (v_fillUp.ifNotHaveRecordOfMorning() && !v_fillUp.ifNotHaveRecordOfAfternoon()) { temp = "上班卡"; } else if (!v_fillUp.ifNotHaveRecordOfMorning() && v_fillUp.ifNotHaveRecordOfAfternoon()) { temp = "下班卡"; } if (!v_fillUp.updateTheRecord()) { return; } this.dgv.DataSource = v_fillUp.getARRecordToFillUpByName(); DGVHelper.AutoSizeForDGV(dgv); ShowResult.show(lblResult, day + ": " + temp + " 已补!", true); timerRestoreTheLblResult.Start(); }
/// <summary> /// 导出考勤记录。 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExportARResult_Click(object sender, EventArgs e) { //先判断当月的休息日是否指定。 if (string.IsNullOrEmpty(YearAndMonthStr)) { ShowResult.show(lblResult, "请先设定年月!", false); return; } //判断当前月份有无设定休息日。 TheDaysOfOvertime restDay = new TheDaysOfOvertime("everybody", YearAndMonthStr); if (!restDay.ifHaveTheDayOfOvertime()) { ShowResult.show(lblResult, string.Format(@"请先设定{0}的:休息日!", YearAndMonthStr), false); return; } this.MdiParent.WindowState = FormWindowState.Minimized; this.MdiParent.ShowInTaskbar = false; ((FrmMainOfAttendanceRecord)this.MdiParent).nfiSystem.Visible = true; if (radioBtnSeparate.Checked) { separateOutputAR(); //显示完成图标 timerCompleted.Start(); return; } toAWholePiece(); //显示完成图标。 timerCompleted.Start(); }
/// <summary> /// 检查第四行 /// </summary> /// <returns></returns> private bool check4thRow(List <String> excelPathList, out int maxColIndex) { maxColIndex = 0; //先清除所有记录。 AR_Temp.deleteTheARTemp(); foreach (string excelPath in excelPathList) { //打开文档 MyExcel myExcel = new MyExcel(excelPath); myExcel.open(); Worksheet firstWS = myExcel.getFirstWorkSheetAfterOpen(); string fileNameWithoutSuffix = DirectoryHelper.getFileNameWithoutSuffix(excelPath); int checkedColIndex = 0; if (!AttendanceRHelper.isAllDigit(firstWS, 4, out checkedColIndex)) { myExcel.close(); lblPrompt.Visible = false; ShowResult.show(lblResult, fileNameWithoutSuffix + ": 第4行" + checkedColIndex.ToString() + "列非数字; 导入取消。", false); timerRestoreTheLblResult.Start(); return(false); } if (maxColIndex == 0) { Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstWS); maxColIndex = uEHelper.getMaxColIndexBeforeBlankCellInSepcificRow(4); } myExcel.close(); } return(true); }
private void btnGenerateDefaultRestDays_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(this.year_And_Month)) { ShowResult.show(lblResult, "请先选择月份!", false); timerRestoreTheLblResult.Enabled = true; return; } OracleParameter param_Name = new OracleParameter("v_Name", OracleDbType.NVarchar2, ParameterDirection.Input); OracleParameter param_YearAndMonth = new OracleParameter("v_yearAndMonth", OracleDbType.Varchar2, ParameterDirection.Input); OracleHelper oH = OracleHelper.getBaseDao(); string procedureName = "Generate_Rest_Day"; param_Name.Value = "everybody"; param_YearAndMonth.Value = this.year_And_Month; OracleParameter[] parameters = new OracleParameter[2] { param_Name, param_YearAndMonth }; int j = oH.ExecuteNonQuery(procedureName, parameters); if (j == 0) { ShowResult.show(lblResult, "Generate_Rest_Day:生成Rest_Day失败!", false); timerRestoreTheLblResult.Enabled = true; return; } this.dgv.DataSource = TheDaysOfOvertime.getRestDays(year_And_Month); DGVHelper.AutoSizeForDGV(dgv); }
private void btnAdd_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(this.year_Month_Day)) { ShowResult.show(lblResult, "请先选择具体日期", false); timerRestoreTheLblResult.Enabled = true; return; } TheDaysOfOvertime restDay = new TheDaysOfOvertime("everybody", year_Month_Day); restDay.addRestDay(); this.dgv.DataSource = TheDaysOfOvertime.getRestDays(year_And_Month); DGVHelper.AutoSizeForDGV(dgv); }
/// <summary> /// 导出策略 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ExportToolStripMenuItem_Click(object sender, EventArgs e) { if (dgv.Rows.Count == 1) { return; } string dir = Environment.CurrentDirectory + "\\个人考勤记录"; DirectoryHelper.createDirecotry(dir); string xlsFilePath = dir + "\\" + RandomStr.getTimeStamp() + ".xls"; ExcelHelper.saveDtToExcel((System.Data.DataTable)dgv.DataSource, xlsFilePath); ShowResult.show(lblResult, "记录存于" + dir, true); timerRestoreTheLblResult.Enabled = true; }
private void delTheLearnerToolStripMenuItem_Click(object sender, EventArgs e) { DataGridViewRow dgvR = dgv.CurrentRow; if (dgvR == null) { return; } string name = dgvR.Cells["姓名"].Value.ToString(); Learning learner = new Learning(name); learner.del(); ShowResult.show(lblResult, "已删除!", true); timerClsResult.Start(); this.dgv.DataSource = Learning.getAllLearners(); DGVHelper.AutoSizeForDGV(dgv); }
private void exportToolStripMenuItem_Click(object sender, EventArgs e) { if (CmdHelper.ifExistsTheProcessByName("EXCEL")) { return; } string _fileName = "同名工序_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"; xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("同名工序汇总:", "*.xls|*.xls", defaultDir, _fileName); if (!xlsFilePath.Contains(@"\")) { return; } System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource; ExcelHelper.saveDtToExcelWithProgressBar(dt, xlsFilePath, pb); //((FrmMainOfProductsCost)this.ParentForm).notifyIcon.ShowBalloonTip(7000, "提示:","汇总保存于: " + xlsFilePath, ToolTipIcon.Info); ShowResult.show(lblResult, "汇总保存于: " + xlsFilePath, true); timerRestoreLblResult.Start(); }
private bool checkName() { string name = cbName.Text.Trim(); if (string.IsNullOrEmpty(name)) { cbName.Focus(); return(false); } string result = nameHelper.checkName(name); if (result.Contains("不")) { ShowResult.show(lblResult, result, false); timerClsResult.Enabled = true; cbName.Focus(); return(false); } return(true); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnGenerateWorkSchedule_Click(object sender, EventArgs e) { //获取MonthCalendar的值 //依据Work Summary 生成一张工作安排表 V_Work_Schedule v_W_S = new V_Work_Schedule(); V_Work_Schedule._YearAndMonthStr = this.YearAndMonthStr; if (!WorkSummary.ifExistsWorkSummary(this.YearAndMonthStr)) { ShowResult.show(lblResult, "请先导入5月份考勤记录!", false); timerRestoreTheLblResult.Enabled = true; return; } //生成工作表。 v_W_S.GenWorkSchedule(); MSG msg = v_W_S.genExcel(out xlsFilePath); ShowResult.show(lblResult, msg.Msg, msg.Flag); this.timerRestoreTheLblResult.Enabled = true; }
private bool checkName() { string name = tbName.Text.Trim(); if (string.IsNullOrEmpty(name)) { tbName.Focus(); return(false); } string result = String.Empty; if (!"unique".Equals(result = nameHelper.checkName(name))) { tbName.Text = ""; ShowResult.show(lblResult, result, false); timerClsResult.Enabled = true; tbName.Focus(); return(false); } return(true); }
/// <summary> /// 提交请假 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSubmit_Click(object sender, EventArgs e) { if (!checkName()) { return; } if (tbName.Text.Trim() == "") { return; } startDateTime = new DateTime(_start_year, _start_month, _start_day, _start_hour, _start_minute, _start_second); endDateTime = new DateTime(_end_year, _end_month, _end_day, _end_hour, _end_minute, _end_second); if (startDateTime >= endDateTime) { ShowResult.show(lblResult, "结束时间需比起始时间大!", false); timerClsResult.Enabled = true; return; } string startTime = startDateTime.ToString("yyyy-MM-dd HH:mm:ss"); string endTime = endDateTime.ToString("yyyy-MM-dd HH:mm:ss"); a_F_L_H = new ASK_For_Leave_Helper(tbName.Text.Trim(), startTime, endTime, tbNO.Text.Trim()); //先判断是否有日期范围的假条 if (a_F_L_H.ifExistsAtRange()) { ShowResult.show(lblResult, "已存在该日期范围的假条!", false); timerClsResult.Enabled = true; return; } if (a_F_L_H.ifExistsVacationAtRange()) { ShowResult.show(lblResult, "所设定的范围,涵盖公司休假日,请分段请假!", false); timerClsResult.Enabled = true; return; } a_F_L_H.save(); tbNO.Text = ASK_For_Leave_Helper.getLastedNO(); this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationList(); DGVHelper.AutoSizeForDGV(dgv); }
private void btnAdd_Click(object sender, EventArgs e) { string line_name = tbLineName.Text.Trim(); string monitor = tbMonitor.Text.Trim(); if (string.IsNullOrEmpty(line_name)) { return; } //判断是否已经存在 if (LineInfo.ifExistsTheLine(line_name)) { ShowResult.show(lblResult, "此线体已存在!", false); timerRestoreTheLblResult.Start(); return; } LineInfo lineInfo = new LineInfo(line_name, monitor); lineInfo.add(); //重新获取线体信息 this.dgv.DataSource = LineInfo.getAllLineInfo(); DGVHelper.AutoSizeForDGV(dgv); }
private void btnSubmit_Click(object sender, EventArgs e) { string name = tbName.Text.Trim(); if (name.Length == 0) { return; } Learning learner = new Learning(name); if (learner.ifExists()) { ShowResult.show(lblResult, "已存在!", false); timerClsResult.Start(); tbName.Clear(); return; } learner.add(); ShowResult.show(lblResult, "已添加!", true); timerClsResult.Start(); refreshDgv(); tbName.Clear(); }
private void btnAdd_Click(object sender, EventArgs e) { string report_team_name = tbTeam_name.Text.Trim(); string monitor = tbMonitor.Text.Trim(); if (string.IsNullOrEmpty(report_team_name)) { return; } //判断是否已经存在 if (Team_Info.ifExistsTheTeam(report_team_name)) { ShowResult.show(lblResult, "此班组已存在!", false); timerRestoreTheLblResult.Start(); return; } Team_Info team_Info = new Team_Info(report_team_name, monitor); team_Info.add(); //重新获取线体信息 this.dgv.DataSource = Team_Info.getAllTeamInfo(); DGVHelper.AutoSizeForDGV(dgv); }
/// <summary> // 导出至Excel. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ExportToExceltoolStripMenuItem_Click(object sender, EventArgs e) { DataGridViewRowCollection drRows = this.dgv.Rows; if (drRows.Count == 0) { return; } //1. 先确定 image path if (CmdHelper.ifExistsTheProcessByName("excel") || CmdHelper.ifExistsTheProcessByName("et")) { Tools.FrmPrompt frmPrompt = new Tools.FrmPrompt("excel.exe|et.exe"); frmPrompt.ShowDialog(); } //2. 打开Excel //先获取时间字符串 string currTimeStr = TimeHelper.getCurrentTimeStr(); string xlsFileName = "成衣成本汇总_" + currTimeStr + ".xls"; string srcFilePath = Application.StartupPath + "\\成本汇总\\ProductsCostSummaryTemplate.xls"; string destDir = Application.StartupPath + "\\成本汇总\\"; string destFileName = xlsFileName; CmdHelper.copyFileToDestDirWithNewFileName(srcFilePath, destDir, destFileName); //目的文件名为: destFilePath = destDir + xlsFileName; MyExcel myExcel = new MyExcel(destFilePath); myExcel.open(true); Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(myExcel.getFirstWorkSheetAfterOpen()); for (int index = 0; index <= drRows.Count - 1; index++) { DataGridViewRow currRow = drRows[index]; string product_name = currRow.Cells["Product_Name"].Value.ToString(); uEHelper.setSpecificCellValue("A" + (2 + index).ToString(), product_name); //(drRow.Cells["product_name"] uEHelper.setSpecificCellValue("B" + (2 + index).ToString(), currRow.Cells["total_man_hours"].Value.ToString()); uEHelper.setSpecificCellValue("C" + (2 + index).ToString(), currRow.Cells["total_labour_cost"].Value.ToString()); uEHelper.setSpecificCellValue("D" + (2 + index).ToString(), currRow.Cells["supplier"].Value.ToString()); uEHelper.setSpecificCellValue("E" + (2 + index).ToString(), currRow.Cells["latest_update_time"].Value.ToString()); //picture //保存此图片 //Image image = ((Image)(currRow.Cells["picture"].Value)); byte[] pictureByteArray = (byte[])currRow.Cells["picture"].Value; Image image = PictureHelper.ReturnPhoto(pictureByteArray); string picPath = string.Format(@"{0}\{1}.jpg", destDir, product_name); image.Save(picPath, image.RawFormat); Microsoft.Office.Interop.Excel.Range range = uEHelper.getRange("F" + (2 + index).ToString(), "F" + (2 + index).ToString()); uEHelper.pastePicture(range, picPath); } myExcel.saveWithoutAutoFit(); myExcel.close(); ShowResult.show(lblResult, string.Format(@"导出完毕,存于:{0}", destFilePath), true); timerRestoreLabel.Start(); }
/// <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"); } }
/// <summary> /// /// </summary> private void separateOutputAR() { string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\考勤汇总"; killHwndOfXls(); Queue <int> prefix_Of_Staffs_Queue = V_AR_RESULT.get_Prefix_Staffs(YearAndMonthStr); if (prefix_Of_Staffs_Queue.Count == 0) { ShowResult.show(lblResult, "尚未导入本月的考勤记录!", false); timerRestoreTheLblResult.Enabled = true; return; } string prefix_Of_Staffs = string.Empty; //分几个工作表储存。 while (prefix_Of_Staffs_Queue.Count > 0) { prefix_Of_Staffs = prefix_Of_Staffs_Queue.Dequeue().ToString(); string _fileName = YearAndMonthStr + "_考勤汇总" + prefix_Of_Staffs.Substring(0, 1) + ".xls"; if (!xlsFilePath.Contains(":")) { //导出到Excel中 xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("考勤汇总:", "*.xls|*.xls", _defaultDir, _fileName); if (!xlsFilePath.Contains(@"\")) { return; } } else { int index = xlsFilePath.LastIndexOf(string.Format(@"{0}_考勤汇总", YearAndMonthStr)); xlsFilePath = xlsFilePath.Remove(index) + _fileName; } //int index = xlsFilePath.LastIndexOf(string.Format(@"{0}_考勤汇总", YearAndMonthStr)); //创建文件 //DirectoryHelper.createFile(xlsFilePath); //xlsFilePath = xlsFilePath.Remove(index) + _fileName; MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.create(); myExcel.openWithoutAlerts(); //追加Hwnd到队列中. hwndOfXls_Queue.Enqueue(myExcel.HwndOfApp); MSG msg = new MSG(); //prefix_Of_Staffs = prefix_Of_Staffs_Queue.Dequeue().ToString(); //依据前缀和月份获取列表。 //获取第一张表 Worksheet _firstWS = myExcel.getFirstWorkSheetAfterOpen(); Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(_firstWS); uEHelper.clearAllContents(_firstWS.UsedRange); //清空该文档中的内容。 Worksheet _secondWS = myExcel.getSecondWorksheetAfterOpen(); uEHelper = new Usual_Excel_Helper(_secondWS); uEHelper.clearAllContents(_secondWS.UsedRange); Worksheet _thirdWS = myExcel.getThirdWorksheetAfterOpen(); uEHelper = new Usual_Excel_Helper(_thirdWS); uEHelper.clearAllContents(_thirdWS.UsedRange); int machine_no = int.Parse(prefix_Of_Staffs.Substring(0, 1)); //获取一个月内,某考勤机的考勤天数 int nums_of_ar_days = GetARSummary.GetARSummary.get_nums_of_ar_days(machine_no, YearAndMonthStr); //考勤机的考勤天数 int nums_of_staffs = GetARSummary.GetARSummary.get_nums_of_staffs(machine_no, YearAndMonthStr); System.Data.DataTable dt_Staff_Info = GetARSummary.GetARSummary.get_Staff_info(YearAndMonthStr, machine_no); System.Data.DataTable dt_AR_Of_Each_Staff = GetARSummary.GetARSummary.get_AR_Of_Each_Staff(YearAndMonthStr, machine_no); System.Data.DataTable dt_AR_Summary = GetARSummary.GetARSummary.Get_AR_Summary(YearAndMonthStr, machine_no); //隐藏 结果 label; lblResult.Visible = false; lblPrompt.Visible = true; lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 员工信息汇总中..."; ExcelHelper.saveDtToExcelWithProgressBar(dt_Staff_Info, _firstWS, pb); lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 考勤记录汇总中..."; ExcelHelper.saveDtToExcelWithProgressBar(dt_AR_Of_Each_Staff, _secondWS, pb); lblPrompt.Text = "考勤机" + prefix_Of_Staffs.Substring(0, 1) + ": 汇总中..."; ExcelHelper.saveDtToExcelWithProgressBar(dt_AR_Summary, _thirdWS, pb); Microsoft.Office.Interop.Excel.Range range_Src_AR; //目标 区域 Microsoft.Office.Interop.Excel.Range range_desc_AR; //AR_Time 在D列存放 Usual_Excel_Helper uEHelper_firstWS = new Usual_Excel_Helper(_firstWS); for (int i = 1; i <= nums_of_staffs; i++) { range_Src_AR = ((Microsoft.Office.Interop.Excel.Range)_secondWS.Range[_secondWS.Cells[(i - 1) * (nums_of_ar_days) + 2, 4], _secondWS.Cells[i * nums_of_ar_days + 1]]); range_Src_AR.Copy(); //第一张sheet,第4列 range_desc_AR = ((Microsoft.Office.Interop.Excel.Range)_firstWS.Cells[i + 1, 4]); uEHelper_firstWS.pasteByTranspose(range_desc_AR); } //关闭excel myExcel.save(); myExcel.close(); } lblResult.Visible = true; lblPrompt.Visible = false; }
private void toAWholePiece() { killHwndOfXls(); Queue <int> prefix_Of_Staffs_Queue = V_AR_RESULT.get_Prefix_Staffs(YearAndMonthStr); if (prefix_Of_Staffs_Queue.Count == 0) { ShowResult.show(lblResult, "尚未导入本月的考勤记录!", false); timerRestoreTheLblResult.Enabled = true; return; } MSG msg = new MSG(); ApplicationClass app = new ApplicationClass(); //追加Hwnd到队列中. int appHwnd = app.Hwnd; app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = (Worksheet)wBook.Worksheets[1]; string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\考勤汇总"; int seq = prefix_Of_Staffs_Queue.Dequeue(); int lastSeq = 0; while (prefix_Of_Staffs_Queue.Count >= 1) { lastSeq = prefix_Of_Staffs_Queue.Dequeue(); } string _fileName = YearAndMonthStr + "_考勤汇总" + seq.ToString() + "-" + lastSeq.ToString() + ".xls"; if (!xlsFilePath.Contains(":")) { //导出到Excel中 xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("考勤汇总:", "*.xls|*.xls", _defaultDir, _fileName); if (!xlsFilePath.Contains(@"\")) { return; } } //依据前缀和月份获取列表。 //获取该月该考勤机的出勤人数。 int AR_Num = V_AttendanceRecord.getARNumByYearAndMonth(YearAndMonthStr); if (AR_Num == 0) { MessageBox.Show("数据源为空,无法导出。", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } int rowMaxCount = AR_Num * 2 + 6; int colMaxCount = AttendanceR.get_AR_Days_Num(YearAndMonthStr); //写标题 try { //每行格式设置,注意标题占一行。 Range range = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[rowMaxCount + 1, colMaxCount + 1]); //设置单元格为文本。 range.NumberFormatLocal = "@"; //水平对齐方式 range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //第一行写考勤分析结果。 wSheet.Cells[1, 1] = YearAndMonthStr + " 考勤分析结果" + seq.ToString() + "-" + lastSeq.ToString(); //获取该日期详细的考勤记录。 #region V_AttendanceRecord.AR_Properties aR_Properties = V_AttendanceRecord.getARProperties(YearAndMonthStr); //第三行:考勤时间 wSheet.Cells[3, 1] = "考勤时间"; wSheet.Cells[3, 3] = String.Format(@"{0} ~ {1}", aR_Properties.Start_Date, aR_Properties.End_Date); wSheet.Cells[3, 10] = "制表时间"; wSheet.Cells[3, 12] = aR_Properties.Tabulation_date; #endregion List <int> dayList = V_AR_DETAIL.getDayListOfTheSpecificMonth(YearAndMonthStr); //需要统计有多少人。 int total_num_of_AttendanceR = AttendanceR.get_Total_Num_Of_Staffs_By_YAndM(YearAndMonthStr); pb.Value = 0; pb.Maximum = dayList.Count + dayList.Count * total_num_of_AttendanceR; pb.Visible = true; lblPrompt.Text = _fileName + ":"; lblPrompt.Visible = true; //写 此月与考勤相关的日。 for (int i = 0; i <= dayList.Count - 1; i++) { //写该月的具体有哪些日:1,2,3.与考勤相关。 wSheet.Cells[4, i + 1] = dayList[i].ToString(); pb.Value++; } //实际出勤天数. wSheet.Cells[4, dayList.Count + 1] = "实际出勤天数"; //事假 wSheet.Cells[4, dayList.Count + 2] = "事假"; //未打卡 wSheet.Cells[4, dayList.Count + 3] = "未打卡"; //延点 wSheet.Cells[4, dayList.Count + 4] = "延点(小时)"; //迟到 wSheet.Cells[4, dayList.Count + 5] = "迟到"; //早退 wSheet.Cells[4, dayList.Count + 6] = "早退"; //餐补 wSheet.Cells[4, dayList.Count + 7] = "餐补"; string AR_YEAR_AND_Month_Str = String.Empty; string AR_Day = string.Empty; List <V_AR_DETAIL> v_AR_Detail_Specific_Day_List = null; for (int j = 1; j <= dayList.Count; j++) { AR_YEAR_AND_Month_Str = aR_Properties.Start_Date.Substring(0, 8); AR_Day = AR_YEAR_AND_Month_Str + dayList[j - 1].ToString().PadLeft(2, '0'); v_AR_Detail_Specific_Day_List = V_AR_DETAIL.get_V_AR_Detail_By_Specific_Day(AR_Day); //按日取。 for (int i = 0; i <= v_AR_Detail_Specific_Day_List.Count - 1; i++) { V_AR_DETAIL v_AR_Detail = v_AR_Detail_Specific_Day_List[i]; if (j == 1) { //第五行写工号。 wSheet.Cells[5 + i * 2, 1] = "工号"; //获取原始的工号,没有前缀。 wSheet.Cells[5 + i * 2, 3] = "'" + v_AR_Detail.Job_number; //9 wSheet.Cells[5 + i * 2, 9] = "姓名"; //11 wSheet.Cells[5 + i * 2, 11] = v_AR_Detail.Name; //19 wSheet.Cells[5 + i * 2, 19] = "部门"; //21 wSheet.Cells[5 + i * 2, 21] = v_AR_Detail.Dept; V_Summary_OF_AR v_summary_of_ar = new V_Summary_OF_AR(v_AR_Detail.Job_number, YearAndMonthStr); System.Data.DataTable dtARSummary = v_summary_of_ar.getSummaryOFAR(); //实际出勤天数. wSheet.Cells[6 + i * 2, dayList.Count + 1] = dtARSummary.Rows[0]["AR_DAYS"].ToString(); //事假 string vacatioin_total_time = dtARSummary.Rows[0]["VACATION_TOTAL_TIME"].ToString(); wSheet.Cells[6 + i * 2, dayList.Count + 2] = "0".Equals(vacatioin_total_time) ? "" : vacatioin_total_time; string not_Finger_Print_num = dtARSummary.Rows[0]["NOT_FINGERPRINT_TIMES"].ToString(); //未打卡 wSheet.Cells[6 + i * 2, dayList.Count + 3] = "0".Equals(not_Finger_Print_num) ? "" : not_Finger_Print_num; string delayTime = dtARSummary.Rows[0]["DELAY_TOTAL_TIME"].ToString(); //延点 wSheet.Cells[6 + i * 2, dayList.Count + 4] = "0.0".Equals(delayTime) ? "" : delayTime; string come_late_Num = dtARSummary.Rows[0]["COME_LATE_NUM"].ToString(); //迟到 wSheet.Cells[6 + i * 2, dayList.Count + 5] = "0".Equals(come_late_Num) ? "" : come_late_Num; string leave_early_num = dtARSummary.Rows[0]["LEAVE_EARLY_NUM"].ToString(); //早退 wSheet.Cells[6 + i * 2, dayList.Count + 6] = "0".Equals(leave_early_num) ? "" : leave_early_num; //餐补 wSheet.Cells[6 + i * 2, dayList.Count + 7] = dtARSummary.Rows[0]["DINNER_SUBSIDY_NUM"].ToString(); } System.Data.DataTable dt = V_AR_Time_Helper.getARTime(v_AR_Detail.Job_number, AR_Day); string tempStr = String.Empty; int length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; for (int k = 0; k <= dt.Rows.Count - 1; k++) { //先设置颜色. if ("0".Equals(dt.Rows[k]["FLAG"].ToString())) { if ("1".Equals(dt.Rows[k]["COME_LATE_NUM"].ToString())) //迟到 { //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //迟到 tempStr = dt.Rows[k]["TIME"].ToString() + (k < dt.Rows.Count - 1 ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; ((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = -16776961; continue; } if ("1".Equals(dt.Rows[k]["LEAVE_EARLY_NUM"].ToString())) { //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //早退 tempStr = dt.Rows[k]["TIME"].ToString() + (k < dt.Rows.Count - 1 ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; //写完即改变前景色。 ((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = -16776961; continue; } //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //正常 //正常上班点. tempStr = dt.Rows[k]["TIME"].ToString() + (k < dt.Rows.Count - 1 ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; ((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = System.Drawing.Color.FromArgb(0, 0, 0).ToArgb(); continue; } //先计算单元格已有字符长度。 length = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1.ToString().Length; //请假点。 tempStr = "<" + dt.Rows[k]["TIME"].ToString() + ">" + (k < (dt.Rows.Count - 1) ? "\r\n" : ""); ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 = ((Range)wSheet.Cells[6 + i * 2, j]).FormulaR1C1 + tempStr; //((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Bold = true; //((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.ThemeColor = XlThemeColor.xlThemeColorDark1; //((Range)wSheet.Cells[6 + i * 2, j]).Characters[length + 1, 5].Font.Color = -16776961; } pb.Value++; } } rowMaxCount = wSheet.UsedRange.Rows.Count; //休息日,背景色变为浅绿色。 for (int j = 1; j <= dayList.Count; j++) { bool ifRestDay = false; AR_YEAR_AND_Month_Str = aR_Properties.Start_Date.Substring(0, 8); AR_Day = AR_YEAR_AND_Month_Str + dayList[j - 1].ToString().PadLeft(2, '0'); ifRestDay = Have_A_Rest_Helper.ifDayOfRest(AR_Day); if (ifRestDay) { //此列背景色改为: /* * ange("AF102").Select * With Selection.Interior * .Pattern = xlSolid * .PatternColorIndex = xlAutomatic * .ThemeColor = xlThemeColorAccent3 * .TintAndShade = 0.599993896298105 * .PatternTintAndShade = 0 * End With * End Sub */ Range rangeRestDay = wSheet.get_Range(wSheet.Cells[4, j], wSheet.Cells[rowMaxCount, j]); rangeRestDay.Interior.Pattern = XlPattern.xlPatternSolid; rangeRestDay.Interior.PatternColorIndex = XlPattern.xlPatternAutomatic; rangeRestDay.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent3; rangeRestDay.Interior.TintAndShade = 0.599993896298105; rangeRestDay.Interior.PatternTintAndShade = 0; } } //合并第一行 Range rangeTitle = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, dayList.Count + 7]); rangeTitle.Merge(); rangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; rangeTitle.VerticalAlignment = XlVAlign.xlVAlignCenter; pb.Visible = false; lblPrompt.Visible = false; //自动调整列宽 //range.EntireColumn.AutoFit(); //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存excel文档并关闭 wBook.SaveAs(xlsFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wBook.Close(true, xlsFilePath, Type.Missing); //退出Excel程序 app.Quit(); //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(wSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //调用GC的垃圾收集方法 GC.Collect(); GC.WaitForPendingFinalizers(); ShowResult.show(lblResult, "存于: " + xlsFilePath, true); timerRestoreTheLblResult.Enabled = true; //生成工作安排表。 } catch (Exception ex) { MessageBox.Show(ex.Message, "提示消息:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } }
private void btnImportRestDay_Click(object sender, EventArgs e) { randomStr = RandomStr.GetRandomString(33, true, true, true, false, ""); xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录:", "*.xls|*.xls", defaultDir); tbRestDayPath.Text = xlsFilePath; //*********************************************************** List <TheDaysOfOvertime> restDayList = new List <TheDaysOfOvertime>(); ApplicationClass app = new ApplicationClass(); app.Visible = false; Workbook wBook = null; //用于确定本月最后一天. //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; try { wBook = app.Workbooks.Open(xlsFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show(ex.ToString()); if (wBook != null) { wBook.Close(true, xlsFilePath, Type.Missing); } //退出excel app.Quit(); //释放资源 if (wBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); } System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //调用GC的垃圾回收方法 GC.Collect(); GC.WaitForPendingFinalizers(); return; } Worksheet ws = (Worksheet)(wBook.Worksheets.Item[1]); //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; //判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表. //检查日期列的值是否符合规范。 string temp = string.Empty; string[] tempArray = { }; for (int rowIndex = 2; rowIndex <= rowsMaxCount; rowIndex++) { temp = ((Range)ws.Cells[rowIndex, 2]).Text.ToString().Trim(); DateTime dt; if (!DateTime.TryParse(temp, out dt)) { ShowResult.show(lblResult, temp + ": 非时间格式!", false); timerRestoreTheLblResult.Enabled = true; return; } //先判断是否含有"/"或者"-" if (!temp.Contains("/") && !temp.Contains("-")) { ShowResult.show(lblResult, "此内容非时间格式: yyyy/MM/dd 或 yyyy-MM-dd!", false); timerRestoreTheLblResult.Enabled = true; return; } tempArray = temp.Split(new char[] { '/', '-' }); string year = tempArray[0]; if (!CheckString.checkYear(year)) { ShowResult.show(lblResult, "前4位非年份!", false); timerRestoreTheLblResult.Enabled = true; return; } string month = tempArray[1]; if (!CheckString.checkMonth(month)) { ShowResult.show(lblResult, "第6,7位非月份!", false); timerRestoreTheLblResult.Enabled = true; return; } string day = tempArray[2]; if (!CheckString.checkDay(day)) { ShowResult.show(lblResult, "第9,10位非天数!", false); timerRestoreTheLblResult.Enabled = true; return; } } string dateStr = string.Empty; string name = string.Empty; //精诚所至,金石为开。 for (int rowIndex = 2; rowIndex <= rowsMaxCount; rowIndex++) { dateStr = ((Range)ws.Cells[rowIndex, 2]).Text.ToString().Trim(); name = ((Range)ws.Cells[rowIndex, 1]).Text.ToString().Trim(); TheDaysOfOvertime restDay = new TheDaysOfOvertime(name, dateStr); restDay.addRestDay(); } //释放对象 int hwndOfApp = app.Hwnd; Tools.CmdHelper.killProcessByHwnd(hwndOfApp); //**************************************************************** tempArray = dateStr.Split(new char[] { '/', '-' }); this.dgv.DataSource = TheDaysOfOvertime.getRestDays(tempArray[0] + "-" + tempArray[1]); DGVHelper.AutoSizeForDGV(dgv); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(cbPN.Text.Trim())) { MessageBox.Show("您未选中成衣名称!", "提示:", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //加载图片 cbPN_SelectedIndexChanged(sender, e); if (CmdHelper.ifExistsTheProcessByName("EXCEL") || CmdHelper.ifExistsTheProcessByName("et")) { Tools.FrmPrompt frmCloseExcel = new Tools.FrmPrompt("excel.exe|et.exe"); frmCloseExcel.ShowDialog(); } //导出到Excel中 string _defaultDir = System.Windows.Forms.Application.StartupPath + "\\成本汇总"; string _fileName = cbPN.Text.Trim() + "_成本汇总.xls"; xlsFilePath = FileNameDialog.getSaveFileNameWithDefaultDir("成衣汇总:", "*.xls|*.xls", _defaultDir, _fileName); if (!xlsFilePath.Contains(@"\")) { return; } //将图片存于粘贴板中。 Clipboard.SetImage(this.pictureBox.Image); System.Data.DataTable dt = (System.Data.DataTable)dgv.DataSource; string PN = dt.Rows[0]["成衣名称"].ToString(); ExcelHelper.saveDtToExcelWithProgressBar((System.Data.DataTable)dgv.DataSource, xlsFilePath, pb); MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.open(); Worksheet wS = myExcel.getFirstWorkSheetAfterOpen(); //获取第二个Sheet. myExcel.App.Visible = false; Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS); int maxRowCount = wS.UsedRange.Rows.Count; uEHelper.insertAboveTheSpecificRow(1); uEHelper.setSpecificCellValue("A1", PN); Range range = (wS.Range)[wS.Cells[2, 2], wS.Cells[maxRowCount, 2]]; uEHelper.delTheSpecificContentShiftToLeft(range); myExcel.App.DisplayAlerts = false; uEHelper.merge("A1", "G1"); uEHelper.MergeTheSpecificColumnWithoutBlankContent(2); //保存图片到I1. Range rangeI1 = uEHelper.getRange("I1", "I1"); /* * ActiveSheet.Range("A1").PasteSpecial( * Excel.Enums.XlPasteType.xlPasteAll, * Excel.Enums.XlPasteSpecialOperation.xlPasteSpecialOperationNone, * false, false); */ rangeI1.Select(); wS.PasteSpecial(); //sheet2中写入文件. IShowProductsCostSummary iShowProductsCostSummary = new ShowProductsCostSummaryImpl(); dt = iShowProductsCostSummary.get_P_C_Each_Port(cbPN.Text.Trim()); //序号, 成衣名称, 概要工序, 指定工序, 指定每部位工序, 工时, 件数, 单价, 最终单价. /* * seq_p_c_record, * product_name, * summary_process, * specific_process, * specific_each_process, * man_hours, * amount, * each_cost, * final_labour_cost */ //第二个表格. myExcel.AddSheetToLastIndex("每部件成本汇总"); Worksheet secondWS = myExcel.getSecondWorksheetAfterOpen(); //先写标题。 secondWS.Cells[1, 1] = "序号"; secondWS.Cells[1, 2] = "成衣名称"; secondWS.Cells[1, 3] = "部位"; secondWS.Cells[1, 4] = "工序"; secondWS.Cells[1, 5] = "单部件"; secondWS.Cells[1, 6] = "单部件_件数"; //secondWS.Cells[1, 6] = "工时"; secondWS.Cells[1, 7] = "单部件_工价"; //secondWS.Cells[1, 7] = "件数"; secondWS.Cells[1, 8] = "整部位_工时"; //secondWS.Cells[1, 8] = "单价"; secondWS.Cells[1, 9] = "整部位_工价"; secondWS.Cells[1, 10] = "最终_工价"; for (int i = 0; i <= dt.Rows.Count - 1; i++) { secondWS.Cells[2 + i, 1] = dt.Rows[i]["seq_p_c_record"].ToString(); secondWS.Cells[2 + i, 2] = dt.Rows[i]["product_name"].ToString(); secondWS.Cells[2 + i, 3] = dt.Rows[i]["summary_process"].ToString(); secondWS.Cells[2 + i, 4] = dt.Rows[i]["specific_process"].ToString(); secondWS.Cells[2 + i, 5] = dt.Rows[i]["specific_each_process"].ToString(); secondWS.Cells[2 + i, 6] = dt.Rows[i]["amount"].ToString(); secondWS.Cells[2 + i, 7] = dt.Rows[i]["each_cost"].ToString(); secondWS.Cells[2 + i, 8] = dt.Rows[i]["man_hours"].ToString(); secondWS.Cells[2 + i, 9] = dt.Rows[i]["labour_cost"].ToString(); secondWS.Cells[2 + i, 10] = dt.Rows[i]["final_labour_cost"].ToString(); } //获取最终单价的区域. Range _range_final_labour_cost; uEHelper = new Usual_Excel_Helper(secondWS); _range_final_labour_cost = uEHelper.getRange("J2", "J" + secondWS.UsedRange.Rows.Count); uEHelper.setFormulaR1C1ForRange(_range_final_labour_cost, "=IF(OR(ISBLANK(RC[-4]),ISBLANK(RC[-3])),RC[-1],RC[-4]*RC[-3])"); secondWS.UsedRange.EntireColumn.AutoFit(); myExcel.save(); myExcel.close(); //((FrmMainOfProductsCost)this.ParentForm).notifyIcon.ShowBalloonTip(7000, "提示:","汇总保存于: " + xlsFilePath, ToolTipIcon.Info); ShowResult.show(lblResult, "汇总保存于: " + xlsFilePath, true); timerRestoreLblResult.Start(); }
/// <summary> /// 导入工作安排表。 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnImportWorkSchedule_Click(object sender, EventArgs e) { string xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择要导入的工作安排表", "*.xls|*.xls", string.Format(@"{0}\{1}", Environment.CurrentDirectory, "工作安排表")); if (!xlsFilePath.Contains(@"\")) { return; } MSG msg = new MSG(); //导入数据的行数. int affectedCount = 0; ApplicationClass app = new ApplicationClass(); app.Visible = false; Workbook wBook = null; //用于确定本月最后一天. string day = String.Empty; //行最大值. int rowsMaxCount = 0; int colsMaxCount = 0; try { wBook = app.Workbooks.Open(xlsFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show(ex.ToString()); if (wBook != null) { wBook.Close(true, xlsFilePath, Type.Missing); } //退出excel app.Quit(); //释放资源 if (wBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); } System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //调用GC的垃圾回收方法 GC.Collect(); GC.WaitForPendingFinalizers(); msg.Flag = false; msg.Msg = ex.ToString(); return; } Worksheet ws = (Worksheet)(wBook.Worksheets.Item[1]); if (!((Range)ws.Cells[1, 1]).Text.ToString().Contains("工作安排")) { msg.Msg = "此表非工作安排表"; msg.Flag = false; ShowResult.show(lblResult, msg.Msg, msg.Flag); return; } //行;列最大值 赋值. rowsMaxCount = ws.UsedRange.Rows.Count; colsMaxCount = ws.UsedRange.Columns.Count; string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim(); string yearAndMonthStr = C3Str.Split('~')[0].Trim().Substring(0, 8); List <V_I_W_S> v_I_W_S_List = new List <V_I_W_S>(); //从第二列开始统计 for (int colIndex = 2; colIndex <= colsMaxCount; colIndex++) { day = ((Range)ws.Cells[4, colIndex]).Text.ToString().Trim(); if (String.IsNullOrEmpty(day)) { continue; } //从第5行开始. for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++) { V_I_W_S v_I_W_S = new V_I_W_S(); string dept = ((Range)ws.Cells[rowIndex, 1]).Text.ToString().Trim(); if (String.IsNullOrEmpty(dept)) { continue; } v_I_W_S.Dept = dept; v_I_W_S.Date = yearAndMonthStr + day.PadLeft(2, '0'); v_I_W_S.BgColor = ((Range)(ws.Cells[rowIndex, colIndex])).Interior.Color; v_I_W_S_List.Add(v_I_W_S); } } for (int j = 0; j <= v_I_W_S_List.Count - 1; j++) { affectedCount += v_I_W_S_List[j].updateWorkSchedule(); } //释放对象 int hwndOfApp = app.Hwnd; Tools.CmdHelper.killProcessByHwnd(hwndOfApp); msg.Flag = true; msg.Msg = String.Format(@"导入完成;计{0}条.", affectedCount.ToString()); ShowResult.show(lblResult, msg.Msg, msg.Flag); }
/// <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_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> /// <param name="sender"></param> /// <param name="e"></param> private void btnImportEmpsARInfo_Click(object sender, EventArgs e) { //判断是否存在Excel进程. if (CmdHelper.ifExistsTheProcessByName("EXCEL.EXE")) { ShowResult.show(lblResult, "存在未关闭的Office Excel进程,请先关闭!", false); return; } string _uncertainWSPath = _defaultDir + "\\uncertainRecord_" + TimeHelper.getCurrentTimeStr() + ".xls"; this.dgv.Visible = false; this.dgv_same_pinyin_of_name.Visible = true; this.dgv_same_name.Visible = false; dgv_same_name.DataSource = null; dgv_same_pinyin_of_name.DataSource = null; dgv.DataSource = null; lblResult.Text = ""; lblResult.BackColor = this.BackColor; tb.Clear(); randomStr = TimeHelper.getCurrentTimeStr(); xlsFilePath = FileNameDialog.getSelectedFilePathWithDefaultDir("请选择考勤记录:", "*.xls|*.xls", defaultDir); string dir = DirectoryHelper.getDirOfFile(xlsFilePath); if (string.IsNullOrEmpty(dir)) { return; } List <string> xlsFileList = DirectoryHelper.getXlsFileUnderThePrescribedDir(dir); List <string> resultList = new List <string>(); foreach (string xlsFile in xlsFileList) { string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFile); if (!CheckString.CheckARName(fileName)) { continue; } //格式符合: 3月考勤记录1。 resultList.Add(xlsFile); } #region 先判断第四行,是否全为数字。 int maxColIndex = 0; if (!check4thRow(resultList, out maxColIndex)) { return; } #endregion if (cbCheckSameNamesButDifferentMachineNo.Checked) { #region 保存关键信息到后台. saveCriticalARInfo(resultList); #endregion #region 打开4个考勤文件 for (int i = 1; i <= resultList.Count; i++) { switch (i) { case 1: _1th_my_excel = new MyExcel(resultList[0]); _1th_my_excel.open(); _1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen(); break; case 2: _2nd_my_excel = new MyExcel(resultList[1]); _2nd_my_excel.open(); _2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen(); break; case 3: _3rd_my_excel = new MyExcel(resultList[2]); _3rd_my_excel.open(); _3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen(); break; case 4: _4th_my_excel = new MyExcel(resultList[3]); _4th_my_excel.open(); _4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen(); break; } } #endregion #region 创建 _uncertain_myExcel; MyExcel uncertainRecordExcel = null; uncertainRecordExcel = new MyExcel(_uncertainWSPath); uncertainRecordExcel.create(); uncertainRecordExcel.open(); uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen(); //先写,日期行。 Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS); uEHelper.writeToSpecificRow(1, 1, maxColIndex); #endregion System.Data.DataTable dt = getSamePinYinButName(); bool have_same_pinyin_flag = false; if (dt != null && dt.Rows.Count > 0) { have_same_pinyin_flag = true; } //*************判断是否拼音相同 开始********************8 if (have_same_pinyin_flag) { this.dgv_same_pinyin_of_name.Visible = true; this.dgv_same_pinyin_of_name.DataSource = dt; DGVHelper.AutoSizeForDGV(dgv_same_pinyin_of_name); ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false); this.lblPrompt.Visible = false; timerRestoreTheLblResult.Enabled = true; btnSwitch.Text = "查看拼音相同的记录"; btnSwitch_Click(sender, e); #region 写记录到不确定文档中. int theRowIndex = 0; int Attendance_Machine_No = 0; for (int i = 0; i <= dt.Rows.Count - 1; i++) { theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString()); Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString()); switch (Attendance_Machine_No) { case 1: //获取源区域 srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); //保存一下。 break; case 2: srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 3: srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 4: srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; } } //显示该文档 CmdHelper.runCmd(_uncertainWSPath); #endregion string promptStr = string.Format(@"存在姓名拼音相同的记录。{0} 继续(OK), 取消导入(Cancel)。", "\r\n"); if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示:", MessageBoxButtons.OKCancel, MessageBoxIcon.Information))) { uncertainWS.UsedRange.ColumnWidth = 3.75M; uncertainRecordExcel.saveWithoutAutoFit(); uncertainRecordExcel.close(); closeThe4ARExcels(); return; } } //closeThe4ARExcels(); //*************判断是否拼音相同 结束*****************88 //1.h dt = getSameNameInfo(); string prompt = string.Empty; if (dt.Rows.Count != 0) { btnSwitch.Text = "查看同名记录"; btnSwitch_Click(sender, e); int theRowIndex = 0; int Attendance_Machine_No = 0; #region 记录书写结束. for (int i = 0; i <= dt.Rows.Count - 1; i++) { theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString()); Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString()); switch (Attendance_Machine_No) { case 1: //获取源区域 srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); //保存一下。 break; case 2: srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 3: srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 4: srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndex]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndex]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; } } #endregion prompt = string.Format(@"存在同名记录,将合并;{0} 继续(OK), 取消导入(Cancel)。", "\r\n"); if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示:", MessageBoxButtons.OKCancel, MessageBoxIcon.Information))) { uEHelper.setAllColumnsWidth(3.75M); uncertainRecordExcel.saveWithoutAutoFit(); uncertainRecordExcel.close(); closeThe4ARExcels(); return; } } //关闭不确定文档。 uEHelper.setAllColumnsWidth(3.75M); uncertainRecordExcel.saveWithoutAutoFit(); uncertainRecordExcel.close(); closeThe4ARExcels(); } resultList.Sort(); //判断该考勤机中是否已经存在,某月的记录 foreach (string xlsFilePath in resultList) { string fileName = DirectoryHelper.getFileNameWithoutSuffix(xlsFilePath); int attendanceMachineFlag = int.Parse(fileName.Substring(fileName.Length - 1, 1)); //打开文档获取考勤机,所记录的日期。 MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.open(); Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen(); Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(firstSheet); string year_and_month_str = uEHelper.getCellContentByRowAndColIndex(3, 3); year_and_month_str = year_and_month_str.Substring(0, 7); myExcel.close(); System.Data.DataTable dtSubmitInfo = getSubmitInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str); if (!"0".Equals(dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString())) { string prompt = string.Format(@"您已经于: {1},{0}提交了{2} 考勤机{3},{0}共计{4}个用户的纪录{0} 覆盖(OK),退出(Cancel)?", "\r\n", dtSubmitInfo.Rows[0]["latest_record_time"].ToString(), year_and_month_str, attendanceMachineFlag, dtSubmitInfo.Rows[0]["nums_of_staffs"].ToString()); if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question))) { return; } delTheInfoOfTheSpecificeMachineAndYearAndMonth(attendanceMachineFlag, year_and_month_str); } } this.dgv.DataSource = null; //this.dgv.Columns.Clear(); lblPrompt.Visible = false; lblPrompt.Text = ""; pb.Value = 0; pb.Maximum = 0; pb.Visible = false; foreach (string xlsFilePath in resultList) { tb.Text = xlsFilePath; lblResult.Visible = false; MSG msg = AttendanceRHelper.ImportAttendanceRecordToDB(xlsFilePath, randomStr, lblPrompt, pb, lblResult); //导入完成后进行保存,保存该文件至prepared目录中 pb.Visible = false; lblPrompt.Visible = false; ShowResult.show(lblResult, msg.Msg, msg.Flag); timerRestoreTheLblResult.Enabled = true; if (!msg.Flag) { return; } //saveTheExcel(xlsFilePath); } dgv_same_name.Visible = false; dgv_same_pinyin_of_name.Visible = false; //加载导入的数据。 this.dgv.DataSource = null; this.dgv.DataSource = aR.getAR(randomStr); this.dgv.Visible = true; DGVHelper.AutoSizeForDGV(dgv); tb.Clear(); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnExport_Click(object sender, EventArgs e) { OracleHelper oH = OracleHelper.getBaseDao(); if (string.IsNullOrEmpty(cbPN.Text.Trim())) { return; } if (CmdHelper.ifExistsTheProcessByName("EXCEL")) { return; } //获取该成品对应的工序。 System.Data.DataTable dt = null; OracleParameter paramPN = new OracleParameter("v_PN", OracleDbType.NVarchar2, 20); OracleParameter paramCur = new OracleParameter("retureCur", OracleDbType.RefCursor); paramPN.Direction = ParameterDirection.Input; paramCur.Direction = ParameterDirection.ReturnValue; paramPN.Value = cbPN.Text.Trim(); OracleParameter[] parameters = new OracleParameter[2] { paramCur, paramPN }; string procName = "Show_Products_Cost.get_P_C_Info_To_Piecework"; dt = oH.getDT(procName, parameters); _fileName = cbPN.Text.Trim() + ".xls"; DirectoryHelper.createDirecotry(_defaultDir); xlsFilePath = _defaultDir + _fileName; //获取一个Excel V_New_Excel v_Excel = new V_New_Excel(xlsFilePath); Worksheet wS = v_Excel.WS; Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(wS); uEHelper.setSpecificCellValue(1, 1, cbPN.Text.Trim() + "报工单", 11, true); //写序号标题 uEHelper.setSpecificCellValue(2, 3, "姓名"); uEHelper.setSpecificCellValue(3, 1, "序号"); uEHelper.setSpecificCellValue(3, 2, "部位"); uEHelper.setSpecificCellValue(3, 3, "工序"); //姓名占据两列 //uEHelper.merge("C2", "D2"); //uEHelper.merge("C3","D3"); //Range range = wS.Range[wS.Cells[2, 3], wS.Cells[2, 4]]; //uEHelper.textAlighRight(range); //工序名称,单据时间靠右 //range = wS.Range[wS.Cells[3, 4], wS.Cells[3, 5]]; //uEHelper.textAlighRight(range); //设置第三列宽度 uEHelper.setColumnWidth("C", 42.25M); uEHelper.setColumnWidth("A", 5.13M); uEHelper.setRowHeight(1, 31); uEHelper.setRowHeight(2, 31); pb.Visible = true; pb.Value = 0; pb.Maximum = dt.Rows.Count; lblResult.Visible = false; lblPrompt.Visible = true; lblPrompt.Text = "请等待!"; //开始写信息 for (int i = 0; i <= dt.Rows.Count - 1; i++) { uEHelper.setSpecificCellValue(i + 4, 1, dt.Rows[i]["row_num"].ToString()); uEHelper.setSpecificCellValue(i + 4, 2, dt.Rows[i]["Summary_Process"].ToString()); uEHelper.setSpecificCellValue(i + 4, 3, dt.Rows[i]["specific_Process"].ToString()); pb.Value++; } pb.Visible = false; pb.Value = 0; lblResult.Visible = true; lblPrompt.Visible = false; lblPrompt.Text = ""; for (int columnIndex = 4; columnIndex <= 30; columnIndex++) { uEHelper.setColumnWidthByColumnIndex(columnIndex, 4.5M); } Range range = wS.Range[wS.Cells[2 + dt.Rows.Count, 30], wS.Cells[2 + dt.Rows.Count, 30]]; uEHelper.setAllTheBoxLine(range); uEHelper.merge("A1", "AD1"); v_Excel.displayAlert(); uEHelper.MergeTheSpecificColumnWithoutBlankContent(2); //设置边框。 uEHelper.setAllTheBoxLine(); v_Excel.saveWithoutAutoFit(); v_Excel.closeTheApp(); ShowResult.show(lblResult, cbPN.Text.Trim() + " 产量统计模板保存于: " + xlsFilePath, true); timerRestoreLblResult.Start(); }
/// <summary> /// 提交请假 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSubmit_Click(object sender, EventArgs e) { if (!checkName()) { MessageBox.Show(cbName.Text.Trim() + ": 没有在近5个月以内的考勤系统人员名单中。请用备用方案请假!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (cbName.Text.Trim() == "") { return; } if (cbTimeSection.Text.Trim() == "") { return; } //startDateTime = new DateTime(_year, _month,_day,_start_hour,_start_minute,_start_second); //endDateTime = new DateTime(_year, _month, _day, _end_hour, _end_minute, _end_second); /*if (startDateTime >= endDateTime) { * ShowResult.show(lblResult, "结束时间需比起始时间大!", false); * timerClsResult.Enabled = true; * return; * } */ //string startTime = startDateTime.ToString("yyyy-MM-dd HH:mm:ss"); //string endTime = endDateTime.ToString("yyyy-MM-dd HH:mm:ss"); startTimeStr = string.Format(@"{0}-{1}-{2} {3}", dtPicker.Value.Year.ToString(), dtPicker.Value.Month.ToString(), dtPicker.Value.Day.ToString(), cbTimeSection.Text.ToString().Split('-')[0].Trim().ToString()); endTimeStr = string.Format(@"{0}-{1}-{2} {3}", dtPicker.Value.Year.ToString(), dtPicker.Value.Month.ToString(), dtPicker.Value.Day.ToString(), cbTimeSection.Text.ToString().Split('-')[1].Trim().ToString()); a_F_L_H = new ASK_For_Leave_Helper(cbName.Text.Trim(), startTimeStr, endTimeStr, ""); //先判断是否有日期范围的假条 if (a_F_L_H.ifExistsAtRange()) { ShowResult.show(lblResult, "已存在该日期范围的假条!", false); timerClsResult.Enabled = true; return; } //判断是否设定了加班日 if (!ifConfigRestDay(year_and_month_str)) { ShowResult.show(lblResult, "请先设定加班日!", false); timerClsResult.Enabled = true; return; } if (ifTheRestDay(year_and_month_day_str)) { ShowResult.show(lblResult, year_and_month_day_str + " :为休息日!", false); timerClsResult.Enabled = true; return; } //a_F_L_H.save(); //tbNO.Text = ASK_For_Leave_Helper.getLastedNO(); string sqlStr = string.Format(@"insert into Ask_For_Leave( job_number, name, leave_date, leave_start_time, leave_end_time ) values( '{0}', '{1}', to_date('{2}','yyyy-MM-dd'), to_date('{3}','yyyy-MM-dd HH24:MI'), to_date('{4}','yyyy-MM-dd HH24:MI') )", cbName.SelectedValue.ToString(), cbName.Text.Trim(), year_and_month_day_str, startTimeStr, endTimeStr );; OracleDaoHelper.executeSQL(sqlStr); this.dgv.DataSource = ASK_For_Leave_Helper.getAllVacationListByNameAndDate(cbName.Text.Trim()); DGVHelper.AutoSizeForDGV(dgv); cbTimeSection.SelectedIndex = -1; }
private void doNextAfterCheckName() { #region 打开4个考勤文件 for (int i = 1; i <= xlsFilePathList.Count; i++) { switch (i) { case 1: _1th_my_excel = new MyExcel(xlsFilePathList[0]); _1th_my_excel.openWithoutAlerts(); _1th_Sheet = _1th_my_excel.getFirstWorkSheetAfterOpen(); break; case 2: _2nd_my_excel = new MyExcel(xlsFilePathList[1]); _2nd_my_excel.openWithoutAlerts(); _2nd_Sheet = _2nd_my_excel.getFirstWorkSheetAfterOpen(); break; case 3: _3rd_my_excel = new MyExcel(xlsFilePathList[2]); _3rd_my_excel.openWithoutAlerts(); _3rd_Sheet = _3rd_my_excel.getFirstWorkSheetAfterOpen(); break; case 4: _4th_my_excel = new MyExcel(xlsFilePathList[3]); _4th_my_excel.openWithoutAlerts(); _4th_Sheet = _4th_my_excel.getFirstWorkSheetAfterOpen(); break; } } #endregion #region 创建 _uncertain_myExcel; MyExcel uncertainRecordExcel = null; uncertainRecordExcel = new MyExcel(_uncertainWSPath); uncertainRecordExcel.create(); uncertainRecordExcel.openWithoutAlerts(); uncertainWS = uncertainRecordExcel.getFirstWorkSheetAfterOpen(); //先写,日期行。 Usual_Excel_Helper uEHelper = new Usual_Excel_Helper(uncertainWS); uEHelper.writeToSpecificRow(1, 1, maxColIndexOfCheckedNameOfExcel); #endregion System.Data.DataTable dt = getSamePinYinButName(); int amountOfGroupOfSamePinYinButName = getAmountOfGroupOfSamePinYinButName(); bool have_same_pinyin_flag = false; if (dt != null && dt.Rows.Count > 0) { have_same_pinyin_flag = true; } //*************判断是否拼音相同 开始********************8 if (have_same_pinyin_flag) { ShowResult.show(lblResult, "存在姓名拼音相同的记录!", false); this.lblPrompt.Visible = false; timerRestoreTheLblResult.Enabled = true; #region 写记录到不确定文档中. int theRowIndex = 0; int Attendance_Machine_No = 0; for (int i = 0; i <= dt.Rows.Count - 1; i++) { theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString()); Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString()); switch (Attendance_Machine_No) { case 1: //获取源区域 //替换源文件的工号为 工号位于第三列 _1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); //保存一下。 break; case 2: _2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 3: _3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 4: _4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; } } //设置列宽 uncertainWS.UsedRange.ColumnWidth = 3.75; //显示该文档对应的图片 #endregion string promptStr = string.Format(@" 存在姓名拼音相同但书写不同的记录:{1}组;{0} 确定: 将视为不同员工; 取消: 取消本次导入;", "\r\n", amountOfGroupOfSamePinYinButName); if (DialogResult.Cancel.Equals(MessageBox.Show(promptStr, "提示:", MessageBoxButtons.OKCancel, MessageBoxIcon.Information))) { closeThe4ARExcels(); uncertainWS.UsedRange.ColumnWidth = 3.75M; uncertainRecordExcel.saveWithoutAutoFit(); uncertainRecordExcel.close(); //显示该文档。 uncertainRecordExcel = new MyExcel(_uncertainWSPath); uncertainRecordExcel.open(true); btnViewTheUncertaiRecordInExcel.Enabled = true; return; } if (!btnViewTheUncertaiRecordInExcel.Enabled) { btnViewTheUncertaiRecordInExcel.Enabled = true; } } //*************判断是否拼音相同 结束*****************88 //1.h dt = getSameNameInfo(); //获取汉字相同的组的数目。 int amountOfGroupOfSameName = getAmountOfGroupOfSameName(); string prompt = string.Empty; if (dt.Rows.Count != 0) { int theRowIndex = 0; int Attendance_Machine_No = 0; #region 记录书写结束. for (int i = 0; i <= dt.Rows.Count - 1; i++) { theRowIndex = int.Parse(dt.Rows[i]["行号"].ToString()); Attendance_Machine_No = int.Parse(dt.Rows[i]["卡机编号"].ToString()); switch (Attendance_Machine_No) { case 1: _1th_Sheet.Cells[theRowIndex, 3] = "'111111111" + ((Range)(_1th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); //获取源区域 srcRange = _1th_Sheet.Range[_1th_Sheet.Cells[theRowIndex, 1], _1th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); //保存一下。 break; case 2: _2nd_Sheet.Cells[theRowIndex, 3] = "'222222222" + ((Range)(_2nd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _2nd_Sheet.Range[_2nd_Sheet.Cells[theRowIndex, 1], _2nd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 3: _3rd_Sheet.Cells[theRowIndex, 3] = "'333333333" + ((Range)(_3rd_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _3rd_Sheet.Range[_3rd_Sheet.Cells[theRowIndex, 1], _3rd_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; case 4: _4th_Sheet.Cells[theRowIndex, 3] = "'444444444" + ((Range)(_4th_Sheet.Cells[theRowIndex, 3])).Text.ToString().PadLeft(3, '0'); srcRange = _4th_Sheet.Range[_4th_Sheet.Cells[theRowIndex, 1], _4th_Sheet.Cells[theRowIndex + 1, maxColIndexOfCheckedNameOfExcel]]; srcRange.Cells.Copy(Type.Missing); //向目标复制。 //或取目标单元格。 currentRow = uncertainWS.UsedRange.Rows.Count; destRange = uncertainWS.Range[uncertainWS.Cells[currentRow + 1, 1], uncertainWS.Cells[currentRow + 2, maxColIndexOfCheckedNameOfExcel]]; //destRange.Select(); uncertainWS.Paste(destRange, false); break; } } #endregion prompt = string.Format(@" 存在同名的记录:{1}组;{0} 确定: 将视为同一员工; 取消: 取消本次导入;", "\r\n", amountOfGroupOfSameName); if (DialogResult.Cancel.Equals(MessageBox.Show(prompt, "提示:", MessageBoxButtons.OKCancel, MessageBoxIcon.Information))) { uEHelper.setAllColumnsWidth(3.75M); uncertainRecordExcel.saveWithoutAutoFit(); uncertainRecordExcel.close(); //显示该文档。 uncertainRecordExcel = new MyExcel(_uncertainWSPath); uncertainRecordExcel.open(true); closeThe4ARExcels(); if (!btnViewTheUncertaiRecordInExcel.Enabled) { btnViewTheUncertaiRecordInExcel.Enabled = true; } return; } if (!btnViewTheUncertaiRecordInExcel.Enabled) { btnViewTheUncertaiRecordInExcel.Enabled = true; } } //关闭不确定文档。 uEHelper.setAllColumnsWidth(3.75M); uncertainRecordExcel.saveWithoutAutoFit(); uncertainRecordExcel.close(); closeThe4ARExcels(); xlsFilePathList.Sort(); //直接删除 MyExcel myExcel = new MyExcel(xlsFilePath); myExcel.openWithoutAlerts(); Worksheet firstSheet = myExcel.getFirstWorkSheetAfterOpen(); Usual_Excel_Helper uEHelperTemp = new Usual_Excel_Helper(firstSheet); string year_and_month_str = uEHelperTemp.getCellContentByRowAndColIndex(3, 3); year_and_month_str = year_and_month_str.Substring(0, 7); myExcel.close(); delARDetailInfoByYearAndMonth(year_and_month_str); //删除完毕。 this.dgv.DataSource = null; //this.dgv.Columns.Clear(); lblPrompt.Visible = false; lblPrompt.Text = ""; pb.Value = 0; pb.Maximum = 0; pb.Visible = false; this.btnImportEmpsInfo.Enabled = false; readDataFromExcelBGWorker.RunWorkerAsync(); }