427 lines
16 KiB
C#
427 lines
16 KiB
C#
using System;
|
||
using System.Collections;
|
||
using System.Collections.Generic;
|
||
using System.IO;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.POIFS.FileSystem;
|
||
using MyCode.Project.Infrastructure.Common;
|
||
using NPOI.SS.Util;
|
||
using System.Linq;
|
||
|
||
namespace MyCode.Project.Infrastructure.Exports
|
||
{
|
||
/// <summary>
|
||
/// 导出Excel配置类
|
||
/// </summary>
|
||
public class ExportExcelConfig
|
||
{
|
||
/// <summary>
|
||
/// 查询条件
|
||
/// </summary>
|
||
public object Condition { get; set; }
|
||
|
||
/// <summary>
|
||
/// Excel中的标题和实体属性中的字段Map，例如：标题->Title
|
||
/// </summary>
|
||
public List<ExportExcelProperty> Properties { get; }
|
||
|
||
public List<NpoiHeadCfg> Heads = new List<NpoiHeadCfg>();
|
||
|
||
/// <summary>
|
||
/// 初始化一个<see cref="ExportExcelConfig"/>类型的实例
|
||
/// </summary>
|
||
public ExportExcelConfig()
|
||
{
|
||
Properties = new List<ExportExcelProperty>();
|
||
Heads = new List<NpoiHeadCfg>();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据数据实体和配置生成相应的Excel文件
|
||
/// </summary>
|
||
/// <param name="sourceData">数据源</param>
|
||
/// <param name="fileName">文件名</param>
|
||
/// <returns></returns>
|
||
public string CreateExcelFile(IList sourceData,string fileName)
|
||
{
|
||
//初始化枚举字典
|
||
var dicEnum = new Dictionary<string, Dictionary<int, string>>();
|
||
|
||
//获取列表第一个元素的数据类型
|
||
Type type = null;
|
||
if (sourceData.Count > 0)
|
||
{
|
||
type = sourceData[0].GetType();
|
||
}
|
||
|
||
//创建2007的Excel
|
||
HSSFWorkbook workbook = null;
|
||
|
||
//如果文件存在，则直接获取采用追加的方式追加进去
|
||
string filePath = GetDownloadPath(fileName);
|
||
|
||
string absFilePath = FileUtils.GetPhysicalPath(filePath);
|
||
|
||
ISheet sheet = null;
|
||
|
||
IRow row = null;
|
||
|
||
// (absFilePath);
|
||
workbook = new HSSFWorkbook();
|
||
|
||
sheet = workbook.CreateSheet();
|
||
|
||
//设置标题列样式
|
||
ICellStyle headStyle = workbook.CreateCellStyle();
|
||
headStyle.VerticalAlignment = VerticalAlignment.Center;
|
||
headStyle.Alignment = HorizontalAlignment.Center;
|
||
|
||
//设置字体-加粗字体
|
||
IFont headFont = workbook.CreateFont();
|
||
headFont.IsBold = true;
|
||
|
||
row = sheet.CreateRow(0);
|
||
|
||
//设置默认边框
|
||
ICellStyle style0 = workbook.CreateCellStyle();
|
||
style0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
style0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
style0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
style0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
|
||
int dataRowIndex = 0;
|
||
int endColIndex = 0;
|
||
if (Heads == null || Heads.Count == 0)
|
||
{
|
||
//创建标题列
|
||
for (int i = 0; i < this.Properties.Count; i++)
|
||
{
|
||
var cell = row.CreateCell(i);
|
||
cell.SetCellValue(this.Properties[i].Caption);
|
||
cell.CellStyle = headStyle;
|
||
cell.CellStyle.SetFont(headFont);
|
||
if (this.Properties[i].Width > 0) { sheet.SetColumnWidth(i, this.Properties[i].Width); }
|
||
//设置边框
|
||
row.Sheet.SetDefaultColumnStyle(i, style0);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
endColIndex = Heads.Max(t => t.EndColIndex);
|
||
//设置边框
|
||
for (int i = 0; i <= endColIndex; i++)
|
||
{
|
||
row.Sheet.SetDefaultColumnStyle(i, style0);
|
||
}
|
||
CreadHeader(workbook, ref sheet, Heads);
|
||
dataRowIndex = Heads.Max(t => t.EndRowIndex);
|
||
}
|
||
//创建内容列
|
||
for (int i = 0; i < sourceData.Count ; i++)
|
||
{
|
||
var entity = sourceData[i];
|
||
//创建新的内容行
|
||
//row = sheet.CreateRow(i + sheet.LastRowNum + 1);
|
||
row = sheet.CreateRow(dataRowIndex + 1);
|
||
|
||
//根据配置的属性列创建对应单元格
|
||
for (int j = 0; j < this.Properties.Count; j++)
|
||
{
|
||
var prop = this.Properties[j].EntityProp;
|
||
|
||
//储存内容值
|
||
object value = null;
|
||
|
||
//检查是否存在+号，如果存在则表示是拼接数据
|
||
if (prop.IndexOf("+", StringComparison.Ordinal) > -1)
|
||
{
|
||
value = GetValueOfAddSymbol(this.Properties, type, entity, j);
|
||
}
|
||
else
|
||
{
|
||
value = this.GetPropValue(prop, type, entity);
|
||
}
|
||
|
||
//根据数据类型填写对应的单元格
|
||
if (value == null)
|
||
{
|
||
row.CreateCell(j).SetCellValue("");
|
||
}
|
||
else if (value is decimal)
|
||
{
|
||
row.CreateCell(j).SetCellValue(Convert.ToDouble(value));
|
||
}
|
||
else if (value is double)
|
||
{
|
||
row.CreateCell(j).SetCellValue((double)value);
|
||
}
|
||
else if (value is DateTime)
|
||
{
|
||
row.CreateCell(j).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
|
||
}
|
||
else if (value is bool)
|
||
{
|
||
row.CreateCell(j).SetCellValue((bool)value);
|
||
}
|
||
else if (value is int )
|
||
{
|
||
//枚举
|
||
if (this.Properties[j].EnumType != null)
|
||
{
|
||
var enumValue = GetEnumValue(dicEnum, this.Properties[j].EnumType, this.Properties[j].EntityProp, Convert.ToInt32(value));
|
||
row.CreateCell(j).SetCellValue(enumValue);
|
||
}
|
||
else
|
||
{
|
||
row.CreateCell(j).SetCellValue((int)value);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
row.CreateCell(j).SetCellValue(value.ToString());
|
||
}
|
||
}
|
||
dataRowIndex++;
|
||
}
|
||
|
||
using (FileStream fs = File.OpenWrite(absFilePath))
|
||
{
|
||
workbook.Write(fs);
|
||
|
||
fs.Close();
|
||
fs.Dispose();
|
||
}
|
||
|
||
workbook = null;
|
||
|
||
//SaveFilePath = filePath;
|
||
return filePath;
|
||
}
|
||
|
||
#region GetEnumValue(得到枚举值)
|
||
/// <summary>
|
||
/// 得到枚举值
|
||
/// </summary>
|
||
/// <param name="dicAllEnum">所有字典</param>
|
||
/// <param name="enumType">枚举类型</param>
|
||
/// <param name="fieldName">属性名</param>
|
||
/// <param name="dicValue">枚举值</param>
|
||
/// <returns></returns>
|
||
private string GetEnumValue(Dictionary<string, Dictionary<int, string>> dicAllEnum,Type enumType,string fieldName,int dicValue)
|
||
{
|
||
if (!dicAllEnum.ContainsKey(fieldName))
|
||
{
|
||
dicAllEnum[fieldName] = EnumHelper.GetDictionary(enumType);
|
||
}
|
||
|
||
if (!dicAllEnum[fieldName].ContainsKey(dicValue)) { return dicValue.ToString(); }
|
||
|
||
return dicAllEnum[fieldName][dicValue];
|
||
}
|
||
#endregion
|
||
|
||
#region GetDownloadPath(获取下载路径)
|
||
/// <summary>
|
||
/// 获取下载路径
|
||
/// </summary>
|
||
/// <param name="fileName">文件名</param>
|
||
/// <returns></returns>
|
||
private string GetDownloadPath(string fileName)
|
||
{
|
||
string dirPath = "/download/exceltemp/";
|
||
string absFilePath = FileUtils.GetPhysicalPath(dirPath);
|
||
if (!Directory.Exists(absFilePath))
|
||
{
|
||
Directory.CreateDirectory(absFilePath);
|
||
}
|
||
//string newFileName = DateTime.Now.ToString("yyyyMMddHHmmss_fff");
|
||
//string filePath = Path.Combine(dirPath, fileName + "_" + newFileName + ".xls");
|
||
//return filePath;
|
||
return Path.Combine(dirPath, fileName);
|
||
}
|
||
#endregion
|
||
|
||
#region GetPropValue(获取字段值)
|
||
/// <summary>
|
||
/// 获取字段值
|
||
/// </summary>
|
||
/// <param name="prop">属性名</param>
|
||
/// <param name="type">类型</param>
|
||
/// <param name="entity">实体对象</param>
|
||
/// <returns></returns>
|
||
private object GetPropValue(string prop, Type type, object entity)
|
||
{
|
||
object value = null;
|
||
//如果存在.号则表示是复杂类型属性，需要依次去获取数据
|
||
if (prop.IndexOf(".", StringComparison.Ordinal) > -1)
|
||
{
|
||
var props = prop.Split('.');
|
||
//获取第一级实体
|
||
value = type.GetProperty(props[0]).GetValue(entity);
|
||
for (int p = 1; p < props.Length; p++)
|
||
{
|
||
//如果值为空，则直接退出
|
||
if (value == null)
|
||
{
|
||
break;
|
||
}
|
||
Type childType = value.GetType();
|
||
value = childType.GetProperty(props[p]).GetValue(value);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
value = type.GetProperty(prop).GetValue(entity);
|
||
}
|
||
return value;
|
||
}
|
||
#endregion
|
||
|
||
#region GetValueOfAddSymbol(如果字段名用了+号)
|
||
/// <summary>
|
||
/// 如果字段名用了+号
|
||
/// </summary>
|
||
/// <param name="prop"></param>
|
||
/// <param name="type"></param>
|
||
/// <param name="entity"></param>
|
||
/// <returns></returns>
|
||
private object GetValueOfAddSymbol(List<ExportExcelProperty> listProp,Type type,object entity,int j)
|
||
{
|
||
var prop = listProp[j].EntityProp;
|
||
|
||
//储存内容值
|
||
object value = null;
|
||
|
||
var joinProps = prop.Split('+');
|
||
for (int jp = 0; jp < joinProps.Length; jp++)
|
||
{
|
||
var jprop = joinProps[jp];
|
||
var newValue = this.GetPropValue(jprop, type, entity);
|
||
if (value != null)
|
||
{
|
||
if (newValue != null)
|
||
{
|
||
//这里只智齿字符串的累加，主要解决地址合并省市区的情况
|
||
value = value.ToString() + listProp[j].JoinPropChar + newValue.ToString();
|
||
}
|
||
}
|
||
else
|
||
{
|
||
value = newValue;
|
||
}
|
||
}
|
||
|
||
return value;
|
||
}
|
||
#endregion
|
||
|
||
#region CreadHeader(创建表头，支持多行)
|
||
/// <summary>
|
||
/// 创建表头，支持多行
|
||
/// </summary>
|
||
/// <param name="book"></param>
|
||
/// <param name="sheet"></param>
|
||
/// <param name="dt"></param>
|
||
/// <param name="heads"></param>
|
||
/// <remarks>
|
||
/// 创建：余宇波 2020-09-04
|
||
/// </remarks>
|
||
private static void CreadHeader(HSSFWorkbook book,ref ISheet sheet, List<NpoiHeadCfg> heads)
|
||
{
|
||
//创建 表格头部
|
||
if (heads != null && heads.Count > 0)
|
||
{
|
||
List<int> rows = new List<int>();
|
||
IRow headRow = sheet.CreateRow(0);//创建空行
|
||
rows.Add(0);
|
||
var style = GetCellStyle(book, heads[0]);
|
||
foreach (var t in heads)
|
||
{
|
||
if (!rows.Contains(t.StartRowIndex)) //假如没有创建行
|
||
{
|
||
rows.Add(t.StartRowIndex);
|
||
headRow = sheet.CreateRow(t.StartRowIndex);
|
||
|
||
}
|
||
else
|
||
{
|
||
headRow = sheet.GetRow(t.StartRowIndex);
|
||
}
|
||
|
||
headRow.Height = (short)(t.Height * 20); //设置行高 为25
|
||
ICell cell = headRow.CreateCell(t.StartColIndex); //创建单元格
|
||
cell.SetCellValue(t.FieldName); //设置单元格内容
|
||
// 设置列宽
|
||
if (t.Width > 0)
|
||
{
|
||
sheet.SetColumnWidth(cell.ColumnIndex, t.Width * 256);
|
||
}
|
||
else
|
||
{
|
||
sheet.SetColumnWidth(cell.ColumnIndex, 13 * 256);
|
||
}
|
||
//合并单元格
|
||
if (t.EndColIndex > t.StartColIndex || t.EndRowIndex > t.StartRowIndex)
|
||
{
|
||
CellRangeAddress region1 = new CellRangeAddress(t.StartRowIndex, t.EndRowIndex, (short)t.StartColIndex, (short)t.EndColIndex);
|
||
headRow.Sheet.AddMergedRegion(region1);
|
||
}
|
||
cell.CellStyle = style;
|
||
}
|
||
}
|
||
}
|
||
|
||
|
||
private static ICellStyle GetCellStyle(HSSFWorkbook book, NpoiHeadCfg headCfg)
|
||
{
|
||
ICellStyle style0 = book.CreateCellStyle();
|
||
// 2、行高
|
||
// row.Height = 30 * 20; //行高为30
|
||
// excelRow.Height = 25 * 20;
|
||
// 单元格 列宽:
|
||
//if (headCfg.Width > 0) {
|
||
// cell.Row.Sheet.SetColumnWidth(cell.ColumnIndex, headCfg.Width * 256);
|
||
//}
|
||
//三、设置居中:
|
||
//cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
|
||
style0.Alignment = HorizontalAlignment.Left;
|
||
style0.VerticalAlignment = VerticalAlignment.Center;
|
||
//四、设置字体:
|
||
IFont font = book.CreateFont();
|
||
font.FontName = "黑体";//.SetFontName("黑体");
|
||
font.FontHeightInPoints = (short)11.5;//.SetFontHeightInPoints((short)16);//设置字体大小
|
||
style0.SetFont(font);//选择需要用到的字体格式
|
||
|
||
//大坑，大坑，大坑，shunlu 2018-10-10
|
||
//必须设置单元格背景色 FillForegroundColor 和 FillPattern 的值才能正确显示背景色
|
||
style0.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //(short)1灰色 NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
|
||
style0.FillPattern = FillPattern.SolidForeground; // CellStyle.SOLID_FOREGROUND
|
||
|
||
//二、设置边框:
|
||
//cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 三、设置居中: cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
|
||
|
||
//style0.BorderBottom = BorderStyle.Medium;// CellStyle.SOLID_FOREGROUND
|
||
//style0.BorderRight = BorderStyle.Medium;
|
||
|
||
|
||
style0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
style0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
style0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
style0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
|
||
|
||
|
||
//三、设置居中:
|
||
//cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
|
||
style0.Alignment = HorizontalAlignment.Left;
|
||
style0.VerticalAlignment = VerticalAlignment.Center;
|
||
|
||
//
|
||
return style0;
|
||
}
|
||
#endregion
|
||
|
||
|
||
}
|
||
}
|