Import Excel data to HTML Table using jQuery Ajax in ASP.Net

Hi sanjay8090,

Check this example. Now please take its reference and correct your code.

HTML

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        var reader = new FileReader();
        $('input[type=file]').change(function () {
            if (typeof (FileReader) != "undefined") {
                var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
                $($(this)[0].files).each(function () {
                    var file = $(this);
                    if (regex.test(file[0].name.toLowerCase())) {
                        reader.readAsDataURL(file[0]);
                    } else {
                        alert(file[0].name + " is not a valid image file.");
                        return false;
                    }
                });
            } else {
                alert("This browser does not support HTML5 FileReader.");
            }
        });

        $('#btnUpload').on("click", function () {
            var byteData = reader.result;
            byteData = byteData.split(';')[1].replace("base64,", "");
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetExcelData",
                data: '{byteData: "' + byteData + '" }',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    var xmlDoc = $.parseXML(response.d);
                    var xml = $(xmlDoc);
                    var customers = xml.find("Table");
                    $("#tblCustomers").show();
                    var row = $("#tblCustomers tr:last-child").removeAttr("style").clone(true);
                    $("#tblCustomers tr").not($("#tblCustomers tr:first-child")).remove();
                    $.each(customers, function () {
                        $("td", row).eq(0).html($(this).find("Id").text());
                        $("td", row).eq(1).html($(this).find("Name").text());
                        $("td", row).eq(2).html($(this).find("Country").text());
                        $("#tblCustomers").append(row);
                        row = $("#tblCustomers tr:last-child").clone(true);
                    });
                },
                error: function (response) {
                    alert(response.responseText);
                }
            });
            return false;
        });
    });
</script>
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnUpload" Text="Upload" runat="server" />
<br />
<br />
<table id="tblCustomers" style="display:none">
    <tr>
        <th>Id</th>
        <th>Name</th>
        <th>Country</th>
    </tr>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Country</td>
    </tr>
</table>

Namespaces

C#

using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.Services;

VB.Net

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Web.Services

Code

C#

[WebMethod]
public static string GetExcelData(string byteData)
{
    byte[] bytes = Convert.FromBase64String(byteData);
    string filePath = HttpContext.Current.Server.MapPath("~/Files/Test.xls");
    if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/Files")))
    {
        Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/Files"));
    }

    // Save file in File folder.
    File.WriteAllBytes(filePath, bytes);

    string extension = Path.GetExtension(filePath);
    string excelConnectionString = "";
    switch (extension)
    {
        case ".xls": //Excel 97-03
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
            break;
        case ".xlsx": //Excel 07
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
            break;
    }
    excelConnectionString = String.Format(excelConnectionString, filePath);
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oleDA = new OleDbDataAdapter();
    cmdExcel.Connection = excelConnection;
    excelConnection.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    excelConnection.Close();
    excelConnection.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oleDA.SelectCommand = cmdExcel;
    DataSet ds = new DataSet();
    oleDA.Fill(ds);
    excelConnection.Close();
    // Delete saved file.
    Directory.Delete(HttpContext.Current.Server.MapPath("~/Files"), true);

    return ds.GetXml();
}

VB.Net

<WebMethod()>
Public Shared Function GetExcelData(ByVal byteData As String) As String
    Dim bytes As Byte() = Convert.FromBase64String(byteData)
    Dim filePath As String = HttpContext.Current.Server.MapPath("~/Files/Test.xls")

    If Not Directory.Exists(HttpContext.Current.Server.MapPath("~/Files")) Then
        Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/Files"))
    End If
    ' Save file in File folder.
    File.WriteAllBytes(filePath, bytes)
    Dim extension As String = Path.GetExtension(filePath)
    Dim excelConnectionString As String = ""

    Select Case extension
        Case ".xls" 'Excel 97-03
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"
        Case ".xlsx" 'Excel 07
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'"
    End Select

    excelConnectionString = String.Format(excelConnectionString, filePath)
    Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString)
    Dim cmdExcel As OleDbCommand = New OleDbCommand()
    Dim oleDA As OleDbDataAdapter = New OleDbDataAdapter()
    cmdExcel.Connection = excelConnection
    excelConnection.Open()
    Dim dtExcelSchema As DataTable
    dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
    Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
    excelConnection.Close()
    excelConnection.Open()
    cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
    oleDA.SelectCommand = cmdExcel
    Dim ds As DataSet = New DataSet()
    oleDA.Fill(ds)
    excelConnection.Close()
    ' Delete saved file.
    Directory.Delete(HttpContext.Current.Server.MapPath("~/Files"), True)

    Return ds.GetXml()
End Function

Screenshots

Excel File

Form with Excel data