Excel file not being downloaded when function is called via ajax method

Question

Situation

I am working on an application where I can have a grid with X items, and each item has a print button. Clicking this print button allows me to call an ajax function which passes the ID of the grid item to a controller. I retrieve the relevant data based on that ID and then download it in an excel file. (The retrieving of the specific item is not yet done)

What I have so far

So far, I have the basic code that downloads an excel file, along with my grid .

Problem

The problem I am facing is, if I click the "Print" button...nothing happens, even with a breakpoint in my exporttoexcel functions shows me that the function is entered and I can step thru it and despite no errors, nothing occurs. However, I added random button that called the same function and when i clicked that button, the excel file was downloaded. As a result, I believe the issue has something to do with aJax.

Code

<input type="button" value="Test" onclick="location.href='@Url.Action("ExportToExcel", "Profile")'" />

This is the code which downloads the file. It was a simple button I added.

function ExportToExcel(id) {
    $.ajax({
        type: "POST",
        url: "@Url.Action("ExportToExcel", "Profile")",
        data: { "id": id },
        dataType: "json"

    });
}

This is the function that I want to work, but it does not work and I cannot see what i've got wrong.

Export to Excel Code

public void ExportToExcelx()
{
    var products = new System.Data.DataTable("teste");
    products.Columns.Add("col1", typeof(int));
    products.Columns.Add("col2", typeof(string));

    products.Rows.Add(1, "product 1");
    products.Rows.Add(2, "product 2");
    products.Rows.Add(3, "product 3");
    products.Rows.Add(4, "product 4");
    products.Rows.Add(5, "product 5");
    products.Rows.Add(6, "product 6");
    products.Rows.Add(7, "product 7");


    var grid = new GridView();
    grid.DataSource = products;
    grid.DataBind();

    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
    Response.ContentType = "application/ms-excel";

    Response.Charset = "";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    grid.RenderControl(htw);

    //Response.Output.Write(sw.ToString());
    //Response.Flush();
    //Response.End();
    // =============


    //Open a memory stream that you can use to write back to the response
    byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
    MemoryStream s = new MemoryStream(byteArray);
    StreamReader sr = new StreamReader(s, Encoding.ASCII);

    //Write the stream back to the response
    Response.Write(sr.ReadToEnd());
    Response.End();



    //  return View("MyView");
}

Theory

I believe the error is somehow tied in to aJax, I am also creating the button in the controller like this. "<button type='button' class='btn btn-warning' onclick='ExportToExcel(" + c.id + ");'>Print</button>",

Since location.href='@Url.Action works, I was wondering if attempting to redo my dynamic button would solve my issue.

Appreciate any insight that could be offered.

Answer 1

Yes you are right you have problem with ajax, Basically you have to call the controller action again from you ajax call when your first ajax call return success. Add below code snippet to your ajax call.

success: function () {

    window.location = '@Url.Action("ExportExcel", "Profile")?id='+id;
}

And you have to change your controller method to return the file, as below

public FileResult ExportToExcelx()
{
 ...............
 
 byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
 return File(byteArray, System.Net.Mime.MediaTypeNames.Application.Octet, "FileName.xlsx");                       
}

Answer 2

File cannot be downloaded until full post back is triggered. Here is how you can do it: Your ExportToExcelx function will hold file in TempData object as following:

TempData["fileHandle"] = s.ToArray();

Rather than returning view return temp data identifier "fileHandle" and file name as shown below:

 return Json(new { fileHandle = "fileHandle", FileName = "file.xls" }, JsonRequestBehavior.AllowGet);

So your modified function will be like this:

public JsonResult ExportToExcelx()
{
    var products = new System.Data.DataTable("teste");
    products.Columns.Add("col1", typeof(int));
    products.Columns.Add("col2", typeof(string));

    products.Rows.Add(1, "product 1");
    products.Rows.Add(2, "product 2");
    products.Rows.Add(3, "product 3");
    products.Rows.Add(4, "product 4");
    products.Rows.Add(5, "product 5");
    products.Rows.Add(6, "product 6");
    products.Rows.Add(7, "product 7");


    var grid = new GridView();
    grid.DataSource = products;
    grid.DataBind();

    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
    Response.ContentType = "application/ms-excel";

    Response.Charset = "";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    grid.RenderControl(htw);

    //Response.Output.Write(sw.ToString());
    //Response.Flush();
    //Response.End();
    // =============


    //Open a memory stream that you can use to write back to the response
    byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
    MemoryStream s = new MemoryStream(byteArray);

    TempData["fileHandle"] = s.ToArray();

    //StreamReader sr = new StreamReader(s, Encoding.ASCII);

    //Write the stream back to the response
    Response.Write(sr.ReadToEnd());
    Response.End();


    return Json(new { fileHandle = "fileHandle", FileName = "file.xls" }, JsonRequestBehavior.AllowGet);
    //  return View("MyView");
}

