CODEWITHSUNDEEP
c#asp.netasp.net-mvcexceloffice-interop
Ashish Kumar
Ashish Kumar

Reputation: 33

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

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>

Upvotes: 2

Views: 6652

Answers (2)

Drona kumar
Drona kumar

Reputation: 1

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.

Upvotes: 0

Scott Dellinger
Scott Dellinger

Reputation: 158

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);
}

Upvotes: 1

Related Questions