Export data to Excel file with ASP.NET MVC 4 C# is rendering into view

You can call helper class in any controller

//view 
 @Html.ActionLink("Export to Excel", "Excel")

//controller Action
public void Excel()
{
    var model = db.GetModel()

    Export export = new Export();
    export.ToExcel(Response, model);
}

//helper class
public class Export
{        public void ToExcel(HttpResponseBase Response, object clientsList)
    {
        var grid = new System.Web.UI.WebControls.GridView();
        grid.DataSource = clientsList;
        grid.DataBind();
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        grid.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }
}

Step 1: View page code

<input type="button" id="btnExport" value="Export" class="btn btn-primary" />
<script>
  $(document).ready(function () {

 $('#btnExport').click(function () {          

  window.location = '/Inventory/ExportInventory';

        });
});
</script>

Step 2: Controller Code

  public ActionResult ExportInventory()
        {
            //Load Data
            var dataInventory = _inventoryService.InventoryListByPharmacyId(pId);
            string xml=String.Empty;
            XmlDocument xmlDoc = new XmlDocument();

            XmlSerializer xmlSerializer = new XmlSerializer(dataInventory.GetType());

            using (MemoryStream xmlStream = new MemoryStream())
            {
                xmlSerializer.Serialize(xmlStream, dataInventory);
                xmlStream.Position = 0;
                xmlDoc.Load(xmlStream);
                xml = xmlDoc.InnerXml;
            }

            var fName = string.Format("Inventory-{0}", DateTime.Now.ToString("s"));

            byte[] fileContents = Encoding.UTF8.GetBytes(xml);

            return File(fileContents, "application/vnd.ms-excel", fName);
        }

  using (MemoryStream mem = new MemoryStream())
  {
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    // Add a row to the cell table.
    Row row;
    row = new Row() { RowIndex = 1 };
    sheetData.Append(row);

    // In the new row, find the column location to insert a cell in A1.  
    Cell refCell = null;
    foreach (Cell cell in row.Elements<Cell>())
    {
      if (string.Compare(cell.CellReference.Value, "A1", true) > 0)
      {
        refCell = cell;
        break;
      }
    }

    // Add the cell to the cell table at A1.
    Cell newCell = new Cell() { CellReference = "A1" };
    row.InsertBefore(newCell, refCell);

    // Set the cell value to be a numeric value of 100.
    newCell.CellValue = new CellValue("100");
    newCell.DataType = new EnumValue<CellValues>(CellValues.Number);

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
      Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
      SheetId = 1,
      Name = "mySheet"
    };

    sheets.Append(sheet);

    workbookpart.Workbook.Save();
    spreadsheetDocument.Close();

    return File(mem.ToArray(), System.Net.Mime.MediaTypeNames.Application.Octet, "text.xlsx");
  }

I have done this before, I think you need to remove the ActionResult. Make it a void and remove the return View(MyView). this is the solution

I used a list in my controller class to set data into grid view. The code works fine for me:

public ActionResult ExpExcl()
{
  List<PersonModel> person= new List<PersonModel>
   { 
    new PersonModel() {FirstName= "Jenny", LastName="Mathew", Age= 23},
    new PersonModel() {FirstName= "Paul", LastName="Meehan", Age=25}
   };
   var grid= new GridView();
   grid.DataSource= person;
   grid.DataBind();

   Response.ClearContent();
   Response.AddHeader("content-disposition","attachement; filename=data.xls");
   Response.ContentType="application/excel";
   StringWriter sw= new StringWriter();
   HtmlTextWriter htw= new HtmlTextWriter(sw); 
   grid.RenderControl(htw);
   Response.Output.Write(sw.ToString());
   Response.Flush();
   Response.End();
   return View();
 }

The modest way to export excel in MVC is using Microsoft CloseXml. I wrote a simple function for exporting my query result as excel sheet:

using ClosedXML.Excel;
...
public ActionResult ToExcel(List<Dictionary<string, string>> data, Dictionary<string, string> columnMap, string fileName, string sheetName)
{
    var dtDataBuffer = new System.Data.DataTable("buffer");

    foreach (string col in columnMap.Values)
    {
        dtDataBuffer.Columns.Add(col, typeof(string));
    }



    foreach (var row in data)
    {
        List<string> rowData = new List<string> { };


        foreach (string col in columnMap.Keys)
        {
            rowData.Add(row[col]);
        }

        dtDataBuffer.Rows.Add(rowData.ToArray());
    }




    var memoryStream = new MemoryStream();

    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add(dtDataBuffer, sheetName);
        worksheet.Rows().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Rows().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Columns().AdjustToContents();
        workbook.SaveAs(memoryStream);
    }

    return File(memoryStream.ToArray(), "application/vnd.ms-excel", fileName);
}

And this is a usage example (in practice I use my own class to run query and generate data. You can find it here for Oracle and SQL Server):

public ActionResult myReportExport(){
    var data=List<Dictionary<string, string>>(){
        {{"Column1_Index","Column1_Value"},{"Column2_Index","Column2_Value"},...}
        ...
    };
    
    
    return ToExcel(data, new Dictionary<string, string> {
            { "Column1_Index", "Column1 Title" } ,
            { "Column2_Index", "Column2 Title" } ,
            ...
        },
        "myFileName.xlsx",
        "my sheet name"
    );
}