/// <summary>
/// 根据model List和指定导出字段来生成excel数据流
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="modelList">数据集</param>
/// <param name="exportPropertiesList">需要导出的参数List，默认为空则会导出model的所有字段，如果只需要导出model的指定字段，则在此list中添加字段的名称</param>
/// <param name="startRow">数据起始行，默认为0</param>
/// <param name="startCol">数据起始列，默认为0</param>>
/// <returns></returns>
public static MemoryStream ExportExcelWithPropertyList <T>(List <T> modelList, List <string> exportPropertiesList,
int startRow = 0, int startCol = 0)
{
//生成模板excel
IWorkbook wk = new XSSFWorkbook();
//读取当前表数据
ISheet sheet = wk.CreateSheet("Sheet");
IRow row = null;
ICell cell = null;
//当前操作的行号
var currentRow = startRow;
//当前操作的列号
var currentColum = startCol;
//遍历model list
foreach (var model in modelList)
{
//创建行
row = sheet.CreateRow(currentRow);
//获取model类型
Type t = typeof(T);
foreach (var propertyInfo in t.GetProperties()) //遍历model的字段
{
if (exportPropertiesList.Contains(propertyInfo.Name)) //填充model的指定字段
{
//创建列
cell = row.GetCell(currentColum) ?? row.CreateCell(currentColum);
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//设置单元格的数值
cell.SetCellValue(value == null ? "" : value.ToString());
currentColum++;
}
}
currentRow++;
}
//使用自定义的MemoryStream 防止内存被提前释放
NpoiMemoryStream memoryStream = new NpoiMemoryStream();
memoryStream.AllowClose = false;
wk.Write(memoryStream);
memoryStream.Flush();
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.AllowClose = true;
return(memoryStream);
}
/// <summary>
/// 向excel中写入几个单独的数据（暂时用于输出异常)
/// </summary>
/// <param name="extraExcelElemetList"></param>
/// <returns></returns>
public static MemoryStream ExportExcel(List <ExtraExcelElemet> extraExcelElemetList)
{
//生成模板excel
IWorkbook wk = new XSSFWorkbook();
//读取当前表数据
ISheet sheet = wk.CreateSheet("sheet");
//行和列
IRow row = null;
ICell cell = null;
if (extraExcelElemetList != null)
{
foreach (var element in extraExcelElemetList) //遍历要写入excel中的元素
{
//创建行和列
row = sheet.CreateRow(element.Row);
cell = row.CreateCell(element.Column);
//设置单元格数值
cell.SetCellValue(element.Text);
}
}
//使用自定义的MemoryStream 防止内存被提前释放
NpoiMemoryStream memoryStream = new NpoiMemoryStream();
memoryStream.AllowClose = false;
//将excel数据流保存到stream中
wk.Write(memoryStream);
memoryStream.Flush();
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.AllowClose = true;
return(memoryStream);
}
/// <summary>
/// 通过指定模板和单独添加的单元格和model List来生成excel数据流
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="modelList">数据集</param>
/// <param name="extraElemetList">需要单独添加的数据，如需要在0，0位置添加一个单独的标题</param>
/// <param name="templetName">excel模板名称，在config文件中配置</param>
/// <param name="startRow">数据起始行，默认为0</param>
/// <param name="startCol">数据起始列，默认为0</param>>
/// <returns></returns>
public static MemoryStream ExportExcelFromTempletWithExtra <T>(List <T> modelList,
List <ExtraExcelElemet> extraElemetList, string templetName, int startRow = 0, int startCol = 0)
{
MemoryStream tempStream = ExportExcelFromTempler(modelList, templetName, startRow, startCol);
//生成模板excel
IWorkbook wk = new XSSFWorkbook(tempStream);
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
IRow row = null; //读取当前行数据
ICell cell = null;
//当前操作的行号
var currentRow = startRow;
foreach (var model in modelList) //遍历数据源
{
//创建行
row = sheet.GetRow(currentRow) ?? sheet.CreateRow(currentRow);
//获取model的类型
Type t = typeof(T);
//当前操作的列号
var currentColum = startCol;
//遍历model的参数
foreach (var propertyInfo in t.GetProperties())
{
//创建列
cell = row.GetCell(currentColum) ?? row.CreateCell(currentColum);
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//设置单元格的数值
cell.SetCellValue(value == null ? "" : value.ToString());
currentColum++;
}
currentRow++;
}
if (extraElemetList != null)
{
foreach (var element in extraElemetList) //遍历额外添加的元素
{
//读取或创建行
row = sheet.GetRow(element.Row) ?? sheet.CreateRow(element.Row);
//读取或创建列
cell = row.GetCell(element.Column) ?? row.CreateCell(element.Column);
//设置单元格的值
cell.SetCellValue(element.Text);
}
}
//使用自定义的MemoryStream 防止内存被提前释放
NpoiMemoryStream memoryStream = new NpoiMemoryStream();
memoryStream.AllowClose = false;
wk.Write(memoryStream);
memoryStream.Flush();
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.AllowClose = true;
return(memoryStream);
}
/// <summary>
/// 通过指定excel模板和model List生成excel数据流
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="modelList">excel数据源</param>
/// <param name="templetExcelPath">excel模板名称，在config文件中配置</param>
/// <param name="startRow">数据起始行，默认为0</param>
/// <param name="startCol">数据起始列，默认为0</param>
/// <param name="exportPropertiesList">需要导出的参数List，默认为空则会导出model的所有字段，如果只需要导出model的指定字段，则在此list中添加字段的名称</param>
/// <returns></returns>
public static MemoryStream ExportExcelFromTempler <T>(List <T> modelList, string templetExcelPath, int startRow = 0,
int startCol = 0, List <string> exportPropertiesList = null)
{
//判断文件存在
if (!File.Exists(templetExcelPath))
{
throw new Exception(string.Format("无法找到模板:{0}", templetExcelPath));
}
FileStream fileStream = new FileStream(templetExcelPath, FileMode.Open);
//从内存中获取模板
byte[] templetByte = new byte[fileStream.Length];
fileStream.Read(templetByte, 0, templetByte.Length);
// 设置当前流的位置为流的开始
fileStream.Seek(0, SeekOrigin.Begin);
//生成模板excel
IWorkbook wk = new XSSFWorkbook(new MemoryStream(templetByte));
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
IRow row = null;
ICell cell = null;
//当前操作的行号
var currentRow = startRow;
foreach (var model in modelList) //遍历数据源
{
//如果模板中存在当前行，则读取当前行，否则创建一个新行
row = sheet.GetRow(currentRow) ?? sheet.CreateRow(currentRow);
//获取model的类型
Type t = typeof(T);
//当前操作的列号
var currentColum = startCol;
if (exportPropertiesList == null) //判断是否需要填充指定字段
{
//填充model的所有字段
//遍历model的参数
foreach (var propertyInfo in t.GetProperties())
{
//读取或创建列
cell = row.GetCell(currentColum) ?? row.CreateCell(currentColum);
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//设置单元格的数值
cell.SetCellValue(value == null ? "" : value.ToString());
currentColum++;
}
}
else
{
foreach (var propertyInfo in t.GetProperties())
{
if (exportPropertiesList.Contains(propertyInfo.Name)) //填充model的指定字段
{
//读取或创建列
cell = row.GetCell(currentColum) ?? row.CreateCell(currentColum);
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//设置单元格的数值
cell.SetCellValue(value == null ? "" : value.ToString());
currentColum++;
}
}
}
currentRow++;
}
//使用自定义的MemoryStream 防止内存被提前释放
NpoiMemoryStream memoryStream = new NpoiMemoryStream();
memoryStream.AllowClose = false;
wk.Write(memoryStream);
memoryStream.Flush();
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.AllowClose = true;
return(memoryStream);
}
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="modelList"></param>
/// <param name="extraElemetList"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="useDefaultStyle"></param>
/// <param name="exportPropertiesList"></param>
/// <returns></returns>
public static MemoryStream ExportExcelWithExtra <T>(List <T> modelList,
List <ExtraExcelElemet> extraElemetList, int startRow = 0, int startCol = 0, bool useDefaultStyle = true, List <string> exportPropertiesList = null)
{
//记录列数
int parmCount = 0;
//生成模板excel
IWorkbook wk = new XSSFWorkbook();
//读取当前表数据
ISheet sheet = wk.CreateSheet("Sheet1");
IRow row = null; //读取当前行数据
ICell cell = null;
//当前操作的行号
var currentRow = startRow;
foreach (var model in modelList) //遍历数据源
{
//创建行
row = sheet.GetRow(currentRow) ?? sheet.CreateRow(currentRow);
//获取model的类型
Type t = typeof(T);
//当前操作的列号
var currentColum = startCol;
if (exportPropertiesList == null)
{
parmCount = t.GetProperties().Length;
}
else
{
parmCount = exportPropertiesList.Count;
}
//遍历model的参数
foreach (var propertyInfo in t.GetProperties())
{
if (exportPropertiesList == null)
{
//创建列
cell = row.GetCell(currentColum) ?? row.CreateCell(currentColum);
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//设置单元格的数值
cell.SetCellValue(value == null ? "" : value.ToString());
if (useDefaultStyle)
{
cell.CellStyle = GetDetaulCellStyle(wk);
}
currentColum++;
}
else
{
if (exportPropertiesList.Contains(propertyInfo.Name))//获取字段名称
{
//创建列
cell = row.GetCell(currentColum) ?? row.CreateCell(currentColum);
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//设置单元格的数值
cell.SetCellValue(value == null ? "" : value.ToString());
if (useDefaultStyle)
{
cell.CellStyle = GetDetaulCellStyle(wk);
}
currentColum++;
}
else if (propertyInfo.Name == "JustOneElement")
{
//获取model的某个字段的value
var value = propertyInfo.GetValue(model);
//合并单元格
if ((bool)value)
{
var region = new CellRangeAddress(currentRow, currentRow, 0, parmCount - 1);
sheet.AddMergedRegion(region);
SetRegionAllBorder(region, sheet, wk);
}
}
}
}
currentRow++;
}
if (extraElemetList != null)
{
foreach (var element in extraElemetList) //遍历额外添加的元素
{
//读取或创建行
row = sheet.GetRow(element.Row) ?? sheet.CreateRow(element.Row);
//读取或创建列
cell = row.GetCell(element.Column) ?? row.CreateCell(element.Column);
//设置单元格的值
cell.SetCellValue(element.Text);
//判断是否添加样式
if (useDefaultStyle)
{
cell.CellStyle = GetDetaulCellStyle(wk);
}
//是否需要合并单元格
if (element.NeedMerge)
{
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
var region = new CellRangeAddress(element.Row, element.MergeEndRow, element.Column, element.MergeEndColumn);
sheet.AddMergedRegion(region);
SetRegionAllBorder(region, sheet, wk);
}
}
}
//自使用列宽
for (int i = 0; i < parmCount; i++)
{
sheet.AutoSizeColumn(i);
}
//使用自定义的MemoryStream 防止内存被提前释放
NpoiMemoryStream memoryStream = new NpoiMemoryStream();
memoryStream.AllowClose = false;
wk.Write(memoryStream);
memoryStream.Flush();
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.AllowClose = true;
return(memoryStream);
}
