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

Last Reply 18 days ago By dharmendr

Posted 19 days ago

How to import excel record into html table on ajax request in c# without using Generic Handler.

on ajax request hit c# methoed and those methoed read excel file and store its data into data table than we have print data into html table in serilize way.

Posted 18 days ago Modified on 18 days ago

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