Bulk Import from Excel file to SQl Server in ASP.NET

<asp:FileUpload ID="FileUpload1" runat="server" Width="250px" Height="25px" /><br />
<asp:RequiredFieldValidator ID="rfv" runat="server" ControlToValidate="FileUpload1"
ErrorMessage="Please select file to upload" Display="Dynamic" ValidationGroup="vgInsert" SetFocusOnError="True"></asp:RequiredFieldValidator>
<asp:Button ID="btnInsert" runat="server" OnClientClick="showPleaseWait()" OnClick="btnInsert_Click"
Text="Save" Width="85px" Visible="false" ValidationGroup="vgInsert" />
protected void btnInsert_Click(object sender, EventArgs e)
{
    if (Page.IsValid)
    {
 if (FileUpload1.HasFile)
 {
     foreach (Process process in Process.GetProcessesByName("EXCEL"))
     {
  if (process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE"))
  {
      process.Kill();
      Thread.Sleep(2000);
      break;
  }
     }
     try
     {
  sFilePath = Path.GetFileName(FileUpload1.FileName);
  if (Path.GetExtension(sFilePath) == ".xlsx" || Path.GetExtension(sFilePath) == ".xls")
  {
      FileUpload1.SaveAs(Server.MapPath(this.UploadFolderPath) + "\\" + sFilePath);
  }
  else
  {
      Label1.Text = "Please upload excel file only.";
      return;
  }
  DateTime dDate = Convert.ToDateTime(txtDate.Text);
  using (OleDbConnection connection = new OleDbConnection(CreateConnectionString(Server.MapPath(this.UploadFolderPath) + "\\" +sFilePath, Path.GetExtension(FileUpload1.PostedFile.FileName))))
  {
      OleDbCommand command = new OleDbCommand("Select * , '" + dDate.ToString("yyyy-MM-dd") + "' as DateExtracted, '" + ddlProjectName.SelectedValue + "' As ProjectName, '" + ddlArea.SelectedValue + "' As Area" + " FROM [Sheet1$]", connection);
      connection.Open();
      // Create DbDataReader to Data Worksheet
      using (DbDataReader dr = command.ExecuteReader())
      {
   // SQL Server Connection String
   string sqlConnectionString = connectionString;
   // Bulk Copy to SQL Server
   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
   {
       bulkCopy.DestinationTableName = TABLENAME;
       ColumnMapping(bulkCopy);
       try
       {
    bulkCopy.WriteToServer(dr);
       }
       catch (Exception ex)
       {
    Console.WriteLine(ex.Message);
       }
       finally
       {
    connection.Close();
       }
       Label5.Text = "File Data imported succesfully";
   }
      }
  }
     }
     catch (Exception ex)
     {
  Label1.Text = "Upload status: The file could not be uploaded.The following error occured:" + ex.Message;
     }
 }
 else
 {
     Label1.Text = "Upload status: The file could not be uploaded.";
 }
    }
}
private void ColumnMapping(SqlBulkCopy bulkCopy)
{
    bulkCopy.ColumnMappings.Add("ID", "ClashID");
    bulkCopy.ColumnMappings.Add("Status", "ClashStatus");
    bulkCopy.ColumnMappings.Add("Type", "ClashType");
    bulkCopy.ColumnMappings.Add("P G", "ClashPG");
    bulkCopy.ColumnMappings.Add("OID", "ClashOID");
    bulkCopy.ColumnMappings.Add("Remark", "ClashRemarks");
    bulkCopy.ColumnMappings.Add("A-System Path", "PartASystemPath");
    bulkCopy.ColumnMappings.Add("A-Name", "PartAName");
    //bulkCopy.ColumnMappings.Add("A-Parent Name", "L_Name");
    bulkCopy.ColumnMappings.Add("A-Aspect", "PartAAspect");
    bulkCopy.ColumnMappings.Add("A-Clashes", "PartAClashes");
    bulkCopy.ColumnMappings.Add("A-P G", "PartAPG");
    bulkCopy.ColumnMappings.Add("A-OID", "PartAOID");
    bulkCopy.ColumnMappings.Add("B-System Path", "PartBSystemPath");
    bulkCopy.ColumnMappings.Add("B-Name", "PartBName");
    //bulkCopy.ColumnMappings.Add("B-Parent Name B", "City");
    bulkCopy.ColumnMappings.Add("B-Aspect", "PartBAspect");
    bulkCopy.ColumnMappings.Add("B-Clashes", "PartBClashes");
    bulkCopy.ColumnMappings.Add("B-P G", "PartBPG");
    bulkCopy.ColumnMappings.Add("B-OID", "PartBOID");
    bulkCopy.ColumnMappings.Add("DateExtracted", "DateExtracted");
    bulkCopy.ColumnMappings.Add("ProjectName", "ProjectName");
    bulkCopy.ColumnMappings.Add("Area", "Area");
}
public string CreateConnectionString(string fileLocation, string fileExtension)
{
    return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileLocation + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
}

Exporting to excel with formating and preserving starting(Leading) zeros

public static void ExportToExcel(DataTable dt, string fileNameWithoutExtension)
{
    try
    {
 if (dt.Rows.Count > 0)
 {
     //Note: For simple Export uncomment the below code and use it.
     //////System.IO.StringWriter tw = new System.IO.StringWriter();
     //////System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
     //////DataGrid dgGrid = new DataGrid();
     //////dgGrid.DataSource = dt;
     //////dgGrid.DataBind();
     ////////Get the HTML for the control.
     //////dgGrid.RenderControl(hw);
     ////////Write the HTML back to the browser.
     ////////Response.ContentType = application/vnd.ms-excel;
     //////HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
     //////HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileNameWithoutExtension.Trim() + ".xls" + "");
     ////////this.EnableViewState = false;
     //////HttpContext.Current.Response.Write(tw.ToString());
     //////HttpContext.Current.Response.End();

     //Note : For Exporting to excel with formating and preserving starting(Leading) zeros use below code.
     String style = "<style>.text{mso-number-format:\\@;}</style>";
     HttpContext.Current.Response.Clear();
     HttpContext.Current.Response.Buffer = true;
     HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileNameWithoutExtension.Trim() + ".xls" + "");
     HttpContext.Current.Response.ContentType = "application/excel";
     StringWriter sw = new StringWriter();
     System.IO.StringWriter ioSw = new System.IO.StringWriter();
     System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(ioSw);
     GridView GridView1 = new GridView();
     GridView1.AllowPaging = false;
     GridView1.DataSource = dt;
     GridView1.DataBind();
    
     //Change the Header Row
     GridView1.HeaderRow.Style.Add("background-color", "#cc0000");
    
     //Apply style to Individual Cells    
     GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
     for (int i = 0; i < GridView1.Rows.Count; i++)
     {
  GridViewRow row = GridView1.Rows[i];
  //Apply text style to each Row        
  row.Attributes.Add("class", "textmode");
  //Apply style to Individual Cells of Alternating Row        
  if (i % 2 != 0)        
  {
   row.Cells[1].Attributes.Add("class", "text");//Format individual Column as text to preserve leading zeros
   row.Cells[2].Style.Add("background-color", "#C2D69B");
  }
     }
     //Remove Controls    
     this.RemoveControls(GridView1);
     GridView1.RenderControl(htw);
     HttpContext.Current.Response.Output.Write(style + ioSw.ToString());
     HttpContext.Current.Response.Flush();
     HttpContext.Current.Response.End();
 }
 else
 {
     throw new Exception("No records to export");
 }
    }
    catch (Exception ex)
    {
 throw ex;
    }
}
private void RemoveControls(Control grid)
{
    Literal literal = new Literal();
    for (int i = 0; i < grid.Controls.Count; i++)
    {
 if (grid.Controls[i] is LinkButton)
 {
     literal.Text = (grid.Controls[i] as LinkButton).Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is DropDownList)
 {
     literal.Text = (grid.Controls[i] as DropDownList).SelectedItem.Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is CheckBox)
 {
     literal.Text = (grid.Controls[i] as CheckBox).Checked ? "True" : "False";
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is HyperLink)
 {
     literal.Text = (grid.Controls[i] as HyperLink).Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 else if (grid.Controls[i] is Button)
 {
     literal.Text = (grid.Controls[i] as Button).Text;
     grid.Controls.Remove(grid.Controls[i]);
     grid.Controls.AddAt(i, literal);
 }
 if (grid.Controls[i].HasControls())
 {
     RemoveControls(grid.Controls[i]);
 }
    }
}

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

Authenticate User using Windows login credentials

 public static bool ValidateW(string UserName, string Password, string DomainName)
        {
            bool valid = false;
            try
            {
               
                if (UserName.IndexOf("\\") != -1)
                {
                    string[] arrT = UserName.Split(SPLIT_1[0]);
                    UserName = arrT[1];
                }
                using (PrincipalContext context = new PrincipalContext(ContextType.Domain, DomainName))
                {
                    valid = context.ValidateCredentials(DomainName+"\\" + UserName, Password);
                   
                }
               
            }
            catch (Exception ex)
            {
                throw ex;               
            }
            return valid;
        }

        bool GetEmployee()
        {
            bool flag = false;
            Entities.Employee data = null;
            string userName = UserName.Text;
            try
            {
                if (userName.IndexOf("@") != -1)
                {
                    string[] arrT = userName.Split(SPLIT_2[0]);
                    userName = arrT[0];
                }
                BusinessLayer.Employee emp = new BusinessLayer.Employee();
                data = emp.GetEmployee(userName);               
                flag = true;
            }
            catch (Exception ex)
            {
               
            }
            return flag;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (ValidateW(UserName.Text, Password.Text, Domain.SelectedValue))
                {
                    if (GetEmployee())
                    {
                        if (Request.QueryString["ReturnUrl"] != null)
                        {
                            FormsAuthentication.RedirectFromLoginPage(UserName.Text, false);
                        }
                        else
                        {
                            FormsAuthentication.SetAuthCookie(UserName.Text, false);
                        }
                    }
                    else
                    {
                        FailureText.Text = "Your details does not exist in the system. Please contact Administrator.";
                    }
                }
                else
                {
                    FailureText.Text = "Your login attempt was not successful. Please try again.";
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

FormsAuthentication.SignOut() does not log the user out

FormsAuthentication.SignOut();
Session.Abandon();
// clear authentication cookie
HttpCookie cookie1 = new HttpCookie(FormsAuthentication.FormsCookieName, "");
cookie1.Expires = DateTime.Now.AddYears(-1);
Response.Cookies.Add(cookie1);
// clear session cookie (not necessary for your current problem but i would recommend you do it anyway)
HttpCookie cookie2 = new HttpCookie("ASP.NET_SessionId", "");
cookie2.Expires = DateTime.Now.AddYears(-1);
Response.Cookies.Add(cookie2);
FormsAuthentication.RedirectToLoginPage();