private void importsexcel2(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); Salesinput_ctrl md = new Salesinput_ctrl(); bool kq = md.deleteedlp(); //if (!kq) //{ // MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information); //} datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //------ //---------------fill data ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); // Sales Group Sales Group desc Sales Off Sales Office Desc System.Data.DataTable batable = new System.Data.DataTable(); // batable.Columns.Add("Soldto", typeof(double)); batable.Columns.Add("ContractNo", typeof(string)); batable.Columns.Add("BatchNo", typeof(double)); batable.Columns.Add("PayType", typeof(string)); batable.Columns.Add("PaidRequestAmt", typeof(double)); batable.Columns.Add("PaidNote", typeof(string)); batable.Columns.Add("PaymentDoc", typeof(string)); // PaymentDoc string username = Utils.getusername(); batable.Columns.Add("Username", typeof(string)); batable.Columns["Username"].DefaultValue = username; batable.Columns.Add("status", typeof(Boolean)); batable.Columns["status"].DefaultValue = false; #region setcolum int PaymentDocid = -1; int ContractNoid = -1; int BatchNoid = -1; int PayTypeid = -1; int PaidRequestAmtid = -1; int PaidNoteid = -1; // View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test"); // vi1.ShowDialog(); // int headindex = -2; for (int rowid = 0; rowid < 3; rowid++) { // headindex = 1; for (int columid = 0; columid < sourceData.Columns.Count; columid++) { string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim() == "ContractNo") { ContractNoid = columid; // headindex = rowid; } if (value.Trim() == ("BatchNo")) { BatchNoid = columid; // headindex = rowid; } if (value.Trim() == ("PayType")) { PayTypeid = columid; // headindex = rowid; } if (value.Trim() == ("PaidRequestAmt")) { PaidRequestAmtid = columid; // headindex = rowid; } if (value.Trim() == "PaidNote") { PaidNoteid = columid; // headindex = rowid; } if (value.Trim() == "PaymentDoc") { PaymentDocid = columid; // headindex = rowid; } #endregion } } // colum } // roww off heatder #endregion if (ContractNoid == -1) { MessageBox.Show("Please check ContractNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (BatchNoid == -1) { MessageBox.Show("Please check BatchNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PayTypeid == -1) { MessageBox.Show("Please check PayType colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PaidRequestAmtid == -1) { MessageBox.Show("Please check PaidRequestAmt colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PaidNoteid == -1) { MessageBox.Show("Please check PaidNote colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (PaymentDocid == -1) { MessageBox.Show("Please check PaymentDoc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of massconfirm // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string PaidRequestAmt = sourceData.Rows[rowixd][PaidRequestAmtid].ToString(); if (PaidRequestAmt != "" && Utils.IsValidnumber(PaidRequestAmt)) { DataRow dr = batable.NewRow(); dr["ContractNo"] = sourceData.Rows[rowixd][ContractNoid].ToString().Trim(); dr["BatchNo"] = double.Parse(sourceData.Rows[rowixd][BatchNoid].ToString().Trim()); dr["PayType"] = sourceData.Rows[rowixd][PayTypeid].ToString().Trim(); dr["PaidRequestAmt"] = double.Parse(sourceData.Rows[rowixd][PaidRequestAmtid].ToString().Trim()); dr["PaidNote"] = sourceData.Rows[rowixd][PaidNoteid].ToString().Trim(); dr["PaymentDoc"] = sourceData.Rows[rowixd][PaymentDocid].ToString().Trim(); batable.Rows.Add(dr); } #endregion }// row string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tbl_MassConfirmTemp"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("ContractNo", "ContractNo"); bulkCopy.ColumnMappings.Add("BatchNo", "BatchNo"); bulkCopy.ColumnMappings.Add("PayType", "PayType"); bulkCopy.ColumnMappings.Add("PaidRequestAmt", "PaidRequestAmt"); bulkCopy.ColumnMappings.Add("PaidNote", "PaidNote"); bulkCopy.ColumnMappings.Add("PaymentDoc", "PaymentDoc"); bulkCopy.ColumnMappings.Add("Username", "Username"); bulkCopy.ColumnMappings.Add("status", "status"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }
private void importsexce2sales(object obj) { string connection_string = Utils.getConnectionstr(); LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string); Salesinput_ctrl md = new Salesinput_ctrl(); bool kq = md.deleteedlp(); //if (!kq) //{ // MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information); //} datainportF inf = (datainportF)obj; string filename = inf.filename; string connectionString = ""; if (filename.Contains(".xlsx") || filename.Contains(".XLSX")) { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;"; } else { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;"; } //------ //---------------fill data ExcelProvider ExcelProvide = new ExcelProvider(); //#endregion System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename); // Sales Group Sales Group desc Sales Off Sales Office Desc System.Data.DataTable batable = new System.Data.DataTable(); batable.Columns.Add("Soldto", typeof(double)); batable.Columns.Add("CustName", typeof(string)); batable.Columns.Add("MatNumber", typeof(string)); batable.Columns.Add("SalesOrg", typeof(string)); batable.Columns.Add("InvoiceDocNr", typeof(double)); batable.Columns.Add("OutboundDelivery", typeof(double)); batable.Columns.Add("DeliveryDate", typeof(DateTime)); batable.Columns.Add("SalesDistrict", typeof(string)); batable.Columns.Add("SalesDistrictdesc", typeof(string)); // batable.Columns.Add("SalesGroup", typeof(string)); // batable.Columns.Add("SalesOff", typeof(string)); // batable.Columns.Add("SalesOfficeDesc", typeof(double)); batable.Columns.Add("KeyAccNr", typeof(double)); batable.Columns.Add("InvoiceDate", typeof(DateTime)); batable.Columns.Add("MatGroup", typeof(double)); batable.Columns.Add("MatGroupText", typeof(string)); batable.Columns.Add("MatText", typeof(string)); batable.Columns.Add("CondType", typeof(string)); // batable.Columns.Add("CondTypedesc", typeof(string)); batable.Columns.Add("BilledQty", typeof(double)); batable.Columns.Add("Netvalue", typeof(double)); batable.Columns.Add("CondValue", typeof(double)); batable.Columns.Add("Currency", typeof(string)); // batable.Columns.Add("Username", typeof(string)); batable.Columns.Add("UoM", typeof(string)); string username = Utils.getusername(); batable.Columns.Add("Username"); batable.Columns["Username"].DefaultValue = username; #region setcolum int Soldtoid = -1; int CustNameid = -1; int MatNumberid = -1; int SalesOrgid = -1; int InvoiceDocNrid = -1; int OutboundDeliveryid = -1; int DeliveryDateid = -1; int SalesDistrictid = -1; int SalesDistrictdescid = -1; int KeyAccNrid = -1; int InvoiceDateid = -1; int MatGroupid = -1; int MatGroupTextid = -1; int MatTextid = -1; int BilledQtyid = -1; int Netvalueid = -1; int CondValueid = -1; int Currencyid = -1; int UoMid = -1; int CondTypeid = -1; // View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test"); // vi1.ShowDialog(); // int headindex = -2; for (int rowid = 0; rowid < 6; rowid++) { if (rowid < sourceData.Rows.Count) { #region for (int columid = 0; columid < sourceData.Columns.Count; columid++) { #region string value = sourceData.Rows[rowid][columid].ToString(); // MessageBox.Show(value +":"+ rowid); if (value != null) { #region setcolum if (value.Trim() == "Sold-to") { Soldtoid = columid; // headindex = rowid; } if (value.Trim() == ("Cust Name")) { CustNameid = columid; // headindex = rowid; } if (value.Trim() == ("Cond Type")) { CondTypeid = columid; // headindex = rowid; } if (value.Trim() == ("Mat Number")) { MatNumberid = columid; // headindex = rowid; } if (value.Trim() == "Sales Org") { SalesOrgid = columid; // headindex = rowid; } if (value.Trim() == "Invoice Doc Nr") { InvoiceDocNrid = columid; // headindex = rowid; } if (value.Trim() == "Outbound Delivery") { OutboundDeliveryid = columid; // headindex = rowid; } if (value.Trim() == "Delivery Date") { DeliveryDateid = columid; // headindex = rowid; } if (value.Trim() == "Sales District") { SalesDistrictid = columid; // headindex = rowid; } if (value.Trim() == "Sales District desc") { SalesDistrictdescid = columid; // headindex = rowid; } if (value.Trim() == "Key Acc Nr") { KeyAccNrid = columid; // headindex = rowid; } if (value.Trim() == "Mat Group") { MatGroupid = columid; // headindex = rowid; } if (value.Trim() == "Mat Group Text") { MatGroupTextid = columid; // headindex = rowid; } if (value.Trim() == "Mat Text") { MatTextid = columid; // headindex = rowid; } if (value.Trim() == "UoM") { UoMid = columid; // headindex = rowid; } if (value.Trim() == "Billed Qty") { BilledQtyid = columid; // headindex = rowid; } if (value.Trim() == "Net value") { Netvalueid = columid; // headindex = rowid; } if (value.Trim().Contains("Cond Value")) { CondValueid = columid; // headindex = rowid; } if (value.Trim() == "Invoice Date") { InvoiceDateid = columid; // headindex = rowid; } if (value.Trim() == "Currency") { Currencyid = columid; // headindex = rowid; } #endregion } #endregion }// colum #endregion } }// roww off heatder #endregion if (Soldtoid == -1) { MessageBox.Show("Please check Sold-to colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CondTypeid == -1) { MessageBox.Show("Please check Cond Type colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CustNameid == -1) { MessageBox.Show("Please check Customer colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatNumberid == -1) { MessageBox.Show("Please check MatNumber colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesOrgid == -1) { MessageBox.Show("Please check SalesOrg colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (InvoiceDocNrid == -1) { MessageBox.Show("Please check Invoice Doc Nr colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (OutboundDeliveryid == -1) { MessageBox.Show("Please check Outbound Delivery colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (DeliveryDateid == -1) { MessageBox.Show("Please check Delivery Date colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesDistrictid == -1) { MessageBox.Show("Please check Sales District colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (SalesDistrictdescid == -1) { MessageBox.Show("Please check Sales District desc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (KeyAccNrid == -1) { MessageBox.Show("Please check KeyAcc Nr colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (InvoiceDateid == -1) { MessageBox.Show("Please check Invoice Date colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatGroupid == -1) { MessageBox.Show("Please check MatGroup colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (MatGroupTextid == -1) { MessageBox.Show("Please check MatGroupText colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (BilledQtyid == -1) { MessageBox.Show("Please check Billed Qty colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Netvalueid == -1) { MessageBox.Show("Please check Netvalue colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (CondValueid == -1) { MessageBox.Show("Please check CondValue colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (Currencyid == -1) { MessageBox.Show("Please check Currency colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (UoMid == -1) { MessageBox.Show("Please check UoM colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++) { #region setvalue of pricelist // string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist); string customer = sourceData.Rows[rowixd][Soldtoid].ToString(); if (customer != "" && Utils.IsValidnumber(customer)) { DataRow dr = batable.NewRow(); dr["Soldto"] = double.Parse(sourceData.Rows[rowixd][Soldtoid].ToString()); dr["CustName"] = sourceData.Rows[rowixd][CustNameid].ToString().Trim(); dr["MatNumber"] = sourceData.Rows[rowixd][MatNumberid].ToString().Trim(); dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrgid].ToString().Trim(); dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNrid].ToString().Trim()); dr["OutboundDelivery"] = double.Parse(sourceData.Rows[rowixd][OutboundDeliveryid].ToString().Trim()); if (Utils.chageExceldatetoData(sourceData.Rows[rowixd][DeliveryDateid].ToString().Trim()) == new DateTime(00001, 01, 01)) { MessageBox.Show("Định dạng ngày tháng của dòng " + rowixd.ToString() + " cột " + DeliveryDateid.ToString() + " sai, must be dd.MM.yyyy", "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else { dr["DeliveryDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][DeliveryDateid].ToString().Trim()); } dr["SalesDistrict"] = sourceData.Rows[rowixd][SalesDistrictid].ToString().Trim(); dr["CondType"] = sourceData.Rows[rowixd][CondTypeid].ToString().Trim(); dr["SalesDistrictdesc"] = sourceData.Rows[rowixd][SalesDistrictdescid].ToString().Trim(); if (sourceData.Rows[rowixd][KeyAccNrid].ToString() != "" && sourceData.Rows[rowixd][KeyAccNrid] != null) { dr["KeyAccNr"] = double.Parse(sourceData.Rows[rowixd][KeyAccNrid].ToString().Trim()); } dr["MatGroup"] = double.Parse(sourceData.Rows[rowixd][MatGroupid].ToString().Trim()); dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupTextid].ToString();//.Trim(); dr["MatText"] = sourceData.Rows[rowixd][MatTextid].ToString().Trim(); dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQtyid].ToString().Trim()); dr["Netvalue"] = double.Parse(sourceData.Rows[rowixd][Netvalueid].ToString().Trim()); dr["Currency"] = sourceData.Rows[rowixd][Currencyid].ToString().Trim(); dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValueid].ToString().Trim()); if (Utils.chageExceldatetoData(sourceData.Rows[rowixd][InvoiceDateid].ToString()) == new DateTime(00001, 01, 01)) { MessageBox.Show("Định dạng ngày tháng của dòng " + rowixd.ToString() + " cột " + InvoiceDateid.ToString() + " sai, must be dd.MM.yyyy", "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else { dr["InvoiceDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][InvoiceDateid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); } dr["UoM"] = sourceData.Rows[rowixd][UoMid].ToString().Trim(); // dr["Username"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][Createdonid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to); batable.Rows.Add(dr); } #endregion }// row string destConnString = Utils.getConnectionstr(); //---------------fill data using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString)) { bulkCopy.BulkCopyTimeout = 0; bulkCopy.DestinationTableName = "tbl_kasalesTemp"; // Write from the source to the destination. bulkCopy.ColumnMappings.Add("Soldto", "[Sold-to]"); bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]"); bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]"); bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]"); bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]"); bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]"); bulkCopy.ColumnMappings.Add("[DeliveryDate]", "[Delivery Date]"); bulkCopy.ColumnMappings.Add("[SalesDistrict]", "[Sales District]"); bulkCopy.ColumnMappings.Add("[SalesDistrictdesc]", "[Sales District desc]"); // bulkCopy.ColumnMappings.Add("[SalesGroup]", "[Sales Group]"); //// bulkCopy.ColumnMappings.Add("[SalesOff]", "[Sales Off]"); // bulkCopy.ColumnMappings.Add("[SalesOfficeDesc]", "[Sales Office Desc]"); bulkCopy.ColumnMappings.Add("[KeyAccNr]", "[Key Acc Nr]"); bulkCopy.ColumnMappings.Add("[InvoiceDate]", "[Invoice Date]"); bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]"); bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]"); bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]"); bulkCopy.ColumnMappings.Add("[CondType]", "[Cond Type]"); // bulkCopy.ColumnMappings.Add("[CondTypedesc]", "[Cond Type desc]"); bulkCopy.ColumnMappings.Add("[BilledQty]", "[EC]"); bulkCopy.ColumnMappings.Add("[Netvalue]", "[NSR]"); bulkCopy.ColumnMappings.Add("[CondValue]", "[GSR]"); bulkCopy.ColumnMappings.Add("[Currency]", "[Currency]"); bulkCopy.ColumnMappings.Add("[Username]", "[Username]"); // bulkCopy.ColumnMappings.Add("[SalesGroupdesc]", "[Sales Group desc]"); bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]"); // bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]"); try { bulkCopy.WriteToServer(batable); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error); Thread.CurrentThread.Abort(); } } }