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(); }
/// <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; } }
/// <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(); }
public Tools.MSG genExcel(out string xlsFilePath) { killHwndOfXls(); MSG msg = new MSG(); string excelName = _YearAndMonthStr + "_工作安排表.xls"; string defaultDir = Environment.CurrentDirectory + string.Format(@"\工作安排表", excelName); string xlsFileFullPath = FileNameDialog.getSaveFileNameWithDefaultDir("请选择考勤记录:", "*.xls|*.xls", defaultDir, excelName); if (!xlsFileFullPath.Contains(@"\")) { msg.Flag = false; msg.Msg = "取消了选择!"; xlsFilePath = xlsFileFullPath; return(msg); } ApplicationClass app = new ApplicationClass(); hwndOfXls_Queue.Enqueue(app.Hwnd); app.Visible = false; Workbook wBook = null; int rowsMaxCount = 0; int colsMaxCount = 0; try { wBook = app.Workbooks.Add(true); Worksheet wS = wBook.Worksheets.Item[1] as Worksheet; //每行格式设置,注意标题占一行。 rowsMaxCount = this.getDeptNum() + 4; Queue <int> daysQueue = this.getAllDaysOfThisMonth(); colsMaxCount = daysQueue.Count; //每行格式设置,注意标题占一行。 Range range = wS.get_Range(wS.Cells[1, 1], wS.Cells[rowsMaxCount + 1, colsMaxCount + 1]); //设置单元格为文本。 range.NumberFormatLocal = "@"; //水平对齐方式 range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //第一行写考勤分析结果。 wS.Cells[1, 1] = _YearAndMonthStr + " 工作安排分析结果"; wS.Cells[1, 3] = "休假"; ((Range)(wS.Cells[1, 4])).Interior.Color = System.Drawing.Color.Green.ToArgb(); //获取该日期详细的考勤记录。 wS.Cells[1, 5] = "周日"; ((Range)wS.Cells[1, 6]).Interior.Color = System.Drawing.Color.Yellow.ToArgb(); V_AR_DETAIL v_AR_Detail = V_AR_DETAIL.get_V_AR_Detail(_YearAndMonthStr); //第三行:考勤时间 wS.Cells[3, 1] = "考勤时间"; wS.Cells[3, 3] = String.Format(@"{0} ~ {1}", v_AR_Detail.Start_date, v_AR_Detail.End_date); wS.Cells[3, 10] = "制表时间"; wS.Cells[3, 12] = v_AR_Detail.Tabulation_time; //书写部门 List <string> deptList = this.getDeptList(); for (int i = 0; i <= deptList.Count - 1; i++) { wS.Cells[5 + i, 1] = deptList[i].ToString(); } string v_w_s_Day = string.Empty; //开始书写此月的日子 int colIndex = 1; while (daysQueue.Count > 0) { colIndex++; int day = daysQueue.Dequeue(); wS.Cells[4, colIndex] = day; v_w_s_Day = _YearAndMonthStr + "-" + day.ToString().PadLeft(2, '0'); for (int i = 0; i <= deptList.Count - 1; i++) { //填写该日内所有部门的休假情况. V_W_S v_W_S = new V_W_S(); v_W_S.Dept = deptList[i].ToString(); v_W_S.Work_and_rest_date = v_w_s_Day; v_W_S = v_W_S.get_V_W_S_By_Date_And_Dept(); wS.Cells[5 + i, colIndex] = v_W_S.Work_rate; if ("休息".Equals(v_W_S.Work_or_rest, StringComparison.OrdinalIgnoreCase)) { ((Range)wS.Cells[5 + i, colIndex]).Interior.Color = System.Drawing.Color.Green.ToArgb(); continue; } //注意周日为每周的第一天。 if ("1" == v_W_S.Day_of_week) { //周日用暗灰色 ((Range)wS.Cells[5 + i, colIndex]).Interior.Color = System.Drawing.Color.Yellow.ToArgb(); } } } //自动调整列宽 //range.EntireColumn.AutoFit(); //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存excel文档并关闭 wBook.SaveAs(xlsFileFullPath, 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, xlsFileFullPath, Type.Missing); //退出Excel程序 app.Quit(); //释放资源 System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(wS); System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //调用GC的垃圾收集方法 GC.Collect(); GC.WaitForPendingFinalizers(); msg.Msg = "工作安排: " + xlsFileFullPath; msg.Flag = true; xlsFilePath = xlsFileFullPath; return(msg); } catch (Exception ex) { MessageBox.Show(ex.Message, "提示消息:", MessageBoxButtons.OK, MessageBoxIcon.Information); msg.Msg = ex.ToString(); msg.Flag = false; xlsFilePath = xlsFileFullPath; return(msg); } }