private bool UploadExcel()
    {
        try
        {
            string FILENAME = string.Empty;
            string OpenPath = string.Empty;
            string SaveLoc = string.Empty;
            string FileExt = string.Empty;
            string hp = string.Empty;
            ExcelReading objExcelData = new ExcelReading();
            string sFileName = string.Empty;
            FILENAME = fupExcelFile.PostedFile.FileName;

            if (FILENAME.Contains("\\"))
            {
                string[] strFile = FILENAME.Split('\\');
                int max = strFile.Length - 1;
                FILENAME = strFile[max].ToString();
            }

            //It is date-username-filename.3digit unique for the day and user numeric ext .xls
            //20111103-admin-filenamemax15char.001.xls
            SaveLoc = Server.MapPath("LeadBulkFiles\\");

            if (System.IO.Directory.Exists(SaveLoc) == false)
            {
                System.IO.DirectoryInfo di = System.IO.Directory.CreateDirectory(SaveLoc);
            }
            SaveLoc = Server.MapPath("LeadBulkFiles\\" + FILENAME);

            fupExcelFile.PostedFile.SaveAs(SaveLoc);

            DataSet ds = objExcelData.GetSalesExcelToDatasetLeads(SaveLoc);
            Session["ExcelDataset"] = ds;
            if (ds.Tables[0].Rows.Count > 0)
            {
                SaveData(ds);
            }

        }

        catch (Exception ex)
        {
            Response.Redirect("Error.aspx");
        }
        return true;
    }
    private void ReadExcelData(string sFileName)
    {
        ArrayList RowNo = new ArrayList();
        ArrayList ColNo = new ArrayList();

        ArrayList SArray = new ArrayList();

        LeadsBL objLeadsBL = new LeadsBL();

        ExcelReading objExcelData = new ExcelReading();

        DataSet ds = new DataSet();
        DataSet dsStatus = new DataSet();

        DataSet dsSales = new DataSet();
        bool bnew = false;
        try
        {

            lblErrorMsg.Text = "";
            ds = objExcelData.GetLeadsExcelToDataset(sFileName);

            DataSet dsError = new DataSet();

            dsError.Tables.Add();
            dsError.Tables["Table1"].Columns.Add("PhoneNo");
            dsError.Tables["Table1"].Columns.Add("RowNo");
            dsError.Tables["Table1"].Columns.Add("Error");

            if (ds.Tables[0].Columns.Contains("PhoneNo") && ds.Tables[0].Columns.Contains("Price") &&
                ds.Tables[0].Columns.Contains("Header") &&
                ds.Tables[0].Columns.Contains("Description") && (ds.Tables[0].Columns.Contains("URL") || ds.Tables[0].Columns.Contains("City")) &&
                ds.Tables[0].Columns.Contains("State") && ds.Tables[0].Columns.Contains("Lead_Date") &&
                ds.Tables[0].Columns.Contains("Make") &&
                    ds.Tables[0].Columns.Contains("Model") &&
                        ds.Tables[0].Columns.Contains("Email") &&
                            ds.Tables[0].Columns.Contains("Year") &&
                                ds.Tables[0].Columns.Contains("VehicleType"))
            {
                int count = objExcelData.GetExcelDistictBTNCOunt_Sales(sFileName);
                ViewState["Count"] = count.ToString();
                if (ds != null)
                {

                    if (ds.Tables[0].Rows.Count > 8000)
                    {
                        lblErrorMsg.Text = "Upload Only 8000 rows Only!";
                        return;
                    }
                    else if (Convert.ToInt32(txtNoofRecords.Text) != Convert.ToInt32(ds.Tables[0].Rows.Count))
                    {
                        lblErrorMsg.Text = "Records Count Does Not Match!";
                        return;
                    }
                    if (ds.Tables[0].Rows.Count == count)
                    {

                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            //return;
                            //RowNo.Add(i);

                            if (ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim() == ""
                             || ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim().Length != 10
                             || !GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 phone number in the excel sheet";

                            }
                            //else if (ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim().Length != 10)
                            //{
                            //    dsError.Tables["Table1"].Rows.Add();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 phone number in the excel sheet";

                            //}
                            //else if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            //{
                            //    dsError.Tables["Table1"].Rows.Add();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 phone number in the excel sheet";
                            //}
                            else if (CheckBTN(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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"] = "Phone number already exists in database.";

                            }
                            else
                            {
                                if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == ""
                                 || ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length != 2
                                 || ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == null)
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 the state code in the excel sheet";

                                }
                                //else if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length != 2)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Phoneno"] = ds.Tables[0].Rows[i]["Phoneno"].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 the proper state code in the excel Sheet";
                                //}
                                //if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == null)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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"] = "Error in record state code is not valid..";

                                //}

                                else if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length == 2)
                                {
                                    string Stateid = GetStateId(ds.Tables[0].Rows[i]["STATE"].ToString().Trim().ToUpper());
                                    if (Stateid == "0")
                                    {
                                        btnUpload.Text = "Upload";
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 check the record in the sheet, state is invalid.";

                                        ColNo.Add(12);
                                    }
                                }
                                //if (ds.Tables[0].Rows[i]["Price"].ToString().Trim().Length < 0)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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";

                                //}
                                if (ds.Tables[0].Rows[i]["Price"].ToString().Trim() != "")
                                {
                                    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]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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";
                                    }
                                }

                                if (ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim() == "")
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 lead date in the excel sheet";

                                }
                                else if (!GeneralFunc.ValidateDate(ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim()))
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 lead date in the excel sheet";
                                }
                                else
                                {
                                    DataSet dsDatetime = objHotLeadBL.GetDatetime();
                                    DateTime dtNow = Convert.ToDateTime(dsDatetime.Tables[0].Rows[0]["Datetime"].ToString());
                                    if (Convert.ToDateTime(ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim()) > dtNow)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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"] = "Lead date cannot be greater than today date";
                                    }

                                }
                                if (ds.Tables[0].Rows[i]["Email"].ToString().Trim() != "")
                                {
                                    if (!GeneralFunc.isEmail(ds.Tables[0].Rows[i]["Email"].ToString().Trim()))
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 email in the excel sheet";

                                    }
                                }
                                if (ds.Tables[0].Rows[i]["Year"].ToString().Trim() != "")
                                {
                                    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]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 year in the excel sheet";

                                    }
                                    else if (ds.Tables[0].Rows[i]["Year"].ToString().Trim().Length != 4)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 year in the excel sheet";
                                    }
                                    else
                                    {
                                        DataSet dsDatetime = objHotLeadBL.GetDatetime();
                                        DateTime dtNow = Convert.ToDateTime(dsDatetime.Tables[0].Rows[0]["Datetime"].ToString());
                                        DateTime EnterDate = Convert.ToDateTime("1/1/" + ds.Tables[0].Rows[i]["Year"].ToString().Trim());
                                        if (EnterDate > dtNow)
                                        {
                                            dsError.Tables["Table1"].Rows.Add();
                                            dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 year in the excel sheet";
                                        }
                                    }
                                }
                            }
                            VehicleTypeBL objVehicleTypeBL = new VehicleTypeBL();

                            DataSet dsVehicleTypes = new DataSet();

                            if (Cache["VehicleType"] == null)
                            {
                                dsVehicleTypes = objVehicleTypeBL.GetVehicleType();
                                Cache["VehicleType"] = dsVehicleTypes;
                            }
                            else
                            {
                                dsVehicleTypes = (DataSet)Cache["VehicleType"];
                            }
                            int cint = 0;

                            foreach (DataRow row in dsVehicleTypes.Tables[0].Rows) // Loop over the rows.
                            {
                                if (row.ItemArray[0].ToString().ToLower() == ds.Tables[0].Rows[i]["VehicleType"].ToString().Trim().ToLower())
                                {
                                    cint = cint + 1;
                                    break;
                                }
                            }
                            if (cint == 0)
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 Proper Vehicle Type";
                            }
                            cint = 0;
                            LeadSourceBL objLeadSourceBL = new LeadSourceBL();
                            DataSet dsLeadSource = new DataSet();
                            if (Cache["LeadSource"] == null)
                            {
                                dsLeadSource = objLeadSourceBL.GetLeadSources();
                                Cache["LeadSource"] = dsVehicleTypes;
                            }
                            else
                            {
                                dsLeadSource = (DataSet)Cache["LeadSource"];
                            }
                            foreach (DataRow row in dsLeadSource.Tables[0].Rows) // Loop over the rows.
                            {
                                if (row.ItemArray[0].ToString().ToLower() == ds.Tables[0].Rows[i]["LeadSourceID"].ToString().Trim().ToLower())
                                {
                                    cint = cint + 1;
                                    break;
                                }
                            }
                            if (cint == 0)
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 Proper Lead Source ID";
                            }
                        }

                        if (dsError.Tables["Table1"].Rows.Count > 0)
                        {
                            grdIntroInfo.DataSource = null;
                            grdIntroInfo.DataBind();
                            grdErrors.DataSource = dsError.Tables["Table1"].DefaultView;
                            grdErrors.DataBind();
                            Header.Visible = false;

                        }
                        else
                        {
                            grdErrors.DataSource = null;
                            grdErrors.DataBind();
                            Header.Visible = true;
                            grdIntroInfo.DataSource = ds.Tables["XLData"].DefaultView;
                            grdIntroInfo.DataBind();
                            btnSubmit.Enabled = false; ;
                            btnUpload.Enabled = true;
                        }
                    }
                    else
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            int phoneCount = 0;
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                if (ds.Tables[0].Rows[j]["PhoneNo"].ToString() == ds.Tables[0].Rows[i]["PhoneNo"].ToString())
                                {

                                    phoneCount = phoneCount + 1;
                                    if (phoneCount > 1)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (j + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Exist Duplicates PhoneNo In Excel Sheet Plz Check Excel Sheet";
                                    }
                                    ColNo.Add(8);

                                }
                            }
                        }

                        if (dsError.Tables["Table1"].Rows.Count > 0)
                        {
                            grdIntroInfo.DataSource = null;
                            grdIntroInfo.DataBind();
                            grdErrors.DataSource = dsError.Tables["Table1"].DefaultView;
                            grdErrors.DataBind();
                            Header.Visible = false;

                        }
                    }
                }
            }

            else
            {
                btnUpload.Text = "Upload";
                lblErrorMsg.Text = "Enter Valid Sales Excel Sheet Proper Columns.";
                btnSubmit.Enabled = true;
            }
        }
        catch (Exception ex)
        {
            if (ex.Message.ToString() == "Not a legal OleAut date.")
            {
                Header.Visible = false;
                lblErrorMsg.Text = "Enter valid date format in excel sheet";
            }
            else
            {
                throw ex;
            }

        }
    }
