private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { List <ErrorList> list = new List <ErrorList>(); ExcelTableFinal = new DataTable(); try { CallParameter cp3 = (CallParameter)e.Argument; ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "ProductSheet$"); while (ExcelTable.Columns.Count > 17) { ExcelTable.Columns.RemoveAt(17); } ExcelTableFinal = ExcelTable.Clone(); ExcelTable.AsEnumerable().Where(row => row["product id"] != null && row["product id"].ToString() != "").ToList().ForEach(row => ExcelTableFinal.ImportRow(row)); if (ExcelTableFinal != null) { if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCT ID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "PRODUCT NAME" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "PRODUCT DESCRIPTION" || ExcelTable.Columns[3].ColumnName.Trim().ToUpper() != "UOM" || ExcelTable.Columns[4].ColumnName.Trim().ToUpper() != "SORT ORDER" || ExcelTable.Columns[5].ColumnName.Trim().ToUpper() != "ACTIVE" || ExcelTable.Columns[6].ColumnName.Trim().ToUpper() != "BC ACTIVE" || ExcelTable.Columns[7].ColumnName.Trim().ToUpper() != "PRODUCT CATEGORY" || ExcelTable.Columns[8].ColumnName.Trim().ToUpper() != "PRODUCT CODE" || ExcelTable.Columns[9].ColumnName.Trim().ToUpper() != "PRICE DISPLAY CODE" || ExcelTable.Columns[10].ColumnName.Trim().ToUpper() != "COST CENTRE CODE" || ExcelTable.Columns[11].ColumnName.Trim().ToUpper() != "MINI BILL COMPLETED" || ExcelTable.Columns[12].ColumnName.Trim().ToUpper() != "STATE" || ExcelTable.Columns[13].ColumnName.Trim().ToUpper() != "ISSTUDIOMPRODUCT" || ExcelTable.Columns[14].ColumnName.Trim().ToUpper() != "INTERNAL DESCRIPTION" || ExcelTable.Columns[15].ColumnName.Trim().ToUpper() != "ADDITIONAL NOTES" || ExcelTable.Columns[16].ColumnName.Trim().ToUpper() != "OPERATION ONLY" ) { ErrorList l = new ErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } else { e.Result = IsSourceValidate(ExcelTableFinal); } } else { ErrorList l = new ErrorList(); l.MessageText = "The excel is empty."; list.Add(l); e.Result = list; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private List <ErrorList> IsSourceValidate(System.Data.DataTable dtable) { bool result = true; List <ErrorList> listerror = new List <ErrorList>(); // step 1 validate product id duplication if (dtable.Rows.Count > 0) { foreach (DataRow dr in dtable.Rows) { if (dr["product id"] == null || dr["product id"].ToString() == "" || dr["uom"] == null || dr["uom"].ToString() == "" || dr["Product Category"] == null || dr["Product Category"].ToString() == "" || dr["Product Code"] == null || dr["Product Code"].ToString() == "" || dr["Price Display Code"] == null || dr["Price Display Code"].ToString() == "" || dr["Cost Centre Code"] == null || dr["Cost Centre Code"].ToString() == "" || dr["State"] == null || dr["State"].ToString() == "" || dr["active"] == null || dr["active"].ToString() == "" || dr["BC active"] == null || dr["BC active"].ToString() == "" || dr["Mini Bill Completed"] == null || dr["Mini Bill Completed"].ToString() == "" || dr["IsStudioMProduct"] == null || dr["IsStudioMProduct"].ToString() == "" || dr["Sort Order"] == null || dr["Sort Order"].ToString() == "" || dr["product name"] == null || dr["product name"].ToString() == "" || dr["product description"] == null || dr["product description"].ToString() == "" || dr["Operation Only"] == null || dr["Operation Only"].ToString() == "" ) { ErrorList em = new ErrorList(); em.ProductID = dr["product id"].ToString(); em.MessageText = @"There are some missing value for this product."; listerror.Add(em); } if ((dr["product id"] != null && dr["product id"].ToString() != "") && dr["product id"].ToString().Length > 15) { ErrorList em = new ErrorList(); em.ProductID = dr["product id"].ToString(); em.MessageText = @"The product ID is over 15 characters in length."; listerror.Add(em); } } var ss = from dr in dtable.AsEnumerable() group dr by dr["product id"] into newdr where newdr.Count() > 1 select newdr.Key; foreach (var row in ss) { ErrorList em = new ErrorList(); em.ProductID = row.ToString(); em.MessageText = "Product ID duplication. Product ID must be unique."; listerror.Add(em); } // step 2 validate UOM value DataSet puom = cr.LoadProductUOM(); var uomList = from c in puom.Tables[0].AsEnumerable() select c.Field <string>("dropdown"); var sourceuomlist = from dr in dtable.AsEnumerable() where dr.Field <string>("uom") != null && dr.Field <string>("uom") != "" select dr.Field <string>("uom"); var nonexist = sourceuomlist.Except(uomList); var ps = from dr in dtable.AsEnumerable() join nx in nonexist on dr["UOM"] equals nx.ToString() select new { productid = dr["product id"], uom = dr["uom"] }; foreach (var row in ps) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.uom + @"' is an invalid value for UOM. Please check sample import file for valid value."; listerror.Add(em); } // step 3 validate product category code DataSet dsproductcategoy = cr.LoadProductCategory(); var pcatList = from c in dsproductcategoy.Tables[0].AsEnumerable() select c.Field <string>("dropdown"); var sourcepcatlist = from dr in dtable.AsEnumerable() where dr.Field <string>("Product Category") != null && dr.Field <string>("Product Category") != "" select dr.Field <string>("Product Category"); nonexist = sourcepcatlist.Except(pcatList); var ps2 = from dr in dtable.AsEnumerable() join nx in nonexist on dr["Product Category"] equals nx.ToString() select new { productid = dr["product id"], pcat = dr["Product Category"] }; foreach (var row in ps2) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.pcat + @"' is an invalid value for product category. Please check sample import file for valid value."; listerror.Add(em); } // step 4 validate product category code DataSet dsproductcode = cr.LoadProductCode(); var pcodeList = from c in dsproductcode.Tables[0].AsEnumerable() select c.Field <string>("dropdown"); var sourcepcodelist = from dr in dtable.AsEnumerable() where dr.Field <string>("Product Code") != null && dr.Field <string>("Product Code") != "" select dr.Field <string>("Product Code"); nonexist = sourcepcodelist.Except(pcodeList); var ps3 = from dr in dtable.AsEnumerable() join nx in nonexist on dr["Product Code"] equals nx.ToString() select new { productid = dr["product id"], pcode = dr["Product Code"] }; foreach (var row in ps3) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.pcode + @"' is an invalid value for product code. Please check sample import file for valid value."; listerror.Add(em); } // step 5 validate product category code DataSet dsproductdiscode = cr.LoadProductDisplayCode(); var pdiscodeList = from c in dsproductdiscode.Tables[0].AsEnumerable() select c.Field <string>("dropdown"); var sourcepdiscodelist = from dr in dtable.AsEnumerable() where dr.Field <string>("Price Display Code") != null && dr.Field <string>("Price Display Code") != "" select dr.Field <string>("Price Display Code"); nonexist = sourcepdiscodelist.Except(pdiscodeList); var ps4 = from dr in dtable.AsEnumerable() join nx in nonexist on dr["Price Display Code"] equals nx.ToString() select new { productid = dr["product id"], pdiscode = dr["Price Display Code"] }; foreach (var row in ps4) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.pdiscode + @"' is an invalid value for product display code. Please check sample import file for valid value."; listerror.Add(em); } // step 6 validate cost center code DataSet dscostcenter = cr.LoadProductCostCenterCode(); var costcenterList = from c in dscostcenter.Tables[0].AsEnumerable() select c.Field <string>("dropdown"); var sourcecostcenterlist = from dr in dtable.AsEnumerable() where dr.Field <string>("Cost Centre Code") != null && dr.Field <string>("Cost Centre Code") != "" select dr.Field <string>("Cost Centre Code"); nonexist = sourcecostcenterlist.Except(costcenterList); var ps5 = from dr in dtable.AsEnumerable() join nx in nonexist on dr["Cost Centre Code"] equals nx.ToString() select new { productid = dr["product id"], costcenter = dr["Cost Centre Code"] }; foreach (var row in ps5) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.costcenter + @"' is an invalid value for cost center code. Please check sample import file for valid value."; listerror.Add(em); } // step 7 validate state value var stateList = from c in cr.SQSState select c.StateAbbreviation; var sourcestatelist = from dr in dtable.AsEnumerable() where dr.Field <string>("State") != null && dr.Field <string>("State") != "" select dr.Field <string>("State"); nonexist = sourcestatelist.Except(stateList); var ps6 = from dr in dtable.AsEnumerable() join nx in nonexist on dr["state"] equals nx.ToString() select new { productid = dr["product id"], state = dr["state"] }; foreach (var row in ps6) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.state + @"' is an invalid value for state. Please check sample import file for valid value."; listerror.Add(em); } // step 8 validate state value var sourceactivelist = from dr in dtable.AsEnumerable() where dr.Field <string>("active") != null && dr.Field <string>("active") != "Y" && dr.Field <string>("active") != "N" select new { productid = dr["product id"], active = dr["active"] }; foreach (var row in sourceactivelist) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.active + @"' is an invalid value for Active. it should be 'Y' or 'N'."; listerror.Add(em); } // step 9 validate state value var sourcebcactivelist = from dr in dtable.AsEnumerable() where dr.Field <string>("bc active") != null && dr.Field <string>("bc active") != "Y" && dr.Field <string>("bc active") != "N" select new { productid = dr["product id"], bcactive = dr["bc active"] }; foreach (var row in sourcebcactivelist) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.bcactive + @"' is an invalid value for BCActive. it should be 'Y' or 'N'."; listerror.Add(em); } // step 10 validate mini bill start value var sourceminibilllist = from dr in dtable.AsEnumerable() where dr.Field <string>("Mini Bill Completed") != null && dr.Field <string>("Mini Bill Completed") != "Y" && dr.Field <string>("Mini Bill Completed") != "N" select new { productid = dr["product id"], minibill = dr["Mini Bill Completed"] }; foreach (var row in sourceminibilllist) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.minibill + @"' is an invalid value for Mini Bill Completed. it should be 'Y' or 'N'."; listerror.Add(em); } // step 10 validate is studio product value var sourcestudiomlist = from dr in dtable.AsEnumerable() where dr.Field <string>("IsStudioMProduct") != null && dr.Field <string>("IsStudioMProduct") != "Y" && dr.Field <string>("IsStudioMProduct") != "N" select new { productid = dr["product id"], studiom = dr["IsStudioMProduct"] }; foreach (var row in sourcestudiomlist) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.studiom + @"' is an invalid value for IsStudioMProduct. it should be 'Y' or 'N'."; listerror.Add(em); } // step 11 validate operation only value var sourceoperationonlylist = from dr in dtable.AsEnumerable() where dr.Field <string>("Operation Only") != null && dr.Field <string>("Operation Only") != "Y" && dr.Field <string>("Operation Only") != "N" select new { productid = dr["product id"], operationonly = dr["Operation Only"] }; foreach (var row in sourceoperationonlylist) { ErrorList em = new ErrorList(); em.ProductID = row.productid.ToString(); em.MessageText = @"'" + row.operationonly + @"' is an invalid value for Operation Only. it should be 'Y' or 'N'."; listerror.Add(em); } foreach (DataRow drr in dtable.Rows) { if (drr["product id"] != null && drr["product id"].ToString() != "") { try { int aa = int.Parse(drr["Sort Order"].ToString()); } catch (Exception ex) { ErrorList em = new ErrorList(); em.ProductID = drr["Product id"].ToString(); em.MessageText = @"Sort order should be a number."; listerror.Add(em); } } } } else { ErrorList em = new ErrorList(); em.ProductID = ""; em.MessageText = @"The excel file is blank."; listerror.Add(em); } //if (listerror.Count > 0) //{ // var newlist = listerror.OrderBy(x => x.ProductID); // ErrorGrid.DataContext = newlist; // btnContinue.IsEnabled = false; // warningmessage.Text = "There are some invalid value in excel. Please fix all error display above, then import again."; // result = false; //} //if product exists then give warning only still allow to process DataSet ds = ValidateProductSheetWithDatabase(dtable); if (ds.Tables[0].Rows.Count > 0) { goahead = true; foreach (DataRow dr in ds.Tables[0].Rows) { ErrorList em = new ErrorList(); em.ProductID = dr["Productid"].ToString(); em.MessageText = @"Product exists. If process will overwrite with the data in this spread sheet. If want to keep the current attributes, please remove this product from import sheet."; listerror.Add(em); } } return(listerror); }