/// <summary>
/// 设置头部颜色
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetHeadColor(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
foreach (var item in excelGlobalDTO.Sheets)
{
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
IRow row = sheet.GetRow(item.StartRowIndex.Value);
//为空判断
if (item.SheetHeadList == null)
{
continue;
}
//创建头部
foreach (var head in item.SheetHeadList)
{
if (head.IsSetHeadColor == true)
{
ICell cell = row.GetCell(head.ColumnIndex);
IFont font = excelGlobalDTO.Workbook.CreateFont(); //创建字体样式
font.Color = HSSFColor.Red.Index; //设置字体颜色
ICellStyle style = excelGlobalDTO.Workbook.CreateCellStyle(); //创建单元格样
style.SetFont(font);
cell.CellStyle = style;
}
}
}
}
/// <summary>
/// 设置隐藏
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetSheetColumnHidden(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//遍历Sheet实体集合
foreach (var item in excelGlobalDTO.Sheets)
{
//为空判断
if (item.SheetHeadList == null)
{
continue;
}
//获取Excel的Sheet，对Excel设置批注
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
foreach (var head in item.SheetHeadList)
{
if (head.IsHiddenColumn == true)
{
//sheet.SetColumnHidden(head.ColumnIndex, head.IsHiddenColumn);
//设置列隐藏，使用批注的时候，如果调整内容，再次导入会报错
sheet.SetColumnWidth(head.ColumnIndex, 20);
}
}
}
}
/// <summary>
/// 清空批注
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void ClearComment(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
foreach (var item in excelGlobalDTO.Sheets)
{
//获取Excel的Sheet，对Excel设置批注
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//清空批注
for (int i = (item.StartRowIndex.Value); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
for (var j = row.FirstCellNum; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
continue;
}
cell.RemoveCellComment();
}
}
}
}
/// <summary>
/// 执行导出
/// </summary>
/// <param name="excelGlobalDTO"> Excel全局对象</param>
public void ExportMemoryStream(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//生成名称
string fileName = excelGlobalDTO.FileName.Split('.')[0]
+ DateTime.Now.ToString("yyyyMMdd")
+ "."
+ excelGlobalDTO.FileName.Split('.')[1];
fileName = string.Format("attachment;filename='{0}';filename*=utf-8''{0}", HttpUtility.UrlEncode(fileName));
//当前请求上下文
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.ClearHeaders();
response.ClearContent();
response.Buffer = true;
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", fileName);
MemoryStream ms = new MemoryStream();
excelGlobalDTO.Workbook.Write(ms);
byte[] buffer = ms.ToArray();
response.AddHeader("Content-Length", buffer.Length.ToString());
response.BinaryWrite(buffer);
response.Flush();
response.Close();
}
/// <summary>
/// 设置批注
/// </summary>
/// <param name="cell"></param>
/// <param name="errorMsg"></param>
/// <param name="excelGlobalDTO"></param>
public void SetCellComment(ICell cell, string errorMsg, ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
ICreationHelper facktory = cell.Row.Sheet.Workbook.GetCreationHelper();
if (cell.CellComment == null)
{
//创建批注区域
IDrawing patr = cell.Row.Sheet.CreateDrawingPatriarch();
var anchor = facktory.CreateClientAnchor();
//设置批注区间大小
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 2;
//设置列
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 3;
cell.CellComment = patr.CreateCellComment(anchor);
}
if (excelGlobalDTO.ExcelVersionEnum == ExcelVersionEnum.V2003)
{
cell.CellComment.String = new HSSFRichTextString(errorMsg);//2003批注方式
}
else
{
cell.CellComment.String = new XSSFRichTextString(errorMsg);//2007批准方式
}
cell.CellComment.Author = "yank";
}
/// <summary>
/// 基于数据执行导出：FilePath、SheetEntityList、StartRowIndex
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void ExecuteByData(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//初始化
excelGlobalDTO.PerformanceMonitoring.Start("=========【导出】数据初始化======");
GlobalDataInit(excelGlobalDTO);
excelGlobalDTO.PerformanceMonitoring.Stop();
//初始化Excel
excelGlobalDTO.PerformanceMonitoring.Start("=========【导出】初始化Excel======");
GlobalExcelInitByData(excelGlobalDTO);
excelGlobalDTO.PerformanceMonitoring.Stop();
//Excel处理
excelGlobalDTO.PerformanceMonitoring.Start("=========【导出】Excel处理======");
ExcelHandle(excelGlobalDTO);
excelGlobalDTO.PerformanceMonitoring.Stop();
//如果路径文件为空则不存储
if (string.IsNullOrEmpty(excelGlobalDTO.FilePath) == true)
{
return;
}
//写入内容
using (FileStream fs = new FileStream(excelGlobalDTO.FilePath, FileMode.Create))
{
excelGlobalDTO.Workbook.Write(fs);
fs.Dispose();
fs.Close();
}
}
/// <summary>
/// 基于数据导出的初始化
/// </summary>
/// <param name="excelGlobalDTO"></param>
private void GlobalDataInit(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
#region 如果Sheet为空则初始化Sheet
if (excelGlobalDTO.Sheets == null)
{
//构建默认一个
ExcelSheetModel <TEntity> sheetModel = new ExcelSheetModel <TEntity>
{
SheetIndex = 0,
StartRowIndex = excelGlobalDTO.GlobalStartRowIndex,
StartColumnIndex = excelGlobalDTO.GlobalStartColumnIndex
};
//设置一个默认
excelGlobalDTO.Sheets = new List <ExcelSheetModel <TEntity> >
{
sheetModel
};
}
else
{
//如果未设置起始行起始列，则以全局为准
foreach (var item in excelGlobalDTO.Sheets)
{
item.StartRowIndex = item.StartRowIndex ?? excelGlobalDTO.GlobalStartRowIndex;
item.StartColumnIndex = item.StartColumnIndex ?? excelGlobalDTO.GlobalStartColumnIndex;
}
}
#endregion
}
/// <summary>
/// 基于导入执行导出
/// </summary>
public void Execute(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
excelGlobalDTO.PerformanceMonitoring.Start("=========【导出】Excel处理======");
//Excel处理
ExcelHandle(excelGlobalDTO);
//删除行
DeleteRow <TEntity> deleteRow = new DeleteRow <TEntity>();
deleteRow.DeleteRows(excelGlobalDTO);
excelGlobalDTO.PerformanceMonitoring.Stop();
//如果路径文件为空则不存储
if (string.IsNullOrEmpty(excelGlobalDTO.FilePath) == true)
{
return;
}
//写入内容
using (FileStream fs = new FileStream(excelGlobalDTO.FilePath, FileMode.Create))
{
excelGlobalDTO.Workbook.Write(fs);
fs.Dispose();
fs.Close();
}
}
/// <summary>
/// 执行
/// </summary>
public void Execute()
{
//级联处理
string exportTemplatePath = Directory.GetCurrentDirectory() + "\\..\\Template\\合同导入模板-导出模板.xls";
//第一步
ExcelGlobalDTO <ContractImportDTO> global = new ExcelGlobalDTO <ContractImportDTO>(1);
global.SetDefaultSheet("合同");
global.FilePath = exportTemplatePath;
//构建数据
ContractImportDTO model = new ContractImportDTO()
{
Name = "A",
Code = "B"
};
global.Sheets.First().SheetEntityList = new List <ContractImportDTO>()
{
model
};
//第一步：导出（渲染Excel）
Export <ContractImportDTO> exprotFile = new Export <ContractImportDTO>();
exprotFile.ExecuteByFile(exportTemplatePath, ExcelVersionEnum.V2007, global);
//第二部
//exprotFile.ExecuteByData(global);
//第三步：导出（构建文件流）
exprotFile.ExportMemoryStream(global);
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void DeleteRows(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
foreach (var item in excelGlobalDTO.Sheets)
{
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//判断是否为空
if (item.SheetEntityList == null)
{
continue;
}
/*
* 反向排序：目的是从下向上移动
* 避免从上乡下导致变更后的行号跟实体的行号对不上
*/
foreach (var entity in item.SheetEntityList.OrderByDescending(o => o.RowNumber))
{
IRow row = sheet.GetRow(entity.RowNumber);
if (entity.IsDeleteRow == true && row != null)
{
/*
* 说明：startRow、endRow从1开始，从开始行至结束行整体向上移动一行
* n：负数代表向上移动，正数代表向下移动
*/
//sheet.ShiftRows(entity.RowNumber, sheet.LastRowNum, -1);//有bug，向上移动行后批注没有了
row.ZeroHeight = true;//隐藏行
}
}
}
}
/// <summary>
/// 设置区块
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetAreaBlock(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//循环遍历设置区块
foreach (var item in excelGlobalDTO.Sheets)
{
//单个Sheet设置区块
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
if (item.AreaBlock != null)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(item.AreaBlock.StartRowIndex, item.AreaBlock.EndRowIndex, item.AreaBlock.StartColumnIndex, item.AreaBlock.EndColumnIndex);
sheet.AddMergedRegion(cellRangeAddress);
//创建行、列
IRow row = sheet.CreateRow(item.AreaBlock.StartRowIndex);
ICell cell = row.CreateCell(item.AreaBlock.StartColumnIndex);
cell.SetCellValue(item.AreaBlock.Content);
//设置列样式
ICellStyle cellStyle = excelGlobalDTO.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.WrapText = true;
cell.CellStyle = cellStyle;
//设置高度
if (item.AreaBlock.Height != null)
{
row.Height = item.AreaBlock.Height.Value;
}
}
}
}
/// <summary>
/// Excel处理
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void ExcelHandleAfter(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
IExport <TEntity> export = ServiceContainer.GetService <IExport <TEntity> >();
if (export != null)
{
export.ExcelHandleAfter(excelGlobalDTO);
}
}
/// <summary>
/// 基于数据导出
/// </summary>
public void ExportByData()
{
List <ContractProductImportDTO> data = new List <ContractProductImportDTO>();
//代码注释
for (int i = 0; i < 10; i++)
{
//代码注释
ContractProductImportDTO entity = new ContractProductImportDTO();
entity.Name = "Name";
entity.Price = 788;
entity.Count = 89;
//代码注释
data.Add(entity);
}
#region 基于数据的导出-单Sheet
////准备
//ExcelGlobalDTO<ContractProductImportDTO> excelGlobalDTO = new ExcelGlobalDTO<ContractProductImportDTO>();
//excelGlobalDTO.SetDefaultSheet();
//excelGlobalDTO.Sheets.First().SheetEntityList = data;
//excelGlobalDTO.FilePath = Environment.CurrentDirectory + "/Template/合同导入模板-导出模板.xls";
////导出
//Export<ContractProductImportDTO> export = new Export<ContractProductImportDTO>();
//export.ExecuteByData(excelGlobalDTO);
#endregion
#region 基于数据的导出-多Sheet
//基于数据的导出
ExcelGlobalDTO <ContractProductImportDTO> excelGlobalDTO = new ExcelGlobalDTO <ContractProductImportDTO>();
excelGlobalDTO.SetDefaultSheet();
excelGlobalDTO.Sheets.First().SheetEntityList = data;
//导出第一个
Export <ContractProductImportDTO> export = new Export <ContractProductImportDTO>();
export.ExecuteByData(excelGlobalDTO);
//代码注释
ExcelGlobalDTO <ContractProductImportDTO> excelGlobalDTO2 = new ExcelGlobalDTO <ContractProductImportDTO>();
excelGlobalDTO2.Workbook = excelGlobalDTO.Workbook;
excelGlobalDTO2.SetDefaultSheet();
excelGlobalDTO2.Sheets.First().SheetEntityList = data;
excelGlobalDTO2.FilePath = Environment.CurrentDirectory + "/Template/合同导入模板-导出模板.xls";
//导出第二个
Export <ContractProductImportDTO> export2 = new Export <ContractProductImportDTO>();
export2.ExecuteByData(excelGlobalDTO2);
#endregion
}
/// <summary>
/// Excel处理
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void ExcelHandle(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//执行处理前扩展
ExcelHandleBefore(excelGlobalDTO);
//设置区块
AreaBlock <TEntity> areaBlock = new AreaBlock <TEntity>();
areaBlock.SetAreaBlock(excelGlobalDTO);
//设置头部颜色
HeadColor <TEntity> headColor = new HeadColor <TEntity>();
headColor.SetHeadColor(excelGlobalDTO);
excelGlobalDTO.PerformanceMonitoring.Start("SetRowColor");
//设置行颜色
RowColor <TEntity> rowColor = new RowColor <TEntity>();
rowColor.SetRowColor(excelGlobalDTO);
excelGlobalDTO.PerformanceMonitoring.Stop();
//设置锁定
SheetLocked <TEntity> sheetLocked = new SheetLocked <TEntity>();
sheetLocked.SetSheetLocked(excelGlobalDTO);
//设置列隐藏
SheetColumnHidden <TEntity> sheetColumnHidden = new SheetColumnHidden <TEntity>();
sheetColumnHidden.SetSheetColumnHidden(excelGlobalDTO);
//批注
Comment <TEntity> comment = new Comment <TEntity>();
//清空批注
comment.ClearComment(excelGlobalDTO);
//设置批注
comment.SetComment(excelGlobalDTO);
//设置列类型
SheetColumnType <TEntity> sheetColumnType = new SheetColumnType <TEntity>();
sheetColumnType.SetSheetColumnType(excelGlobalDTO);
//从Excel处理
SlaveExcel <TEntity> slaveExcel = new SlaveExcel <TEntity>();
slaveExcel.SlaveExcelHandle(excelGlobalDTO);
//执行处理后扩展
ExcelHandleAfter(excelGlobalDTO);
}
/// <summary>
/// 主从级联：从Excel处理
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SlaveExcelHandle(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//判断是否有从Excel（主从级联）
if (excelGlobalDTO.SlaveExcelGlobalDTO != null)
{
//设置Workbook,不断基于父级写样式
Type globalType = excelGlobalDTO.SlaveExcelGlobalDTO.GetType();
globalType.GetProperty("Workbook").SetValue(excelGlobalDTO.SlaveExcelGlobalDTO, excelGlobalDTO.Workbook);
string className = "Warship.Excel.Export.Export`1";
Type slaveType = Type.GetType(className).MakeGenericType(globalType.GetGenericArguments()[0]);
object slaveEntity = Activator.CreateInstance(slaveType);
slaveType.GetMethod("Execute").Invoke(slaveEntity, new object[] { excelGlobalDTO.SlaveExcelGlobalDTO });
}
}
public void AddArea()
{
string excelPath = Directory.GetCurrentDirectory() + "\\Excel\\合同材料导入模版.xls";
//基于数据的导出
ExcelGlobalDTO <ContractProductImportDTO> excelGlobalDTO = new ExcelGlobalDTO <ContractProductImportDTO>();
excelGlobalDTO.SetDefaultSheet();
excelGlobalDTO.GlobalStartRowIndex = 1;
//循环设置区块内容
foreach (var item in excelGlobalDTO.Sheets)
{
//设置区块
item.AreaBlock = new AreaBlock();
item.AreaBlock.StartRowIndex = 0;
item.AreaBlock.EndRowIndex = 0;
//设置区块
item.AreaBlock.StartColumnIndex = 0;
item.AreaBlock.EndColumnIndex = 6;
item.AreaBlock.Height = 256 * 3;
//设置区块内容
StringBuilder noteString = new StringBuilder("相关数据字典：（★★请严格按照相关格式填写，以免导入错误★★）\n");
noteString.Append("1.列名带有' * '是必填列;\n");
noteString.Append("2.会员卡号：会员卡号长度为3~20位,且只能数字或者英文字母;\n");
//设置区块内容
noteString.Append("3.性别：填写“男”或者“女”;\n");
//设置区块内容
noteString.Append("4.手机号码：只能是11位数字的标准手机号码;\n");
noteString.Append("5.固定电话：最好填写为“区号+电话号码”，例：075529755361;\n");
//设置区块内容
noteString.Append("6.会员生日：填写格式“年-月-日”，例：1990-12-27，没有则不填;\n");
//设置区块
item.AreaBlock.Content = noteString.ToString();
}
//设置导出错误信息
Export <ContractProductImportDTO> export = new Export <ContractProductImportDTO>();
export.ExecuteByData(excelGlobalDTO);
}
/// <summary>
/// 备选项导出信息
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="datas">导出信息</param>
public static IWorkbook OptionExport <Entity>(IWorkbook workbook, string sheetName, List <Entity> datas) where Entity : ExcelRowModel, new()
{
#region 备选项
//备选项
ExcelGlobalDTO <Entity> excelGlobalDTO = new ExcelGlobalDTO <Entity>();
excelGlobalDTO.SetDefaultSheet();
excelGlobalDTO.Sheets.First().SheetName = sheetName;
excelGlobalDTO.Sheets.First().SheetEntityList = datas;
excelGlobalDTO.Workbook = workbook;
//创建导出对象
Export <Entity> export = new Export <Entity>();
export.ExecuteByData(excelGlobalDTO);
return(excelGlobalDTO.Workbook);
#endregion
}
public void OptionsSet()
{
string excelPath = Directory.GetCurrentDirectory() + "\\Excel\\合同材料导入模版.xls";
//基于数据的导出
ExcelGlobalDTO <ContractProductImportDTO> excelGlobalDTO = new ExcelGlobalDTO <ContractProductImportDTO>();
excelGlobalDTO.SetDefaultSheet();
excelGlobalDTO.GlobalStartRowIndex = 1;
Dictionary <string, List <string> > dic = new Dictionary <string, List <string> >();
dic.Add("类型", new List <string>()
{
"工程类A", "采购类B"
});
excelGlobalDTO.Sheets.First().ColumnOptions = dic;
//设置导出错误信息
Export <ContractProductImportDTO> export = new Export <ContractProductImportDTO>();
export.ExecuteByData(excelGlobalDTO);
}
/// <summary>
/// 执行
/// </summary>
public void Execute()
{
//初始化全局Excel
ExcelGlobalDTO <ContractImportDTO> excelGlobalDTO = new ExcelGlobalDTO <ContractImportDTO>();
excelGlobalDTO.SetDefaultSheet();
//设置级联：父级对象错误信息
ContractImportDTO contract = new ContractImportDTO();
contract.ColumnErrorMessage.Add(new ColumnErrorMessage()
{
ColumnName = "A"
});
//设置级联：从对象错误信息
ContractProductImportDTO product = new ContractProductImportDTO();
product.ColumnErrorMessage.Add(new ColumnErrorMessage()
{
ColumnName = "B"
});
contract.Products = new List <ContractProductImportDTO>()
{
product
};
//设置实体集合
excelGlobalDTO.Sheet.SheetEntityList = new List <ContractImportDTO>()
{
contract
};
//获取所有错误信息
var errors = excelGlobalDTO.GetColumnErrorMessages();
}
/// <summary>
/// 初始化Excel
/// </summary>
/// <param name="excelGlobalDTO"></param>
private void GlobalExcelInitByData(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//工作簿为空判断
if (excelGlobalDTO.Workbook == null)
{
excelGlobalDTO.Workbook = ExcelHelper.CreateWorkbook(excelGlobalDTO.ExcelVersionEnum);
}
//遍历Sheet，创建头部、数据行
foreach (var item in excelGlobalDTO.Sheets)
{
item.SheetHeadList = ExcelAttributeHelper <TEntity> .GetHeads();
//获取动态列
List <ColumnModel> dynamicColumns = System.Activator.CreateInstance <TEntity>().GetDynamicColumns();
if (dynamicColumns != null && dynamicColumns.Count > 0)
{
if (item.SheetHeadList == null)
{
item.SheetHeadList = new List <ExcelHeadDTO>();
}
foreach (ColumnModel columnModel in dynamicColumns)
{
//头部实体赋值
ExcelHeadDTO excelHeadDTO = new ExcelHeadDTO();
excelHeadDTO.ColumnIndex = columnModel.ColumnIndex;
excelHeadDTO.SortValue = columnModel.SortValue;
excelHeadDTO.HeadName = columnModel.ColumnName;
excelHeadDTO.IsValidationHead = columnModel.IsValidationHead;
excelHeadDTO.IsSetHeadColor = columnModel.IsSetHeadColor;
excelHeadDTO.IsLocked = columnModel.IsLocked;
excelHeadDTO.ColumnType = columnModel.ColumnType;
excelHeadDTO.IsHiddenColumn = columnModel.IsHiddenColumn;
excelHeadDTO.ColumnWidth = columnModel.ColumnWidth;
excelHeadDTO.Format = columnModel.Format;
excelHeadDTO.BackgroundColor = columnModel.BackgroundColor;
item.SheetHeadList.Add(excelHeadDTO);
}
//排序
item.SheetHeadList = item.SheetHeadList.OrderBy(o => o.SortValue).ToList();
int order = 0;
item.SheetHeadList.ForEach(it => { it.ColumnIndex = order++; });
}
//创建Sheet
ISheet sheet = null;
#region 创建Sheet
//判断Sheet是否存在，不存在则创建
if (string.IsNullOrEmpty(item.SheetName))
{
sheet = excelGlobalDTO.Workbook.CreateSheet();
item.SheetName = sheet.SheetName;
}
else
{
//如果存在则使用存在
ISheet existSheet = excelGlobalDTO.Workbook.GetSheet(item.SheetName);
if (existSheet == null)
{
//处理特殊字符
string sheetName = item.SheetName.Replace("\\", "").Replace("/", "").Replace(":", "").Replace("?", "")
.Replace("*", "").Replace("[", "").Replace("]", "");
sheet = excelGlobalDTO.Workbook.CreateSheet(sheetName);
item.SheetName = sheet.SheetName;
}
else
{
sheet = existSheet;
}
}
#endregion
item.SheetIndex = excelGlobalDTO.Workbook.GetSheetIndex(sheet.SheetName);
//初始化sheet页表头和单元格样式
InitCellStyle(item.SheetHeadList, excelGlobalDTO.Workbook);
#region 创建头部
//创建头部
bool isExistHead = sheet.GetRow(item.StartRowIndex.Value) == null ? false : true;//是否存在头部
IRow row = sheet.GetRow(item.StartRowIndex.Value) ?? sheet.CreateRow(item.StartRowIndex.Value);
Dictionary <string, ICellStyle> styleDic = new Dictionary <string, ICellStyle>();
//头部
foreach (var head in item.SheetHeadList)
{
//获取单元格对象
ICell cell = row.GetCell(head.ColumnIndex) ?? row.CreateCell(head.ColumnIndex);
//设置样式
if (cell.CellStyle.IsNullOrEmpty())
{
cell.CellStyle = HeadCellStyleDic[head.HeadName];
}
//设置值
cell.SetCellValue(head.HeadName);
//设置列宽
if (head.ColumnWidth == 0 && head.HeadName != null)
{
head.ColumnWidth = Encoding.Default.GetBytes(head.HeadName).Length;
}
if (isExistHead == false)
{
head.ColumnWidth = head.ColumnWidth + 1;//多留一个字符的宽度
sheet.SetColumnWidth(head.ColumnIndex, head.ColumnWidth * 256);
}
}
#endregion
//如果没有实体集合则跳出
if (item.SheetEntityList == null)
{
continue;
}
//设置行号
int rowNumber = item.StartRowIndex.Value;
foreach (var entity in item.SheetEntityList)
{
//设置实体的行号
rowNumber++;
entity.RowNumber = rowNumber;
}
#region 创建行、列及设置值
foreach (var entity in item.SheetEntityList)
{
//创建行
IRow dataRow = sheet.GetRow(entity.RowNumber) ?? sheet.CreateRow(entity.RowNumber);
//循环创建列
foreach (var head in item.SheetHeadList)
{
//获取单元格对象
ICell cell = dataRow.GetCell(head.ColumnIndex) ?? dataRow.CreateCell(head.ColumnIndex);
//获取单元格样式
if (cell.CellStyle.IsNullOrEmpty())
{
cell.CellStyle = CellStyleDic[head.HeadName];
}
object value = null;
if (string.IsNullOrEmpty(head.PropertyName) == false)
{
value = entity.GetType().GetProperty(head.PropertyName).GetValue(entity);
}
else
{
if (entity.OtherColumns != null)
{
ColumnModel columnModel = entity.OtherColumns.Where(w => w.ColumnName == head.HeadName).FirstOrDefault();
value = columnModel?.ColumnValue;
}
}
if (value != null)
{
if (head.ColumnType == Attribute.Enum.ColumnTypeEnum.Date && string.IsNullOrEmpty(head.Format) == false)
{
cell.SetCellValue(((DateTime)value).ToString(head.Format));
}
else if (head.ColumnType == Attribute.Enum.ColumnTypeEnum.Decimal && string.IsNullOrEmpty(head.Format) == false)
{
string cellValue = ((decimal)value).ToString(GetNewFormatString(head.Format));
cell.SetCellValue(cellValue);
}
else if (head.ColumnType == Attribute.Enum.ColumnTypeEnum.Option && string.IsNullOrEmpty(head.Format) == false)
{
string cellValue = ((decimal)value).ToString(GetNewFormatString(head.Format));
cell.SetCellValue(cellValue);
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
}
#endregion
}
}
/// <summary>
/// 生成代理
/// </summary>
/// <param name="excelGlobalDTO"></param>
/// <returns></returns>
public static object GenerateProxy <TEntity>(ExcelGlobalDTO <TEntity> excelGlobalDTO) where TEntity : ExcelRowModel
{
string classCode = @"
using System;
using System.Collections.Generic;
namespace Warship.Utility.CodeCompiler {{
public class ProxyEntity{{
{0}
}}
}}
";
//创建代码串
StringBuilder attrCode = new StringBuilder();
PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in propertyInfos)
{
if (prop.PropertyType.IsValueType)
{
attrCode.AppendFormat("public {0} {1} {{ set; get; }}", prop.PropertyType.Name, prop.Name);
attrCode.AppendLine();
}
}
var dynamicColumn = Activator.CreateInstance <TEntity>().GetDynamicColumns();
if (dynamicColumn != null)
{
foreach (ColumnModel prop in dynamicColumn)
{
attrCode.AppendFormat("public string {0} {{ set; get; }}", prop.PropertyName);
attrCode.AppendLine();
}
}
if (excelGlobalDTO.Sheet.ColumnConfig != null)
{
foreach (ColumnModel prop in excelGlobalDTO.Sheet.ColumnConfig)
{
attrCode.AppendFormat("public string {0} {{ set; get; }}", prop.PropertyName);
attrCode.AppendLine();
}
}
string compilerCode = string.Format(classCode, attrCode.ToString());
//编译器的传入参数
CompilerParameters cp = new CompilerParameters();
cp.ReferencedAssemblies.Add("system.dll"); //添加程序集 system.dll 的引用
cp.GenerateExecutable = false; //不生成可执行文件
cp.GenerateInMemory = true; //在内存中运行
//创建C#编译器实例
CodeDomProvider provider = CodeDomProvider.CreateProvider("C#");
//得到编译器实例的返回结果
CompilerResults cr = provider.CompileAssemblyFromSource(cp, compilerCode);
//如果有错误则抛异常
if (cr.Errors.HasErrors)
{
StringBuilder error = new StringBuilder(); //创建错误信息字符串
foreach (CompilerError err in cr.Errors) //遍历每一个出现的编译错误
{
error.Append(err.ErrorText); //添加进错误文本，每个错误后换行
error.Append(Environment.NewLine);
}
}
////获取编译器实例的程序集
Assembly assembly = cr.CompiledAssembly;
//获取类型
Type type = assembly.GetType("Warship.Utility.CodeCompiler.ProxyEntity");
return(type);
}
/// <summary>
/// 设置锁定
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetSheetLocked(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//遍历Sheet实体集合
foreach (var item in excelGlobalDTO.Sheets)
{
//为空判断
if (item.SheetHeadList == null)
{
continue;
}
//获取Excel的Sheet，对Excel设置批注
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//锁定列头
ICellStyle cellStyle = excelGlobalDTO.Workbook.CreateCellStyle();
#region 注释代码
foreach (var head in item.SheetHeadList)
{
//行是否锁定，此处设置的是否锁定只针对未输入行上的列
//IRow row = sheet.GetRow(item.StartRowIndex.Value);
//ICell cell = row.GetCell(head.ColumnIndex);
//ICellStyle cellStyle = cell.CellStyle;
//cellStyle.IsLocked = head.IsLocked;
//cell.CellStyle = cellStyle;
//设置整列样式
//sheet.SetDefaultColumnStyle(head.ColumnIndex, cellStyle);
//锁定
//if (head.IsLocked == true)
//{
// sheet.ProtectSheet("MD5");
//}
}
#endregion
//遍历行，设置锁定
for (int j = (item.StartRowIndex.Value) + 1; j <= sheet.LastRowNum; j++)
{
//获取行
IRow row = sheet.GetRow(j);
//不存在则跳出
if (row == null)
{
continue;
}
//遍历头部，设置列
foreach (var head in item.SheetHeadList)
{
//获取列
ICell cell = row.GetCell(head.ColumnIndex);
//判断单元格是否为空
if (cell == null)
{
continue;
}
if (cell.CellStyle == null)
{
cell.CellStyle = excelGlobalDTO.Workbook.CreateCellStyle();
}
cell.CellStyle.IsLocked = head.IsLocked;
}
}
foreach (var head in item.SheetHeadList)
{
//锁定
if (head.IsLocked == true)
{
sheet.ProtectSheet("MD5");
}
}
}
}
/// <summary>
/// 根据模板Excel执行
/// </summary>
/// <param name="buffer"></param>
/// <param name="excelVersionEnum"></param>
/// <param name="excelGlobalDTO"></param>
public virtual void ExecuteByEmptyBuffer(byte[] buffer, ExcelVersionEnum excelVersionEnum, ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
Stream stream = new MemoryStream(buffer);
excelGlobalDTO.Workbook = ExcelHelper.GetWorkbook(stream, excelVersionEnum);
this.ExecuteByData(excelGlobalDTO);
}
/// <summary>
/// 动态列验证
/// </summary>
/// <param name="excelGlobalDTO"></param>
/// <param name="validationModelEnum"></param>
public static void ValidationHead(ExcelGlobalDTO <TEntity> excelGlobalDTO, ValidationModelEnum validationModelEnum = ValidationModelEnum.ConfigColumn)
{
//为空判断
if (excelGlobalDTO == null || excelGlobalDTO.Sheets == null)
{
return;
}
//遍历Sheet实体集合
foreach (var sheetModel in excelGlobalDTO.Sheets)
{
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(sheetModel.SheetIndex);
//头部校验
IRow row = sheet.GetRow(sheetModel.StartRowIndex.Value);
if (row == null)
{
continue;
}
//获取表头信息
List <string> cellValues = row.Cells.Select(s => ExcelHelper.GetCellValue(s)).ToList();
if (cellValues == null)
{
continue;
}
//获取列的设置信息
List <ColumnModel> columnList = null;
if (validationModelEnum == ValidationModelEnum.ConfigColumn)
{
columnList = sheetModel.ColumnConfig;
}
else
{
columnList = System.Activator.CreateInstance <TEntity>().GetDynamicColumns();
}
//当为空的时候跳出,场景：
if (columnList == null)
{
continue;
}
//遍历配置列
foreach (ColumnModel columnModel in columnList)
{
//如果无校验则跳过
if (columnModel.ColumnValidations == null)
{
continue;
}
//遍历校验
foreach (var validation in columnModel.ColumnValidations)
{
//校验必填的，判断表头是否在excel中存在
if (validation.ValidationTypeEnum == ValidationTypeEnum.Required && cellValues.Contains(columnModel.ColumnName) == false)
{
throw new Exception(excelGlobalDTO.ExcelValidationMessage.Clgyl_Common_Import_TempletError);
}
}
}
}
}
/// <summary>
/// 动态列验证
/// </summary>
/// <param name="excelGlobalDTO"></param>
/// <param name="validationModelEnum"></param>
public static void ValidationValue(ExcelGlobalDTO <TEntity> excelGlobalDTO, ValidationModelEnum validationModelEnum = ValidationModelEnum.ConfigColumn)
{
//为空判断
if (excelGlobalDTO == null || excelGlobalDTO.Sheets == null)
{
return;
}
//遍历Sheet实体集合
foreach (var sheet in excelGlobalDTO.Sheets)
{
//获取Sheet头部实体集合
var headDtoList = sheet.SheetHeadList;
foreach (var item in sheet.SheetEntityList)
{
//获取列的设置信息
List <ColumnModel> columnList = null;
if (validationModelEnum == ValidationModelEnum.ConfigColumn)
{
columnList = sheet.ColumnConfig;
}
else
{
columnList = System.Activator.CreateInstance <TEntity>().GetDynamicColumns();
}
//当为空的时候跳出,场景：
if (columnList == null)
{
continue;
}
foreach (var config in columnList)
{
//Excel获取的动态列
ColumnModel columnModel = item.OtherColumns.Where(n => n.ColumnName == config.ColumnName).FirstOrDefault();
#region 类型校验
try
{
switch (config.ColumnType)
{
case ColumnTypeEnum.Decimal:
Convert.ToDecimal(columnModel.ColumnValue);
break;
case ColumnTypeEnum.Date:
Convert.ToDateTime(columnModel.ColumnValue);
break;
case ColumnTypeEnum.DateTime:
Convert.ToDateTime(columnModel.ColumnValue);
break;
}
}
catch (Exception ex)
{
//异常信息
ColumnErrorMessage errorMsg = new ColumnErrorMessage()
{
ColumnName = config.ColumnName
};
if (config.ColumnConvertError != null)
{
errorMsg.ErrorMessage = config.ColumnConvertError.ErrorMessage;
}
else
{
errorMsg.ErrorMessage = ex.Message;
}
//添加至集合
item.ColumnErrorMessage.Add(errorMsg);
}
#endregion
if (config.ColumnValidations == null)
{
continue;
}
//遍历列校验
foreach (ColumnValidationModel validation in config.ColumnValidations)
{
//是否有错误
bool isError = true;
string errorMessage = null;
switch (validation.ValidationTypeEnum)
{
//必填校验
case ValidationTypeEnum.Required:
isError = string.IsNullOrEmpty(columnModel.ColumnValue) ? true : false;
errorMessage = validation.RequiredAttribute.ErrorMessage;
break;
//长度校验
case ValidationTypeEnum.Length:
isError = columnModel.ColumnValue?.Length > validation.LengthAttribute?.Length ? true : false;
errorMessage = validation.LengthAttribute.ErrorMessage;
break;
//范围校验
case ValidationTypeEnum.Range:
errorMessage = validation.RangeAttribute.ErrorMessage;
RangeAttributeValidation <TEntity> rangeAttributeValidation = new RangeAttributeValidation <TEntity>();
isError = rangeAttributeValidation.CheckIsError(validation.RangeAttribute, columnModel.ColumnValue);
break;
//格式校验
case ValidationTypeEnum.Format:
#region 格式校验
errorMessage = validation.FormatAttribute.ErrorMessage;
FormatAttributeValidation <TEntity> formatAttributeValidation = new FormatAttributeValidation <TEntity>();
isError = formatAttributeValidation.CheckIsError(validation.FormatAttribute, columnModel.ColumnValue);
#endregion
break;
}
//是否有异常
if (isError == true)
{
//异常信息
ColumnErrorMessage errorMsg = new ColumnErrorMessage()
{
ColumnName = columnModel.ColumnName,
ErrorMessage = errorMessage
};
//添加至集合
item.ColumnErrorMessage.Add(errorMsg);
}
}
}
}
}
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="globalStartRowIndex">起始行</param>
/// <param name="globalStartColumnIndex">起始列</param>
public Import(int globalStartRowIndex, int?globalStartColumnIndex = 0)
{
ExcelGlobalDTO = new ExcelGlobalDTO <TEntity>();
ExcelGlobalDTO.GlobalStartRowIndex = globalStartRowIndex;
ExcelGlobalDTO.GlobalStartColumnIndex = globalStartColumnIndex ?? 0;
}
/// <summary>
/// 构造函数
/// </summary>
public Import()
{
ExcelGlobalDTO = new ExcelGlobalDTO <TEntity>();
}
/// <summary>
/// 设置批注
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetComment(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
foreach (var item in excelGlobalDTO.Sheets)
{
//值判断
if (item.SheetEntityList == null)
{
continue;
}
//获取Excel的Sheet，对Excel设置批注
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//批注总数
int commentCount = 0;
foreach (var entity in item.SheetEntityList)
{
if (entity.ColumnErrorMessage == null)
{
continue;
}
//NPOI目前最大批注个数设置的默认值是1024个,如果超出默认值就会报错
if (commentCount >= 1000)
{
break;
}
//获取行对象
IRow row = sheet.GetRow(entity.RowNumber);
//基于属性设置批注
var groupPropertyName = entity.ColumnErrorMessage.Where(w => w.PropertyName != null).GroupBy(g => g.PropertyName);
foreach (var groupItem in groupPropertyName)
{
//NPOI目前最大批注个数设置的默认值是1024个,如果超出默认值就会报错
if (commentCount >= 1000)
{
break;
}
//获取单元格，设置批注
ExcelHeadDTO headDto = item.SheetHeadList.Where(n => n.PropertyName == groupItem.Key).FirstOrDefault();
ICell cell = row.GetCell(headDto.ColumnIndex);
if (cell == null)
{
cell = row.CreateCell(headDto.ColumnIndex);
}
//设置批注
string errorMsg = string.Join(";", groupItem.Select(s => s.ErrorMessage).Distinct().ToArray());
SetCellComment(cell, errorMsg, excelGlobalDTO);
commentCount++;
}
//基于列头设置批注，适用于动态列
var groupColumnName = entity.ColumnErrorMessage.Where(w => w.PropertyName == null).GroupBy(g => g.ColumnName);
foreach (var groupItem in groupColumnName)
{
//NPOI目前最大批注个数设置的默认值是1024个,如果超出默认值就会报错
if (commentCount >= 1000)
{
break;
}
//获取单元格，设置批注
ExcelHeadDTO headDto = item.SheetHeadList.Where(n => n.HeadName == groupItem.Key).FirstOrDefault();
ICell cell = row.GetCell(headDto.ColumnIndex);
if (cell == null)
{
cell = row.CreateCell(headDto.ColumnIndex);
}
//设置批注
string errorMsg = string.Join(";", groupItem.Select(s => s.ErrorMessage).Distinct().ToArray());
SetCellComment(cell, errorMsg, excelGlobalDTO);
commentCount++;
}
}
}
}
/// <summary>
/// 根据模板导出Excel（适用模板+数据）
/// </summary>
/// <param name="filePath"></param>
/// <param name="excelVersionEnum"></param>
/// <param name="excelGlobalDTO"></param>
public virtual void ExecuteByFile(string filePath, ExcelVersionEnum excelVersionEnum, ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//级联处理
FileInfo fileInfo = new FileInfo(filePath);
byte[] buffers = new byte[fileInfo.Length];
using (FileStream fs = new FileStream(filePath, FileMode.Open))
{
int read;
while ((read = fs.Read(buffers, 0, buffers.Length)) > 0)
{
fs.Dispose();
}
//fs.Read(buffers, 0, buffers.Length)
}
ExecuteByEmptyBuffer(buffers, excelVersionEnum, excelGlobalDTO);
}
/// <summary>
/// 设置列类型
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetSheetColumnType(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//遍历Sheet实体集合
foreach (var item in excelGlobalDTO.Sheets)
{
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//判断是否为空
if (item.SheetEntityList == null)
{
continue;
}
//遍历行
foreach (var entity in item.SheetEntityList)
{
IRow row = sheet.GetRow(entity.RowNumber);
foreach (var head in item.SheetHeadList)
{
//获取列
ICell cell = row.GetCell(head.ColumnIndex);
//设置生成下拉框的行和列
//var cellRegions = new NPOI.SS.Util.CellRangeAddressList((item.StartRowIndex.Value) + 1, sheet.LastRowNum, head.ColumnIndex, head.ColumnIndex);
var cellRegions = new NPOI.SS.Util.CellRangeAddressList(entity.RowNumber, entity.RowNumber, head.ColumnIndex, head.ColumnIndex);
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvConstraint = null;
IDataValidation dataValidate = null;
switch (head.ColumnType)
{
//列类型为文本
case Attribute.Enum.ColumnTypeEnum.Text:
break;
//列类型为日期
case Attribute.Enum.ColumnTypeEnum.Date:
head.Format = string.IsNullOrEmpty(head.Format) ? "yyyy-MM-dd" : head.Format;
dvConstraint = dvHelper.CreateDateConstraint(OperatorType.BETWEEN, DateTime.MinValue.ToString(head.Format), DateTime.MaxValue.ToString(head.Format), head.Format);
dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "必须为日期");
dataValidate.ShowPromptBox = true;
break;
//列类型为浮点
case Attribute.Enum.ColumnTypeEnum.Decimal:
dvConstraint = dvHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "0", "9999999999");
dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "必须在0~9999999999之间。");
dataValidate.ShowPromptBox = true;
break;
//列类型为选项
case Attribute.Enum.ColumnTypeEnum.Option:
List <string> options = null;
#region 全局列设置
if (item.ColumnOptions != null && item.ColumnOptions.Count > 0)
{
string key = null;
//如果头部名称存在则取头部名称（以头部名称设置选项）
if (item.ColumnOptions.Keys.Contains(head.HeadName) == true)
{
key = head.HeadName;
}
//如果属性存在则取头部名称（以属性名称设置选项）
if (item.ColumnOptions.Keys.Contains(head.PropertyName) == true)
{
key = head.PropertyName;
}
//不为空说明存在，则设置选项
if (key != null)
{
options = item.ColumnOptions[key];
}
}
#endregion
#region 单个列设置
//行的优先级高于Sheet的优先级
if (entity.ColumnOptions != null && entity.ColumnOptions.Count > 0)
{
string key = null;
//如果头部名称存在则取头部名称（以头部名称设置选项）
if (entity.ColumnOptions.Keys.Contains(head.HeadName) == true)
{
key = head.HeadName;
}
//如果属性存在则取头部名称（以属性名称设置选项）
if (entity.ColumnOptions.Keys.Contains(head.PropertyName) == true)
{
key = head.PropertyName;
}
//不为空说明存在，则设置选项
if (key != null)
{
options = entity.ColumnOptions[key];
}
}
#endregion
//不符合条件则跳出
if (options == null)
{
continue;
}
if (options.Count() > 0)
{
dvConstraint = dvHelper.CreateExplicitListConstraint(options.ToArray());
dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
dataValidate.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
dataValidate.ShowPromptBox = true;
}
break;
}
//类型在指定的范围内是才设置校验
if (dataValidate != null)
{
sheet.AddValidationData(dataValidate);
}
}
}
}
}