private void backButton_Click(object sender, EventArgs e) { lf.Close(); WorkOrder wo = new WorkOrder(); wo.toolStripUsername.Text = toolStripUsername.Text; wo.Show(); this.Hide(); }
private void btnGenerate_Click_1(object sender, EventArgs e) { btnHome.Enabled = false; btnWO.Enabled = false; LoadForm lf = new LoadForm(); lf.Show(); int totalpart; int totalPointRow; int remarkRow; int footerRow; // to split model and process string str = cmbLLModel.Text; char ch = '|'; var model = str.Split(ch); //truncate result tabel var cmd = new MySqlCommand("", connection); connection.Open(); for (int i = 0; i < dataGridViewCompareLLWOResult.Rows.Count; i++) { string queryResult = "TRUNCATE tbl_resultcompare"; cmd.CommandText = queryResult; cmd.ExecuteNonQuery(); } connection.Close(); // Create a new workbook with a single sheet excelConvert.NewFile(); // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; // see the excel sheet behind the program app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet // set hide gridlines app.ActiveWindow.DisplayGridlines = false; connection.Open(); for (int i = 0; i < dataGridViewCompareLLWOResult.Rows.Count; i++) { string queryResult = "INSERT INTO tbl_resultcompare (tbl_resultcompare.model_No, tbl_resultcompare.process_Name, " + "tbl_resultcompare.reel, tbl_resultcompare.partcode, tbl_resultcompare.alt_No, tbl_resultcompare.tp, tbl_resultcompare.qty," + " tbl_resultcompare.loc, tbl_resultcompare.dec, tbl_resultcompare.f_Type)" + "SELECT tbl_reel.model_No, tbl_reel.process_Name, tbl_reel.reel, tbl_partcodedetail.partcode,tbl_lldetail.alt_No," + " tbl_partcodedetail.tp, tbl_reel.qty, tbl_reel.loc, tbl_partcodedetail.dec,tbl_reel.f_Type " + "FROM tbl_reel, tbl_partcodedetail, tbl_lldetail WHERE tbl_reel.reel = '" + dataGridViewCompareLLWOResult.Rows[i].Cells[0].Value.ToString() + "' " + "AND tbl_partcodedetail.partcode = '" + dataGridViewCompareLLWOResult.Rows[i].Cells[1].Value.ToString() + "' AND tbl_reel.model_No = '" + model[0].Replace(" ", "").ToString() + "' " + "AND tbl_reel.process_Name = '" + model[1].Replace(" ", "") + "' " + "AND tbl_partcodedetail.partcode = tbl_lldetail.partcode"; cmd.CommandText = queryResult; cmd.ExecuteNonQuery(); } connection.Close(); worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 9]].Merge(); worksheet.Cells[1, 1].Font.Name = "Times New Roman"; worksheet.Cells[1, 1].Font.FontStyle = "Bold"; worksheet.Cells[1, 1].Font.Size = 20; worksheet.Cells[1, 1].Font.Color = Color.Blue; worksheet.Cells[1, 1].EntireRow.Font.Bold = true; worksheet.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; worksheet.Cells[1, 1] = "SMT MACHINE LOADING LIST"; worksheet.Cells[2, 9] = "Page 1 of 1"; worksheet.Cells[2, 9].Style.Font.Size = 10; //worksheet.Cells.Font.Color = Color.Blue; worksheet.Cells[2, 9].EntireRow.Font.Italic = true; worksheet.Range[worksheet.Cells[3, 1], worksheet.Cells[7, 1]].Font.Name = "Courier New"; worksheet.Cells[3, 1].Font.FontStyle = "Bold"; worksheet.Cells[3, 1].Font.Size = 10; worksheet.Cells[3, 1] = "MODEL : " + tbModel.Text; worksheet.Range[worksheet.Cells[3, 6], worksheet.Cells[3, 9]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue); worksheet.Range[worksheet.Cells[3, 6], worksheet.Cells[3, 9]].Font.Color = Color.White; worksheet.Range[worksheet.Cells[2, 6], worksheet.Cells[3, 9]].Font.Name = "Times New Roman"; worksheet.Range[worksheet.Cells[3, 6], worksheet.Cells[3, 9]].Style.Font.Size = 8; worksheet.Cells[3, 6] = "Rev."; worksheet.Cells[3, 7] = "Prepared by"; worksheet.Cells[3, 8] = "Checked by"; worksheet.Cells[3, 9] = "Approved by"; worksheet.Cells[4, 1].Font.FontStyle = "Bold"; worksheet.Cells[4, 1].Font.Size = 9; worksheet.Cells[4, 1] = "MACHINE : " + tbMachine.Text; worksheet.Cells[5, 1].Font.FontStyle = "Bold"; worksheet.Cells[5, 1].Font.Size = 9; worksheet.Cells[5, 1] = "PWB TYPE : " + tbPWBType.Text; worksheet.Cells[6, 1].Font.FontStyle = "Bold"; worksheet.Cells[6, 1].Font.Size = 9; worksheet.Cells[6, 1] = "PROG.NO. : " + tbProgNo.Text; worksheet.Cells[7, 1].Font.FontStyle = "Bold"; worksheet.Cells[7, 1].Font.Size = 9; worksheet.Cells[7, 1] = "DATE : " + DateTime.Now.ToString("dd MMMM yyyy"); worksheet.Range[worksheet.Cells[8, 1], worksheet.Cells[1000, 7]].Font.Name = "Times New Roman"; worksheet.Range[worksheet.Cells[8, 1], worksheet.Cells[8, 7]].Font.FontStyle = "Bold"; worksheet.Range[worksheet.Cells[8, 1], worksheet.Cells[8, 7]].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow); worksheet.Cells[8, 1] = "REEL"; worksheet.Cells[8, 2] = "PART CODE"; worksheet.Cells[8, 3] = "TP"; worksheet.Cells[8, 4] = "QTY"; worksheet.Cells[8, 5] = "LOC."; worksheet.Cells[8, 6] = "DEC."; worksheet.Cells[8, 7] = "F. TYPE"; connection.Open(); string resultPartCode = "SELECT tbl_resultcompare.reel, tbl_resultcompare.partcode, tbl_resultcompare.tp, tbl_resultcompare.qty, " + "tbl_resultcompare.loc,tbl_resultcompare.dec, tbl_resultcompare.f_Type FROM tbl_resultcompare " + "WHERE tbl_resultcompare.model_No = '" + model[0].Replace(" ", "") + "' AND tbl_resultcompare.process_Name = '" + model[1].Replace(" ", "") + "'"; using (MySqlDataAdapter dscmd = new MySqlDataAdapter(resultPartCode, connection)) { DataSet ds = new DataSet(); dscmd.Fill(ds); totalpart = ds.Tables[0].Rows.Count; for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { string data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); worksheet.Cells[i + 9, j + 1] = "'" + data; } } } int woQtyy = Convert.ToInt32(woQty.Text); int totalPoint = woQtyy - 1; totalPointRow = totalpart + 9; worksheet.Range[worksheet.Cells[totalPointRow, 1], worksheet.Cells[totalPointRow, 3]].Merge(); worksheet.Range[worksheet.Cells[totalPointRow, 1], worksheet.Cells[totalPointRow, 6]].Font.FontStyle = "Bold"; worksheet.Cells[totalPointRow, 1] = "TOTAL POINT"; worksheet.Cells[totalPointRow, 4] = totalPoint; worksheet.Cells[totalPointRow, 5] = " PCB NO: " + tbPCB.Text; worksheet.Cells[totalPointRow, 6] = " STENCIL NO : " + tbStencil.Text; remarkRow = totalpart + 11; string remark = "SELECT remarks FROM tbl_ll"; using (MySqlDataAdapter dscmd = new MySqlDataAdapter(remark, connection)) { DataSet ds = new DataSet(); dscmd.Fill(ds); for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { string data = ds.Tables[0].Rows[i].ItemArray[i].ToString(); worksheet.Range[worksheet.Cells[remarkRow, 1], worksheet.Cells[remarkRow, 9]].Merge(); worksheet.Cells[remarkRow, 1] = data; } } connection.Close(); worksheet.Cells[remarkRow + 2, 1] = "FM - SMT - ENG - 011"; // Saving the file in a speicifed path // excelConvert.SaveAs(@"D:\" + model[0].Replace(" ", "").ToString() + " ( " + model[1].Replace(" ", "").ToString() + " )"); // Closing the file excelConvert.Close(); lf.Close(); MessageBox.Show("Excel File Success Generated", "Generate Excel", MessageBoxButtons.OK, MessageBoxIcon.Information); btnHome.Enabled = true; btnWO.Enabled = true; }