导出的帮助类
public class ExcelHelper
{
/// <summary>
/// 将给定的模型列表转换为 Excel 内存流，第一行和第二行是居中对齐加粗的
/// </summary>
/// <typeparam name="T">模型类型</typeparam>
/// <param name="models">模型列表</param>
/// <param name="firstRow">Excel 第一行数据</param>
/// <param name="secondRow">Excel 第二行数据</param>
/// <returns>Excel 内存流</returns>
public static MemoryStream ExportExcelStream<T>(List<T> models, string firstRow, string secondRow)
{
NPOIMemoryStream ms = new NPOIMemoryStream();//使用自定义类
IWorkbook workbook = Parse(models, firstRow, secondRow);
workbook.Write(ms);
ms.Position = 0;
return ms;
}
/// <summary>
/// 将数据导出到Excel内存流中
/// </summary>
/// <typeparam name="T">类型参数</typeparam>
/// <param name="models">要导出的数据列表</param>
/// <param name="firstRow">第一行的信息</param>
/// <param name="secondRow">第二行的信息</param>
/// <param name="hashtable">Hashtable类型的参数</param>
/// <returns>Excel内存流</returns>
public static MemoryStream ExportExcelStream<T>(List<T> models, string firstRow, string secondRow,Hashtable hashtable)
{
NPOIMemoryStream ms = new NPOIMemoryStream();//使用自定义类
IWorkbook workbook = Parse(models, hashtable, firstRow, secondRow);
workbook.Write(ms);
ms.Position = 0;
return ms;
}
/// <summary>
/// 内存流保存到Excel文件中
/// </summary>
/// <param name="stream"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public static string ConvertStreamToExcel(MemoryStream stream, string filePath)
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
fileStream.Write(buffer, 0, buffer.Length);
}
return filePath;
}
/// <summary>
/// 将数据传输对象列表解析为HSSFWorkbook对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="models"></param>
/// <param name="firstRow"></param>
/// <param name="secondRow"></param>
/// <returns></returns>
private static HSSFWorkbook Parse<T>(List<T> models, string firstRow = "", string secondRow = "")
{
var book = new HSSFWorkbook();
var sheet = book.CreateSheet();
var headerStyle = GetHeaderStyle(book);
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
var itemStyle = GetItemStyle(book);
itemStyle.Alignment = HorizontalAlignment.Center;
itemStyle.BorderBottom = BorderStyle.Thin;
itemStyle.BorderLeft = BorderStyle.Thin;
itemStyle.BorderRight = BorderStyle.Thin;
itemStyle.BorderTop = BorderStyle.Thin;
var properties = typeof(T).GetProperties();
var columns = properties.Select(x => new ColumnEntry
{
Property = x,
Header = x.GetCustomAttribute<ExportHeaderAttribute>()
})
.Where(x => x.Header != null)
.OrderBy(x => x.Header.Order)
.ToList();
// 列数
var columnCount = columns.Count();
// 行数
var rowCount = models.Count();
SetColumnWith(sheet, columnCount);
WriteMergeRow(0, firstRow, 0, columnCount - 1);
WriteMergeRow(1, secondRow, 0, columnCount - 1);
WriteRow(2, columns.Select(x => x.Header.Name).ToList());
for (var rowIndex = 3; rowIndex <= rowCount + 2; rowIndex++)
{
var values = new List<string>();
var model = models[rowIndex - 3];
foreach (var column in columns)
{
var value = column.Property.GetValue(model)?.ToString();
values.Add(value);
}
WriteRow(rowIndex, values);
}
return book;
void WriteRow(int rowIndex, List<string> rows)
{
var row = sheet.CreateRow(rowIndex);
if (rowIndex == 0)
{
// 设置表头高度
row.Height = row.Height;
}
for (int i = 0; i < columnCount; i++)
{
var cell = row.CreateCell(i);
if (rowIndex == 2)
{
// 设置表头单元格样式
cell.CellStyle = headerStyle;
cell.SetCellValue(rows[i]?.ToString());
}
else
{
cell.CellStyle = itemStyle;
if (double.TryParse(rows[i], out double tryNumberValue))
{
cell.SetCellType(CellType.Numeric);
//cell.CellStyle.DataFormat = book.CreateDataFormat().GetFormat("#.######");
cell.SetCellValue(tryNumberValue);
}
else
{
cell.SetCellValue(rows[i]?.ToString());
}
}
}
}
void WriteMergeRow(int rowIndex, string rowValue, int fromCell, int toCell)
{
var row = sheet.CreateRow(rowIndex);
if (rowIndex == 0)
{
// 设置表头高度
row.Height = row.Height;
}
var cell = row.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(rowValue);
var region = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1);
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
row.Sheet.AddMergedRegion(region);
}
}
/// <summary>
/// 将数据传输对象列表解析为HSSFWorkbook对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="models"></param>
/// <param name="hashtable"></param>
/// <param name="firstRow"></param>
/// <param name="secondRow"></param>
/// <returns></returns>
private static HSSFWorkbook Parse<T>(List<T> models,Hashtable hashtable ,string firstRow = "", string secondRow = "")
{
var book = new HSSFWorkbook();
var sheet = book.CreateSheet();
var headerStyle = GetHeaderStyle(book);
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
var itemStyle = GetItemStyle(book);
itemStyle.Alignment = HorizontalAlignment.Center;
itemStyle.BorderBottom = BorderStyle.Thin;
itemStyle.BorderLeft = BorderStyle.Thin;
itemStyle.BorderRight = BorderStyle.Thin;
itemStyle.BorderTop = BorderStyle.Thin;
var properties = typeof(T).GetProperties();
var columns = properties.Select(x => new ColumnHeaderEntity
{
Property = x,
Header = hashtable[x.Name].ToString()
})
.Where(x => x.Header != null)
.ToList();
// 列数
var columnCount = columns.Count();
// 行数
var rowCount = models.Count();
SetColumnWith(sheet, columnCount);
WriteMergeRow(0, firstRow, 0, columnCount - 1);
WriteMergeRow(1, secondRow, 0, columnCount - 1);
WriteRow(2, columns.Select(x => x.Header).ToList());
for (var rowIndex = 3; rowIndex <= rowCount + 2; rowIndex++)
{
var values = new List<string>();
var model = models[rowIndex - 3];
foreach (var column in columns)
{
var value = column.Property.GetValue(model)?.ToString();
values.Add(value);
}
WriteRow(rowIndex, values);
}
return book;
void WriteRow(int rowIndex, List<string> rows)
{
var row = sheet.CreateRow(rowIndex);
if (rowIndex == 0)
{
// 设置表头高度
row.Height = row.Height;
}
for (int i = 0; i < columnCount; i++)
{
var cell = row.CreateCell(i);
if (rowIndex == 2)
{
// 设置表头单元格样式
cell.CellStyle = headerStyle;
cell.SetCellValue(rows[i]?.ToString());
}
else
{
cell.CellStyle = itemStyle;
if (double.TryParse(rows[i], out double tryNumberValue))
{
cell.SetCellType(CellType.Numeric);
//cell.CellStyle.DataFormat = book.CreateDataFormat().GetFormat("#.######");
cell.SetCellValue(tryNumberValue);
}
else
{
cell.SetCellValue(rows[i]?.ToString());
}
}
}
}
void WriteMergeRow(int rowIndex, string rowValue, int fromCell, int toCell)
{
var row = sheet.CreateRow(rowIndex);
if (rowIndex == 0)
{
// 设置表头高度
row.Height = row.Height;
}
var cell = row.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(rowValue);
var region = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1);
HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
row.Sheet.AddMergedRegion(region);
}
}
/// <summary>
/// 获取表头样式
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
private static ICellStyle GetHeaderStyle(HSSFWorkbook book)
{
ICellStyle cellStyle = book.CreateCellStyle();
IFont font = book.CreateFont();
font.Color = new HSSFColor.Black().Indexed;
font.IsBold = true;
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
return cellStyle;
}
/// <summary>
/// 获取内容单元格样式
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
private static ICellStyle GetItemStyle(HSSFWorkbook book)
{
var style = book.CreateCellStyle();
var font = book.CreateFont();
//font.FontName = "方正舒体";
font.Color = new HSSFColor.Black().Indexed;
//font.IsItalic = true;
//font.FontHeightInPoints = 16;
font.IsBold = false;
style.SetFont(font);
//style.FillBackgroundColor = new HSSFColor.Grey50Percent().Indexed;
//style.FillForegroundColor = 0;
style.FillPattern = FillPattern.SolidForeground;
//((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 0, 176, 240 });
return style;
}
/// <summary>
/// 设置列宽
/// </summary>
/// <param name="sheet"></param>
/// <param name="columnCount"></param>
private static void SetColumnWith(ISheet sheet, int columnCount)
{
int num = 4096;
for (int i = 0; i < columnCount; i++)
{
sheet.SetColumnWidth(i, num);
}
}
}
导出需要用到的Model
public class ColumnHeaderEntity
{
public PropertyInfo Property { get; set; }
public string Header { get; set; }
}
public class ColumnEntry
{
public PropertyInfo Property { get; set; }
public ExportHeaderAttribute Header { get; set; }
}
public class ExportHeaderAttribute : Attribute
{
//
// 摘要:
// 列名称
public string Name { get; }
//
// 摘要:
// 列顺序
public short Order { get; }
public ExportHeaderAttribute(string name)
: this(name, -1)
{
}
public ExportHeaderAttribute(string name, short order)
{
Name = name;
Order = order;
}
}