Hi BugHunter,

I have created a sample please take its refrence and correct your code.

For this i have used ClosedXML libray. You can get more details from the below articles.

Read (Import) Excel file without OLEDB, Microsoft Office or Interop Library in C# and VB.Net

Export SQL data to Excel with Column Headers (Column Names) in C# and VB.Net

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dtExcel = ImportExcel(@"D:\Test.xls");
    DataTable dtNew = new DataTable();
    dtNew.Columns.Add("uniqueCode", typeof(Int32));
    dtNew.Columns.Add("name1", typeof(string));
    dtNew.Columns.Add("name2", typeof(string));
    dtNew.Columns.Add("gender", typeof(string));
    dtNew.Columns.Add("col2", typeof(string));
    dtNew.Columns.Add("col4", typeof(string));
    dtNew.Columns.Add("col6", typeof(string));
    for (int i = 0; i < dtExcel.Rows.Count; i++)
    {
        DataRow dr = dtNew.NewRow();
        dr["uniqueCode"] = dtExcel.Rows[i]["id"];
        dr["name1"] = dtExcel.Rows[i]["first_name"];
        dr["name2"] = dtExcel.Rows[i]["last_name"];
        dr["gender"] = dtExcel.Rows[i]["gender"];
        dr["col2"] = dtExcel.Rows[i]["email"];
        dr["col4"] = "";
        dr["col6"] = dtExcel.Rows[i]["email"];
        dtNew.Rows.Add(dr);
    }

    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dtNew, "Details");
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=Details.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

private DataTable ImportExcel(String strFilePath)
{
    string filePath = @"C:\Users\anand\Desktop\Test.xlsx";

    //Create a new DataTable.
    DataTable dt = new DataTable();
    //Open the Excel file using ClosedXML.
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        //Read the first Sheet from Excel file.
        IXLWorksheet workSheet = workBook.Worksheet(1);

        //Loop through the Worksheet rows.
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            //Use the first row to add columns to DataTable.
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }
    return dt;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dtExcel As DataTable = ImportExcel("C:\Users\anand\Desktop\Test.xls")
    Dim dtNew As DataTable = New DataTable()
    dtNew.Columns.Add("uniqueCode", GetType(Int32))
    dtNew.Columns.Add("name1", GetType(String))
    dtNew.Columns.Add("name2", GetType(String))
    dtNew.Columns.Add("gender", GetType(String))
    dtNew.Columns.Add("col2", GetType(String))
    dtNew.Columns.Add("col4", GetType(String))
    dtNew.Columns.Add("col6", GetType(String))
    For i As Integer = 0 To dtExcel.Rows.Count - 1
        Dim dr As DataRow = dtNew.NewRow()
        dr("uniqueCode") = dtExcel.Rows(i)("id")
        dr("name1") = dtExcel.Rows(i)("first_name")
        dr("name2") = dtExcel.Rows(i)("last_name")
        dr("gender") = dtExcel.Rows(i)("gender")
        dr("col2") = dtExcel.Rows(i)("email")
        dr("col4") = ""
        dr("col6") = dtExcel.Rows(i)("email")
        dtNew.Rows.Add(dr)
    Next
    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(dtNew, "Details")
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=Details.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Private Function ImportExcel(ByVal strFilePath As String) As DataTable
    Dim filePath As String = "C:\Users\anand\Desktop\Test.xlsx"
    Dim dt As DataTable = New DataTable()
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next
    End Using
    Return dt
End Function

Screenshots

Excel File

Output Excel