NPOI 整理

公共方法

using Microsoft.EntityFrameworkCore.Storage;

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace NpoiDemo.Common
{
    using S = String;
    public class NpoiHelper
    {
        //code here
    }
}

CreateBasicBook

/// <summary>
/// 创建book的基础方法
/// </summary>
public static void CreateBasicBook()
{
    //2003格式
    var book = new HSSFWorkbook();
    var sheet = book.CreateSheet("book");
    var row = sheet.CreateRow(0);
    var cell = row.CreateCell(0);
    var cellStyle = book.CreateCellStyle();
    var font = book.CreateFont();
}

SetBorderStyle

/// <summary>
/// 设置单元格边框线,默认Thin
/// </summary>
/// <param name="cellStyle"></param>
public static void SetBorderStyle(ICellStyle cellStyle)
{
    cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
}

/// <summary>
/// 设置单元格边框线,需要传入borderStyle
/// </summary>
/// <param name="cellStyle"></param>
public static void SetBorderStyle(ICellStyle cellStyle, BorderStyle border)
{
    cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = border;
}

SetBackground

/// <summary>
/// 设置前景色和背景色
/// </summary>
/// <param name="cellStyle"></param>
/// <param name="foreColor">HSSFColor.Black.Index</param>
/// <param name="bgColor"></param>
public static void SetBackground(ICellStyle cellStyle, short foreColor, short bgColor)
{
    cellStyle.FillBackgroundColor = foreColor;
    cellStyle.FillForegroundColor = bgColor;
    cellStyle.FillPattern = FillPattern.SolidForeground;
}

GetColourByRGB

/// <summary>
/// 通过RGB获取颜色索引
/// </summary>
/// <param name="workbook"></param>
/// <param name="r"></param>
/// <param name="g"></param>
/// <param name="b"></param>
/// <returns></returns>
public static short GetColourByRGB(HSSFWorkbook workbook, byte r, byte g, byte b)
{
    var palette = workbook.GetCustomPalette();
    var hssfColor = palette.FindColor(r, g, b);
    if (hssfColor == null)
    {
        palette.SetColorAtIndex(HSSFColor.Lavender.Index, r, g, b);
        hssfColor = palette.GetColor(HSSFColor.Lavender.Index);
    }
    if (hssfColor != null)
    {
        return hssfColor.Indexed;
    }
    else
    {
        return short.MinValue;
    }
}

SetFont

/// <summary>
/// 设置字体
/// </summary>
/// <param name="workbook">book对象</param>
/// <param name="cellStyle">单元格对象</param>
/// <param name="fontName">字体名称</param>
/// <param name="size">字体大小</param>
/// <param name="isBold">加粗</param>
/// <param name="isItalic">加谢</param>
/// <param name="underlineType">下划线</param>
public static void SetFont(HSSFWorkbook workbook, ICellStyle cellStyle, S fontName = "等线",
    double size = 10, bool isBold = false, bool isItalic = false,
    FontUnderlineType underlineType = FontUnderlineType.None)
{
    var font = workbook.CreateFont();
    font.FontName = fontName;
    font.FontHeightInPoints = size;
    font.IsBold = isBold;
    font.IsItalic = isItalic;
    font.Underline = underlineType;
    cellStyle.SetFont(font);
}

SetAutoSizeColumn

/// <summary>
/// 自适应列宽
/// </summary>
/// <param name="sheet">表格对象</param>
/// <param name="totalCols">总共的列数</param>
public static void SetAutoSizeColumn(ISheet sheet, int totalCols)
{
    Enumerable.Range(0, totalCols).ToList().ForEach(x =>
    {
        sheet.AutoSizeColumn(x);
    });
}

UnionCells

/// <summary>
/// 指定区域合并单元格,都是闭区间，索引0开始
/// </summary>
/// <param name="sheet">表格对象</param>
/// <param name="firstRowIndex">开始行</param>
/// <param name="lastRowIndex">结束行</param>
/// <param name="firstColIndex">开始列</param>
/// <param name="lastColIndex">结束列</param>
public static void UnionCells(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex)
{
    sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex));
}

SetAutoFilter

/// <summary>
/// 数据自动筛选
/// </summary>
/// <param name="sheet">表格对象</param>
/// <param name="reference">单元格的引用字符串表达方式</param>
public static void SetAutoFilter(ISheet sheet, S reference)
{
    sheet.SetAutoFilter(CellRangeAddress.ValueOf(reference));
}

SetRowHeight

/// <summary>
/// 设置行高
/// </summary>
/// <param name="row">Irow对象</param>
/// <param name="height">高度</param>
public static void SetRowHeight(IRow row, float height)
{
    row.HeightInPoints = height;
}

SetAlignment

/// <summary>
/// 设置单元格的水平和垂直对齐方式
/// </summary>
/// <param name="cell"></param>
/// <param name="horizontal"></param>
/// <param name="vertical"></param>
public static void SetAlignment(ICellStyle cell, HorizontalAlignment horizontal, VerticalAlignment vertical)
{
    cell.Alignment = horizontal;
    cell.VerticalAlignment = vertical;
}

SetFormula

/// <summary>
/// 公式设置
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="formula">excel的计算公式就行</param>
public static void SetFormula(ICell cell, S formula)
{
    //$"SUM(A1,A3)"
    cell.SetCellFormula(formula);
}

SetColumnWidth

/// <summary>
/// 设置列宽，列宽按照字符进行计算的所以有个固定字符256
/// </summary>
/// <param name="sheet"></param>
/// <param name="colIndex"></param>
/// <param name="colwidthChar"></param>
public static void SetColumnWidth(ISheet sheet, int colIndex, int colwidthChar)
{
    sheet.SetColumnWidth(colIndex, colwidthChar * 256);
}

