Export excel file using MVC and JQuery

INTRODUCTION

We will see how to download an excel file with huge data using JQuery and MVC. When you use ajax call in asp.net MVC, you can just return JSON object not file so we need to convert that JSON data into downloadable blob file. A blob object represents a chuck of bytes that holds data of a file. But a blob is not a reference to an actual file. It may seem like it is.

Step 1: Returning JSON object file data from the controller side

public ActionResult Export(Data)
 { 
 Datatable dtable=”” //logic of fetching all the records from database based on data sent from request 
 FileContentResult fobject;
 using (XLWorkbook wb = new XLWorkbook())
 {
 wb.Worksheets.Add(dtable);
 using (MemoryStream stream = new MemoryStream())
 {
 wb.SaveAs(stream);
 var bytesdata = File(stream.ToArray(), “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, “Report.xlsx”); 
 //Content type is used according to latest xlsx format
 fobject = bytesdata;
 }
 }
 return Json(fobject, JsonRequestBehavior.AllowGet); //returning bytes of file data as json object
 }

Step 2: Converting JSON object to blob downloadable in ajax

$.ajax({
 type: “POST”,
 url: “@(Url.Action(“Export”,”Master”))” // method name, controller name 
 data: formData,
 success: function (filedata) {
 var bytes = new Uint8Array(filedata.FileContents); 
 var blob = new Blob([bytes], {type: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet” });
 var link = document.createElement(‘a’);
 link.href = window.URL.createObjectURL(blob);
 link.download = “Report.xlsx”;
 link.click();
 },
 error: function (err) {
 }
 cache: false,
 contentType: false,
 processData: false,
 });

PROBLEM AND SOLUTION

Problem:

If your data in file is large enough you will get an error of exceeding the bounds of your serializer can handle and you will be met with an exception of error during serialization and deserialization using JSON.JavaScriptSerializer.

Solution:

1. Setting the MaxJsonLength property default value within your web.config

<configuration>
<system.web.extensions>
<scripting>
<webServices>
<jsonSerialization maxJsonLength=”68323055" />
</webServices>
</scripting>
</system.web.extensions>
</configuration>

2.If you are using MVC4 to handle returning your JSON values, you may want to override the default JsonResult() ActionResult and change the maximum size manually.

JsonResult result = Json(fobject);
result.MaxJsonLength = 7895389;

Thank You, See you in the next article !!

You can reach out to me here,

LinkedIn: https://www.linkedin.com/in/vaibhav-bhapkar

Email: vaibhavbhapkar.medium@gmail.com