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