Import Excel Data using FileUpload Control To Database using AJAX jQuery

Last Reply 10 months ago By AnandM

Posted 10 months ago

How to extract excel data to Database with upload using ajax Jquery because I don't the page to load. And I don't want to use Update Panel thanks.

Posted 10 months ago

Hi zzz,

I have created a sample which full fill your requirement taking reference of below articles

Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net

Upload multiple files with Progress Bar using Flash in ASP.Net

FilePath,cs

public static class FilePath
{
    /// <summary>
    /// Gets or sets FilePath.
    /// </summary>
    public static string FilePathDetail { get; set; }
}

Default.aspx

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <br />
    <asp:Button ID="btnDisplay" Text="Display" runat="server" />
    <br />
    <br />
    <table id="tblExcel">
    </table>
</div>
<div>
    <link rel="Stylesheet" type="text/css" href="CSS/uploadify.css" />
    <script type="text/javascript" src='http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.3.min.js'></script>
    <script type="text/javascript" src="scripts/jquery.uploadify.js"></script>
    <script type="text/javascript">
        $(function () {
            $("#<%=FileUpload1.ClientID%>").fileUpload({
                'uploader': 'scripts/uploader.swf',
                'cancelImg': 'images/cancel.png',
                'buttonText': 'Browse Files',
                'script': 'Upload.ashx',
                'folder': 'uploads',
                'fileDesc': 'Excel Files',
                'fileExt': '*.xlsx',
                'multi': false,
                'auto': true
            });

            $('#btnDisplay').click(function () {
                $.ajax({
                    type: "POST",
                    url: "Default.aspx/BindExcelData",
                    data: '{}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        $("#tblExcel").empty();
                        var xmlDoc = $.parseXML(response.d);
                        var xml = $(xmlDoc);
                        var details = xml.find("ExcelData");
                        var table = $("#tblExcel");
                        var row = $(table[0].insertRow(-1));
                        details.eq(0).children().each(function () {
                            var headerCell = $("<th />");
                            headerCell.html(this.nodeName);
                            row.append(headerCell);
                        });
                        $(details).each(function () {
                            row = $(table[0].insertRow(-1));
                            $(this).children().each(function () {
                                var cell = $("<td />");
                                cell.html($(this).text());
                                row.append(cell);
                            });
                        });
                    }
                });
                return false;
            });
        });
    </script>
</div>

Default.aspx.cs

[WebMethod(EnableSession = true)]
public static string BindExcelData()
{
    string filePath = FilePath.FilePathDetail;

    DataTable dt = new DataTable("ExcelData");

    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);

        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }
    dt.Rows.RemoveAt(dt.Rows.Count - 1);
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd;
    con.Open();
    foreach (DataRow row in dt.Rows)
    {
        string query = "INSERT INTO [CustomersWithOutIdentity] (";
        foreach (DataColumn column in dt.Columns)
        {
            query = query + column + ",";
        }
        query = query.Remove(query.Length - 1);
        query = query + ") VALUES (";
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            query = query + "'" + row[dt.Columns[i]] + "',";
        }
        query = query.Remove(query.Length - 1);
        query = query + ")";
        cmd = new SqlCommand(query, con);
        cmd.ExecuteNonQuery();
    }
    con.Close();
    DataSet ds = new DataSet();
    ds.Tables.Add(dt);
    return ds.GetXml();
}

Upload.ashx

<%@ WebHandler Language="C#" Class="Upload" %>

using System;
using System.Web;
using System.IO;
using System.Web.SessionState;
public class Upload : IHttpHandler, IRequiresSessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        context.Response.Expires = -1;
        try
        {
            HttpPostedFile postedFile = context.Request.Files["Filedata"];
            string savepath = "";
            string tempPath = "";
            tempPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];
            savepath = context.Server.MapPath(tempPath);
            string filename = postedFile.FileName;
            FilePath.FilePathDetail = savepath + @"\" + filename;
            if (!Directory.Exists(savepath))
                Directory.CreateDirectory(savepath);
            postedFile.SaveAs(savepath + @"\" + filename);
        }
        catch (Exception ex)
        {
            context.Response.Write("Error: " + ex.Message);
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

ScreenShot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html