/// <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; }