NPOI 操作Excel的基本使用

首先说名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;
    }