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;
}
}
