gpt4 book ai didi

c# - 如何导出到 Excel?

转载 作者:太空狗 更新时间:2023-10-29 21:33:03 24 4
gpt4 key购买 nike

我关注了this guide这是在另一篇关于此的帖子中推荐的,但我不能完全让它工作。尝试重定向到/LogModelsController/ExportData 时出现 404 错误,据我所知,这是我应该做的。

_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" />
}

_LogPartialLayout.cshtml

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:如何将此表正确导出到 Excel?

奖励:它也适用于 PagedList 吗?


编辑 3 - 已解决

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

我在 CommandText 中犯了一个错误,用“Id”代替了“CustomerId”——它现在返回 Excel 文件中的整个表格。

最佳答案

该指南没有展示如何创建 Excel 文件,它展示了创建带有表格的 HTML 文件的技巧。 Excel 将尝试使用默认设置导入带有表格的 csv 或 HTML 文件,这通常会失败。

使用 EPPlus 这样的库创建正确 Excel 文件要容易得多.我确定之前已经回答过这个问题,但我找不到同时显示如何 create an Excel file from data 的答案。并设置 proper content type对于 xlsx 文件。

EPPlus 提供 LoadFromDataTable 和 LoadFromCollection 方法,用从 DataTable 或通用集合中提取的数据填充 Excel 范围。列名或属性名分别用作标题。

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 或 LoadFromCollection 返回可用于格式化表格的 Excel 单元格范围:

var range=ws.Cells["A1"].LoadFromDataTable(table);
range.Style.Numberformat.Format = "#,##0.00";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

您还可以将表格样式应用于范围,或将所需样式传递给 LoadFromDataTableLoadFromCollection,例如:

ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

图书馆的Githup wiki显示如何 format the result 、生成公式、数据透视表、处理表格等。

如果您想创建一个相对较大的工作表,保存到 MemoryStream 可能是个问题。将相同的数据写入两次,一次写入 MemoryStream,然后将其复制到输出,这会浪费周期。但是,直接从 MVC 操作写入输出流是一种不好的做法。诀窍是创建一个自定义 FileResult 来处理 EPPlus 包并返回它而不是 File 返回的 FileResult,例如:

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

它允许您编写以下操作:

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

关于c# - 如何导出到 Excel?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32837808/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com