如何导出到Excel？ [英] How do I export To Excel?
问题描述
我遵循
LogModelsController.cs
public ActionResult ExportData（）
{
GridView gv = new GridView（）;
gv.DataSource = db.Log.ToList（）;
gv.DataBind（）;
Response.ClearContent（）;
Response.Buffer = true;
Response.AddHeader（content-disposition，attachment; filename = Loglist.xls）;
Response.ContentType =application / ms-excel;
Response.Charset =;
StringWriter sw = new StringWriter（）;
HtmlTextWriter htw = new HtmlTextWriter（sw）;
gv.RenderControl（htw）;
Response.Output.Write（sw.ToString（））;
Response.Flush（）;
Response.End（）;
return RedirectToAction（〜/ LogModels / Index）;
}
LogModel.cs
命名空间ASDMVC.Models
{
[Table（Log）]
public class LogModel
{
[Key]
public long id {get;组; }
public string message {get;组; }
public DateTime timeStamp {get;组; }
public string level {get;组; }
public int customerId {get;组;
public class LogDBContext：DbContext
{
public LogDBContext（）：base（MySqlConnection）
{
}
public DbSet&LogModel>日志{get;组; }
}
}
TD; DR：怎么做我正确导出这个表到Excel？
奖金：它是否与PagedList一起工作？
编辑3 - 解决
Index.cshtml：
@using（Html.BeginForm（ExportData，LogModels））
{
< input type =submitname =Exportid =导出值=导出/>
}
LogModelsController.cs
public ActionResult ExportData（）
{
DataTable dataTable = GetData（）;
使用（ExcelPackage package = new ExcelPackage（））
{
var ws = package.Workbook.Worksheets.Add（My Sheet）;
// true生成标头
ws.Cells [A1]。LoadFromDataTable（dataTable，true）;
var stream = new MemoryStream（）;
package.SaveAs（stream）;
string fileName =myfilename.xlsx;
string contentType =application / vnd.openxmlformats-officedocument.spreadsheetml.sheet;
stream.Position = 0;
return File（stream，contentType，fileName）;
}
}
public DataTable GetData（）
{
DataTable dt = new DataTable（）;
if（ModelState.IsValid）
{
using（SqlConnection conn = new SqlConnection（System.Configuration.ConfigurationManager.ConnectionStrings [MySqlConnection]。ConnectionString））
{
using（SqlCommand comm = conn.CreateCommand（））
{
comm.Parameters.AddWithValue（@ val1，Session [myID]）;
comm.CommandText =SELECT * FROM dbo.Log WHERE CustomerId = @ val1;
try
{
conn.Open（）;
dt.Load（comm.ExecuteReader（））;
}
catch（SqlException e）
{
throw new Exception（e.ToString（））;
}
}
}
}
return dt;
}
我在CommandText中出错，放置Id而不是CustomerId - 它现在返回Excel文件中的整个表。
该指南不显示如何创建Excel文件，它显示了一个使用表格创建HTML文件的hack 。 Excel将尝试使用默认设置导入csv或HTML文件，这些默认设置通常会失败。
使用像正确的 Excel文件要容易得多包/ EPPlus /rel =nofollow noreferrer> EPPlus 。
我确定以前已经回答过，但我找不到一个答案，显示如何从数据创建一个Excel文件，并设置正确的内容类型为 xlsx 文件。
EPPlus提供LoadFromDataTable和LoadFromCollection方法，以从DataTable或通用集合中提取的数据填充Excel范围。列或属性名称分别用作标题。
public ActionResult ExportData（）
{
//以某种方式，使用（ExcelPackage package = new ExcelPackage（））将数据加载到DataTable
{
var ws = package.Workbook.Worksheets.Add 我的单）;
// true生成标头
ws.Cells [A1]。LoadFromDataTable（dataTable，true）;
var stream = new MemoryStream（）;
package.SaveAs（stream）;
string fileName =myfilename.xlsx;
string contentType =application / vnd.openxmlformats-officedocument.spreadsheetml.sheet;
stream.Position = 0;
return File（stream，contentType，fileName）;
}
}
LoadFromDataTable或LoadFromCollection返回一个Excel单元格范围，可以是用于格式化表：
var range = ws.Cells [A1]。LoadFromDataTable（table）;
range.Style.Numberformat.Format =＃，## 0.00;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
您还可以将表格样式应用于范围，或将所需样式传递到 LoadFromDataTable 或 LoadFromCollection ，例如：
ws.Cells [1,1] .LoadFromDataTable（table，true，TableStyles.Light1）;
图书馆的显示如何格式化结果，生成公式，数据透视表，句柄表等。
保存到MemoryStream可能是一个问题，如果你想创建一个比较大的工作表。将相同的数据写入两次，一次到MemoryStream，然后将其复制到输出，浪费循环。直接从MVC动作直接写入输出流是不好的做法。诀窍是创建一个自定义FileResult来处理EPPlus包并返回，而不是由文件返回的FileResult，例如：
public class EpplusResult：FileResult
{
public EpplusResult（ExcelPackage package）
：base（application / vnd.openxmlformats-officedocument.spreadsheetml。表单）
{
if（package == null）
{
throw new ArgumentNullException（package）;
}
Package = package;
}
public ExcelPackage Package {get;私人集
protected override void WriteFile（HttpResponseBase响应）
{
//抓取数据块并写入输出流
流outputStream = response.OutputStream;
使用（包）
{
Package.SaveAs（outputStream）;
}
}
}
以下操作：
public FileResult ExportData（）
{
ExcelPackage package = new ExcelPackage （）;
var ws = package.Workbook.Worksheets.Add（My Sheet）;
...
ws.Cells [1,1] .LoadFromDataTable（table，true，TableStyles.Light1）;
返回新的EpplusResult（包）{FileDownloadName =SomeFile.xlsx};
}
I followed this guide which was recommended in another post about this but I can't quite get it to work. I get a 404 error when trying to redirect to /LogModelsController/ExportData which - as I understood it - was what I was supposed to do.
_LogPartialLayout.cshtml
@using (Html.BeginForm("ExportData", "LogModelsController", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<table class="table">
<tr>
<th>
@Html.ActionLink("message", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
</th>
<th>
@Html.ActionLink("timestamp", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
</th>
<th>
@Html.ActionLink("level", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.message)
</td>
<td>
@Html.DisplayFor(modelItem => item.timeStamp)
</td>
<td>
@Html.DisplayFor(modelItem => item.level)
</td>
</tr>
}
</table>
<input type="submit" name="Export" id="Export" value="Export" />
}
LogModelsController.cs
public ActionResult ExportData()
{
GridView gv = new GridView();
gv.DataSource = db.Log.ToList();
gv.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=Loglist.xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
return RedirectToAction("~/LogModels/Index");
}
LogModel.cs
namespace ASDMVC.Models
{
[Table("Log")]
public class LogModel
{
[Key]
public long id { get; set; }
public string message { get; set; }
public DateTime timeStamp { get; set; }
public string level { get; set; }
public int customerId { get; set; }
}
public class LogDBContext:DbContext
{
public LogDBContext() : base("MySqlConnection")
{
}
public DbSet <LogModel> Log { get; set; }
}
}
TD;DR: How do I properly export this table to Excel?
Bonus: Does it work with PagedList too?
Edit 3 - SOLVED
Index.cshtml:
@using (Html.BeginForm("ExportData", "LogModels"))
{
<input type="submit" name="Export" id="Export" value="Export" />
}
LogModelsController.cs
public ActionResult ExportData()
{
DataTable dataTable = GetData();
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("My Sheet");
//true generates headers
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
var stream = new MemoryStream();
package.SaveAs(stream);
string fileName = "myfilename.xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
stream.Position = 0;
return File(stream, contentType, fileName);
}
}
public DataTable GetData()
{
DataTable dt = new DataTable();
if (ModelState.IsValid)
{
using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
{
using (SqlCommand comm = conn.CreateCommand())
{
comm.Parameters.AddWithValue("@val1", Session["myID"]);
comm.CommandText = "SELECT * FROM dbo.Log WHERE CustomerId = @val1";
try
{
conn.Open();
dt.Load(comm.ExecuteReader());
}
catch (SqlException e)
{
throw new Exception(e.ToString());
}
}
}
}
return dt;
}
I made a mistake in the CommandText and put "Id" instead of "CustomerId" - it now returns the entire table in the Excel file.
The guide doesn't show how to create an Excel file, it shows a hack that creates an HTML file with a Table. Excel will try to import csv or HTML files with tables using default settings which can often fail.
It's far easier to create a proper Excel file using a library like EPPlus.
I'm sure this has been answered before but I can't find an answer that shows both how to create an Excel file from data and set the proper content type for an xlsx file.
EPPlus provides the LoadFromDataTable and LoadFromCollection methods to fill an Excel range with data pulled from a DataTable or generic collection. The column or property names are used as headers respectively.
public ActionResult ExportData()
{
//Somehow, load data to a DataTable
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("My Sheet");
//true generates headers
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
var stream = new MemoryStream();
package.SaveAs(stream);
string fileName = "myfilename.xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
stream.Position = 0;
return File(stream, contentType, fileName);
}
}
LoadFromDataTable or LoadFromCollection return an Excel cell range which can be used to format the table:
var range=ws.Cells["A1"].LoadFromDataTable(table);
range.Style.Numberformat.Format = "#,##0.00";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
You can also apply a table style to the range, or pass the desired style to LoadFromDataTable or LoadFromCollection, eg:
ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);
The library's Codeplex site shows how to format the result, generate formulas, PivotTables, handle tables etc.
Saving to a MemoryStream may be an issue if you want to create a relatively large sheet. Writing the same data twice, once to the MemoryStream and then copying it to output, wastes cycles. Writing directly to the output stream from an MVC action is a bad practice though. The trick is to create a custom FileResult that handles EPPlus packages and return that instead of the FileResult returned by File, eg:
public class EpplusResult:FileResult
{
public EpplusResult(ExcelPackage package)
: base("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
if (package == null)
{
throw new ArgumentNullException("package");
}
Package = package;
}
public ExcelPackage Package { get; private set; }
protected override void WriteFile(HttpResponseBase response)
{
// grab chunks of data and write to the output stream
Stream outputStream = response.OutputStream;
using (Package)
{
Package.SaveAs(outputStream);
}
}
}
Which allows you to write the following action:
public FileResult ExportData()
{
ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("My Sheet");
...
ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);
return new EpplusResult(package){FileDownloadName = "SomeFile.xlsx"};
}
这篇关于如何导出到Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
