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;
}
}
}
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)
{
//设置导出的文件名称
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(CoreProgram.Callerlocalizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(CoreProgram.Callerlocalizer?["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();
}
