private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { List <PriceErrorList> list = new List <PriceErrorList>(); SQSAdminServiceClient client = new SQSAdminServiceClient(); client.Endpoint.Address = new System.ServiceModel.EndpointAddress(CommonVariables.WcfEndpoint); try { CallParameter cp3 = (CallParameter)e.Argument; ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "PriceSheet$"); if (ExcelTable != null && (ExcelTable.Columns.Count == 8 || ExcelTable.Columns.Count == 68 || ExcelTable.Columns.Count == 16)) { if (ExcelTable.Columns.Count == 8)// vertical format { if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCTID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "REGIONNAME" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "EFFECTIVEDATE" || ExcelTable.Columns[3].ColumnName.Trim().ToUpper() != "DIRECTBUILDCOST" || ExcelTable.Columns[4].ColumnName.Trim().ToUpper() != "BUILDINGTRANSFERCOST" || ExcelTable.Columns[5].ColumnName.Trim().ToUpper() != "SELLPRICE" || ExcelTable.Columns[6].ColumnName.Trim().ToUpper() != "TARGETMARGINPERCENT" || ExcelTable.Columns[7].ColumnName.Trim().ToUpper() != "DERIVEDCOST" ) { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format. Please follow the sample data sheet format."; list.Add(l); e.Result = list; } else { e.Result = IsSourceValidate(ExcelTable); } } else if ((ExcelTable.Columns.Count == 16 && stateid == 2) || (ExcelTable.Columns.Count == 68 && stateid == 3))// NSW/QLD horizontal format { DataSet dsTemp = client.SQSAdmin_Generic_GetRegionList(stateid); string[] colnamearray = new string[dsTemp.Tables[0].Rows.Count * 4 + 4]; int idx = 3; foreach (DataRow dr in dsTemp.Tables[0].Rows) { colnamearray[idx] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_DIRECTBUILDCOST"; colnamearray[idx + dsTemp.Tables[0].Rows.Count] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_BUILDINGTRANSFERCOST"; colnamearray[idx + dsTemp.Tables[0].Rows.Count * 2] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_TARGETMARGINPERCENT"; colnamearray[idx + dsTemp.Tables[0].Rows.Count * 3] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_SELLPRICE"; idx++; } if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCTID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "HOUSETYPE" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "EFFECTIVEDATE" || ExcelTable.Columns[ExcelTable.Columns.Count - 1].ColumnName.Trim().ToUpper() != "DERIVEDCOST") { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } else { for (int i = 3; i < ExcelTable.Columns.Count - 2; i++) { if (ExcelTable.Columns[i].ColumnName.Trim().ToUpper() != colnamearray[i]) { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; break; } } } e.Result = IsHorizontalSourceValidate(ExcelTable); } else { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } } else { PriceErrorList l = new PriceErrorList(); l.MessageText = "Excel provided does not match the standard format."; list.Add(l); e.Result = list; } } catch (Exception ex) { PriceErrorList l = new PriceErrorList(); l.MessageText = ex.Message; list.Add(l); e.Result = list; } }