private void btnInsert_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataTable table = ofd.FileName.EndsWith(".xls") ? ImportExcel2003.TranslateToTable(ofd.FileName) : ImportExcel2007.TranslateToTable(ofd.FileName); foreach (DataRow row in table.Rows) { string chaseno = row.ItemArray[0].ToString().Trim(); string partno = row.ItemArray[1].ToString().Trim(); string rev = row.ItemArray[2].ToString().Trim(); if (rev.Length == 1) { rev = "0" + rev; } string mouldno = row.ItemArray[3].ToString().Trim(); string div = row.ItemArray[4].ToString().Trim(); string curr = row.ItemArray[5].ToString().Trim(); string amt = row.ItemArray[6].ToString().Trim(); string amthkd = row.ItemArray[7].ToString().Trim(); string mpa = row.ItemArray[8].ToString().Trim(); string fa = row.ItemArray[9].ToString().Trim(); string tmpfa = row.ItemArray[10].ToString().Trim(); string qty = row.ItemArray[11].ToString().Trim(); string model = row.ItemArray[12].ToString().Trim(); string po = row.ItemArray[13].ToString().Trim(); string porev = row.ItemArray[14].ToString().Trim(); string issued = ImportExcel2003.ParseDateTime(row.ItemArray[15].ToString().Trim()).ToString("yyyy/MM/dd"); if (issued == "0001/01/01") { issued = row.ItemArray[15].ToString().Trim(); } string category = row.ItemArray[16].ToString().Trim(); string ringi = row.ItemArray[17].ToString().Trim(); string vendor = row.ItemArray[18].ToString().Trim(); if (vendor.Length == 9) { vendor = "0" + vendor; } string mouldcode = row.ItemArray[19].ToString().Trim(); string status = row.ItemArray[20].ToString().Trim(); string oem = row.ItemArray[21].ToString().Trim(); string remarks = row.ItemArray[22].ToString().Trim(); string instock = ImportExcel2003.ParseDateTime(row.ItemArray[23].ToString().Trim()).ToString("yyyy/MM/dd"); if (instock == "0001/01/01") { instock = row.ItemArray[23].ToString().Trim(); } string instock50 = ImportExcel2003.ParseDateTime(row.ItemArray[24].ToString().Trim()).ToString("yyyy/MM/dd"); if (instock50 == "0001/01/01") { instock50 = row.ItemArray[24].ToString().Trim(); } string pgroup = row.ItemArray[25].ToString().Trim(); string ac = row.ItemArray[26].ToString().Trim(); string cc = row.ItemArray[27].ToString().Trim(); string checkdate = ImportExcel2003.ParseDateTime(row.ItemArray[28].ToString().Trim()).ToString("yyyy/MM/dd"); if (checkdate == "0001/01/01") { checkdate = row.ItemArray[28].ToString().Trim(); } string cav = row.ItemArray[29].ToString().Trim(); string weight = row.ItemArray[30].ToString().Trim(); string equipment = row.ItemArray[31].ToString().Trim(); string shot = row.ItemArray[32].ToString().Trim(); string vertical = row.ItemArray[33].ToString().Trim(); string horizontal = row.ItemArray[34].ToString().Trim(); string height = row.ItemArray[35].ToString().Trim(); string collectdate = ImportExcel2003.ParseDateTime(row.ItemArray[36].ToString().Trim()).ToString("yyyy/MM/dd"); if (collectdate == "0001/01/01") { collectdate = row.ItemArray[36].ToString().Trim(); } string itemtext = mouldno + "MP+" + mouldcode + "+" + div; string projecttext = partno + "-" + rev; string type = "Single"; string owner = "KDTHK"; string indate = DateTime.Today.ToString("yyyy/MM/dd"); string request = partno + rev; if (chaseno == "") { continue; } string st = status == "入庫濟" ? "S" : "K"; string query = string.Format("if not exists (select * from tb_betamould where tm_chaseno = '{0}') insert into tb_betamould (tm_chaseno, tm_itemcode, tm_rev, tm_mouldno" + ", tm_status, tm_type, tm_currency, tm_amount, tm_amounthkd, tm_mpa, tm_fixedassetcode, tm_tmpfixedassetcode, tm_qty, tm_itemtext, tm_request, tm_indate, tm_projecttext" + ", tm_model, tm_po, tm_porev, tm_poissued, tm_category, tm_ringi_code, tm_vendor_code, tm_mouldcode_code, tm_st_code, tm_owner, tm_rm, tm_instockdate" + ", tm_instockdate50, tm_group, tm_accountcode, tm_costcentre, tm_checkdate, tm_cav, tm_weight, tm_accessory, tm_camera, tm_vertical, tm_horizontal, tm_height, tm_collectdate)" + " values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', N'{27}'" + ", '{28}', '{29}', '{30}', '{31}', '{32}', '{33}', '{34}', '{35}', '{36}', '{37}', '{38}', '{39}', '{40}', '{41}')", chaseno, partno, rev, mouldno, div, type, curr, amt, amthkd, mpa, fa, tmpfa, qty, itemtext, request, indate, projecttext, model, po, porev, issued, category, ringi, vendor, mouldcode, st, owner, remarks, instock, instock50, pgroup, ac, cc, checkdate, cav, weight, equipment, shot, vertical, horizontal, height, collectdate); DataService.GetInstance().ExecuteNonQuery(query); } MessageBox.Show("Record has been saved."); } }