/// <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());
}
