NPOI 给导出Excel添加简单样式
需求分析：如下图为我之前导出的Excel数据，没有一点样式，标题行不明显，各个列的数据紧凑，查看数据时得手动拉宽每列，故这次要针对以上问题对它进行优化
结果展示：
代码：
/// <summary> /// 每日所有数据按每个总成一个工作簿导出到excel中 /// </summary> /// <param name="exportDataList">excel数据内容</param> /// <param name="fieldInfies">excel表头</param> /// <returns></returns> public static HSSFWorkbook createHSSFWorkbook_Day(Dictionary<string, List<T>> exportDataList, List<ExportFieldInfo> fieldInfies) { HSSFWorkbook book = new HSSFWorkbook(); try { if (exportDataList.Count != 0) { //这段代码为“有数据”情况下的正常处理 foreach (var items in exportDataList) { //创建一个名称为“items.Key”的工作簿 ISheet sheet = book.CreateSheet(items.Key); //创建2行 IRow rowTitle = sheet.CreateRow(0); IRow rowtemp; //设置表头行的行高为20个字符 rowTitle.Height = 20 * 20; //创建单元格样式 HSSFCellStyle CellStyle_Title = (HSSFCellStyle)book.CreateCellStyle(); //创建字体 HSSFFont font_Title = (HSSFFont)book.CreateFont(); font_Title.FontHeight = 14 * 20; font_Title.FontName = "宋体"; font_Title.Color = HSSFColor.Black.Index; font_Title.Boldweight = (short)FontBoldWeight.Bold; CellStyle_Title.SetFont(font_Title); //给工作簿添加表头,设置表头样式及列宽 for (int k = 0; k < fieldInfies.Count; k++) { rowTitle.CreateCell(k).SetCellValue(fieldInfies.ElementAt(k).DisplayName); //给每列设置样式 rowTitle.GetCell(k).CellStyle = CellStyle_Title; //给每列设置列宽 switch (k) { case 0: sheet.SetColumnWidth(0, 20 * 256); break; case 2: case 3: sheet.SetColumnWidth(k, 60 * 256); break; case 4: sheet.SetColumnWidth(4, 40 * 256); break; case 5: sheet.SetColumnWidth(5, 20 * 256); break; case 10: sheet.SetColumnWidth(10, 20 * 256); break; default: break; } } //添加数据 for (int j = 0; j < items.Value.Count; j++) { object tempobj = items.Value[j]; //获取类型 Type type = tempobj.GetType(); List<System.Reflection.PropertyInfo> ps = type.GetProperties().ToList(); //将数据写入sheet各个行 rowtemp = sheet.CreateRow(j + 1); for (int k = 0; k < fieldInfies.Count; k++) { //获取属性值 var value = ps.First(t => t.Name == fieldInfies.ElementAt(k).FieldName).GetValue(tempobj, null); switch (fieldInfies.ElementAt(k).DataType) { case DataTypeEnum.Int: rowtemp.CreateCell(k).SetCellValue(Convert.ToInt32(value)); break; case DataTypeEnum.Float: rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value)); break; case DataTypeEnum.Double: rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value)); break; case DataTypeEnum.String: rowtemp.CreateCell(k).SetCellValue(Convert.ToString(value)); break; case DataTypeEnum.DateTime: rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.SSS")); break; case DataTypeEnum.Date: rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd")); break; default: break; } } tempobj = null; ps = null; } } } else { //这段代码为“无数据”情况下的特殊处理 //创建一个名称为“Sheet1”的工作簿 ISheet sheet = book.CreateSheet("Sheet1"); //创建2行 IRow rowTitle = sheet.CreateRow(0); IRow rowtemp = sheet.CreateRow(1); //设置表头行的行高为20个字符 rowTitle.Height = 20 * 20; //给工作簿添加表头,设置表头样式及列宽 //创建单元格样式 HSSFCellStyle CellStyle_Title = (HSSFCellStyle)book.CreateCellStyle(); //创建字体 HSSFFont font_Title = (HSSFFont)book.CreateFont(); font_Title.FontHeight = 14 * 20; font_Title.FontName = "宋体"; font_Title.Color = HSSFColor.Black.Index; font_Title.Boldweight = (short)FontBoldWeight.Bold; CellStyle_Title.SetFont(font_Title); for (int k = 0; k < fieldInfies.Count; k++) { rowTitle.CreateCell(k).SetCellValue(fieldInfies.ElementAt(k).DisplayName); //给每列设置样式 rowTitle.GetCell(k).CellStyle = CellStyle_Title; //给每列设置列宽 switch (k) { case 0: sheet.SetColumnWidth(0, 20 * 256); break; case 2: case 3: sheet.SetColumnWidth(k, 60 * 256); break; case 4: sheet.SetColumnWidth(4, 40 * 256); break; case 5: sheet.SetColumnWidth(5, 20 * 256); break; case 10: sheet.SetColumnWidth(10, 20 * 256); break; default: break; } } //给第二行第一列的单元格赋值 rowtemp.CreateCell(0).SetCellValue("无数据！"); //创建单元格样式 HSSFCellStyle fCellStyle = (HSSFCellStyle)book.CreateCellStyle(); //创建字体 HSSFFont ffont = (HSSFFont)book.CreateFont(); //给字体设置颜色 ffont.Color = HSSFColor.Red.Index; //给样式添加颜色 fCellStyle.SetFont(ffont); //给第二行第一列单元格添加样式 rowtemp.GetCell(0).CellStyle = fCellStyle; } } catch(Exception ex) { Console.WriteLine(ex.Message); } return book; }
