/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <param name="headerCell">Excel表头</param>
/// <param name="table">源DataTable</param>
/// <param name="savePath">保存路径</param>
/// <param name="action">sheet自定义处理委托</param>
public static void EPPlusExportExcelToFile(ExcelHeaderCell headerCell, DataTable table, string savePath, Action <ExcelWorksheet> action = null)
{
if (table?.Rows.Count > 0)
{
using (var package = new ExcelPackage(new FileInfo(savePath)))
{
using (var sheet = package.Workbook.Worksheets.Add(table.TableName.IsNullOrEmpty() ? "Sheet1" : table.TableName))
{
//设置边框样式
sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
//水平居中
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//垂直居中
sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//单元格自动适应大小
sheet.Cells.AutoFitColumns();
//构建表头
BuildExcelHeader(null, null, headerCell, sheet);
//加载数据
var firstCell = headerCell.ChildHeaderCells.FirstOrDefault();
sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromDataTable(table, false);
//单独设置单元格
action?.Invoke(sheet);
package.Save();
}
}
}
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="headerCell">Excel表头</param>
/// <param name="list">源泛型集合</param>
/// <param name="savePath">保存路径</param>
/// <param name="action">sheet自定义处理委托</param>
public static void EPPlusExportExcelToFile <T>(ExcelHeaderCell headerCell, IEnumerable <T> list, string savePath, Action <ExcelWorksheet> action = null) where T : class, new()
{
if (list?.Count() > 0)
{
using (var package = new ExcelPackage(new FileInfo(savePath)))
{
using (var sheet = package.Workbook.Worksheets.Add("Sheet1"))
{
//设置边框样式
sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
//水平居中
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//垂直居中
sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//单元格自动适应大小
sheet.Cells.AutoFitColumns();
//构建表头
BuildExcelHeader(null, null, headerCell, sheet);
//加载数据
var firstCell = headerCell.ChildHeaderCells.FirstOrDefault();
sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false);
//单独设置单元格
action?.Invoke(sheet);
package.Save();
}
}
}
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="headerCell">Excel表头</param>
/// <param name="list">源泛型集合</param>
/// <param name="fileName">文件名</param>
/// <param name="ext">扩展名(.xls|.xlsx)可选参数</param>
/// <param name="responseEnd">是否输出结束，默认：是</param>
/// <param name="action">sheet自定义处理委托</param>
public static void EPPlusExportExcel <T>(ExcelHeaderCell headerCell, List <T> list, string fileName, string ext = ".xlsx", bool responseEnd = true, Action <ExcelWorksheet> action = null) where T : class, new()
{
if (list?.Count > 0)
{
try
{
using (var package = new ExcelPackage())
{
//配置文件属性
package.Workbook.Properties.Category = "类别";
package.Workbook.Properties.Author = "作者";
package.Workbook.Properties.Comments = "备注";
package.Workbook.Properties.Company = "公司名称";
package.Workbook.Properties.Keywords = "关键字";
package.Workbook.Properties.Manager = "张强";
package.Workbook.Properties.Status = "内容状态";
package.Workbook.Properties.Subject = "主题";
package.Workbook.Properties.Title = "标题";
package.Workbook.Properties.LastModifiedBy = "最后一次保存者";
using (var sheet = package.Workbook.Worksheets.Add("Sheet1"))
{
//设置边框样式
sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
//水平居中
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//垂直居中
sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//单元格自动适应大小
sheet.Cells.AutoFitColumns();
//构建表头
BuildExcelHeader(null, null, headerCell, sheet);
//加载数据
var firstCell = headerCell.ChildHeaderCells.FirstOrDefault();
sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false);
//单独设置单元格
action?.Invoke(sheet);
//写到客户端（下载）
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.AddHeader("content-disposition", $"attachment;filename={HttpUtility.UrlEncode(fileName + ext, Encoding.UTF8)}");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("utf-8");
HttpContext.Current.Response.BinaryWrite(package.GetAsByteArray());
HttpContext.Current.Response.Flush();
if (responseEnd)
{
HttpContext.Current.Response.End();
}
}
}
}
catch (Exception ex)
{
LogHelper.Error(ex, "EPPlus导出Excel(2003/2007)");
}
}
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="headerCell">Excel表头</param>
/// <param name="list">源泛型集合</param>
/// <param name="action">sheet自定义处理委托</param>
public static byte[] EPPlusExportExcelToBytes <T>(ExcelHeaderCell headerCell, IEnumerable <T> list, Action <ExcelWorksheet> action = null) where T : class, new()
{
if (list.IsNullOrEmpty())
{
return(null);
}
using var package = new ExcelPackage();
using var sheet = package.Workbook.Worksheets.Add("Sheet1");
//设置边框样式
sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
//水平居中
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//垂直居中
sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//单元格自动适应大小
sheet.Cells.AutoFitColumns();
//构建表头
BuildExcelHeader(null, null, headerCell, sheet);
//加载数据
var firstCell = headerCell.ChildHeaderCells.FirstOrDefault();
sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false);
//单独设置单元格
action?.Invoke(sheet);
return(package.GetAsByteArray());
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <param name="headerCell">Excel表头</param>
/// <param name="table">源DataTable</param>
/// <param name="action">sheet自定义处理委托</param>
/// <returns></returns>
public static byte[] EPPlusExportExcelToBytes(ExcelHeaderCell headerCell, DataTable table, Action <ExcelWorksheet> action = null)
{
if (table?.Rows == null || table.Rows.Count == 0)
{
return(null);
}
using var package = new ExcelPackage();
using var sheet = package.Workbook.Worksheets.Add(table.TableName.IsNullOrEmpty() ? "Sheet1" : table.TableName);
//设置边框样式
sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
//水平居中
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//垂直居中
sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//单元格自动适应大小
sheet.Cells.AutoFitColumns();
//构建表头
BuildExcelHeader(null, null, headerCell, sheet);
//加载数据
var firstCell = headerCell.ChildHeaderCells.FirstOrDefault();
sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromDataTable(table, false);
//单独设置单元格
action?.Invoke(sheet);
return(package.GetAsByteArray());
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="headerCell">Excel表头</param>
/// <param name="list">源泛型集合</param>
/// <param name="fileName">文件名</param>
/// <param name="ext">扩展名(.xls|.xlsx)可选参数</param>
/// <param name="action">sheet自定义处理委托</param>
public static async Task EPPlusExportExcelAsync <T>(ExcelHeaderCell headerCell, IEnumerable <T> list, string fileName, string ext = ".xlsx", Action <ExcelWorksheet> action = null) where T : class, new()
{
var bytes = EPPlusExportExcelToBytes(headerCell, list, action);
if (bytes != null)
{
await FileHelper.GetFileAsync(bytes, HttpUtility.UrlEncode(fileName + ext, Encoding.UTF8), "application/ms-excel");
}
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <param name="headerCell">Excel表头</param>
/// <param name="table">源DataTable</param>
/// <param name="fileName">文件名</param>
/// <param name="ext">扩展名(.xls|.xlsx)可选参数</param>
/// <param name="action">sheet自定义处理委托</param>
public static async Task EPPlusExportExcelAsync(ExcelHeaderCell headerCell, DataTable table, string fileName, string ext = ".xlsx", Action <ExcelWorksheet> action = null)
{
//获取bytes
var bytes = EPPlusExportExcelToBytes(headerCell, table, action);
if (bytes != null)
{
await FileHelper.GetFileAsync(bytes, HttpUtility.UrlEncode(fileName + ext, Encoding.UTF8), "application/ms-excel");
}
}
/// <summary>
/// 构建Excel表头
/// </summary>
/// <param name="prevCell">前一个单元格</param>
/// <param name="parentSell">父级单元格</param>
/// <param name="currCell">当前单元格</param>
/// <param name="sheet">Excel WorkSheet</param>
public static void BuildExcelHeader(ExcelHeaderCell prevCell, ExcelHeaderCell parentSell, ExcelHeaderCell currCell, ExcelWorksheet sheet)
{
currCell.FromRow = prevCell != null ? prevCell.FromRow : (parentSell != null ? parentSell.FromRow + 1 : 1);
currCell.FromCol = prevCell != null ? prevCell.ToCol + 1 : (parentSell != null ? parentSell.FromCol : 1);
currCell.ToRow = currCell.FromRow;
currCell.ToCol = currCell.FromCol;
if (currCell.IsRowspan)
{
currCell.ToRow = currCell.FromRow - 1 + currCell.Rowspan;
}
if (currCell.IsColspan)
{
currCell.ToCol = currCell.FromCol - 1 + currCell.Colspan;
}
var sell = sheet.Cells[currCell.FromRow, currCell.FromCol, currCell.ToRow, currCell.ToCol];
//设置单元格属性
sell.Value = currCell.Title;
sell.Style.Font.Bold = currCell.Bold;
sell.Style.Font.Color.SetColor(currCell.FontColor);
if (currCell.HorizontalAlignment != null)
{
sell.Style.HorizontalAlignment = currCell.HorizontalAlignment.Value;
}
if (currCell.VerticalAlignment != null)
{
sell.Style.VerticalAlignment = currCell.VerticalAlignment.Value;
}
if (currCell.BackgroundColor != null)
{
sell.Style.Fill.SetBackground(currCell.BackgroundColor.Value);
}
//合并单元格
if (currCell.IsColspan || currCell.IsRowspan)
{
sheet.Cells[currCell.FromRow, currCell.FromCol, currCell.ToRow, currCell.ToCol].Merge = true;
}
//判断是否有子元素，递归调用
if (currCell.ChildHeaderCells?.Count > 0)
{
foreach (var item in currCell.ChildHeaderCells)
{
var index = currCell.ChildHeaderCells.IndexOf(item);
BuildExcelHeader(index == 0 ? null : currCell.ChildHeaderCells[index - 1], currCell, item, sheet);
}
}
}
/// <summary>
/// EPPlus导出Excel(2003/2007)
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="headerCell">Excel表头</param>
/// <param name="list">源泛型集合</param>
/// <param name="savePath">保存路径</param>
/// <param name="action">sheet自定义处理委托</param>
public static void EPPlusExportExcelToFile <T>(ExcelHeaderCell headerCell, List <T> list, string savePath, Action <ExcelWorksheet> action = null) where T : class, new()
{
if (list?.Count > 0)
{
try
{
using (var package = new ExcelPackage(new FileInfo(savePath)))
{
//配置文件属性
package.Workbook.Properties.Category = "类别";
package.Workbook.Properties.Author = "作者";
package.Workbook.Properties.Comments = "备注";
package.Workbook.Properties.Company = "公司名称";
package.Workbook.Properties.Keywords = "关键字";
package.Workbook.Properties.Manager = "张强";
package.Workbook.Properties.Status = "内容状态";
package.Workbook.Properties.Subject = "主题";
package.Workbook.Properties.Title = "标题";
package.Workbook.Properties.LastModifiedBy = "最后一次保存者";
using (var sheet = package.Workbook.Worksheets.Add("Sheet1"))
{
//设置边框样式
sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
//水平居中
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//垂直居中
sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//单元格自动适应大小
sheet.Cells.AutoFitColumns();
//构建表头
BuildExcelHeader(null, null, headerCell, sheet);
//加载数据
var firstCell = headerCell.ChildHeaderCells.FirstOrDefault();
sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false);
//单独设置单元格
action?.Invoke(sheet);
package.Save();
}
}
}
catch (Exception ex)
{
LogHelper.Error(ex, "EPPlus导出Excel(2003/2007)");
}
}
}
