Import Excel Data using FileUpload Control To Database using AJAX jQuery

Last Reply 11 months ago By AnandM

Posted 11 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 11 months ago
Hi @zzz,
Please try the following

Insert data into database using jQuery AJAX and ASP.Net Example

It might help you.

Cheers Andrea.

Posted 11 months ago

Sorry Andrea, it is not what Im looking for.


Posted 11 months ago

Hi zzz,

I am working. Once done i will get back to you.


Posted 11 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