private void ReadExcelData(string sFileName) { ArrayList RowNo = new ArrayList(); ArrayList ColNo = new ArrayList(); ArrayList SArray = new ArrayList(); ExcelReading objExcelData = new ExcelReading(); DataSet ds = new DataSet(); DataSet dsStatus = new DataSet(); DataSet dsSales = new DataSet(); bool bnew = false; grdErrors.DataSource = null; grdErrors.DataBind(); try { lblErrorMsg.Text = ""; ds = objExcelData.GetExcelToDataset(sFileName); Session["ExcelData"] = ds; DataSet dsError = new DataSet(); dsError.Tables.Add(); dsError.Tables["Table1"].Columns.Add("DealerUniqueID"); dsError.Tables["Table1"].Columns.Add("RowNo"); dsError.Tables["Table1"].Columns.Add("Error"); if (ds != null) { if (ds.Tables.Count > 0) { if (ds.Tables[0].Columns.Contains("Price") && ds.Tables[0].Columns.Contains("year") && ds.Tables[0].Columns.Contains("Make") && ds.Tables[0].Columns.Contains("Model") && ds.Tables[0].Columns.Contains("Description") && ds.Tables[0].Columns.Contains("Mileage") && ds.Tables[0].Columns.Contains("BodyStyle") && ds.Tables[0].Columns.Contains("ExteriorColor") && ds.Tables[0].Columns.Contains("InteriorColor") && ds.Tables[0].Columns.Contains("VIN") && ds.Tables[0].Columns.Contains("FuelType") && ds.Tables[0].Columns.Contains("Transmission") && ds.Tables[0].Columns.Contains("Wheelbase") && ds.Tables[0].Columns.Contains("Doors") && ds.Tables[0].Columns.Contains("Drivetrain") && ds.Tables[0].Columns.Contains("SellerNotes") && //ds.Tables[0].Columns.Contains("VehicleCondition") && ds.Tables[0].Columns.Contains("ConditionDescription") && ds.Tables[0].Columns.Contains("NumberOfCylinder") ) { if (ds != null) { if (ds.Tables[0].Rows.Count > 5000) { lblErrorMsg.Text = "Upload Only 5000 rows Only!"; return; } else if (Convert.ToInt32(txtRecordCount.Text) != Convert.ToInt32(ds.Tables[0].Rows.Count)) { lblErrorMsg.Text = "Records Count Does Not Match!"; return; } else if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (ds.Tables[0].Rows[i]["Make"].ToString().Trim() == "") { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Make in the Excel sheet"; } else if (ds.Tables[0].Rows[i]["Model"].ToString().Trim() == "") { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Valid Model in the Excel sheet"; } else if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["year"].ToString().Trim())) { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Numeric Year in the Excel sheet"; } else if (!GeneralFunc.CheckCurrentYear(ds.Tables[0].Rows[i]["year"].ToString().Trim())) { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Year which should be less than or equal to current year in Excel Sheet "; } else if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["Mileage"].ToString().Trim())) { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Numeric Mileage in the Excel sheet"; } if (ds.Tables[0].Rows[i]["Price"].ToString().Trim() == "") { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Price in the Excel sheet"; } else if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["Price"].ToString().Trim())) { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Numeric Price in the Excel sheet"; } else if (ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim() == "") { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please Enter Dealer UniqueID in the Excel sheet"; } DealerActions objActions = new DealerActions(); DataSet dsCheckUser = objActions.DealerCheckUniqueID(ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(), Session[Constants.DealerCode].ToString()); if (dsCheckUser.Tables[0].Rows.Count > 0) { dsError.Tables["Table1"].Rows.Add(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["DealerUniqueID"] = ds.Tables[0].Rows[i]["DealerUniqueID"].ToString().Trim(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString(); dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Dealer Unique already exists"; } } if (dsError.Tables[0].Rows.Count > 0) { grdIntroInfo.DataSource = null; grdIntroInfo.DataBind(); recGrid.Style.Add("display", "block"); grdErrors.DataSource = dsError.Tables["Table1"].DefaultView; grdErrors.DataBind(); //Header.Visible = false; } else { grdErrors.DataSource = null; grdErrors.DataBind(); Header.Visible = true; recGrid.Style.Add("display", "block"); grdIntroInfo.DataSource = ds.Tables[0].DefaultView; grdIntroInfo.DataBind(); btnSubmit.Enabled = false; btnUpload.Enabled = true; } } } } else { btnUpload.Text = "Upload"; lblErrorMsg.Text = "Enter Valid Sales Excel Sheet Contain Proper Columns."; btnSubmit.Enabled = true; } } else { btnUpload.Text = "Upload"; lblErrorMsg.Text = "No Records access from file!"; btnSubmit.Enabled = true; } } } catch (Exception ex) { throw ex; } }