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;
}
//The below is the key for showing Progress bar
private void StartProgress(String strStatusText)
{
LoadForm lf = new LoadForm();
ShowProgress();
}
