private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ofd.FileName.EndsWith("xlsx") ? ImportExcel2007.TranslateToTable(ofd.FileName) : ImportExcel2003.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string type = row.ItemArray[0].ToString().Trim();
string mould = row.ItemArray[1].ToString().Trim();
string partno = row.ItemArray[2].ToString().Trim();
string rev = row.ItemArray[3].ToString().Trim();
string asset = row.ItemArray[4].ToString().Trim();
string vendor = row.ItemArray[5].ToString().Trim();
if (vendor.Length == 9)
{
vendor = "0" + vendor;
}
string vendorname = row.ItemArray[6].ToString().Trim();
if (vendorname.Contains("'"))
{
vendorname = vendorname.Replace("'", "''");
}
string p2003no = row.ItemArray[7].ToString().Trim();
if (p2003no.Contains("'"))
{
p2003no = p2003no.Replace("'", "''");
}
string p2003ans = row.ItemArray[8].ToString().Trim();
try
{
p2003ans = ImportExcel2007.ParseDateTime(p2003ans).ToString("yyyy/MM/dd");
if (p2003ans.StartsWith("00"))
{
p2003ans = row.ItemArray[8].ToString().Trim();
}
}
catch
{
}
if (p2003ans.Contains("'"))
{
p2003ans = p2003ans.Replace("'", "''");
}
string p2003result = row.ItemArray[9].ToString().Trim();
try
{
p2003result = ImportExcel2007.ParseDateTime(p2003result).ToString("yyyy/MM/dd");
if (p2003result.StartsWith("00"))
{
p2003result = row.ItemArray[9].ToString().Trim();
}
}
catch
{
}
if (p2003result.Contains("'"))
{
p2003result = p2003result.Replace("'", "''");
}
string p2003date = row.ItemArray[10].ToString().Trim();
try
{
p2003date = ImportExcel2007.ParseDateTime(p2003date).ToString("yyyy/MM/dd");
if (p2003date.StartsWith("00"))
{
p2003date = row.ItemArray[10].ToString().Trim();
}
}
catch
{
}
string p2004no = row.ItemArray[11].ToString().Trim();
if (p2004no.Contains("'"))
{
p2004no = p2004no.Replace("'", "''");
}
string p2004ans = row.ItemArray[12].ToString().Trim();
try
{
p2004ans = ImportExcel2007.ParseDateTime(p2004ans).ToString("yyyy/MM/dd");
if (p2004ans.StartsWith("00"))
{
p2004ans = row.ItemArray[12].ToString().Trim();
}
}
catch
{
}
if (p2004ans.Contains("'"))
{
p2004ans = p2004ans.Replace("'", "''");
}
string p2004result = row.ItemArray[13].ToString().Trim();
try
{
p2004result = ImportExcel2007.ParseDateTime(p2004result).ToString("yyyy/MM/dd");
if (p2004result.StartsWith("00"))
{
p2004result = row.ItemArray[13].ToString().Trim();
}
}
catch
{
}
if (p2004result.Contains("'"))
{
p2004result = p2004result.Replace("'", "''");
}
string p2004date = row.ItemArray[14].ToString().Trim();
try
{
p2004date = ImportExcel2007.ParseDateTime(p2004date).ToString("yyyy/MM/dd");
if (p2004date.StartsWith("00"))
{
p2004date = row.ItemArray[14].ToString().Trim();
}
}
catch
{
}
string kdcno = row.ItemArray[15].ToString().Trim();
if (kdcno.Contains("'"))
{
kdcno = kdcno.Replace("'", "''");
}
string kdcdate = row.ItemArray[16].ToString().Trim();
try
{
kdcdate = ImportExcel2007.ParseDateTime(kdcdate).ToString("yyyy/MM/dd");
if (kdcdate.StartsWith("00"))
{
kdcdate = row.ItemArray[16].ToString().Trim();
}
}
catch
{
}
string kdcrps = row.ItemArray[17].ToString().Trim();
try
{
kdcrps = ImportExcel2007.ParseDateTime(kdcrps).ToString("yyyy/MM/dd");
if (kdcrps.StartsWith("00"))
{
kdcrps = row.ItemArray[17].ToString().Trim();
}
}
catch
{
}
if (kdcrps.Contains("'"))
{
kdcrps = kdcrps.Replace("'", "''");
}
string kdcans = row.ItemArray[18].ToString().Trim();
try
{
kdcans = ImportExcel2007.ParseDateTime(kdcans).ToString("yyyy/MM/dd");
if (kdcans.StartsWith("00"))
{
kdcans = row.ItemArray[18].ToString().Trim();
}
}
catch
{
}
if (kdcans.Contains("'"))
{
kdcans = kdcans.Replace("'", "''");
}
string kdcresult = row.ItemArray[19].ToString().Trim();
try
{
kdcresult = ImportExcel2007.ParseDateTime(kdcresult).ToString("yyyy/MM/dd");
if (kdcresult.StartsWith("00"))
{
kdcresult = row.ItemArray[19].ToString().Trim();
}
}
catch
{
}
if (kdcresult.Contains("'"))
{
kdcresult.Replace("'", "''");
}
string kdcupdate = row.ItemArray[20].ToString().Trim();
try
{
kdcupdate = ImportExcel2007.ParseDateTime(kdcupdate).ToString("yyyy/MM/dd");
if (kdcupdate.StartsWith("00"))
{
kdcupdate = row.ItemArray[20].ToString().Trim();
}
}
catch
{
}
string assetdesc = row.ItemArray[21].ToString().Trim();
if (assetdesc.Contains("'"))
{
assetdesc = assetdesc.Replace("'", "''");
}
string capdate = row.ItemArray[22].ToString().Trim();
try
{
capdate = ImportExcel2007.ParseDateTime(capdate).ToString("yyyy/MM/dd");
if (capdate.StartsWith("00"))
{
capdate = row.ItemArray[22].ToString().Trim();
}
}
catch
{
}
string acquis = row.ItemArray[23].ToString().Trim();
string accum = row.ItemArray[24].ToString().Trim();
string closing = row.ItemArray[25].ToString().Trim();
try
{
closing = ImportExcel2007.ParseDateTime(closing).ToString("yyyy/MM/dd");
if (closing.StartsWith("00"))
{
closing = row.ItemArray[25].ToString().Trim();
}
}
catch
{
}
string bookval = row.ItemArray[26].ToString().Trim();
string year = row.ItemArray[27].ToString().Trim();
try
{
year = ImportExcel2007.ParseDateTime(year).ToString("yyyy/MM/dd");
if (year.StartsWith("00"))
{
year = row.ItemArray[27].ToString().Trim();
}
}
catch
{
}
string bookval2 = row.ItemArray[28].ToString().Trim();
string ringi = row.ItemArray[29].ToString().Trim();
string reportno = row.ItemArray[30].ToString().Trim();
if (reportno.Contains("''"))
{
reportno = reportno.Replace("'", "''");
}
string reportissued = row.ItemArray[31].ToString().Trim();
try
{
reportissued = ImportExcel2007.ParseDateTime(reportissued).ToString("yyyy/MM/dd");
if (reportissued.StartsWith("00"))
{
reportissued = row.ItemArray[31].ToString().Trim();
}
}
catch
{
}
string reportdate = row.ItemArray[32].ToString().Trim();
try
{
reportdate = ImportExcel2007.ParseDateTime(reportdate).ToString("yyyy/MM/dd");
if (reportdate.StartsWith("00"))
{
reportdate = row.ItemArray[32].ToString().Trim();
}
}
catch
{
}
string vendorans = row.ItemArray[33].ToString().Trim();
string assetdisposal = row.ItemArray[34].ToString().Trim();
try
{
assetdisposal = ImportExcel2007.ParseDateTime(assetdisposal).ToString("yyyy/MM/dd");
if (assetdisposal.StartsWith("00"))
{
assetdisposal = row.ItemArray[34].ToString().Trim();
}
}
catch
{
}
string remarks = row.ItemArray[35].ToString().Trim();
string status = row.ItemArray[36].ToString().Trim();
if (closing.Contains("'"))
{
closing = closing.Replace("'", "''");
}
if (year.Contains("'"))
{
year = year.Replace("'", "''");
}
string query = string.Format("if not exists (select * from tb_disposaldetail where dd_mould = '{2}' and dd_partno = '{3}' and dd_asset = '{5}')" +
" insert into tb_disposaldetail (dd_type, dd_status, dd_mould, dd_partno, dd_rev, dd_asset, dd_vendor, dd_vendorname" +
", dd_2003no, dd_2003answer, dd_2003result, dd_2003updated, dd_2004no, dd_2004answer, dd_2004result, dd_2004updated" +
", dd_kdcno, dd_kdcissued, dd_kdcrps, dd_kdcanswer, dd_kdcresult, dd_kdcupdated, dd_assetdesc, dd_capdate, dd_acquishkd" +
", dd_accumhkd, dd_closing, dd_bookhkd, dd_fy, dd_bookhkd2, dd_disposalringi, dd_reportno, dd_reportissued, dd_reportreceived" +
", dd_vendorresult, dd_fadisposaldate, dd_disposalremarks) values (N'{0}', N'{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}', '{27}', '{28}', '{29}', '{30}'" +
", N'{31}', '{32}', '{33}', '{34}', '{35}', N'{36}') else update tb_disposaldetail set dd_type = N'{0}', dd_rev = '{4}', dd_asset = '{5}', dd_vendor = '{6}'" +
", dd_vendorname = N'{7}', dd_2003no = '{8}', dd_2003answer = '{9}', dd_2003result = '{10}', dd_2003updated = '{11}', dd_2004no = '{12}'" +
", dd_2004answer = '{13}', dd_2004result = '{14}', dd_2004updated = '{15}', dd_kdcno = '{16}', dd_kdcissued = '{17}', dd_kdcrps = '{18}'" +
", dd_kdcanswer = '{19}', dd_kdcresult = '{20}', dd_kdcupdated = '{21}', dd_assetdesc = '{22}', dd_capdate = '{23}', dd_acquishkd = '{24}'" +
", dd_accumhkd = '{25}', dd_closing = '{26}', dd_bookhkd = '{27}', dd_fy = '{28}', dd_bookhkd2 = '{29}', dd_disposalringi = '{30}', dd_reportno = N'{31}'" +
", dd_reportissued = '{32}', dd_reportreceived = '{33}', dd_vendorresult = '{34}', dd_fadisposaldate = '{35}', dd_disposalremarks = N'{36}', dd_status = N'{1}' where dd_mould = '{2}' and dd_partno = '{3}'"
, type, status, mould, partno, rev, asset, vendor, vendorname, p2003no, p2003ans,
p2003result, p2003date, p2004no, p2004ans, p2004result, p2004date, kdcno, kdcdate, kdcrps, kdcans, kdcresult, kdcupdate, assetdesc,
capdate, acquis, accum, closing, bookval, year, bookval2, ringi, reportno, reportissued, reportdate, vendorans, assetdisposal, remarks);
DataService.GetInstance().ExecuteNonQuery(query);
UpdateMouldData(mould, partno, reportno, status);
}
SearchData("");
MessageBox.Show("Record has been uploaded.");
}
}
public static void UpdateData()
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files |*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = 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 mould = row.ItemArray[3].ToString().Trim();
string div = row.ItemArray[4].ToString().Trim();
string type = row.ItemArray[5].ToString().Trim();
string currency = row.ItemArray[6].ToString().Trim();
string amount = row.ItemArray[7].ToString().Trim();
string amounthkd = row.ItemArray[8].ToString().Trim();
string mpa = row.ItemArray[9].ToString().Trim();
string fa = row.ItemArray[10].ToString().Trim();
string tmpfa = row.ItemArray[11].ToString().Trim();
string qty = row.ItemArray[12].ToString().Trim();
string common = row.ItemArray[13].ToString().Trim();
string itemtext = row.ItemArray[14].ToString().Trim();
string request = row.ItemArray[15].ToString().Trim();
string indate = ImportExcel2007.ParseDateTime(row.ItemArray[16].ToString().Trim()).ToString("yyyy/MM/dd");
if (indate == "0001/01/01")
{
indate = row.ItemArray[16].ToString().Trim();
}
string deliverydate = ImportExcel2007.ParseDateTime(row.ItemArray[17].ToString().Trim()).ToString("yyyy/MM/dd");
if (deliverydate == "0001/01/01")
{
deliverydate = row.ItemArray[17].ToString().Trim();
}
string projecttext = row.ItemArray[18].ToString().Trim();
string model = row.ItemArray[19].ToString().Trim();
string po = row.ItemArray[20].ToString().Trim();
string porev = row.ItemArray[21].ToString().Trim();
string issued = ImportExcel2007.ParseDateTime(row.ItemArray[22].ToString().Trim()).ToString("yyyy/MM/dd");
if (issued == "0001/01/01")
{
issued = row.ItemArray[22].ToString().Trim();
}
string category = row.ItemArray[23].ToString().Trim();
string ringi = row.ItemArray[24].ToString().Trim();
string vendor = row.ItemArray[25].ToString().Trim();
if (vendor.Length == 9)
{
vendor = "0" + vendor;
}
string mouldcode = row.ItemArray[26].ToString().Trim();
string status = row.ItemArray[27].ToString().Trim();
string owner = row.ItemArray[28].ToString().Trim();
string oem = row.ItemArray[29].ToString().Trim();
string remarks = row.ItemArray[30].ToString().Trim();
string instockdate = ImportExcel2007.ParseDateTime(row.ItemArray[31].ToString().Trim()).ToString("yyyy/MM/dd");
if (instockdate == "0001/01/01")
{
instockdate = row.ItemArray[31].ToString().Trim();
}
string instockdate50 = ImportExcel2007.ParseDateTime(row.ItemArray[32].ToString().Trim()).ToString("yyyy/MM/dd");
if (instockdate50 == "0001/01/01")
{
instockdate50 = row.ItemArray[32].ToString().Trim();
}
string pgroup = row.ItemArray[33].ToString().Trim();
string ac = row.ItemArray[34].ToString().Trim();
string cc = row.ItemArray[35].ToString().Trim();
string checkdate = ImportExcel2007.ParseDateTime(row.ItemArray[36].ToString().Trim()).ToString("yyyy/MM/dd");
if (checkdate == "0001/01/01")
{
checkdate = row.ItemArray[36].ToString().Trim();
}
string checkdate2 = ImportExcel2007.ParseDateTime(row.ItemArray[37].ToString().Trim()).ToString("yyyy/MM/dd");
if (checkdate2 == "0001/01/01")
{
checkdate2 = row.ItemArray[37].ToString().Trim();
}
string cav = row.ItemArray[38].ToString().Trim();
string weight = row.ItemArray[39].ToString().Trim();
string equipment = row.ItemArray[40].ToString().Trim();
string shot = row.ItemArray[41].ToString().Trim();
string vertical = row.ItemArray[42].ToString().Trim();
string horizontal = row.ItemArray[43].ToString().Trim();
string height = row.ItemArray[44].ToString().Trim();
string ismodifiy = row.ItemArray[45].ToString().Trim();
string instockremarks = row.ItemArray[46].ToString().Trim();
string collectdate = ImportExcel2007.ParseDateTime(row.ItemArray[47].ToString().Trim()).ToString("yyyy/MM/dd");
if (collectdate == "0001/01/01")
{
collectdate = row.ItemArray[47].ToString().Trim();
}
string pass = row.ItemArray[48].ToString().Trim();
string pcs = row.ItemArray[49].ToString().Trim();
string rmb50 = row.ItemArray[50].ToString().Trim();
string rmbtax = row.ItemArray[51].ToString().Trim();
string cndate = ImportExcel2007.ParseDateTime(row.ItemArray[52].ToString().Trim()).ToString("yyyy/MM/dd");
if (cndate == "0001/01/01")
{
cndate = row.ItemArray[52].ToString().Trim();
}
string cnsenddate = ImportExcel2007.ParseDateTime(row.ItemArray[53].ToString().Trim()).ToString("yyyy/MM/dd");
if (cnsenddate == "0001/01/01")
{
cnsenddate = row.ItemArray[53].ToString().Trim();
}
string query = string.Format("update tb_betamould set tm_itemcode = '{0}', tm_rev = '{1}', tm_mouldno = '{2}', tm_status = '{3}', tm_type = '{4}', tm_currency = '{5}', tm_amount = '{6}', tm_amounthkd = '{7}'" +
", tm_mpa = '{8}', tm_fixedassetcode = '{9}', tm_tmpfixedassetcode = '{10}', tm_qty = '{11}', tm_common = '{12}', tm_itemtext = '{13}', tm_request = '{14}', tm_indate = '{15}', tm_deliverydate = '{16}'" +
", tm_projecttext = '{17}', tm_model = '{18}', tm_po = '{19}', tm_porev = '{20}', tm_poissued = '{21}', tm_category = '{22}', tm_ringi_code = '{23}', tm_vendor_code = '{24}', tm_mouldcode_code = '{25}'" +
", tm_st_code = '{26}', tm_owner = '{27}', tm_oemasset = '{28}', tm_rm = N'{29}', tm_instockdate = '{30}', tm_instockdate50 = '{31}', tm_group = '{32}', tm_accountcode = '{33}', tm_costcentre = '{34}'" +
", tm_checkdate = '{35}', tm_checkdate2 = '{36}', tm_cav = '{37}', tm_weight = '{38}', tm_accessory = '{39}', tm_camera = '{40}', tm_vertical = '{41}', tm_horizontal = '{42}', tm_height = '{43}', tm_ismodify = '{44}'" +
", tm_instockremarks = N'{45}', tm_collectdate = '{46}', tm_passremarks = N'{47}', tm_pcs = '{48}', tm_is50 = '{49}', tm_tax = '{50}', tm_cndatetime = '{51}', tm_cnsendtime = '{52}' where tm_chaseno = '{53}'",
partno, rev, mould, div, type, currency, amount, amounthkd, mpa, fa, tmpfa, qty, common, itemtext, request, indate, deliverydate, projecttext, model, po, porev, issued, category, ringi, vendor,
mouldcode, status, owner, oem, remarks, instockdate, instockdate50, pgroup, ac, cc, checkdate, checkdate2, cav, weight, equipment, shot, vertical, horizontal, height, ismodifiy, instockremarks,
collectdate, pass, pcs, rmb50, rmbtax, cndate, cnsenddate, chaseno);
DataService.GetInstance().ExecuteNonQuery(query);
}
MessageBox.Show("Record has been saved.");
}
}
