tutorial - Exportar datos a un archivo de Excel con ASP.NET MVC 4 C#se está mostrando a la vista

razor mvc

Question

Estoy teniendo problemas para exportar datos a Excel. Lo siguiente parece representar la vista de cuadrícula en mi Vista, en lugar de pedirle al usuario que abra con Excel, que he instalado en mi máquina.

 Public ActionResult ExportToExcel()
{            
    var products = this.Repository.Products.ToList();

    var grid = new GridView();
    grid.DataSource = from p in products
                      select new
                      {
                          Id = p.Id,
                          Name = p.Name
                      };
    grid.DataBind();

    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
    Response.ContentType = "application/ms-excel";

    Response.Charset = "";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    grid.RenderControl(htw);

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

    return View("MyView"); 
}

¿Qué estoy haciendo mal?

Answer 1

He hecho esto antes, creo que necesitas eliminar ActionResult. Conviértalo en un vacío y elimine la Vista de retorno (MyView). esta es la solucion

Answer 2

He intentado su código y funciona bien. El archivo se está creando sin ningún problema, este es el código que usé (es su código, acabo de cambiar la fuente de datos para la prueba):

    public ActionResult ExportToExcel()
    {
        var products = new System.Data.DataTable("teste");
        products.Columns.Add("col1", typeof(int));
        products.Columns.Add("col2", typeof(string));

        products.Rows.Add(1, "product 1");
        products.Rows.Add(2, "product 2");
        products.Rows.Add(3, "product 3");
        products.Rows.Add(4, "product 4");
        products.Rows.Add(5, "product 5");
        products.Rows.Add(6, "product 6");
        products.Rows.Add(7, "product 7");


        var grid = new GridView();
        grid.DataSource = products;
        grid.DataBind();

        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
        Response.ContentType = "application/ms-excel";

        Response.Charset = "";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        grid.RenderControl(htw);

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

        return View("MyView");
    }

Answer 3

Puedes llamar a la clase auxiliar en cualquier controlador.

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

Answer 4

Utilicé una lista en mi clase de controlador para configurar los datos en la vista de cuadrícula. El código funciona bien para mí:

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

Answer 5

Paso 1 : Ver código de página

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

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

  window.location = ''/Inventory/ExportInventory'';

        });
});
</script>

Paso 2 : Código del controlador

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

Answer 6

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