private static ICellStyle SetRowCellStyle(IWorkbook book, ICellStyle cellStyle, ExportStyleModel styleModel)
{
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.Alignment = HorizontalAlignment.LEFT;
if (!string.IsNullOrEmpty(styleModel.Alignment))
{
if (styleModel.Alignment.Equals("center"))
{
cellStyle.Alignment = HorizontalAlignment.CENTER;
}
else if (styleModel.Alignment.Equals("left"))
{
cellStyle.Alignment = HorizontalAlignment.LEFT;
}
else
{
cellStyle.Alignment = HorizontalAlignment.RIGHT;
}
}
cellStyle.WrapText = true;//换行
//必须设置单元格背景色 FillForegroundColor 和 FillPattern 的值才能正确显示背景色
if (styleModel.FillForegroundColor >= 1)
{
if (styleModel.FillForegroundColor == 1)
{
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; //(short)白色
}
if (styleModel.FillForegroundColor == 2)
{
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.RED.index; //(short)红色
}
if (styleModel.FillForegroundColor == 3)
{
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; //(short)黑色
}
if (styleModel.FillForegroundColor == 4)
{
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; //(short)浅蓝色
}
if (styleModel.FillForegroundColor == 5)
{
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index; //(short)黄色
}
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; // CellStyle.SOLID_FOREGROUND
}
if (styleModel.borderWidth > 0)
{
if (styleModel.borderColor == 1)
{
cellStyle.BorderLeft = BorderStyle.THIN;
cellStyle.BorderRight = BorderStyle.THIN;
cellStyle.BorderBottom = BorderStyle.THIN;
cellStyle.BorderTop = BorderStyle.THIN;
}
}
if (styleModel.Indention > 0)
{
cellStyle.Indention = styleModel.Indention;
}
//四、设置字体:
IFont font = book.CreateFont();
if (styleModel.FontHeightInPoints <= 0)
{
styleModel.FontHeightInPoints = (short)11.5;
}
font.FontHeightInPoints = styleModel.FontHeightInPoints;//.SetFontHeightInPoints((short)16);//设置字体大小
if (styleModel.Color == 1)
{
font.Color = NPOI.HSSF.Util.HSSFColor.RED.index;
}
else if (styleModel.Color == 2)
{
font.Color = NPOI.HSSF.Util.HSSFColor.BLUE.index;
}
else if (styleModel.Color == 3)
{
font.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
}
if (string.IsNullOrEmpty(styleModel.FontName))
{
font.FontName = "黑体";//.SetFontName("黑体");
}
else
{
font.FontName = styleModel.FontName;
}
cellStyle.SetFont(font);//选择需要用到的字体格式
return(cellStyle);
}
public static void CreadDataRows(DataTable dt, ref ISheet sheet, IWorkbook workbook, List <NpoiHeadCfg> heads = null, List <ExportStyleModel> styleModelList = null)
{
if (dt == null || dt.Rows.Count == 0)
{
return;
}
if (heads != null && heads.Count > 0)
{
List <NpoiHeadCfg> curhds = new List <NpoiHeadCfg>();
//遍历所有顶层节点
for (int x = 0; x < heads.Count; x++)
{
List <NpoiHeadCfg> hds = GetAllLeafNode(heads[x]);//获取所有叶子子节点
curhds.AddRange(hds);
}
//遍历所有数据行
for (int i = 0; i < dt.Rows.Count; i++)
{
var myStyleModelList = new ExportStyleModel[] { };
if (styleModelList != null && styleModelList.Count > 0)
{
myStyleModelList = styleModelList.Where(p => p.rowIndex == i).ToArray();
}
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);//创建空行
int colIndex = 0;
//遍历所有叶子子节点
for (var y = 0; y < curhds.Count; y++)
{
var ColumnName = curhds[y].FieldName;
if (y < dt.Columns.Count)
{
ColumnName = string.IsNullOrEmpty(ColumnName) ? dt.Columns[y].ColumnName : ColumnName;
}
if (dt.Columns.Contains(ColumnName))
{//数据源列是否含有配置列
ICell cell = row.CreateCell(colIndex);
if (dt.Rows[i][ColumnName] != DBNull.Value)
{
var strValue = dt.Rows[i][ColumnName].ToString();
SetCellValue(workbook, ColumnName, cell, strValue);
}
colIndex++;
if (myStyleModelList.Length > 0)
{
var myStyleModel = myStyleModelList.FirstOrDefault(p => p.cellIndex <= 0 && string.IsNullOrEmpty(p.cellName));
if (myStyleModel == null)
{
myStyleModel = myStyleModelList.FirstOrDefault(p => !string.IsNullOrEmpty(p.cellName) && p.cellName.Equals(ColumnName));
}
if (myStyleModel != null)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle = SetRowCellStyle(workbook, cellStyle, myStyleModel);
cell.CellStyle = cellStyle;
if (myStyleModel.rowHeight > 0)
{
row.Height = myStyleModel.rowHeight;
}
}
}
}
}
}
}
else
{
//填充内容
for (int i = 0; i < dt.Rows.Count; i++)
{
var myStyleModelList = new ExportStyleModel[] { };
if (styleModelList != null && styleModelList.Count > 0)
{
myStyleModelList = styleModelList.Where(p => p.rowIndex == i).ToArray();
}
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);//创建空行
for (int j = 0; j < dt.Columns.Count; j++)
{
string ColumnName = dt.Columns[j].ColumnName;
ICell cell = row.CreateCell(j);
if (dt.Rows[i][ColumnName] != DBNull.Value)
{
var strValue = dt.Rows[i][ColumnName].ToString();
SetCellValue(workbook, ColumnName, cell, strValue);
}
if (myStyleModelList.Length > 0)
{
var myStyleModel = myStyleModelList.FirstOrDefault(p => p.cellIndex <= 0 && string.IsNullOrEmpty(p.cellName));
if (myStyleModel == null)
{
myStyleModel = myStyleModelList.FirstOrDefault(p => !string.IsNullOrEmpty(p.cellName) && p.cellName.Equals(ColumnName));
}
if (myStyleModel != null)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle = SetRowCellStyle(workbook, cellStyle, myStyleModel);
cell.CellStyle = cellStyle;
if (myStyleModel.rowHeight > 0)
{
row.Height = myStyleModel.rowHeight;
}
}
}
}
}
}
}