How to export data in a Excel Sheet on a button click using ASP.NET MVC

Question

I am trying to export data on a button click in ASP.NET MVC. I have written a JSONResult ExportDataInExcel() Class and here I have written code to export data which I am getting from MyService.

I am able to get all the data but this data is not getting exported to excel sheet on button click. I want that when I click on my button then it should export the data into Excel Sheet. But currently my data is getting displayed on View and not getting exported to Excel Sheet. Could any one please help me understand what is that I am missing?

Controller:

public JsonResult ExportDataInExcel(int A, int B, string C)
{
    Response.Clear();
    Response.Buffer = true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-disposition", "attachment; filename=Test.xls");
    Response.Charset = "";

    MyService.ServiceInfoClient objMyService = new MyService.ServiceInfoClient();

    System.IO.StringWriter stringWriter = new System.IO.StringWriter();
    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

    GridView ExcelGrid = new GridView();
    ExcelGrid.DataSource = from data in objMyService.ExportTestData(lId,wId,cid)
        select new
        {
            A=data.a,
            B=data.b,
            C=data.c,
            D=data.d,
            E=data.e,
            F=data.f,
            G=data.g
        };
    ExcelGrid.DataBind();
    ExcelGrid.HeaderStyle.Font.Bold = true;
    ExcelGrid.HeaderStyle.ForeColor = System.Drawing.Color.White; ;
    ExcelGrid.HeaderStyle.BackColor = System.Drawing.Color.Green;
    ExcelGrid.RenderControl(htmlTextWriter);

    Response.Output.Write(stringWriter.ToString());
    Response.Flush();
    Response.End();

    return Json(ExcelGrid);
}

View

<script type="text/javascript">
    function ExportTestDet(){
        if ($("#ddl1").val() == '0') {
            alert('Please select');
            return false;
        }

        if ($("#ddl2").val() == '0') {
            alert('Please select');
            return false;
        }

        $.ajax({
            type: 'POST',
            url: '@Url.Action("ExportDataInExcel")',
            datatype: 'JSON',
            data: { LId: $("#ddl1").val(), WID: $("#ddl2").val() },
            success: function (data) {
                $("#DynamicContent").html(data);
                $("#tbl").show();
                alert("Exported");
            },
            error: function (ex) {
                alert('Failed to export data: ' + ex.responseText);
            }
        });
    }
</script>

<div>
    <input type="submit" value="Export Excel" onclick="return ExportTestDet()" />
</div>

Well you're not creating an actual Excel file, so that's a problem. Rather than rending HTML to a file and serving it up with an Excel MIME type and extension, why not create an actual Excel file? Use EPPlus, NPOI, ClosedXML, Office XML SDK, Aspose etc... — mason
– mason, Commented Aug 3, 2017 at 20:25

Scott Dellinger · Accepted Answer · 2017-08-03 22:41:55Z

1

I'm not sure why you're returning a JSON string, but expecting Excel.

Instead of a JsonResult, return an ActionResult, something like this:

public ActionResult ExportDataInExcel(int A, int B, string C)
{
    string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    string fileName = "WhateverFile.xlsx";

    // CODE TO GENERATE EXCEL FILE (OR CSV) HERE (I recommend EPPlus)

    // output the file
    var stream = new MemoryStream();
    EPPlusExcelFileYouGenerated.SaveAs(stream); // however you get your generated file to the MemoryStream is fine.

    stream.Position = 0;
    return File(stream, contentType, fileName);
}

answered Aug 3, 2017 at 22:41

Scott Dellinger

1581 silver badge15 bronze badges

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

3 Comments

Ashish Kumar Over a year ago

I am using JSONResult since I want to pass the arguments in my JSONResult class through Ajax call. I have two drop downs and the value of these two are passed in my JSONResult.

Ashish Kumar Over a year ago

Is it possible to export the data from Ajax? I am trying to do that now instead of relying on JSONResult. If you can help me with that?

Scott Dellinger Over a year ago

No... you can't display it from Ajax. You need to return it as an excel file (as I showed here) so that it can be opened by the user.

Drona kumar · Accepted Answer · 2017-08-04 06:12:34Z

0

use Jquery jquery.table2excel.js this will help you for porting date from html table to excel and table must be in proper format head and body tags and table id also must.

answered Aug 4, 2017 at 6:12

Drona kumar

11 bronze badge

Collectives™ on Stack Overflow

How to export data in a Excel Sheet on a button click using ASP.NET MVC

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related