首先说名NPOI的版本:2.2.1.0
创建Excel对象
HSSFWork workbook = new HSSFWorkbook()
创建工作簿
HSSFSheet sheet = workbook.CreateSheet("Sheet1");
初始化样式
HSSFCellStyle cellStyle = workbook.CreateCellStyle();
设置样式
cellStyle.Alignment = horizontalAlignment;//水平对齐
cellStyle.VerticalAlignment = verticalAlignment;//垂直对齐
cellStyle.BorderTop = BorderStyle.Thin;//设置边框
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.TopBorderColor = IndexedColors.Black.Index;//设置边框颜色
cellStyle.RightBorderColor = IndexedColors.Black.Index;
cellStyle.BottomBorderColor = IndexedColors.Black.Index;
cellStyle.LeftBorderColor = IndexedColors.Black.Index;
设置字体
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 20;
font.FontName = "黑体";
cellStyle.SetFont(font);
设置字体普通颜色
font.Color = HSSFColor.Red.Indexed;
设置字体RGB颜色
int[] rgb = new int[3];
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
palette.SetColorAtIndex((short)(8 + index), (byte)rgb[0], (byte)rgb[1], (byte)rgb[2]);
HSSFColor hssfColor = palette.FindColor((byte)rgb[0], (byte)rgb[1], (byte)rgb[2]);
font.Color = hssfColor.Indexed;
设置普通背景色
style.FillPattern = CellFillPattern.SOLID_FOREGROUND;
style.FillBackgroundColor = HSSFColor.BLUE.Indexed;
设置RGB背景色
HSSFPalette palette = workbookAll.GetCustomPalette();
palette.SetColorAtIndex((short)8, 179, 179, 179);//RGB颜色值,第一个值:8~64之间(不同颜色间,需要取不同的值),后面三个值为RGB色值
HSSFColor hSSFColor=palette.FindColor(179,179,179);
style.FillForegroundColor = hSSFColor.Indexed;
创建行
HSSFRow row = sheet.CreateRow(0);//行从0开始
row.Height = 25*20;//设置行高
应用样式到单元格
HSSFCell cell = row.CreateCell(0);//列从0开始
cell.CellStyle = cellStyle;
文字多种颜色设置
HSSFFont fontRed = workbook.CreateFont();
fontRed.Color = HSSFColor.Red.Indexed;//也可以使用上面自定义的颜色hSSFColor.Indexed;
HSSFFont fontBlue = workbook.CreateFont();
fontBlue.Color = HSSFColor.Blue.Indexed;
HSSFRichTextString tx = new HSSFRichTextString("red,blue");
tx.ApplyFont(0, 2, fontRed);
tx.ApplyFont(4, 7, fontBlue);
cell.SetCellValue(tx);
16进制转RGB
public static int[] ColorHx16toRGB(string str)//0xff0000
{
int[] rgb = new int[3];
try
{
rgb[0] = Convert.ToInt32("0x" + str.Substring(1, 2), 16);
rgb[1] = Convert.ToInt32("0x" + str.Substring(3, 2), 16);
rgb[2] = Convert.ToInt32("0x" + str.Substring(5, 2), 16);
}
catch (Exception ex)
{
rgb[0] = 255;
rgb[1] = 255;
rgb[2] = 255;
}
return rgb;
}