Exemple #3
0
        /// <summary>
        /// 从Excel读取数据表
        /// </summary>
        private DataTable getData(string pathName, string sheetName, string HDR = "NO")
        {
            ExcelReading er = new ExcelReading(pathName, sheetName, HDR);

            return(er.dataTable);
        }
    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;
        }
    }
Exemple #5
0
    private void ReadExcelData(string sFileName)
    {
        ArrayList RowNo = new ArrayList();
        ArrayList ColNo = new ArrayList();

        ArrayList SArray = new ArrayList();

        LeadsBL objLeadsBL = new LeadsBL();

        ExcelReading objExcelData = new ExcelReading();

        DataSet ds       = new DataSet();
        DataSet dsStatus = new DataSet();

        DataSet dsSales = new DataSet();
        bool    bnew    = false;

        try
        {
            lblErrorMsg.Text = "";
            ds = objExcelData.GetLeadsExcelToDataset(sFileName);

            DataSet dsError = new DataSet();

            dsError.Tables.Add();
            dsError.Tables["Table1"].Columns.Add("PhoneNo");
            dsError.Tables["Table1"].Columns.Add("RowNo");
            dsError.Tables["Table1"].Columns.Add("Error");



            if (ds.Tables[0].Columns.Contains("PhoneNo") && ds.Tables[0].Columns.Contains("Price") &&
                ds.Tables[0].Columns.Contains("Header") &&
                ds.Tables[0].Columns.Contains("Description") && (ds.Tables[0].Columns.Contains("URL") || ds.Tables[0].Columns.Contains("City")) &&
                ds.Tables[0].Columns.Contains("State") && ds.Tables[0].Columns.Contains("Lead_Date") &&
                ds.Tables[0].Columns.Contains("Make") &&
                ds.Tables[0].Columns.Contains("Model") &&
                ds.Tables[0].Columns.Contains("Email") &&
                ds.Tables[0].Columns.Contains("Year") &&
                ds.Tables[0].Columns.Contains("VehicleType"))
            {
                int count = objExcelData.GetExcelDistictBTNCOunt_Sales(sFileName);
                ViewState["Count"] = count.ToString();
                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 8000)
                    {
                        lblErrorMsg.Text = "Upload Only 8000 rows Only!";
                        return;
                    }
                    else if (Convert.ToInt32(txtNoofRecords.Text) != Convert.ToInt32(ds.Tables[0].Rows.Count))
                    {
                        lblErrorMsg.Text = "Records Count Does Not Match!";
                        return;
                    }
                    if (ds.Tables[0].Rows.Count == count)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            //return;
                            //RowNo.Add(i);

                            if (ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim() == "" ||
                                ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim().Length != 10 ||
                                !GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 phone number in the excel sheet";
                            }
                            //else if (ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim().Length != 10)
                            //{
                            //    dsError.Tables["Table1"].Rows.Add();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 phone number in the excel sheet";

                            //}
                            //else if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            //{
                            //    dsError.Tables["Table1"].Rows.Add();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 phone number in the excel sheet";
                            //}
                            else if (CheckBTN(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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"]   = "Phone number already exists in database.";
                            }
                            else
                            {
                                if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == "" ||
                                    ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length != 2 ||
                                    ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == null)
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 the state code in the excel sheet";
                                }
                                //else if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length != 2)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Phoneno"] = ds.Tables[0].Rows[i]["Phoneno"].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 the proper state code in the excel Sheet";
                                //}
                                //if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == null)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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"] = "Error in record state code is not valid..";

                                //}

                                else if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length == 2)
                                {
                                    string Stateid = GetStateId(ds.Tables[0].Rows[i]["STATE"].ToString().Trim().ToUpper());
                                    if (Stateid == "0")
                                    {
                                        btnUpload.Text = "Upload";
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 check the record in the sheet, state is invalid.";

                                        ColNo.Add(12);
                                    }
                                }
                                //if (ds.Tables[0].Rows[i]["Price"].ToString().Trim().Length < 0)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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";

                                //}
                                if (ds.Tables[0].Rows[i]["Price"].ToString().Trim() != "")
                                {
                                    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]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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";
                                    }
                                }

                                if (ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim() == "")
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 lead date in the excel sheet";
                                }
                                else if (!GeneralFunc.ValidateDate(ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim()))
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 lead date in the excel sheet";
                                }
                                else
                                {
                                    DataSet  dsDatetime = objHotLeadBL.GetDatetime();
                                    DateTime dtNow      = Convert.ToDateTime(dsDatetime.Tables[0].Rows[0]["Datetime"].ToString());
                                    if (Convert.ToDateTime(ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim()) > dtNow)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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"]   = "Lead date cannot be greater than today date";
                                    }
                                }
                                if (ds.Tables[0].Rows[i]["Email"].ToString().Trim() != "")
                                {
                                    if (!GeneralFunc.isEmail(ds.Tables[0].Rows[i]["Email"].ToString().Trim()))
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 email in the excel sheet";
                                    }
                                }
                                if (ds.Tables[0].Rows[i]["Year"].ToString().Trim() != "")
                                {
                                    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]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 year in the excel sheet";
                                    }
                                    else if (ds.Tables[0].Rows[i]["Year"].ToString().Trim().Length != 4)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 year in the excel sheet";
                                    }
                                    else
                                    {
                                        DataSet  dsDatetime = objHotLeadBL.GetDatetime();
                                        DateTime dtNow      = Convert.ToDateTime(dsDatetime.Tables[0].Rows[0]["Datetime"].ToString());
                                        DateTime EnterDate  = Convert.ToDateTime("1/1/" + ds.Tables[0].Rows[i]["Year"].ToString().Trim());
                                        if (EnterDate > dtNow)
                                        {
                                            dsError.Tables["Table1"].Rows.Add();
                                            dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 year in the excel sheet";
                                        }
                                    }
                                }
                            }
                            VehicleTypeBL objVehicleTypeBL = new VehicleTypeBL();

                            DataSet dsVehicleTypes = new DataSet();

                            if (Cache["VehicleType"] == null)
                            {
                                dsVehicleTypes       = objVehicleTypeBL.GetVehicleType();
                                Cache["VehicleType"] = dsVehicleTypes;
                            }
                            else
                            {
                                dsVehicleTypes = (DataSet)Cache["VehicleType"];
                            }
                            int cint = 0;

                            foreach (DataRow row in dsVehicleTypes.Tables[0].Rows) // Loop over the rows.
                            {
                                if (row.ItemArray[0].ToString().ToLower() == ds.Tables[0].Rows[i]["VehicleType"].ToString().Trim().ToLower())
                                {
                                    cint = cint + 1;
                                    break;
                                }
                            }
                            if (cint == 0)
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 Proper Vehicle Type";
                            }
                            cint = 0;
                            LeadSourceBL objLeadSourceBL = new LeadSourceBL();
                            DataSet      dsLeadSource    = new DataSet();
                            if (Cache["LeadSource"] == null)
                            {
                                dsLeadSource        = objLeadSourceBL.GetLeadSources();
                                Cache["LeadSource"] = dsVehicleTypes;
                            }
                            else
                            {
                                dsLeadSource = (DataSet)Cache["LeadSource"];
                            }
                            foreach (DataRow row in dsLeadSource.Tables[0].Rows) // Loop over the rows.
                            {
                                if (row.ItemArray[0].ToString().ToLower() == ds.Tables[0].Rows[i]["LeadSourceID"].ToString().Trim().ToLower())
                                {
                                    cint = cint + 1;
                                    break;
                                }
                            }
                            if (cint == 0)
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].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 Proper Lead Source ID";
                            }
                        }

                        if (dsError.Tables["Table1"].Rows.Count > 0)
                        {
                            grdIntroInfo.DataSource = null;
                            grdIntroInfo.DataBind();
                            grdErrors.DataSource = dsError.Tables["Table1"].DefaultView;
                            grdErrors.DataBind();
                            Header.Visible = false;
                        }
                        else
                        {
                            grdErrors.DataSource = null;
                            grdErrors.DataBind();
                            Header.Visible          = true;
                            grdIntroInfo.DataSource = ds.Tables["XLData"].DefaultView;
                            grdIntroInfo.DataBind();
                            btnSubmit.Enabled = false;;
                            btnUpload.Enabled = true;
                        }
                    }
                    else
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            int phoneCount = 0;
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                if (ds.Tables[0].Rows[j]["PhoneNo"].ToString() == ds.Tables[0].Rows[i]["PhoneNo"].ToString())
                                {
                                    phoneCount = phoneCount + 1;
                                    if (phoneCount > 1)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (j + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Exist Duplicates PhoneNo In Excel Sheet Plz Check Excel Sheet";
                                    }
                                    ColNo.Add(8);
                                }
                            }
                        }

                        if (dsError.Tables["Table1"].Rows.Count > 0)
                        {
                            grdIntroInfo.DataSource = null;
                            grdIntroInfo.DataBind();
                            grdErrors.DataSource = dsError.Tables["Table1"].DefaultView;
                            grdErrors.DataBind();
                            Header.Visible = false;
                        }
                    }
                }
            }

            else
            {
                btnUpload.Text    = "Upload";
                lblErrorMsg.Text  = "Enter Valid Sales Excel Sheet Proper Columns.";
                btnSubmit.Enabled = true;
            }
        }
        catch (Exception ex)
        {
            if (ex.Message.ToString() == "Not a legal OleAut date.")
            {
                Header.Visible   = false;
                lblErrorMsg.Text = "Enter valid date format in excel sheet";
            }
            else
            {
                throw ex;
            }
        }
    }