Import Excel Data using FileUpload Control To Database using AJAX jQuery

Last Reply 6 months ago By AnandM

Posted 6 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 6 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 6 months ago

Sorry Andrea, it is not what Im looking for.


Posted 6 months ago

Hi zzz,

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


Posted 6 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?aff_id=108