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
– Tetsuya Yamamoto, Commented Aug 7, 2018 at 4: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
– Tetsuya Yamamoto, Commented Aug 7, 2018 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
– Tetsuya Yamamoto, Commented Aug 7, 2018 at 21:58

Ha66y-Day · Accepted Answer · 2018-08-09 13:58:14Z

1

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

edited Aug 9, 2018 at 13:58

Ha66y-Day

2171 gold badge3 silver badges13 bronze badges

answered Aug 8, 2018 at 1:28

Tetsuya Yamamoto

25.1k8 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ha66y-Day Over a year ago

Thanks for your help, but with that code i am now getting a jquery error...

Ha66y-Day Over a year ago

VM33:1 Uncaught SyntaxError: Unexpected token o in JSON at position 1 at JSON.parse (<anonymous>) at Object.success (AllUsers:2502)

Ha66y-Day Over a year ago

(This is the line causing the error) var response = JSON.parse(data);

Tetsuya Yamamoto Over a year ago

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.

Collectives™ on Stack Overflow

Excel Export Not Working in MVC. No Errors being thrown

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related