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; } } } } } } }