我想使用EPPlus将数据表导出到Excel文件。该数据表具有int类型的属性,因此我希望Excel文件中也保留相同的格式。
有人知道如何导出这样的DataTable到Excel吗?
using (ExcelPackage pck = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
pck.Save();
}
那应该对你有用。如果你的字段被定义为int类型,EPPlus会将列正确转换为数字或浮点数。
如果您希望在浏览器响应中下载
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);
}
要在浏览器中下载Excel表格,请使用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();
}
这里有一段代码片段,可以将 DataSet 导出到 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));
}
}
使用语句:
using OfficeOpenXml;
using System.Data;
using System.IO;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial
DataTable dt = ...;
string sheetName = ...;
string dateFormat = "yyyy-MM-dd HH:mm:ss";
using var p = new ExcelPackage();
var ws = p.Workbook.Worksheets.Add(sheetName);
ws.Cells["A1"].LoadFromDataTable(dt, PrintHeaders: true);
for (int c = 0; c < dt.Columns.Count; c++)
{
if (dt.Columns[c].DataType == typeof(DateTime))
{
ws.Column(c + 1).Style.Numberformat.Format = dateFormat;
}
}
string fileName = ...; //without extension
return File(p.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName + ".xlsx");
注意 using
的范围!这是 C#8 语法,只在声明它的代码块结束之前有效。
Dim dt As DataTable = ...
Dim sheetName As String = ...
Dim dateFormat As String = "yyyy-MM-dd HH:mm:ss"
Using p As New ExcelPackage()
Dim ws = p.Workbook.Worksheets.Add(sheetName)
ws.Cells("A1").LoadFromDataTable(dt, PrintHeaders:=True)
For c As Integer = 0 To dt.Columns.Count - 1
If dt.Columns(c).DataType Is GetType(Date) Then
ws.Column(c + 1).Style.Numberformat.Format = dateFormat
End If
Next
End Using
关于下载,必须将其放置在使用块内
Dim fileName As String = ... 'without extension
Return File(p.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName & ".xlsx")