public void SetPrintExcel() { string Defprinter = null; Defprinter = application.ActivePrinter; //application.ActivePrinter = "Microsoft XPS Document Writer"; var _with = worksheet.PageSetup; _with.PaperSize = Excel.XlPaperSize.xlPaperA5; _with.Orientation = Excel.XlPageOrientation.xlPortrait; _with.FitToPagesTall = 1; _with.FitToPagesWide = 1; _with.LeftMargin = application.InchesToPoints(0.52); _with.RightMargin = application.InchesToPoints(0); _with.TopMargin = application.InchesToPoints(0.32); _with.BottomMargin = application.InchesToPoints(0); _with.HeaderMargin = application.InchesToPoints(0); _with.FooterMargin = application.InchesToPoints(0); worksheet.PrintOutEx(1, 1, 2, false, false, false, false); //application.ActivePrinter = Defpr application.ActivePrinter = Defprinter; }
void PrintMyExcelFile(string sFileName) { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); // Open the Workbook: Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open(sFileName, 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); // Get the first worksheet. // (Excel uses base 1 indexing, not base 0.) Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]; var _with1 = ws.PageSetup; // A4 papersize _with1.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; // Landscape orientation _with1.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; // Fit Sheet on One Page _with1.FitToPagesWide = 1; _with1.FitToPagesTall = 1; // Normal Margins _with1.LeftMargin = excelApp.InchesToPoints(0.7); _with1.RightMargin = excelApp.InchesToPoints(0.7); _with1.TopMargin = excelApp.InchesToPoints(0.75); _with1.BottomMargin = excelApp.InchesToPoints(0.75); _with1.HeaderMargin = excelApp.InchesToPoints(0.3); _with1.FooterMargin = excelApp.InchesToPoints(0.3); object misValue = System.Reflection.Missing.Value; // Print out 1 copy to the default printer: ws.PrintOut( Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Cleanup: GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(ws); wb.Close(false, Type.Missing, Type.Missing); Marshal.FinalReleaseComObject(wb); excelApp.Quit(); Marshal.FinalReleaseComObject(excelApp); }
//生成Excel private bool ExportToExcel(DataTable dt) { bool result = false; this.UseWaitCursor = true; if (dt.Rows.Count > 65536) { MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return result; } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { labMsg.Text = "Excel正在匯出中…………"; if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机器未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(true); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "過膠機生產日報表"; //worksheet.QueryTables.Add #region worksheet的格式 worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印 worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印 worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效 worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁 worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中 worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102); worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126); worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126); xlApp.Visible = false; #endregion #region 列名 #region 循環填充列名,很慢,已經REMARK //for (i = 1; i <= dt.Columns.Count - 1; i++) //{ // if (i == 11 || i == 12 || i == 13) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3); // } // else if (i == 14 || i == 15 || i == 16 || i == 17) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8); // } // else if (i >= 23 && i <= 31) // { // MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim(); // } // else // { // MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535); // xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName; // } //} #endregion int RowIndex = 2; MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題 MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0); RowIndex = RowIndex + 1; MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35); MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12); MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9); #endregion #region 填充數據 向Excel中逐行逐列写入表格中的数据 RowIndex = RowIndex + 1; //數據開始的行號 int ExcelCurrectRow = RowIndex; //當前行 int dtRowNO; int realRowNO = 0;//dt實際行 int dtKeyRowNO = RowIndex;//NO:4 int megreCount = 0; int dtMetrialRowNO = 23;//物料編號所在列 int tempRow; int tempCol; #region 較慢的填充數據方法 已經REMARK //for (int row = 0; row < dt.Rows.Count; row++) //{ // //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString()); // for (int col = 1; col < dt.Columns.Count; col++) // { // if (row > 0) //班次內工程ID相等 col22之後不合併 // { // if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22) // { // CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true); // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // if (col < 22) currectRow = RowIndex; // } // } // else // { // CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false); // xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString(); // } // } // RowIndex++; // //if (RowIndex == 15) goto range; // for 測試 //} #endregion DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" }); for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++) { DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' "); megreCount = currectKeyRow.Length; for (tempRow = 0; tempRow < megreCount; tempRow++) { if (tempRow == 0) { for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true); } realRowNO += megreCount; } for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false); } dtKeyRowNO++; } //dtKeyRowNO++; //if (realRowNO > dt.Rows.Count) break; /* * DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" }); List<Thread> threads = new List<Thread>(); for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++) { */ } #endregion #region 設置Sheet邊框 //range: Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]); SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; #endregion Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]); DataFormat.Font.Name = "楷体"; DataFormat.Font.Size = 10; Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]); IntFormat.NumberFormatLocal = "0.00"; #region savecode Object missing = Missing.Value; SaveFileDialog saveForm = new SaveFileDialog(); saveForm.Filter = "Execl files (*.xls)|*.xls"; saveForm.FilterIndex = 0; saveForm.FileName = "過膠機生產日報表"; if (saveForm.ShowDialog() == DialogResult.OK) { string fileName = saveForm.FileName; workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } #endregion result = true; } catch (Exception ex) { throw ex; } finally { labMsg.Text = ""; this.UseWaitCursor = false; xlApp.Quit(); IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法 int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); System.GC.Collect(); } labMsg.Text = ""; return result; }
//生成Excel private bool ExportToExcel(DataTable dt) { bool result = false; if (dt.Rows.Count > 65536) { MessageBox.Show("數據記錄太多(最多不能超過65536條),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); return result; } Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { labMsg.Text = "Excel正在匯出中…………"; if (xlApp == null) { throw new Exception("无法创建Excel对象,可能您的机器未安装Excel"); } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(true); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; worksheet.Name = "過膠機品質自檢日報表"; worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印 worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印 worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效 worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁 worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中 worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102); worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787); worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126); worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126); xlApp.Visible = false; #region 列名 int RowIndex = 2; MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 32], "過膠機品質自檢日報表", 16, 65535, 0); //大標題 MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 32], "當班品檢情況", 12, 65535, 0); RowIndex = RowIndex + 1; MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7); MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "累計數量", 12, 65535, 9); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "車速", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "溫度", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "壓力", 12, 65535, 5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 23], xlApp.Cells[RowIndex, 23], "當班品檢總次數", 12, 65535, 10); MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "第( )次品檢", 12, 65535, 11); MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "打卡數量", 12, 65535, 11); MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "抽檢數量", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "品檢問題", 12, 65535, 18); MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "次品數量", 12, 65535, 11); MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "廢品數量", 12, 65535, 9.5); MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "問題處理辦法", 12, 65535, 24); MergeColumn(xlApp, xlApp.Cells[RowIndex, 31], xlApp.Cells[RowIndex, 31], "品檢開始時間", 12, 65535, 24); MergeColumn(xlApp, xlApp.Cells[RowIndex, 32], xlApp.Cells[RowIndex, 32], "品檢結束時間", 12, 65535, 24); #endregion #region 填充數據 向Excel中逐行逐列写入表格中的数据 RowIndex = RowIndex + 1; //數據開始的行號 int ExcelCurrectRow = RowIndex; //當前行 int dtRowNO; int realRowNO = 0;//dt實際行 int dtKeyRowNO = RowIndex;//NO:4 int megreCount = 0; int dtProblemRowNO = 24;//物料編號所在列 int tempRow; int tempCol; DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" }); for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++) { DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' "); megreCount = currectKeyRow.Length; for (tempRow = 0; tempRow < megreCount; tempRow++) { if (tempRow == 0) { for (tempCol = 1; tempCol < dtProblemRowNO; tempCol++) //先合併物料編號之前的單元格 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true); } realRowNO += megreCount; } for (tempCol = dtProblemRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料 { CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false); } dtKeyRowNO++; } } #endregion #region 設置Sheet邊框 //range: Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]); SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; #endregion Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]); DataFormat.Font.Name = "楷体"; DataFormat.Font.Size = 10; Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]); IntFormat.NumberFormatLocal = "0.00"; #region savecode Object missing = Missing.Value; SaveFileDialog saveForm = new SaveFileDialog(); saveForm.Filter = "Execl files (*.xls)|*.xls"; saveForm.FilterIndex = 0; saveForm.FileName = "過膠機品質自檢日報表"; if (saveForm.ShowDialog() == DialogResult.OK) { string fileName = saveForm.FileName; workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } #endregion result = true; } catch (Exception ex) { throw; } finally { labMsg.Text = ""; xlApp.Quit(); IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法 int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); System.GC.Collect(); } labMsg.Text = ""; return result; }