Now you need another function in your controller to download file like following:

    [HttpGet]
    public virtual ActionResult Download(string fileHandle, string fileName)
    {
        if (TempData[fileHandle] != null)
        {
            byte[] data = TempData[fileHandle] as byte[];
            return File(data, "application/vnd.ms-excel", fileName);
        }
        else
        {

            return new EmptyResult();
        }
    }

On successful call to ExportToExcelx function your ajax call will call download function as following:

    $.ajax({
    type: 'GET',
    cache: false,
    url: '/url',      
    success: function (data) {
        window.location = '/url/Download?fileHandle=' + data.fileHandle
            + '&filename=' + data.FileName; //call download function
    },
    error: function (e) {
        //handle error
    }

Download function then will return the file.

Hope this helps.

Answer 3

I've had a similar problem here, and it did solve with a dynamic button as well. I just had to include a responseType:'blob' in my request. And get the response to the button:

var link = document.createElement('a');
link.href = window.URL.createObjectURL(response.data);
link.download='filename.xlsx';

document.body.appendChild(link);
link.click();
document.body.removeChild(link);

And my Controller writes to a output stream and produces a "application/xls"

response.setContentType("application/xls");
response.setHeader("Content-disposition", "attachment;");
response.getOutputStream().write(content);

Answer 4

There are multiple solutions to this problem:

Solution 1

Let's imagine you have a Product model like this:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

In you controller:

[HttpPost]
public JsonResult ReportExcel(string id)
{
   // Your Logic Here: <DB operation> on input id
   // or whatsoever ...

    List<Product> list = new List<Product>() {
        new Product{  Id = 1, Name = "A"},
        new Product{  Id = 2, Name = "B"},
        new Product{  Id = 3, Name = "C"},
    };

    return Json(new { records = list }, JsonRequestBehavior.AllowGet);
}

Then inside your View (.cshtml), use the JSONToCSVConvertor as a utility function and just Don't touch it as it converts the array of json objects received into Excel and prompts for download.

@{
    ViewBag.Title = "View export to Excel";
}

<h2>....</h2>

@*  All Your View Content goes here *@
@* This is a sample form *@

<form>
    <div class="form-group">
        <label>Product ID</label>
        <div class="col-md-10">
            <input id="productID" name="productID" class="form-control"/>
        </div>
    </div>

    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input id="submit" type="submit" value="Create" class="btn btn-default" />
        </div>
    </div>
</form> 


@section scripts{
    <script>
        $('#submit').click(function (e) {
            e.preventDefault();

            var ID = $('#productID').val();

            $.ajax({
                cache: false,
                type: 'POST',
                url: '/YourControllerName/ReportExcel',
                data: {id: ID},
                success: function (data) {
                    console.log(data);                    
                    JSONToCSVConvertor(data.records, "Sample Report", true);
                }
            })
        });


        function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {
            //If JSONData is not an object then JSON.parse will parse the JSON string in an Object
            var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;

            var CSV = 'sep=,' + '\r\n\n';

            //This condition will generate the Label/Header
            if (ShowLabel) {
                var row = "";

                //This loop will extract the label from 1st index of on array
                for (var index in arrData[0]) {

                    //Now convert each value to string and comma-seprated
                    row += index + ',';
                }

                row = row.slice(0, -1);

                //append Label row with line break
                CSV += row + '\r\n';
            }

            //1st loop is to extract each row
            for (var i = 0; i < arrData.length; i++) {
                var row = "";

                //2nd loop will extract each column and convert it in string comma-seprated
                for (var index in arrData[i]) {
                    row += '"' + arrData[i][index] + '",';
                }

                row.slice(0, row.length - 1);

                //add a line break after each row
                CSV += row + '\r\n';
            }

            if (CSV == '') {
                alert("Invalid data");
                return;
            }

            //Generate a file name
            var fileName = "MyReport_";
            //this will remove the blank-spaces from the title and replace it with an underscore
            fileName += ReportTitle.replace(/ /g, "_");

            //Initialize file format you want csv or xls
            var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);

            // Now the little tricky part.
            // you can use either>> window.open(uri);
            // but this will not work in some browsers
            // or you will not get the correct file extension    

            //this trick will generate a temp <a /> tag
            var link = document.createElement("a");
            link.href = uri;

            //set the visibility hidden so it will not effect on your web-layout
            link.style = "visibility:hidden";
            link.download = fileName + ".csv";

            //this part will append the anchor tag and remove it after automatic click
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    </script>
}

I ran and built the above code successfully, so feel free to grab and tweak it as you wish.

Also here is the jsFiddle link, thanks to its developer: https://jsfiddle.net/1ecj1rtz/

Solution 2

Call this action method through $.ajax and get the file downloaded:

public FileResult Export(int id) 
 {
    //......... create the physical file ....//

    byte[] fileBytes = File.ReadAllBytes(filePath);
    return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);
 }

Along with Solution 2, this thread gives you a good idea: https://stackoverflow.com/a/16670517/4687359

Hope this helped. :)

