Hi mahesh213,

I have created 3 boolean method to validate Number, Letter and Date.

You need to first read the Excel data to DataTable. Then loop through all the rows and validate the column with Number, Letter and Date.

If any one column fails then set message in ViewBag and break the loop to display the message.

If all rows are valid then insert in database using SqlBulkCopy class.

Check the below sample code.

Namespaces

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Index(HttpPostedFileBase postedFile)
    {
        string filePath = string.Empty;
        if (postedFile != null)
        {
            string path = Server.MapPath("~/Uploads/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            filePath = path + Path.GetFileName(postedFile.FileName);
            string extension = Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(filePath);

            string conString = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03.
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 and above.
                    conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }

            DataTable dt = new DataTable();
            conString = string.Format(conString, filePath);

            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;
                        //Get the name of First Sheet.
                        connExcel.Open();
                        DataTable dtExcelSchema;
                        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        connExcel.Close();
                        //Read Data from First Sheet.
                        connExcel.Open();
                        cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                        odaExcel.SelectCommand = cmdExcel;
                        odaExcel.Fill(dt);
                        connExcel.Close();
                    }
                }
            }
            bool isAllValid = true;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string age = dt.Rows[i]["Age"].ToString();
                string name = dt.Rows[i]["Name"].ToString();
                string date = dt.Rows[i]["Date"].ToString();
                // Check Age is Number or not.
                if (!IsNumber(age))
                {
                    ViewBag.Message = "Invalid Age at row " + (i + 1) + " in excel.";
                    isAllValid = false;
                    break;
                }
                // Check Name is Number or not.
                if (!IsLetter(name))
                {
                    ViewBag.Message = "Invalid Name at row " + (i + 1) + " in excel.";
                    isAllValid = false;
                    break;
                }
                // Check Date is Number or not.
                if (!IsDate(date))
                {
                    ViewBag.Message = "Invalid Date at row " + (i + 1) + " in excel.";
                    isAllValid = false;
                    break;
                }
            }

            if (isAllValid)
            {
                conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
                using (SqlConnection con = new SqlConnection(conString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name.
                        sqlBulkCopy.DestinationTableName = "dbo.Customers";
                        //[OPTIONAL]: Map the Excel columns with that of the database table.
                        sqlBulkCopy.ColumnMappings.Add("Age", "Age");
                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                        sqlBulkCopy.ColumnMappings.Add("Date", "Date");
                        con.Open();
                        //sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }
            }
        }

        return View();
    }

    private bool IsNumber(string value)
    {
        return value.All(char.IsDigit);
    }

    private bool IsLetter(string value)
    {
        Regex regexLetter = new Regex(@"^[a-zA-Z]+$");
        return regexLetter.IsMatch(value);
    }

    private bool IsDate(string value)
    {
        Regex regex = new Regex(@"(((0|1)[0-9]|2[0-9]|3[0-1])\/(0[1-9]|1[0-2])\/((19|20)\d\d))$");
        //Verify whether date entered in dd/MM/yyyy format.
        bool isValid = regex.IsMatch(value);
        //Verify whether entered date is Valid date.
        DateTime dt;
        isValid = DateTime.TryParseExact(value, "dd/MM/yyyy", new CultureInfo("en-GB"), DateTimeStyles.None, out dt);
        return isValid;
    }
}

View

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }
    </style>
</head>
<body>
    @using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        <input type="file" name="postedFile" />
        <input type="submit" value="Import" />
    }
    @if (ViewBag.Message != null)
    {
        <script type="text/javascript">
            window.onload = function () {
                alert("@ViewBag.Message");
            };
        </script>
    }
</body>
</html>

Screenshots

The Excel

Validation message