/// <summary>
/// DataTable转Excel，并在浏览器中输出。
/// </summary>
/// <param name="dt"></param>
/// <param name="options"></param>
/// <returns>写入Excel的行数</returns>
public static void ToExcelInBrowser(DataTable dt, ExcelExportOptions options)
{
var workbook = GetWorkbook(dt, options);
if (workbook == null)
{
return;
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
workbook.Close();
#if NETSTANDARD
HttpContext httpContext = HttpContextExt.Current ?? throw new ArgumentNullException("HttpContextExt.Current");
httpContext.Response.ContentType = "application/octet-stream; charset=utf-8";
httpContext.Response.Headers.Add("Content-Disposition", $"attachment; filename={HttpUtility.UrlEncode(options.ExcelFilePath, Encoding.UTF8)}");
var data = ms.ToArray();
httpContext.Response.Body.WriteAsync(data, 0, data.Length);
#else
HttpContext httpContext = HttpContext.Current;
httpContext.Response.Clear();
httpContext.Response.Buffer = true;
//httpContext.Response.ContentType = "application/ms-excel";
httpContext.Response.ContentEncoding = Encoding.UTF8;
//httpContext.Response.Charset = "UTF-8";
httpContext.Response.AddHeader("Content-Disposition", $"attachment; filename={HttpUtility.UrlEncode(options.ExcelFilePath, Encoding.UTF8)}");
//httpContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", HttpUtility.UrlPathEncode(excelFileName)));
httpContext.Response.BinaryWrite(ms.ToArray());
httpContext.Response.End();
#endif
}
}
/// <summary>
/// 泛型数据导出到Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="options"></param>
public static void ToExcel <T>(IList <T> list, ExcelExportOptions options)
{
var workbook = GetWorkbook(list, options);
if (workbook != null)
{
Save(workbook, options.ExcelFilePath);
}
}
/// <summary>
/// DataTable转Excel。
/// </summary>
/// <param name="dt"></param>
/// <param name="options"></param>
/// <returns>写入Excel的行数</returns>
public static void ToExcel(DataTable dt, ExcelExportOptions options)
{
var workbook = GetWorkbook(dt, options);
if (workbook != null)
{
Save(workbook, options.ExcelFilePath);
}
}
protected override IWorkbook GetWorkbook(ExcelExportOptions options)
{
if (options.ExcelType.Equals(ExcelTypeEnum.Xlsx))
{
return(new XSSFWorkbook());
}
else
{
return(new HSSFWorkbook());
}
}
/// <summary>
/// 导出
/// </summary>
/// <typeparam name="TExportDto"><paramref name="data"/> 集合中元素的类（导出的表头顺序为字段顺序）</typeparam>
/// <param name="data">数据</param>
/// <param name="optionAction">配置选项</param>
/// <param name="onlyExportHeaderName">只需要导出的表头名称（指定则按 <typeparamref name="TExportDto"/> 字段顺序导出全部，不指定空则按数组顺序导出）</param>
/// <returns></returns>
public byte[] Export <TExportDto>(List <TExportDto> data, Action <ExcelExportOptions> optionAction, string[] onlyExportHeaderName)
where TExportDto : class, new()
{
try
{
ExcelExportOptions options = new ExcelExportOptions();
optionAction?.Invoke(options);
options.CheckError();
//获取工作册
TWorkbook workbook = GetWorkbook(options);
//创建工作表
TSheet worksheet = CreateSheet(workbook, options);
//验证表头，并获取要导出的表头信息
ExcelExportHeaderInfo[] headers = CheckHeader <TExportDto>(onlyExportHeaderName);
//表头行下标
int headerRowIndex = options.HeaderRowIndex - 1;
//先获取所有表头列的样式和字体
var headerStyle = GetHeaderColumnStyleAndFont <TExportDto>(workbook, worksheet);
//处理表头单元格
ProcessHeaderCell <TExportDto>(workbook, worksheet, headers, headerRowIndex, headerStyle);
//数据起始行下标
int dataRowIndex = options.DataRowStartIndex - 1;
//先获取所有数据列的样式和字体
var dataStyle = GetDataColumnStyleAndFont <TExportDto>(workbook, worksheet);
//处理数据单元格
ProcessDataCell(workbook, worksheet, headers, data, dataRowIndex, dataStyle, out int footerRowIndex);
//处理底部数据统计
ProcessFooterStatistics <TExportDto>(workbook, worksheet, headers, dataRowIndex, footerRowIndex, headerStyle, dataStyle);
//处理列宽【有数据才能处理自动列宽，所以必须放到最后进行处理】
ProcessColumnWidth <TExportDto>(workbook, worksheet, headers);
//转换并获取工作册字节
return(GetAsByteArray(workbook, worksheet));
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
/// <summary> /// 创建工作表【步骤 2】 /// </summary> /// <param name="workbook">工作册</param> /// <param name="options">配置选项</param> /// <returns></returns> protected abstract TSheet CreateSheet(TWorkbook workbook, ExcelExportOptions options);
/// <summary> /// 获取工作册【步骤 1】 /// </summary> /// <param name="options">配置选项</param> /// <returns></returns> protected abstract TWorkbook GetWorkbook(ExcelExportOptions options);
protected override ExcelWorksheet CreateSheet(ExcelWorkbook workbook, ExcelExportOptions options)
{
return(workbook.Worksheets.Add(options.SheetName));
}
protected override ExcelWorkbook GetWorkbook(ExcelExportOptions options)
{
var excelPackage = new ExcelPackage();
return(excelPackage.Workbook);
}
private static IWorkbook GetWorkbook <T>(IList <T> list, ExcelExportOptions options)
{
if (list == null)
{
throw new ArgumentNullException(nameof(list));
}
IWorkbook workbook = null;
ISheet sheet;
switch (options.CreateFileType)
{
case CreateFileType.GetOrCreate:
workbook = GetOrCreateWorkbook(options.ExcelFilePath);
break;
case CreateFileType.Get:
workbook = GetWorkbook(options.ExcelFilePath);
break;
case CreateFileType.Create:
workbook = CreateWorkbook(options.ExcelFilePath);
break;
}
if (workbook == null)
{
return(null);
}
#region 设置单元格样式
ICellStyle cellStyleHeader = null;
ICellStyle cellStyleContent = null;
if (options.DefaultCellStyle != null)
{
#region 标题
cellStyleHeader = workbook.CreateCellStyle();
if (options.DefaultCellStyle.Border)
{
//设置边框格式
cellStyleHeader.BorderTop = BorderStyle.Thin;
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.BorderLeft = BorderStyle.Thin;
cellStyleHeader.BorderRight = BorderStyle.Thin;
}
if (options.DefaultCellStyle.TitleFontHorizontalCenter)
{
cellStyleHeader.Alignment = HorizontalAlignment.Center; //字体水平居中
}
IFont fontHeader = workbook.CreateFont();
fontHeader.IsBold = options.DefaultCellStyle.TitleFontBold; //字体是否加粗
fontHeader.FontHeightInPoints = options.DefaultCellStyle.TitleFontSize; //字体大小
cellStyleHeader.SetFont(fontHeader);
#endregion
#region 内容
cellStyleContent = workbook.CreateCellStyle();
if (options.DefaultCellStyle.Border)
{
//设置边框格式
cellStyleContent.BorderTop = BorderStyle.Thin;
cellStyleContent.BorderBottom = BorderStyle.Thin;
cellStyleContent.BorderLeft = BorderStyle.Thin;
cellStyleContent.BorderRight = BorderStyle.Thin;
}
IFont fontContent = workbook.CreateFont();
fontContent.FontHeightInPoints = options.DefaultCellStyle.ContentFontSize; //字体大小
cellStyleContent.SetFont(fontContent);
#endregion
}
#endregion
var count = 0;
#region 创建sheet
if (string.IsNullOrWhiteSpace(options.SheetName))
{
options.SheetName = typeof(T).Name;
}
if (workbook.GetSheet(options.SheetName) == null)
{
//sheet不存在
sheet = workbook.CreateSheet(options.SheetName); //创建一个带名称的工作表
}
else
{
//sheet已存在
int index = workbook.GetSheetIndex(options.SheetName);
workbook.RemoveSheetAt(index);
sheet = workbook.CreateSheet(options.SheetName); //创建一个带名称的工作表
workbook.SetSheetOrder(options.SheetName, index);
}
#endregion
if (sheet == null)
{
return(null);
}
var propertyInfos = typeof(T).GetProperties();
#region 写标题
if (options.OutputHeader)
{
IRow row = sheet.CreateRow(0);
for (var i = 0; i < propertyInfos.Length; i++)
{
var propertyInfo = propertyInfos[i];
var propertyInfoName = propertyInfo.Name;
ICell cell = row.CreateCell(i);
if (cell != null)
{
cell.SetCellValue(propertyInfoName);
if (options.DefaultCellStyle != null)
{
cell.CellStyle = cellStyleHeader;
sheet.SetColumnWidth(i, options.DefaultCellStyle.ColumnWidth);
}
}
}
count++;
}
#endregion
#region 写内容
foreach (var model in list)
{
if (model == null)
{
continue;
}
IRow row = sheet.CreateRow(count);
for (var i = 0; i < propertyInfos.Length; i++)
{
var propertyInfo = propertyInfos[i];
var propertyValue = propertyInfo.GetValue(model, null);
ICell cell = row.CreateCell(i);
if (cell != null)
{
if (propertyValue is DateTime time)
{
if (DateTimeFormat != null)
{
cell.SetCellValue(DateTimeFormat(time));
}
else
{
cell.SetCellValue(time);
}
}
else if (propertyValue is double doubleValue)
{
cell.SetCellValue(doubleValue);
}
else if (propertyValue is bool booleanValue)
{
cell.SetCellValue(booleanValue);
}
else
{
cell.SetCellValue(propertyValue?.ToString());
}
if (options.DefaultCellStyle != null)
{
cell.CellStyle = cellStyleContent;
}
}
}
count++;
}
#endregion
return(workbook);
}
private static IWorkbook GetWorkbook(DataTable dt, ExcelExportOptions options)
{
if (dt == null)
{
throw new ArgumentNullException(nameof(dt));
}
IWorkbook workbook = null;
switch (options.CreateFileType)
{
case CreateFileType.GetOrCreate:
workbook = GetOrCreateWorkbook(options.ExcelFilePath);
break;
case CreateFileType.Get:
workbook = GetWorkbook(options.ExcelFilePath);
break;
case CreateFileType.Create:
workbook = CreateWorkbook(options.ExcelFilePath);
break;
}
if (workbook == null)
{
return(null);
}
#region 设置单元格样式
ICellStyle cellStyleHeader = null;
ICellStyle cellStyleContent = null;
if (options.DefaultCellStyle != null)
{
#region 标题
cellStyleHeader = workbook.CreateCellStyle();
if (options.DefaultCellStyle.Border)
{
//设置边框格式
cellStyleHeader.BorderTop = BorderStyle.Thin;
cellStyleHeader.BorderBottom = BorderStyle.Thin;
cellStyleHeader.BorderLeft = BorderStyle.Thin;
cellStyleHeader.BorderRight = BorderStyle.Thin;
}
if (options.DefaultCellStyle.TitleFontHorizontalCenter)
{
cellStyleHeader.Alignment = HorizontalAlignment.Center; //字体水平居中
}
IFont fontHeader = workbook.CreateFont();
fontHeader.IsBold = options.DefaultCellStyle.TitleFontBold; //字体是否加粗
fontHeader.FontHeightInPoints = options.DefaultCellStyle.TitleFontSize; //字体大小
cellStyleHeader.SetFont(fontHeader);
#endregion
#region 内容
cellStyleContent = workbook.CreateCellStyle();
if (options.DefaultCellStyle.Border)
{
//设置边框格式
cellStyleContent.BorderTop = BorderStyle.Thin;
cellStyleContent.BorderBottom = BorderStyle.Thin;
cellStyleContent.BorderLeft = BorderStyle.Thin;
cellStyleContent.BorderRight = BorderStyle.Thin;
}
IFont fontContent = workbook.CreateFont();
fontContent.FontHeightInPoints = options.DefaultCellStyle.ContentFontSize; //字体大小
cellStyleContent.SetFont(fontContent);
#endregion
}
#endregion
int count = 0;
#region 创建sheet
ISheet sheet;
if (string.IsNullOrWhiteSpace(options.SheetName))
{
sheet = workbook.CreateSheet(); //创建一个工作表
}
else
{
if (workbook.GetSheet(options.SheetName) == null)
{
//sheet不存在
sheet = workbook.CreateSheet(options.SheetName); //创建一个带名称的工作表
}
else
{
//sheet已存在
int index = workbook.GetSheetIndex(options.SheetName);
workbook.RemoveSheetAt(index);
sheet = workbook.CreateSheet(options.SheetName); //创建一个带名称的工作表
workbook.SetSheetOrder(options.SheetName, index);
}
}
#endregion
//在指定sheet中写数据
if (sheet != null)
{
#region 写标题
if (options.OutputHeader)
{
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
if (cell != null)
{
cell.SetCellValue(dt.Columns[i].ColumnName);
if (options.DefaultCellStyle != null)
{
cell.CellStyle = cellStyleHeader;
sheet.SetColumnWidth(i, options.DefaultCellStyle.ColumnWidth);
}
}
}
count++;
}
#endregion
#region 写内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(count);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
if (cell != null)
{
var val = dt.Rows[i][j];
if (DateTimeFormat != null && val is DateTime time)
{
cell.SetCellValue(DateTimeFormat(time));
}
else
{
cell.SetCellValue(val.ToString());
}
if (options.DefaultCellStyle != null)
{
cell.CellStyle = cellStyleContent;
}
}
}
count++;
}
#endregion
}
return(workbook);
}
protected override ISheet CreateSheet(IWorkbook workbook, ExcelExportOptions options)
{
return(workbook.CreateSheet(options.SheetName));
}
