Import excel with Column having multiple types in ASP.net

Note:

• Setting “IMEX=1” in the connection of the Excel sheet. This can be set in the connection string in case of Excel Source (Excel 2003) and can be set using ‘Extended Properties’ in case of OLE DB Source (Excel 2007). This setting tells that excel sheet is having mixed data types and thus import it as ‘Text’ values.

function getFileExtension(filename) {
    var ext = /^.+\.([^.]+)$/.exec(filename);
    return ext == null ? "" : ext[1];
}
function SelectFile() {
    var objFileName = $(".fileUploadCss").val();
    var extension = getFileExtension(objFileName);
    if (extension == 'XLS' || extension == 'xls' || extension == 'xlsx' || extension == 'XLSX') {
        if (objFileName != "") {
            $(".btnImp").removeAttr('disabled', 'false');
        }
        else {
            $(".btnImp").attr('disabled', 'true');
        }
    }
    else {
        $(".btnImp").attr('disabled', 'true');
        alert("Please select excel file only.");
    }

}
<asp:FileUpload ID="FileUpload1" runat="server" OnChange="SelectFile()" CssClass="fileUploadCss" />
<asp:Button ID="btnImport" runat="server" Text="Import Employees" CssClass="btnImp actWinForSelected-button-import"
                        OnClick="btnImport_Click" Enabled="false" CausesValidation="False" />

protected void btnImport_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
 try
 {
     string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
     string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
     string fileLocation = Server.MapPath("~/App_Data/" + fileName);
     FileUpload1.SaveAs(fileLocation);
     BusinessLayer.ExcelUpload excel = new BusinessLayer.ExcelUpload();
     gvEmployeeDetails.DataSource = excel.ExcelUploadSystem(fileLocation, fileExtension);
     gvEmployeeDetails.DataBind();
     //divGridBox.Visible = true;
    
 }
 catch (Exception ex)
 {
     ShowMessage("error", "Some error occur please try again: ");
     ExceptionLogger.Error("btnImport_Click(): Error occured while processing your request", ex);
     throw ex;
 }
    }
    else
    {
 ShowMessage("error", "Please select a excel file: ");
 ExceptionLogger.Info("btnImport_Click(): Select only excel file");
    }
}
public List<Entities.Employee> ExcelUploadSystem(string fileLocation, string fileExtension)
{
   
   DataAccess.ExcelUpload excelUpload = new DataAccess.ExcelUpload();
   DataTable dt = excelUpload.GetEmployeeList(fileLocation, fileExtension);
   StringBuilder excelData = new StringBuilder();
   string strData = string.Empty;
   if (dt.Rows.Count > 0)
   {
       for (int i = 0; i < dt.Rows.Count; i++)
       {
    excelData.Append("'" + appendLeadingZeros(dt.Rows[i][0].ToString()) + "'" + ",");
       }
       strData = excelData.ToString();
       strData = strData.Remove(strData.Length - 1, 1);             
   }          
   List<Entities.Employee> EmployeeList = GetEmployee(strData);
   return EmployeeList;

}
private string appendLeadingZeros(string BecRef)
{
   switch (BecRef.Trim().Length)
   {
       case 0:
    BecRef = "000000" + BecRef;
    break;
       case 1:
    BecRef = "00000" + BecRef;
    break;
       case 2:
    BecRef = "0000" + BecRef;
    break;
       case 3:
    BecRef = "000" + BecRef;
    break;
       case 4:
    BecRef = "00" + BecRef;
    break;
       case 5:
    BecRef = "0" + BecRef;
    break;
   }
   return BecRef;
}
//Match Excel becref with database
public List<Entities.Employee> GetEmployee(string BecRef)
{
      Entities.Employee data = null;
      List<Entities.Employee> empList = new List<Entities.Employee>();
       DataAccess.Employee emp = new DataAccess.Employee();
       using (IDataReader reader = emp.GetExcelEmployee(BecRef))
       {
    if (reader!=null)
    {
        while (reader.Read())
        {
     data = new Entities.Employee();
     data.BecRef = Convert.ToString(reader["BecRef"]);
     data.EmployeeNumber = appendLeadingZeros(reader["Employee_Number"].ToString());
     data.BunId = Convert.ToString(reader["Employee_Bun_Id"]);
     data.EmployeeName = Convert.ToString(reader["Employee_Name"]);
     //data.Grade = Convert.ToInt16(reader["Grade"]);
     data.BaseOrg = Convert.ToString(reader["Base_Org_Unit"]);
     data.FromData = "EXCEL";
     empList.Add(data);
        }
    }
       }
       return empList;
}
public string CreateConnectionString(string fileLocation, string fileExtension)
{
   string connectionString = string.Empty;
   if (fileExtension == ".xls")
   {
       connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
   }
   else if (fileExtension == ".xlsx")
   {
       connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
   }
   return connectionString;
}
public DataTable GetEmployeeList(string fileLocation, string fileExtension)
{
   OleDbConnection con = new OleDbConnection(CreateConnectionString(fileLocation,fileExtension));
   OleDbCommand cmd = new OleDbCommand();
   cmd.CommandType = System.Data.CommandType.Text;
   cmd.Connection = con;
   OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
   DataTable dtExcelRecords = new DataTable();
   con.Open();
   DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
   string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
   cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
   dAdapter.SelectCommand = cmd;
   dAdapter.Fill(dtExcelRecords);
   con.Close();
   return dtExcelRecords;
}
public IDataReader GetExcelEmployee(string BecRef)
{
    IDataReader reader = null;
    try
    {
 reader = dbHelper.ExecuteReader(GetCmdEmployeeList(BecRef));
    }
    catch
    {
 throw ;
    }
    return reader;
}
private DbCommand GetCmdEmployeeList(string BecRef)
{
    DbCommand cmd = null;
    try
    {
 string SQL_QUERY =
 "select Employee_Bun_Id,Employee_Name,BecRef,Employee_Number,Base_Org_Unit from EmployeeDetails where becRef in(" + BecRef + ") and ACTIVE='Y' And ISNULL(UPPER(EmpType), '') = 'PERMANENT'";
 cmd = dbHelper.CreateCommand(SQL_QUERY);
    }
    catch
    {
 throw ;
    }
    return cmd;
}