/// <summary>
/// 生成Excel
/// </summary>
/// <returns>生成的Excel文件</returns>
public virtual byte[] GenerateExcel()
{
NeedPage = false;
if (GridHeaders == null)
{
GetHeaders();
}
if (IsSearched == false)
{
DoSearch();
}
HSSFWorkbook workbook = new HSSFWorkbook();
List<HSSFSheet> sheets = new List<NPOI.HSSF.UserModel.HSSFSheet>();
//准备好sheet，每6万行生成一个sheet
var sheetno = ((EntityList.Count - 1) / 60000) + 1;
var headerStyle = workbook.CreateCellStyle();
//设定单元格边框
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
//用灰色填充背景
var headerbg = HSSFColor.Grey25Percent.Index;
headerStyle.FillForegroundColor = headerbg;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.FillBackgroundColor = headerbg;
//去掉 Id 列和动作列
RemoveActionAndIdColumn();
//循环生成所有sheet，并为每个sheet添加表头
var headerrows = 0;
for (int i = 1; i <= sheetno; i++)
{
HSSFSheet sheet = workbook.CreateSheet("Sheet" + i) as HSSFSheet;
//生成表头
headerrows = MakeExcelHeader(sheet, GridHeaders, 0, 0, headerStyle);
sheets.Add(sheet);
}
var rowIndex = headerrows;
var colIndex = 0;
//Excel中用到的style，每种前景色和背景色的组合为一个style。Nopi值支持4000个style，所以同样的style要重复使用
Dictionary<string, ICellStyle> styles = new Dictionary<string, ICellStyle>();
//Excel中用到的font，主要是为了更改字体颜色
Dictionary<string, IFont> fonts = new Dictionary<string, IFont>();
//循环数据
foreach (var row in EntityList)
{
var sheetindex = ((rowIndex - headerrows) / 60000);
colIndex = 0;
string bgColor = "";
string fColor = "";
//获取设定的行背景色
bgColor = SetFullRowBgColor(row);
//获取设定的行前景色
fColor = SetFullRowColor(row);
var dr = sheets[sheetindex].CreateRow(rowIndex - sheetindex * 60000) as HSSFRow;
foreach (var baseCol in GridHeaders)
{
//处理枚举变量的多语言
bool IsEmunBoolParp = false;
var proType = baseCol.FieldType;
if (proType.IsEnumOrNullableEnum())
{
IsEmunBoolParp = true;
}
foreach (var col in baseCol.BottomChildren)
{
//获取数据，并过滤特殊字符
string text = Regex.Replace(col.GetText(row).ToString(), @"<[^>]*>", String.Empty);
//处理枚举变量的多语言
if (IsEmunBoolParp)
{
if (int.TryParse(text, out int enumvalue))
{
text = PropertyHelper.GetEnumDisplayName(proType, enumvalue);
}
}
//建立excel单元格
var cell = dr.CreateCell(colIndex);
ICellStyle style = null;
IFont font = null;
var styleKey = string.Empty;
//获取设定的单元格背景色
string backColor = col.GetBackGroundColor(row);
//获取设定的单元格前景色
string foreColor = col.GetForeGroundColor(row);
//如果行背景色或单元格背景色有值，则用颜色的ARGB的值作为style的key
if (bgColor != "" || backColor != "")
{
styleKey = backColor == "" ? bgColor : backColor;
}
//如果行前景色或单元格前景色有值，则用颜色的ARGB加上背景色的ARGB作为style的key
if (fColor != "" || foreColor != "")
{
styleKey += foreColor == "" ? foreColor : fColor;
}
//如果已经有符合条件的style，则使用
if (styles.ContainsKey(styleKey))
{
style = styles[styleKey];
}
//如果没有，则新建一个style
else
{
var newKey = "";
var newFontKey = "";
//新建style
style = workbook.CreateCellStyle();
//设定单元格边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//如果行前景色或单元格前景色有值，则设定单元格的填充颜色
if (bgColor != "" || backColor != "")
{
newKey = backColor == "" ? bgColor : backColor;
var ci = Utils.GetExcelColor(backColor == "" ? bgColor : backColor);
style.FillForegroundColor = ci;
style.FillPattern = FillPattern.SolidForeground;
style.FillBackgroundColor = ci;
}
//如果行前景色或单元格前景色有值，则设定单元格的字体的颜色
if (fColor != "" || foreColor != "")
{
newFontKey = foreColor == "" ? foreColor : fColor;
newKey += foreColor == "" ? foreColor : fColor;
//如果已经有符合条件的字体，则使用
if (fonts.ContainsKey(newFontKey))
{
font = fonts[newFontKey];
}
//如果没有，则新建
else
{
//新建字体
font = workbook.CreateFont();
//设定字体颜色
font.Color = Utils.GetExcelColor(foreColor == "" ? fColor : foreColor);
//向集合中添加新字体
fonts.Add(newFontKey, font);
}
//设定style中的字体
style.SetFont(font);
}
//将新建的style添加到集合中
styles.Add(newKey, style);
}
cell.CellStyle = style;
cell.SetCellValue(text);
colIndex++;
}
}
rowIndex++;
}
//获取Excel文件的二进制数据
byte[] rv = new byte[] { };
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
rv = ms.ToArray();
}
return rv;
}
