Excel Export Not Working in MVC. No Errors being thrown

Question

I am trying to export all the users in my database to an excel file.

After running this code and debugging i run into no errors and the LetsExcelAll() method is being hit and run all the way through. Not sure what I am missing. When code is done running no errors are encountered and no download begins.

Controller:

public void LetsExcelAll()
    {
        try
        {
            var userRepo = new UsersRepository();
            XLWorkbook wb = null;

            wb = userRepo.DownloadUsers();

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            Response.AddHeader("content-disposition", "attachment;filename=Users.xlsx");

            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }

REPO:

public XLWorkbook DownloadUsers()
    {
        try
        {
            if (thisConnection.State == ConnectionState.Closed)
                thisConnection.Open();

            MySqlCommand download = thisConnection.CreateCommand();

            download.CommandText = UserQueries.DownloadUsers;


            using (MySqlDataAdapter sda = new MySqlDataAdapter())
            {
                sda.SelectCommand = download;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        var ws = wb.Worksheets.Add(dt, "ALL_Users");
                        ws.Columns().AdjustToContents();
                        ws.Columns().Style.Alignment.SetWrapText();

                        return wb;

                    }
                }
            }
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            thisConnection.Close();
        }
    }

VIEW: (HOW I CALL THE METHOD)

    $("#downloadAllUsers").click(function () {
    $.post("/Users/LetsExcelAll")                    
});

How `LetsExcelAll` called inside controller/code-behind? If it's called in MVC controller, you should return `FileStreamResult` instead of setting `Response` headers manually. — Tetsuya Yamamoto, Aug 07 '18 at 04:06
How you're calling the method from view? Still unclear what view engine you're using and why `void` used as method return type instead of `ActionResult` or `FileResult`. — Tetsuya Yamamoto, Aug 07 '18 at 15:17
Is that `downloadAllUsers` a button element? The issue seems going clear: you're initiating AJAX request to invoke `void` method, that's why it not download anything. To download file you need to initiate GET request (not from AJAX) which returns `FileResult` with file name as argument. — Tetsuya Yamamoto, Aug 07 '18 at 21:58

score 1 · Accepted Answer · edited Aug 09 '18 at 13:58

You can't return a file as response of jQuery.post() method directly. It is necessary to store the file as byte array in TempData or Session state and pass it to another controller which marked as HttpGetAttribute to let user download the file.

Here are these steps to do:

1) Change LetsExcelAll method to return JsonResult containing file name and store output of MemoryStream inside TempData/Session variable with certain key as given below.

[HttpPost]
public ActionResult LetsExcelAll()
{
    try
    {
        var userRepo = new UsersRepository();
        XLWorkbook wb = null;

        wb = userRepo.DownloadUsers();

        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);

            // set stream to starting position
            MyMemoryStream.Position = 0;

            // store file contents inside TempData
            TempData["ExcelFile"] = MyMemoryStream.ToArray();
        }

        // return file name as JSON data
        return new JsonResult() { Data = new { FileName = "Users.xlsx", Status = "Success" } };
    }
    catch(Exception ex)
    {
        throw ex;
    }

    return new JsonResult() { Data = new { Status = "Nothing" } };
}

2) Set window.location in AJAX POST response to pass file name as query string parameter for action method name which uses GET (assumed downloadAllUsers is a button element).

$("#downloadAllUsers").click(function () {
    $.post("/Users/LetsExcelAll", function(data) {
        if (data.Status == "Success") {
            window.location = '/Users/DownloadFile?fileName=' + data.FileName;
        }
    });
});

3) Create a new controller with HttpGetAttribute using file name as input parameter and return FileResult from it.

[HttpGet]
public ActionResult DownloadFile(string fileName)
{
    if (TempData["ExcelFile"] != null)
    {
        var data = TempData["ExcelFile"] as byte[];
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        return File(data, contentType, fileName);
    }
    else
    {
        // if TempData messing up, return nothing
        return new EmptyResult();
    }
}

From this point, file downloading should working fine as expected.

Related issue:

Download Excel file via AJAX MVC

Thanks for your help, but with that code i am now getting a jquery error... — Ha66y-Day, Aug 08 '18 at 14:27
VM33:1 Uncaught SyntaxError: Unexpected token o in JSON at position 1 at JSON.parse () at Object.success (AllUsers:2502) — Ha66y-Day, Aug 08 '18 at 14:27
(This is the line causing the error) var response = JSON.parse(data); — Ha66y-Day, Aug 08 '18 at 14:27
`Unexpected token o` means that the response is already an object. No need to use `JSON.parse`, just use `data.Status` to reveal response state. — Tetsuya Yamamoto, Aug 08 '18 at 22:06

Excel Export Not Working in MVC. No Errors being thrown

1 Answers1