Download Data from database to Excel using jQuery Ajax in ASP.Net MVC

Dorababu

SOLVED CLOSED ACTIVE ANSWERED
User: Dorababu
Posted: on Nov 07, 2021 07:51 PM
Forum: ASP.Net MVC
Answer: 2
Views: 23762

download a file through ajax request in asp.net MVC 4

I am trying to download the data to excel using ajax request, I have applied few approaches which were mentioned but no luck

Referred to this article Export DataTable (DataSet) to Excel file in ASP.Net MVC but no luck

function downloadReport() {
    var data = JSON.stringify({
        'startDt': $("#eventFromDate").val(),
        'endDt': $("#eventTodate").val()
    });
    $.ajax({
        type: "POST",
        url: "/EventReport/DonwloadExcel",
        data: data,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: OnSuccess,
        failure: function (response) {
            alert(response.d);
        },
        error: function (response) {
            alert(response.d);
        }
    });
}

public ActionResult DonwloadExcel(DateTime startDt, DateTime endDt)
{
   ReportableEventRepository reportableEventRepository = new ReportableEventRepository();
   MemoryStream stream = reportableEventRepository.DownloadToExcel(startDt, endDt);
   return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
}

public void DownloadToExcel(DateTime startDt, DateTime endDate)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["BMA"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("up_ReportableEventData", con))
        {
            cmd.Parameters.AddWithValue("@StartDt", startDt.ToString("yyyy-MM-dd"));
            cmd.Parameters.AddWithValue("@EndDt", endDate.ToString("yyyy-MM-dd"));
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable("ReportableEvent"))
                {
                    sda.Fill(dt);
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        wb.Worksheets.Add(dt);
                        wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        wb.Style.Font.Bold = true;
 
                        HttpContext.Current.Response.Clear();
                        HttpContext.Current.Response.Buffer = true;
                        HttpContext.Current.Response.Charset = "";
                        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename= EmployeeReport.xlsx");
 
                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {
                            wb.SaveAs(MyMemoryStream);
                            MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                            HttpContext.Current.Response.Flush();
                            HttpContext.Current.Response.End();
                        }
                    }
                }
            }
        }
    }
}

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 7

Reply
ANSWER
User: dharmendr
Replied: on Nov 09, 2021 09:21 AM
Report

Hi Dorababu,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

using System.IO;
using System.Data;
using System.Linq;
using System.Web.Mvc;
using ClosedXML.Excel;

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        NorthwindEntities entities = new NorthwindEntities();
        return View(from customer in entities.Customers.Take(10)
                    select customer);
    }

    public JsonResult DonwloadExcel()
    {
        NorthwindEntities entities = new NorthwindEntities();
        DataTable dt = new DataTable("Grid");
        dt.Columns.AddRange(new DataColumn[4] { new DataColumn("CustomerId"),
                                        new DataColumn("ContactName"),
                                        new DataColumn("City"),
                                        new DataColumn("Country") });

        var customers = from customer in entities.Customers.Take(10)
                        select customer;

        foreach (var customer in customers)
        {
            dt.Rows.Add(customer.CustomerID, customer.ContactName, customer.City, customer.Country);
        }

        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt);
            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return Json(Convert.ToBase64String(stream.ToArray(), 0, stream.ToArray().Length), JsonRequestBehavior.AllowGet);
            }
        }
    }
}

View

@model IEnumerable<Export_Excel_MVC.Customer>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('#btnExport').on('click', function () {
                $.ajax({
                    type: "POST",
                    url: '/Home/DonwloadExcel',
                    dataType: "json",
                    data: {},
                    success: function (r) {
                        //Convert Base64 string to Byte Array.
                        var bytes = Base64ToBytes(r);

                        //Convert Byte Array to BLOB.
                        var blob = new Blob([bytes], { type: "application/octetstream" });

                        //Check the Browser type and download the File.
                        var isIE = false || !!document.documentMode;
                        if (isIE) {
                            window.navigator.msSaveBlob(blob, "Grid.xlsx");
                        } else {
                            var url = window.URL || window.webkitURL;
                            link = url.createObjectURL(blob);
                            var a = $("<a />");
                            a.attr("download", "Grid.xlsx");
                            a.attr("href", link);
                            $("body").append(a);
                            a[0].click();
                            $("body").remove(a);
                        }
                    }
                });
            });
        });

        function Base64ToBytes(base64) {
            var binary_string = window.atob(base64);
            var len = binary_string.length;
            var bytes = new Uint8Array(len);
            for (var i = 0; i < len; i++) {
                bytes[i] = binary_string.charCodeAt(i);
            }
            return bytes.buffer;
        }
    </script>
</head>
<body>
    <h4>Customers</h4>
    <hr />
    <table>
        <tr>
            <th>CustomerID</th>
            <th>ContactName</th>
            <th>City</th>
            <th>Country</th>
        </tr>
        @foreach (Customer customer in Model)
        {
            <tr>
                <td>@customer.CustomerID</td>
                <td>@customer.ContactName</td>
                <td>@customer.City</td>
                <td>@customer.Country</td>
            </tr>
        }
    </table>
    <br />
    <br />
    <input id="btnExport" type="button" value="Export" />
</body>
</html>

Grid (Html Table)

Export DataTable (DataSet) to Excel file in ASP.Net MVC

Exported Excel File

Export DataTable (DataSet) to Excel file in ASP.Net MVC

Dorababu

0 13

Reply
ANSWER
User: Dorababu
Replied: on Nov 10, 2021 07:44 AM
Report

I remove json.stringify and it works

var data = {
'startDt': $("#eventFromDate").val(),
'endDt': $("#eventTodate").val()
};

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

Download Data from database to Excel using jQuery Ajax in ASP.Net MVC

Company

Explore

Follow us

Contact Us