C# 使用 EPPlus 将数据表导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13669733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export DataTable to Excel with EPPlus
提问by Davood Hanifi
I want to export a data table to an Excel file with EPPlus. That data table has a property with int type, so I want the same format in the Excel file.
我想使用 EPPlus 将数据表导出到 Excel 文件。该数据表具有 int 类型的属性，因此我希望 Excel 文件中具有相同的格式。
Does anyone know way to export a DataTable like this to Excel?
有谁知道如何将这样的数据表导出到 Excel？
采纳答案by bastianwegge
using (ExcelPackage pck = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
pck.Save();
}
That should do the trick for you. If your fields are defined as int EPPlus will properly cast the columns into a number or float.
那应该对你有用。如果您的字段定义为 int EPPlus 将正确地将列转换为数字或浮点数。
回答by Taran
and if you want to download in browser response
如果您想在浏览器响应中下载
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode("Logs.xlsx", System.Text.Encoding.UTF8));
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Logs");
ws.Cells["A1"].LoadFromDataTable(dt, true);
var ms = new System.IO.MemoryStream();
pck.SaveAs(ms);
ms.WriteTo(Response.OutputStream);
}
回答by KanisXXX
For downloading excelsheet in browser use HttpContext.Current.Responseinstead of Responseotherwise you will get Response is not available in this context.error.Here is my code
在浏览器中下载 excelsheet 使用HttpContext.Current.Response而不是Response否则你会得到Response is not available in this context.错误。这是我的代码
public void ExporttoExcel(DataTable table, string filename)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=GridData.xlsx");
using (ExcelPackage pack = new ExcelPackage())
{
ExcelWorksheet ws = pack.Workbook.Worksheets.Add(filename);
ws.Cells["A1"].LoadFromDataTable(table, true);
var ms = new System.IO.MemoryStream();
pack.SaveAs(ms);
ms.WriteTo(HttpContext.Current.Response.OutputStream);
}
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
回答by SubqueryCrunch
Here is a snippet to export DataSet to Excel:
这是将数据集导出到 Excel 的代码段：
private static void DataSetToExcel(DataSet dataSet, string filePath)
{
using (ExcelPackage pck = new ExcelPackage())
{
foreach (DataTable dataTable in dataSet.Tables)
{
ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(dataTable.TableName);
workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);
}
pck.SaveAs(new FileInfo(filePath));
}
}
And using statements:
并使用语句：
using OfficeOpenXml;
using System.Data;
using System.IO;