Answer 5

ANSWER:

You need to include success in your ajax call.

    function ExportToExcel(id) {
        $.ajax({
            type: "POST",
            url: "@Url.Action("ExportToExcel", "Profile")",
            data: { "id": id },
            dataType: "json"
            success: function () {
                 window.location = '@Url.Action("ExportExcel", "Profile")?id='+id;
            }
        });
    }

For Duplicate headers received from the server

Duplicate headers received from server

Answer 6

The data will be transformed to a query string on an AJAX GET request; just do that yourself using the jQuery param function:

$('#excel').on('click',function(){
    var query = {
        location: $('#location').val(),
        area: $('#area').val(),
        booth: $('#booth').val()
    }

   var url = "{{URL::to('downloadExcel_location_details')}}?" + $.param(query)

   window.location = url;
});

Answer 7

First of all, I would not use GridView to generate excel. Despite being "easy", it won't generate an actual excel file, but rather a html file with xls extension:

<div>
    <table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
        <tr>
            <th scope="col">col1</th><th scope="col">col2</th>
        </tr><tr>
            <td>1</td><td>product 1</td>
        </tr><tr>
            <td>2</td><td>product 2</td>
        </tr><tr>
            <td>3</td><td>product 3</td>
        </tr><tr>
            <td>4</td><td>product 4</td>
        </tr><tr>
            <td>5</td><td>product 5</td>
        </tr><tr>
            <td>6</td><td>product 6</td>
        </tr><tr>
            <td>7</td><td>product 7</td>
        </tr>
    </table>
</div>

This results in a file that when opened will cause this:

that is pretty annoying (and unprofessional). If you're not bounded to old excel version - xls - but can use most recent file format xlsx, I'd rather use DocumentFormat.OpenXml nuget package or other packages/libraries for excel generation. Honestly, DocumentFormat.OpenXml is powerful but a little boring to use, when you have many columns and you just have a flat list of objects to report. If you are using .NET Framework (not Dotnet Core), you can try CsvHelper.Excel nuget package. Usage is pretty straight forward. Your ExportToExcel method will become something like:

public ActionResult ExportToExcel(string id)
{
    // TODO: Replace with correct products retrieving logic using id input
    var products = new [] {
       { col1 = 1, col2 = "product 1" },
       { col1 = 2, col2 = "product 2" },
       { col1 = 3, col2 = "product 3" },
       { col1 = 4, col2 = "product 4" },
       { col1 = 5, col2 = "product 5" },
       { col1 = 6, col2 = "product 6" },
       { col1 = 7, col2 = "product 7" },
       { col1 = 1, col2 = "product 1" },
       { col1 = 1, col2 = "product 1" },
    };

    var ms = new MemoryStream();
    var workbook = new XLWorkbook();
    using (var writer = new CsvWriter(new ExcelSerializer(workbook)))
    {
       writer.WriteRecords(products);
    }
    workbook.SaveAs(ms);
    ms.Flush();
    ms.Seek(0, SeekOrigin.Begin);
    return File(ms, MimeMapping.GetMimeMapping("file.xlsx"), $"MyExcelFile.xlsx");

}

Another package pretty powerful is EPPlus, that allows you to load a DataTable (see this: https://stackoverflow.com/a/53957999/582792).

Coming to the AJAX part, well... I do not think you need it at all: once you set the location to the new ExportToExcel action, it should just download the file. Assuming you are using Bootstrap 3, for each of your item in the collection you can just:

<a href="@Url.Action("ExportToExcel", "Profile", new {id=item.Id})" class="btn btn-info">
<i class="glyphicon glyphicon-download-alt" />
</a>

Answer 8

Here is how I got this working for PDF. Excel download should be similar

$.ajax({
  url: '<URL_TO_FILE>',
  success: function(data) {
    var blob=new Blob([data]);
    var link=document.createElement('a');
    link.href=window.URL.createObjectURL(blob);
    link.download="<FILENAME_TO_SAVE_WITH_EXTENSION>";
    link.click();
  }
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

OR using download.js

$.ajax({
  url: '<URL_TO_FILE>',
  success: download.bind(true, "<FILENAME_TO_SAVE_WITH_EXTENSION>", "<FILE_MIME_TYPE>")
});