ASP.Net: Import Excel file data to DataTable using JavaScipt

Hi Jilsoft,

First you need to read the Excel data using JavaScript and send the data to WebMethod using Ajax call. Inside the WebMethod convert the data to DataTable.

Please refer below sample.

Excel Data

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>
<input type="file" id="fileUpload" />
<input type="button" id="btnUpload" value="Upload" />
<script type="text/javascript">
    $(function () {
        $('#btnUpload').on('click', function () {
            var fileUpload = document.getElementById("fileUpload");

            //Validate whether File is valid Excel file.
            var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
            if (regex.test(fileUpload.value.toLowerCase())) {
                if (typeof (FileReader) != "undefined") {
                    var reader = new FileReader();

                    //For Browsers other than IE.
                    if (reader.readAsBinaryString) {
                        reader.onload = function (e) {
                            ProcessExcel(e.target.result);
                        };
                        reader.readAsBinaryString(fileUpload.files[0]);
                    } else {
                        //For IE Browser.
                        reader.onload = function (e) {
                            var data = "";
                            var bytes = new Uint8Array(e.target.result);
                            for (var i = 0; i < bytes.byteLength; i++) {
                                data += String.fromCharCode(bytes[i]);
                            }
                            ProcessExcel(data);
                        };
                        reader.readAsArrayBuffer(fileUpload.files[0]);
                    }
                } else {
                    alert("This browser does not support HTML5.");
                }
            } else {
                alert("Please upload a valid Excel file.");
            }
        });
    });
    function ProcessExcel(data) {
        //Read the Excel File data.
        var workbook = XLSX.read(data, {
            type: 'binary'
        });

        //Fetch the name of First Sheet.
        var firstSheet = workbook.SheetNames[0];

        //Read all rows from First Sheet into an JSON array.
        var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetExcelData",
            data: "{json:'" + JSON.stringify(excelRows) + "'}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                alert(response.d);
            },
            error: function (response) {
                alert(response.d);
            }
        });
    };
</script>

Namespaces

C#

using System.Data;
using System.Reflection;
using System.Web.Services;
using System.Web.Script.Serialization;

VB.Net

Imports System.Data
Imports System.Reflection
Imports System.Web.Services
Imports System.Web.Script.Serialization

Code

C#

[WebMethod]
public static string GetExcelData(string json)
{
    JavaScriptSerializer js = new JavaScriptSerializer();
    List<MyClass> customers = js.Deserialize<List<MyClass>>(json);
    DataTable dt = ToDataTable<MyClass>(customers);
    DataSet ds = new DataSet();
    ds.Tables.Add(dt);
    return ds.GetXml();
}

public class MyClass
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

public static DataTable ToDataTable<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);

    //Get all the properties
    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Setting column names as Property names
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i++)
        {
            //inserting property values to datatable rows
            values[i] = Props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    //put a breakpoint here and check datatable
    return dataTable;
}

VB.Net

<WebMethod>
Public Shared Function GetExcelData(ByVal json As String) As String
    Dim js As JavaScriptSerializer = New JavaScriptSerializer()
    Dim customers As List(Of [MyClass]) = js.Deserialize(Of List(Of [MyClass]))(json)
    Dim dt As DataTable = ToDataTable(Of [MyClass])(customers)
    Dim ds As DataSet = New DataSet()
    ds.Tables.Add(dt)
    Return ds.GetXml()
End Function

Public Class [MyClass]
    Public Property CustomerId As Integer
    Public Property Name As String
    Public Property Country As String
End Class

Public Shared Function ToDataTable(Of T)(ByVal items As List(Of T)) As DataTable
    Dim dataTable As DataTable = New DataTable(GetType(T).Name)
    Dim Props As PropertyInfo() = GetType(T).GetProperties(BindingFlags.[Public] Or BindingFlags.Instance)

    For Each prop As PropertyInfo In Props
        dataTable.Columns.Add(prop.Name)
    Next

    For Each item As T In items
        Dim values = New Object(Props.Length - 1) {}
        For i As Integer = 0 To Props.Length - 1
            values(i) = Props(i).GetValue(item, Nothing)
        Next
        dataTable.Rows.Add(values)
    Next

    Return dataTable
End Function

Screenshot

The DataTable