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.");
}
}
