在导出excel格式的数据时,需要设置某些单元格的背景颜色,比如标题列等
实现代码
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
namespace NPOI.Excel
{
public class ExcelHelper
{
//导出用户信息
public void ExportExcel(DataTable dtUser,out byte[]bytes)
{
try
{
/* Excel表格创建*/
HSSFWorkbook hssfworkbook = new HSSFWorkbook();//初始化文件
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");//创建Sheet
//单元格字体样式 默认字体样式
IFont font = hssfworkbook.CreateFont();
font.FontHeight = 12 * 20;
//单元格字体样式 白色(标题列使用)
IFont fontWhite = hssfworkbook.CreateFont();
fontWhite.FontHeight = 12 * 20;
fontWhite.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
//单元格样式 默认
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment= VerticalAlignment.Center;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.SetFont(font);
//单元格样式 红色背景(标题列使用)
ICellStyle cellStyleRed = hssfworkbook.CreateCellStyle();
cellStyleRed.Alignment = HorizontalAlignment.Center;
cellStyleRed.VerticalAlignment = VerticalAlignment.Center;
cellStyleRed.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleRed.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleRed.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleRed.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//单元格背景颜色设置 FillForegroundColor与FillPattern 都要设置
cellStyleRed.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
cellStyleRed.FillPattern = FillPattern.SolidForeground;
cellStyleRed.SetFont(fontWhite);
IRow firstRow = sheet1.CreateRow(0);//创建第一行 标题列
//标题列创建
ICell cellUserName = firstRow.CreateCell(0);
cellUserName.SetCellValue("用户姓名");
cellUserName.CellStyle = cellStyleRed;
//联系电话
ICell cellTel= firstRow.CreateCell(1);
cellTel.SetCellValue("联系电话");
cellTel.CellStyle = cellStyleRed;
//性别
ICell cellSex = firstRow.CreateCell(2);
cellSex.SetCellValue("性别");
cellSex.CellStyle = cellStyleRed;
//设置列宽
for (int n = 0; n < 3; n++)
{
sheet1.SetColumnWidth(n, 10000);
}
//数据解析
int rowIndex=1;
foreach(DataRow dr in dtUser.Rows)
{
IRow row = sheet1.CreateRow(rowIndex);
//第一列 用户姓名
ICell cellItemUserName = row.CreateCell(0);
cellItemUserName.SetCellValue(dr["UserName"]);
cellItemUserName.CellStyle = cellStyle;
//第二列 联系电话
ICell cellItemTel = row.CreateCell(1);
cellItemTel.SetCellValue(dr["UserTel"]);
cellItemTel.CellStyle = cellStyle;
//第三列 性别
ICell cellItemSex = row.CreateCell(2);
cellItemSex.SetCellValue(dr["UserSex"]);
cellItemSex.CellStyle = cellStyle;
rowIndex++;
}
var stream = new System.IO.MemoryStream();
hssfworkbook.Write(stream);
bytes = stream.ToArray();
}
catch(Exception ex)
{
bytes = null;
}
}
}
}
设置单元格背景的最主要的代码是设置单元格样式时的代码
//单元格样式 红色背景(标题列使用)
ICellStyle cellStyleRed = hssfworkbook.CreateCellStyle();
cellStyleRed.Alignment = HorizontalAlignment.Center;
cellStyleRed.VerticalAlignment = VerticalAlignment.Center;
cellStyleRed.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleRed.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleRed.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleRed.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//单元格背景颜色设置 FillForegroundColor与FillPattern 都要设置
cellStyleRed.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index;
cellStyleRed.FillPattern = FillPattern.SolidForeground;
cellStyleRed.SetFont(fontWhite);
所有颜色到HSSFColor中查看