.NetCore+EPPlus导出Excel报表

适用于跨平台部署

• NuGet引入EPPlus.Core
• 定义公共调用方法
• 完成调用

NuGet引入EPPlus.Core

• 因为本人使用的框架是.NetCore 2.0版，使用docker发布在Linux上。引入最新版本的EPPlus（1.5.4）发布后会报异常（The type initializer for ‘Gdip’ threw an exception.），所以现在我使用的版本是1.5.2
  

定义公共调用方法

• 直接贴代码

using OfficeOpenXml;
using System;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using System.Text;
namespace Application.Common
{
#region EPPlus
/// <summary>
/// EPPlus导出
/// </summary>
public class ExportReport
{
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="sWebRootFolder">webRoot文件夹</param>
/// <param name="sFileName">文件名</param>
/// <param name="sColumnName">自定义列名（不传默认dt列名）</param>
/// <returns></returns>
public static bool ExportExcel(DataTable dt, string sWebRootFolder, string sFileName, string[] sColumnName, ref string msg)
{
try
{
if (dt == null || dt.Rows.Count == 0)
{
msg = "没有符合条件的数据！";
return false;
}
//转utf-8
UTF8Encoding utf8 = new UTF8Encoding();
byte[] buffer = utf8.GetBytes(sFileName);
sFileName = utf8.GetString(buffer);
//判断文件夹
sWebRootFolder = Path.Combine(sWebRootFolder, "ExprotExcel");
if (!Directory.Exists(sWebRootFolder))
Directory.CreateDirectory(sWebRootFolder);
//删除大于7天的文件
string[] files = Directory.GetFiles(sWebRootFolder, "*.xlsx", SearchOption.AllDirectories);
foreach (string item in files)
{
FileInfo f = new FileInfo(item);
DateTime now = DateTime.Now;
TimeSpan t = now - f.CreationTime;
int day = t.Days;
if (day > 7)
{
File.Delete(item);
}
}
//判断同名文件
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
if (file.Exists)
{
//判断同名文件创建时间
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
}
using (ExcelPackage package = new ExcelPackage(file))
{
//添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName.Split('.')[0]);
//添加表头
int column = 1;
if (sColumnName.Count() == dt.Columns.Count)
{
foreach (string cn in sColumnName)
{
worksheet.Cells[1, column].Value = cn.Trim();
worksheet.Cells[1, column].Style.Font.Bold = true;//字体为粗体
worksheet.Cells[1, column].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//水平居中
worksheet.Cells[1, column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//设置样式类型
worksheet.Cells[1, column].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(159, 197, 232));//设置单元格背景色
column++;
}
}
else
{
foreach (DataColumn dc in dt.Columns)
{
worksheet.Cells[1, column].Value = dc.ColumnName;
worksheet.Cells[1, column].Style.Font.Bold = true;//字体为粗体
worksheet.Cells[1, column].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//水平居中
worksheet.Cells[1, column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//设置样式类型
worksheet.Cells[1, column].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(159, 197, 232));//设置单元格背景色
column++;
}
}
//添加数据
int row = 2;
foreach (DataRow dr in dt.Rows)
{
int col = 1;
foreach (DataColumn dc in dt.Columns)
{
worksheet.Cells[row, col].Value = dr[col - 1].ToString();
col++;
}
row++;
}
//自动列宽
worksheet.Cells.AutoFitColumns();
//保存workbook.
package.Save();
}
return true;
}
catch (Exception ex)
{
msg = "生成Excel失败：" + ex.Message;
UnionLog.WriteLog("", Common.LogType.UNION_INFO, "生成Excel失败：" + ex.Message);
return false;
}
}
}
#endregion EPPlus
}

完成调用

• 一般浏览器直接打开文件url会直接弹出下载提示，直接贴代码：

using Microsoft.AspNetCore.Hosting;
public class OrderController : WebController
{
private IHostingEnvironment _hostingEnvironment;
public OrderController(IHostingEnvironment hostingEnvironment)
{
_hostingEnvironment = hostingEnvironment;
}
//调用方法
public ActionResult ActiveOrder_ToExcel()
{
//自己查的数据
DataTable dt = new DataTable();
var msg = string.Empty;//返回消息
string sWebRootFolder = _hostingEnvironment.WebRootPath;//获取网络根路径
string sFileName = @"ActiveOrder" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";//文件名
string[] sColumnName = { "积分明细编号", "订单编号(子)", "活动名称", "SPID", "手机号", "新增积分", "创建时间", "发行时间", "受理状态", "发行状态", "备注" };//自定义列名
bool isResult = ExportReport.ExportExcel(dt, sWebRootFolder, sFileName, sColumnName, ref msg);//调用方法
URL = string.Format("{0}://{1}/{2}/{3}", Request.Scheme, Request.Host, "ExprotExcel", sFileName);//导出文件url
if (isResult)
return Json(new { url = URL, msg = "成功！" });
else
return Json(new { url = "", msg = string.IsNullOrWhiteSpace(msg) ? "生成Excel失败！" : msg });
}
}

转载请标明原创出处

最后

以上就是诚心咖啡豆为你收集整理的.NetCore+EPPlus导出Excel报表.NetCore+EPPlus导出Excel报表的全部内容，希望文章能够帮你解决.NetCore+EPPlus导出Excel报表.NetCore+EPPlus导出Excel报表所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。