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