/// <summary>
/// 复制Excel属性
/// </summary>
/// <param name="excelPropety">单元格属性</param>
/// <returns>复制后的单元格</returns>
private ExcelPropety CopyExcelPropety(ExcelPropety excelPropety)
{
ExcelPropety ep = new ExcelPropety
{
BackgroudColor = excelPropety.BackgroudColor,
ColumnName = excelPropety.ColumnName,
DataType = excelPropety.DataType,
ResourceType = excelPropety.ResourceType,
IsNullAble = excelPropety.IsNullAble,
ListItems = excelPropety.ListItems,
MaxValuseOrLength = excelPropety.MaxValuseOrLength,
MinValueOrLength = excelPropety.MinValueOrLength,
Value = excelPropety.Value,
SubTableType = excelPropety.SubTableType,
CharCount = excelPropety.CharCount,
ReadOnly = excelPropety.ReadOnly,
FormatData = excelPropety.FormatData,
FormatSingleData = excelPropety.FormatSingleData,
FieldName = excelPropety.FieldName
};
List <ExcelPropety> li = new List <ExcelPropety>();
foreach (var item in excelPropety.DynamicColumns)
{
li.Add(CopyExcelPropety(item));
}
ep.DynamicColumns = li;
return(ep);
}
protected void SetEntityFieldValue(object entity, ExcelPropety ep, int rowIndex, string fieldName, T templateVM)
{
if (ep.FormatData != null)
{
ProcessResult processResult = ep.FormatData(ep.Value, templateVM);
if (processResult != null)
{
//未添加任何处理结果
if (processResult.EntityValues.Count == 0)
{
PropertyHelper.SetPropertyValue(entity, fieldName, ep.Value, stringBasedValue: true);
}
//字段为一对一
if (processResult.EntityValues.Count == 1)
{
ep.Value = processResult.EntityValues[0].FieldValue;
if (!string.IsNullOrEmpty(processResult.EntityValues[0].ErrorMsg))
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = processResult.EntityValues[0].ErrorMsg, ExcelIndex = rowIndex
});
}
PropertyHelper.SetPropertyValue(entity, fieldName, ep.Value, stringBasedValue: true);
}
//字段为一对多
if (processResult.EntityValues.Count > 1)
{
foreach (var entityValue in processResult.EntityValues)
{
if (!string.IsNullOrEmpty(entityValue.ErrorMsg))
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = entityValue.ErrorMsg, ExcelIndex = rowIndex
});
}
PropertyHelper.SetPropertyValue(entity, entityValue.FieldName, entityValue.FieldValue, stringBasedValue: true);
}
}
}
}
else if (ep.FormatSingleData != null)
{
ep.FormatSingleData(ep.Value, templateVM, out string singleEntityValue, out string errorMsg);
if (!string.IsNullOrEmpty(errorMsg))
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = errorMsg, ExcelIndex = rowIndex
});
}
PropertyHelper.SetPropertyValue(entity, fieldName, singleEntityValue, stringBasedValue: true);
}
else
{
PropertyHelper.SetPropertyValue(entity, fieldName, ep.Value, stringBasedValue: true);
}
}
private void CreateDataTable()
{
TemplateDataTable = new DataTable();
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
foreach (var p in propetys)
{
ExcelPropety excelPropety = (ExcelPropety)p.GetValue(this);
ColumnDataType dateType = excelPropety.DataType;
switch (dateType)
{
case ColumnDataType.Bool:
TemplateDataTable.Columns.Add(p.Name, typeof(bool));
break;
case ColumnDataType.Date:
TemplateDataTable.Columns.Add(p.Name, typeof(string));
break;
case ColumnDataType.Number:
TemplateDataTable.Columns.Add(p.Name, typeof(int));
break;
case ColumnDataType.Text:
TemplateDataTable.Columns.Add(p.Name, typeof(string));
break;
case ColumnDataType.Float:
TemplateDataTable.Columns.Add(p.Name, typeof(decimal));
break;
default:
TemplateDataTable.Columns.Add(p.Name, typeof(string));
break;
}
}
}
/// <summary>
/// 读取模版中的数据
/// </summary>
private void DoMapList()
{
try
{
Template.InitExcelData();
Template.InitCustomFormat();
TemplateData = new List <T>();
hssfworkbook = new HSSFWorkbook();
if (UploadFileId == null)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = Program._localizer["PleaseUploadTemplate"]
});
return;
}
var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault();
hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo);
if (ValidityTemplateType && hssfworkbook.GetSheetAt(1).GetRow(0).Cells[2].ToString() != typeof(T).Name)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = Program._localizer["WrongTemplate"]
});
return;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
//所有ExcelPropety属性
List <ExcelPropety> excelPropetys = new List <ExcelPropety>();
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template);
excelPropetys.Add(ep);
}
#region 验证模版正确性 add by dufei
//取得列数
int columnCount = excelPropetys.Count;
//int excelPropetyCount = excelPropetys.Count;
var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault();
if (dynamicColumn != null)
{
columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1;
//excelPropetyCount = excelPropetyCount + dynamicColumn.DynamicColumns.Count - 1;
}
int pIndex = 0;
var cells = sheet.GetRow(0).Cells;
if (columnCount != cells.Count)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = Program._localizer["WrongTemplate"]
});
return;
}
else
{
bool hassubtable = false;
for (int i = 0; i < cells.Count; i++)
{
if (excelPropetys[pIndex].SubTableType != null)
{
hassubtable = true;
}
if (excelPropetys[pIndex].DataType != ColumnDataType.Dynamic)
{
if (cells[i].ToString().Trim('*') != excelPropetys[pIndex].ColumnName)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = Program._localizer["WrongTemplate"]
});
return;
}
pIndex++;
}
else
{
var listDynamicColumns = excelPropetys[i].DynamicColumns;
int dcCount = listDynamicColumns.Count;
for (int dclIndex = 0; dclIndex < dcCount; dclIndex++)
{
if (cells[i].ToString().Trim('*') != listDynamicColumns[dclIndex].ColumnName)
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = Program._localizer["WrongTemplate"]
});
break;
}
i = i + 1;
}
i = i - 1;
pIndex++;
}
}
//如果有子表，则设置主表字段非必填
if (hassubtable == true)
{
for (int i = 0; i < cells.Count; i++)
{
if (excelPropetys[i].SubTableType == null)
{
excelPropetys[i].IsNullAble = true;
}
}
}
}
#endregion
int rowIndex = 2;
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
if (IsEmptyRow(row, columnCount))
{
return;
}
T result = new T();
int propetyIndex = 0;
for (int i = 0; i < columnCount; i++)
{
ExcelPropety excelPropety = CopyExcelPropety(excelPropetys[propetyIndex]); //excelPropetys[propetyIndex];
var pts = propetys[propetyIndex];
string value = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
if (excelPropety.DataType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
//验证数据类型并添加错误信息
excelPropety.DynamicColumns[dynamicColIndex].ValueValidity(row.GetCell(i + dynamicColIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(), ErrorListVM.EntityList, rowIndex);
}
i = i + dynamicColCount - 1;
}
else
{
excelPropety.ValueValidity(value, ErrorListVM.EntityList, rowIndex);
}
if (ErrorListVM.EntityList.Count == 0)
{
pts.SetValue(result, excelPropety);
}
propetyIndex++;
}
result.ExcelIndex = rowIndex;
TemplateData.Add(result);
rowIndex++;
}
return;
}
catch
{
ErrorListVM.EntityList.Add(new ErrorMessage {
Message = Program._localizer["WrongTemplate"]
});
//ErrorListVM.ErrorList.Add(new ErrorMessage { Message = ex.Message });
}
return;
}
/// <summary>
/// 获取上传的结果值
/// </summary>
public virtual void SetEntityList()
{
EntityList = new List <P>();
//获取Model类的所有属性
var pros = typeof(P).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
var excelPros = typeof(T).GetFields(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance).Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
if (TemplateData == null)
{
DoMapList();
}
//循环Excel中的数据
foreach (var item in TemplateData)
{
int rowIndex = 2;
bool isMainData = false;
//主表信息
Dictionary <string, ExcelPropety> mainInfo = new Dictionary <string, ExcelPropety>();
string mainValString = string.Empty;
//子表信息
Dictionary <Type, List <FieldInfo> > subpros = new Dictionary <Type, List <FieldInfo> >();
Dictionary <Type, string> subValString = new Dictionary <Type, string>();
//循环TemplateVM中定义的所有的列，区分出主子表
foreach (var epro in excelPros)
{
//获取本列的ExcelProperty的值
if (typeof(T).GetField(epro.Name).GetValue(item) is ExcelPropety ep)
{
//如果是子表的字段
if (ep.SubTableType != null)
{
//保存子表字段信息稍后处理
if (!subpros.ContainsKey(ep.SubTableType))
{
subpros[ep.SubTableType] = new List <FieldInfo>();
}
subpros[ep.SubTableType].Add(epro);
}
else
{
//PropertyHelper.SetPropertyValue(entity, epro.Name, ep.Value, stringBasedValue: true);
//保存子表字段信息稍后处理
mainInfo.Add(ep.FieldName, ep);
mainValString += ep.Value;
}
}
}
//子表信息是否为空
foreach (var sub in subpros)
{
string subVal = string.Empty;
foreach (var field in sub.Value)
{
ExcelPropety ep = typeof(T).GetField(field.Name).GetValue(item) as ExcelPropety;
subVal += ep.Value;
}
subValString.Add(sub.Key, subVal);
}
P entity = null;
//说明主表信息为空
if (string.IsNullOrEmpty(mainValString))
{
entity = EntityList.LastOrDefault();
}
else
{
//初始化一个新的Entity
entity = new P();
isMainData = true;
//给主表赋值
foreach (var mep in mainInfo)
{
SetEntityFieldValue(entity, mep.Value, rowIndex, mep.Key, item);
}
}
//给子表赋值
foreach (var sub in subpros)
{
//循环Entity的所有属性，找到List<SubTableType>类型的字段
foreach (var pro in pros)
{
if (pro.PropertyType.IsGenericType)
{
var gtype = pro.PropertyType.GetGenericArguments()[0];
if (gtype == sub.Key)
{
//子表
var subList = entity.GetType().GetProperty(pro.Name).GetValue(entity);
string fk = DC.GetFKName <P>(pro.Name);
//如果子表不为空
if (!string.IsNullOrEmpty(subValString.Where(x => x.Key == sub.Key).FirstOrDefault().Value))
{
IList list = null;
if (subList == null)
{
//初始化List<SubTableType>
list = typeof(List <>).MakeGenericType(gtype).GetConstructor(Type.EmptyTypes).Invoke(null) as IList;
}
else
{
list = subList as IList;
}
//初始化一个SubTableType
var obj = gtype.GetConstructor(System.Type.EmptyTypes).Invoke(null);
//给SubTableType中和本ExcelProperty同名的字段赋值
foreach (var field in sub.Value)
{
ExcelPropety ep = typeof(T).GetField(field.Name).GetValue(item) as ExcelPropety;
//PropertyHelper.SetPropertyValue(obj, field.Name, ep.Value, stringBasedValue: true);
SetEntityFieldValue(obj, ep, rowIndex, ep.FieldName, item);
}
if (string.IsNullOrEmpty(fk) == false)
{
PropertyHelper.SetPropertyValue(obj, fk, entity.GetID());
}
//将付好值得SubTableType实例添加到List中
list.Add(obj);
PropertyHelper.SetPropertyValue(entity, pro.Name, list);
}
break;
}
}
}
}
entity.ExcelIndex = item.ExcelIndex;
var cinfo = this.SetDuplicatedCheck();
//if (IsUpdateRecordDuplicated(cinfo, entity) == false)
//{
if (isMainData)
{
EntityList.Add(entity);
}
//}
}
isEntityListSet = true;
}
public ErrorObj GetErrorJson()
{
var mse = new ErrorObj();
mse.Form = new Dictionary <string, string>();
var err = ErrorListVM?.EntityList?.Where(x => x.Index == 0).FirstOrDefault()?.Message;
if (string.IsNullOrEmpty(err))
{
var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault();
hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo);
var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
List <ExcelPropety> excelPropetys = new List <ExcelPropety>();
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template);
excelPropetys.Add(ep);
}
int columnCount = excelPropetys.Count;
//int excelPropetyCount = excelPropetys.Count;
var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault();
if (dynamicColumn != null)
{
columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
var errorStyle = hssfworkbook.CreateCellStyle();
IFont f = hssfworkbook.CreateFont();
f.Color = HSSFColor.Red.Index;
errorStyle.SetFont(f);
errorStyle.IsLocked = true;
foreach (var e in ErrorListVM?.EntityList)
{
if (e.Index > 0)
{
var c = sheet.GetRow((int)(e.Index - 1)).CreateCell(columnCount);
c.CellStyle = errorStyle;
c.SetCellValue(e.Message);
}
}
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
ms.Position = 0;
FileAttachmentVM vm = new FileAttachmentVM();
vm.CopyContext(this);
vm.Entity.FileName = "Error-" + fa.FileName;
vm.Entity.Length = ms.Length;
vm.Entity.UploadTime = DateTime.Now;
vm.Entity.SaveFileMode = ConfigInfo.FileUploadOptions.SaveFileMode;
vm = FileHelper.GetFileByteForUpload(vm, ms, ConfigInfo, vm.Entity.FileName, null, null);
vm.Entity.IsTemprory = true;
if ((!string.IsNullOrEmpty(vm.Entity.Path) && (vm.Entity.SaveFileMode == SaveFileModeEnum.Local || vm.Entity.SaveFileMode == SaveFileModeEnum.DFS)) || (vm.Entity.FileData != null && vm.Entity.SaveFileMode == SaveFileModeEnum.Database))
{
vm.DoAdd();
}
ms.Close();
ms.Dispose();
err = "导入时发生错误";
mse.Form.Add("Entity.Import", err);
mse.Form.Add("Entity.ErrorFileId", vm.Entity.ID.ToString());
}
else
{
mse.Form.Add("Entity.Import", err);
}
return(mse);
}
public static ExcelPropety CreateProperty <T>(Expression <Func <T, object> > field, bool isDateTime = false)
{
ExcelPropety cp = new ExcelPropety();
cp.ColumnName = field.GetPropertyDisplayName();
var fname = field.GetPropertyName();
Type t = field.GetPropertyInfo().PropertyType;
if (fname.Contains('.'))
{
int index = fname.LastIndexOf('.');
cp.FieldName = fname.Substring(index + 1);
cp.SubTableType = field.GetPropertyInfo().DeclaringType;
}
else
{
cp.FieldName = fname;
}
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var req = field.GetPropertyInfo().GetCustomAttributes(typeof(RequiredAttribute), false).Cast <RequiredAttribute>().FirstOrDefault();
if (req == null)
{
cp.IsNullAble = true;
}
t = t.GenericTypeArguments[0];
}
if (t == typeof(int) || t == typeof(long) || t == typeof(short))
{
var sl = t.GetCustomAttributes(typeof(RangeAttribute), false).Cast <RangeAttribute>().FirstOrDefault();
cp.DataType = ColumnDataType.Number;
if (sl != null)
{
if (sl.Maximum != null)
{
cp.MaxValuseOrLength = sl.Maximum.ToString();
}
if (sl.Minimum != null)
{
cp.MinValueOrLength = sl.Minimum.ToString();
}
}
}
else if (t == typeof(float) || t == typeof(double) || t == typeof(decimal))
{
cp.DataType = ColumnDataType.Float;
}
else if (t == typeof(bool))
{
cp.DataType = ColumnDataType.Bool;
}
else if (t.IsEnum)
{
cp.DataType = ColumnDataType.Enum;
cp.EnumType = t;
}
else if (t == typeof(DateTime))
{
cp.DataType = ColumnDataType.Date;
if (isDateTime)
{
cp.DataType = ColumnDataType.DateTime;
}
}
else
{
var sl = field.GetPropertyInfo().GetCustomAttributes(typeof(StringLengthAttribute), false).Cast <StringLengthAttribute>().FirstOrDefault();
var req = field.GetPropertyInfo().GetCustomAttributes(typeof(RequiredAttribute), false).Cast <RequiredAttribute>().FirstOrDefault();
cp.DataType = ColumnDataType.Text;
if (req == null)
{
cp.IsNullAble = true;
}
if (sl != null)
{
if (sl.MaximumLength != 0)
{
cp.MaxValuseOrLength = sl.MaximumLength + "";
}
if (sl.MinimumLength != 0)
{
cp.MinValueOrLength = sl.MinimumLength + "";
}
}
}
cp.CharCount = 20;
return(cp);
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
InitExcelData();
CreateDataTable(); //add by dufei
SetTemplateDataValus(); //add by dufei
if (!string.IsNullOrEmpty(FileDisplayName))
{
displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
else
{
displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
//模板sheet页
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName);
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.HeightInPoints = 20;
HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet();
#region 设置excel模板列头
//默认灰色
var headerStyle = GetCellStyle(workbook);
headerStyle.IsLocked = true;
//黄色
var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
yellowStyle.IsLocked = true;
//红色
var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
redStyle.IsLocked = true;
//取得所有ExcelPropety
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
int _currentColunmIndex = 0;
bool IsProtect = false;
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = excelPropety.DataType;
if (excelPropety.ReadOnly)
{
IsProtect = true;
}
//给必填项加星号
string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(colName);
//修改列头样式
switch (excelPropety.BackgroudColor)
{
case BackgroudColorEnum.Yellow:
row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
break;
case BackgroudColorEnum.Red:
row.Cells[_currentColunmIndex].CellStyle = redStyle;
break;
default:
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
break;
}
var dataStyle = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
if (dateType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
if (dynamicCol.ReadOnly)
{
IsProtect = true;
}
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置单元格样式及数据类型
dataStyle.IsLocked = excelPropety.ReadOnly;
dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
else
{
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置是否锁定
dataStyle.IsLocked = excelPropety.ReadOnly;
//设置单元格样式及数据类型
excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
#endregion
#region 添加模版数据 add by dufei
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(1 + i);
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
string colName = propetys[porpetyIndex].Name;
tableRow[colName].ToString();
dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
}
}
}
#endregion
//冻结行
sheet.CreateFreezePane(0, 1, 0, 1);
//锁定excel
if (IsProtect)
{
sheet.ProtectSheet("password");
}
workbook.SetSheetHidden(1, true);
workbook.SetSheetHidden(2, true);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
//设置导出的文件名称
string SheetName = !string.IsNullOrEmpty(FileDisplayName) ? FileDisplayName : this.GetType().Name;
displayName = SheetName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xlsx";
//1.声明Excel文档
IWorkbook workbook = new XSSFWorkbook();
//加载初始化数据和下拉菜单数据，可重载
InitExcelData();
//设置TemplateDataTable的各列的类型
CreateDataTable();
//设置初始化数据到DataTable中
SetTemplateDataValus();
//2.设置workbook的sheet页
ISheet sheet = workbook.CreateSheet();
workbook.SetSheetName(0, SheetName);
//3.设置Sheet页的Row
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 20;
ISheet enumSheet = workbook.CreateSheet();
IRow enumSheetRow1 = enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
ISheet dataSheet = workbook.CreateSheet();
#region 设置excel模板列头
//默认灰色
var headerStyle = GetCellStyle(workbook);
headerStyle.IsLocked = true;
//黄色
var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
yellowStyle.IsLocked = true;
//红色
var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
redStyle.IsLocked = true;
//取得所有ExcelPropety
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
//设置列的索引
int _currentColunmIndex = 0;
//设置Excel是否需要保护，默认不保护
bool IsProtect = false;
//循环类的属性，赋值给列
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
//依次获取属性字段
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = (excelPropety.DataType == ColumnDataType.DateTime || excelPropety.DataType == ColumnDataType.Date) ? ColumnDataType.Text : excelPropety.DataType; //日期类型默认设置成Text类型,在赋值时会进行日期验证
//设置是否保护Excel
if (excelPropety.ReadOnly)
{
IsProtect = true;
}
//给必填项加星号
string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(colName);
//修改列头样式
switch (excelPropety.BackgroudColor)
{
case BackgroudColorEnum.Yellow:
row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
break;
case BackgroudColorEnum.Red:
row.Cells[_currentColunmIndex].CellStyle = redStyle;
break;
default:
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
break;
}
var dataStyle = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
if (dateType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
if (dynamicCol.ReadOnly)
{
IsProtect = true;
}
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置单元格样式及数据类型
dataStyle.IsLocked = excelPropety.ReadOnly;
dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
else
{
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置是否锁定
dataStyle.IsLocked = excelPropety.ReadOnly;
//设置单元格样式及数据类型
excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
#endregion
#region 添加模版数据
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
IRow dataRow = sheet.CreateRow(1 + i);
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
string colName = propetys[porpetyIndex].Name;
tableRow[colName].ToString();
dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
}
}
}
#endregion
//冻结行
sheet.CreateFreezePane(0, 1, 0, 1);
//锁定excel
if (IsProtect)
{
sheet.ProtectSheet("password");
}
//隐藏前2个Sheet
workbook.SetSheetHidden(1, SheetState.Hidden);
workbook.SetSheetHidden(2, SheetState.Hidden);
//返回byte数组
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}