How to return excel file via ajax call using epplus

Question

Trying to return an excel file via an ajax call but something isnt right. The jquery code falls right into the error block.

mvc controller

   public ActionResult GetExceptionBillingExport(BillableStopSearchFilter bFilter)
    {
        var results = GetDataFromDatabase();
        
        using (var package = new ExcelPackage())
        {
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Sheet1");
            sheet.Cells.LoadFromCollection(results, true);
            return File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
        }
            
    }

jQuery method

$.ajax({
    type: 'GET',
    url: 'controller-url-here',
    data: filter,
    beforeSend: function () {
       
    },
    complete: function () {
       
    },
    success: function (response) {
        console.log(response);
        var blob = new Blob([response], { type: 'application/vnd.ms-excel' });
        var downloadUrl = URL.createObjectURL(blob);
        var a = document.createElement("a");
        a.href = downloadUrl;
        a.download = "Report.xlsx";
        document.body.appendChild(a);
        a.click();
    },
    error: function (err) {
        alert(err);
    }
});

If anything here helps, please let me know in a comment. Let me know which link is most helpful. https://stackoverflow.com/questions/13669733/export-datatable-to-excel-with-epplus or https://stackoverflow.com/questions40517017/safari-adding-html-to-download-xlsx-using-epplus-jquery-filedownload-js or https://stackoverflow.com/questions/26764750/allow-client-to-download-excel-file or https://stackoverflow.com/questions/16670209/download-excel-file-via-ajax-mvc — react_or_angluar, Dec 17 '20 at 01:25
I think I see the idea in those threads - to just write to the response within the server code, rather than have jquery do it? — bitshift, Dec 17 '20 at 02:42
Why not simply a link? `download excel` of if you want to use jquery: `location.href = '/controller-url-here'` — VDWWD, Dec 18 '20 at 09:48

score 0 · Answer 1 · answered Dec 18 '20 at 18:28

Got it working. Key changes were: (1) wrapping the response with Json, then (2) unpacking the array before creating the Blob

jQuery:

$.ajax({
            type: 'POST',
            url: '@Url.Action("GetBillingExport", "BillingExceptions")',
            data: searchFilterData,
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            beforeSend: function () {

               
            },
            complete: function () {
                
            },
            success: function (response) {
                console.log(response);
                var bytes = new Uint8Array(response.FileContents);
                var blob = new Blob([bytes], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
                var downloadUrl = URL.createObjectURL(blob);
                var a = document.createElement("a");
                a.href = downloadUrl;
                a.download = "Report.xlsx";
                document.body.appendChild(a);
                a.click();
            },
            error: function (err) {
                alert(err);
            },
            cache: false
        });

controller:

[System.Web.Http.HttpPost]
public ActionResult GetBillingExport([FromBody] BillableStopSearchFilter searchFilterData)
{


    var results = GetDataFromDatabase(BillableStopSearchFilter);
    ExcelPackage.LicenseContext = LicenseContext.Commercial;
    using (var package = new ExcelPackage())
    {
        ExcelWorksheet sheet = package.Workbook.Worksheets.Add("BillingReport");
        sheet.Cells.LoadFromCollection(results, true);
        package.Save();
        var fileObject = File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
        return Json(fileObject, JsonRequestBehavior.AllowGet);
    }




}

How to return excel file via ajax call using epplus

1 Answers1