完整代码

using Microsoft.EntityFrameworkCore.Storage;

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace NpoiDemo.Common
{
    using S = String;
    public class NpoiHelper
    {
        /// <summary>
        /// 创建book的基础方法
        /// </summary>
        public static void CreateBasicBook()
        {
            //2003格式
            var book = new HSSFWorkbook();
            var sheet = book.CreateSheet("book");
            var row = sheet.CreateRow(0);
            var cell = row.CreateCell(0);
            var cellStyle = book.CreateCellStyle();
            var font = book.CreateFont();
        }

        /// <summary>
        /// 设置单元格边框线,需要传入borderStyle
        /// </summary>
        /// <param name="cellStyle"></param>
        public static void SetBorderStyle(ICellStyle cellStyle, BorderStyle border)
        {
            cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = border;
        }
        /// <summary>
        /// 设置单元格边框线,默认Thin
        /// </summary>
        /// <param name="cellStyle"></param>
        public static void SetBorderStyle(ICellStyle cellStyle)
        {
            cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
        }
        /// <summary>
        /// 设置前景色和背景色
        /// </summary>
        /// <param name="cellStyle"></param>
        /// <param name="foreColor">HSSFColor.Black.Index</param>
        /// <param name="bgColor"></param>
        public static void SetBackground(ICellStyle cellStyle, short foreColor, short bgColor)
        {
            cellStyle.FillBackgroundColor = foreColor;
            cellStyle.FillForegroundColor = bgColor;
            cellStyle.FillPattern = FillPattern.SolidForeground;
        }
        /// <summary>
        /// 通过RGB获取颜色索引
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="r"></param>
        /// <param name="g"></param>
        /// <param name="b"></param>
        /// <returns></returns>
        public static short GetColourByRGB(HSSFWorkbook workbook, byte r, byte g, byte b)
        {
            var palette = workbook.GetCustomPalette();
            var hssfColor = palette.FindColor(r, g, b);
            if (hssfColor == null)
            {
                palette.SetColorAtIndex(HSSFColor.Lavender.Index, r, g, b);
                hssfColor = palette.GetColor(HSSFColor.Lavender.Index);
            }
            if (hssfColor != null)
            {
                return hssfColor.Indexed;
            }
            else
            {
                return short.MinValue;
            }
        }

        /// <summary>
        /// 设置字体
        /// </summary>
        /// <param name="workbook">book对象</param>
        /// <param name="cellStyle">单元格对象</param>
        /// <param name="fontName">字体名称</param>
        /// <param name="size">字体大小</param>
        /// <param name="isBold">加粗</param>
        /// <param name="isItalic">加谢</param>
        /// <param name="underlineType">下划线</param>
        public static void SetFont(HSSFWorkbook workbook, ICellStyle cellStyle, S fontName = "等线",
            double size = 10, bool isBold = false, bool isItalic = false,
            FontUnderlineType underlineType = FontUnderlineType.None)
        {
            var font = workbook.CreateFont();
            font.FontName = fontName;
            font.FontHeightInPoints = size;
            font.IsBold = isBold;
            font.IsItalic = isItalic;
            font.Underline = underlineType;
            cellStyle.SetFont(font);
        }

        /// <summary>
        /// 自适应列宽
        /// </summary>
        /// <param name="sheet">表格对象</param>
        /// <param name="totalCols">总共的列数</param>
        public static void SetAutoSizeColumn(ISheet sheet, int totalCols)
        {
            Enumerable.Range(0, totalCols).ToList().ForEach(x =>
            {
                sheet.AutoSizeColumn(x);
            });
        }

        /// <summary>
        /// 指定区域合并单元格,都是闭区间，索引0开始
        /// </summary>
        /// <param name="sheet">表格对象</param>
        /// <param name="firstRowIndex">开始行</param>
        /// <param name="lastRowIndex">结束行</param>
        /// <param name="firstColIndex">开始列</param>
        /// <param name="lastColIndex">结束列</param>
        public static void UnionCells(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex)
        {
            sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex));
        }

        /// <summary>
        /// 数据自动筛选
        /// </summary>
        /// <param name="sheet">表格对象</param>
        /// <param name="reference">单元格的引用字符串表达方式</param>
        public static void SetAutoFilter(ISheet sheet, S reference)
        {
            sheet.SetAutoFilter(CellRangeAddress.ValueOf(reference));
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="row">Irow对象</param>
        /// <param name="height">高度</param>
        public static void SetRowHeight(IRow row, float height)
        {
            row.HeightInPoints = height;
        }
        /// <summary>
        /// 设置单元格的水平和垂直对齐方式
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="horizontal"></param>
        /// <param name="vertical"></param>
        public static void SetAlignment(ICellStyle cell, HorizontalAlignment horizontal, VerticalAlignment vertical)
        {
            cell.Alignment = horizontal;
            cell.VerticalAlignment = vertical;
        }

        /// <summary>
        /// 公式设置
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="formula">excel的计算公式就行</param>
        public static void SetFormula(ICell cell, S formula)
        {
            //$"SUM(A1,A3)"
            cell.SetCellFormula(formula);
        }
        /// <summary>
        /// 设置列宽，列宽按照字符进行计算的所以有个固定字符256
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="colIndex"></param>
        /// <param name="colwidthChar"></param>
        public static void SetColumnWidth(ISheet sheet, int colIndex, int colwidthChar)
        {
            sheet.SetColumnWidth(colIndex, colwidthChar * 256);
        }
    }
}