NPOI 操作 Excel 从入门到放弃（以 .xlsx 为例）

PM> Install-Package NPOI -Version 2.5.5

基础操作

实例化 Workbook#

public static IWorkbook CreateWorkbook(string fileName, Stream ms = null)
{
    var fileType = Path.GetExtension(fileName);

    var isSuffixMatched = new Func<string, string, bool>((extension, suffix) =>
        string.Equals(extension, suffix, StringComparison.OrdinalIgnoreCase));

    if (isSuffixMatched(fileType, ".xls"))
    {
        return ms == null ? new HSSFWorkbook() : new HSSFWorkbook(ms);
    }

    if (isSuffixMatched(fileType, ".xlsx"))
    {
        return ms == null ? new XSSFWorkbook() : new XSSFWorkbook(ms);
    }

    throw new NotSupportedException("不支持的文件类型");
}

基于模板实例化 Workbook

using FileStream fs = File.OpenRead(fileName);
Workbook = WorkbookFactory.Create(stream);

基于 IFormFile 实例化 Workbook#

using var ms = new MemoryStream();
file.CopyTo(ms);
ms.Seek(0, SeekOrigin.Begin);
Workbook = CreateWorkbook(_fileName, ms);

# 保存为 FileContentResult
using var ms = new MemoryStream();
ms.Seek(0, SeekOrigin.Begin);
Workbook.Write(ms);
var bytes = ms.ToArray();
var file = new FileContentResult(bytes, "application/vnd.ms-excel") { FileDownloadName = _fileName };

Vue 下载 FileContentResult 类型的 Excel 文件#

 download(){
     var fileBytes = this.File.FileContents
     var bytes = window.atob(fileBytes)
     var n = bytes.length
     var u8arr = new Uint8Array(n)
     while (n--) {
     u8arr[n] = bytes.charCodeAt(n)
     }
     this.download(u8arr,this.FileName)
 },
 download(file, fileName) {
     const blob = new Blob([file])
     if (window.navigator.msSaveBlob) {
         // IE10+ 使用的下载方式
         return window.navigator.msSaveBlob(blob, fileName)
     }
     // 其他浏览器下载方式
     const reader = new FileReader()
     reader.readAsDataURL(blob)
     reader.onload = e => {
         const a = document.createElement('a')
         a.download = fileName
         a.href = e.target.result
         document.body.appendChild(a)
         a.click()
         document.body.removeChild(a)
     }
 }

进阶操作

宽度设置#

//自适应宽度
cell.Sheet.AutoSizeColumn(cell.ColumnIndex);
//以 100 个字符长度为宽度
cell.Sheet.SetColumnWidth(cell.ColumnIndex, 100 * 256);

样式设置#

样式总数有不能超过65535，建议使用字典方式来缓存所有样式列表；同一个单元格的样式只能由一个；如果修改了一个单元格对应的引用样式，则所有引用该样式的单元格都会生效

private static ICellStyle CreateBaseStyle(IWorkbook wb, Action<ICellStyle> styleAction,Action<IFont> fontAction)
{
    var style = wb.CreateCellStyle();

    #region 边框设置
    style.BorderTop = BorderStyle.Thin;
    style.BorderBottom = BorderStyle.Thin;
    style.BorderLeft = BorderStyle.Thin;
    style.BorderRight = BorderStyle.Thin;
    #endregion

    #region 背景色设置
    style.FillForegroundColor = HSSFColor.White.Index;
    style.FillPattern = FillPattern.SolidForeground;
    #endregion

    #region 布局设置
    style.Alignment = HorizontalAlignment.Left;
    style.VerticalAlignment = VerticalAlignment.Center;
    #endregion

    styleAction?.Invoke(style);

    #region 字体设置
    var font = wb.CreateFont();
    font.Color = HSSFColor.Black.Index;
    font.FontName = "微软雅黑";
    font.IsBold = false;
    font.FontHeightInPoints = 10;
    font.Underline = FontUnderlineType.None;
    fontAction?.Invoke(font);
    style.SetFont(font);
    #endregion

    return style;
}

自定义背景色#

var style = CreateBaseStyle(wb, sa =>
{
    try
    {
        sa.FillForegroundColor = 0;
        ((XSSFColor)sa.FillForegroundColorColor).SetRgb(new byte[] { 255, 199, 206 });
    }
    catch (Exception e)
    {
        Trace.WriteLine(e);
        sa.FillForegroundColor = HSSFColor.Rose.Index;
    }
}, fa => { });

设置超链接#

public static void SetHyperlink(ICell cell, string description, string address)
{
    var hy = cell.Sheet.Workbook.GetCreationHelper().CreateHyperlink(HyperlinkType.Document);
    hy.Address = address;
    cell.Hyperlink = hy;
    cell.SetCellValue(description);
}

设置下拉框#

支持对非法数据校验，但是不支持空数据校验

public static void SetDropDownList<TEnum>(ICell cell) where TEnum : struct, Enum
{
    var attributes = typeof(TEnum).GetMembers()
        .SelectMany(member =>
            member.GetCustomAttributes(typeof(DescriptionAttribute), true).Cast<DescriptionAttribute>())
        .Select(x => x.Description).ToArray();

    if (Enum.TryParse(cell.StringCellValue, out TEnum result))
    {
        cell.SetCellValue(result.GetDescription());
    }

    var dvHelper = cell.Sheet.GetDataValidationHelper();
    var constraint = dvHelper.CreateExplicitListConstraint(attributes);
    var addressList = new CellRangeAddressList(
        cell.RowIndex,
        65535,
        cell.ColumnIndex,
        cell.ColumnIndex);
    var dataValidation = dvHelper.CreateValidation(constraint, addressList);
    dataValidation.CreateErrorBox("错误", "请选择符合要求的数值");
    dataValidation.ShowErrorBox = true;
    cell.Sheet.AddValidationData(dataValidation);
}

合并单元格#

设置合并区域样式时，需要设置每个单元格；取合并区域对应的数据时，只需要取左上角第一个单元格的值即可

public static void AddMergedRegions(ISheet sheet,
    params (string cellValue, CellRangeAddress cellRangeAddress, ICellStyle style)[] regions)
{
    for (var i = 0; i < regions.Length; i++)
    {
        var (cellValue, cellRangeAddress, style) = regions[i];
        for (var rowIndex = cellRangeAddress.FirstRow;
            rowIndex <= cellRangeAddress.LastRow;
            rowIndex++)
        {
            var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
            for (var colIndex = cellRangeAddress.FirstColumn;
                colIndex <= cellRangeAddress.LastColumn;
                colIndex++)
            {
                var cell = row.GetCell(colIndex) ?? row.CreateCell(colIndex, CellType.String);
                if (cell.ColumnIndex == cellRangeAddress.FirstColumn &&
                    cell.RowIndex == cellRangeAddress.FirstRow)
                {
                    cell.SetCellValue(cellValue);
                }

                cell.CellStyle = style;
            }
        }

        sheet.AddMergedRegion(cellRangeAddress);
    }
}

创建批注#

批注数量不能超过65535，建议总量不要超过1000

public static void SetCellComment(ISheet sheet, int rowNum, int colNum, string message, ICellStyle cellStyle)
{
    var row = sheet.GetRow(rowNum) ?? sheet.CreateRow(rowNum);
    var cell = row.GetCell(colNum) ?? row.CreateCell(colNum);
    cell.RemoveCellComment();

    var creationHelper = sheet.Workbook.GetCreationHelper();
    //使用批注默认位置
    //var anchor = creationHelper.CreateClientAnchor();
    //指定批注位置
    var anchor = drawing.CreateAnchor(
      0,
      0,
      0,
      0,
      colNum + 1,
      rowNum + 1,
      colNum + 4,
      rowNum + 4);
    var drawing = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch();
    var comment = drawing.CreateCellComment(anchor);
    comment.String = creationHelper.CreateRichTextString(message);
    comment.Author = "hippieZhou";
    comment.Visible = false;
    comment.Address = cell.Address;
    cell.CellComment = comment;

    cell.CellStyle = cellStyle;
}

// 移除批注
 var sheet = workbook.GetSheetAt(i);
 var comments = sheet.GetCellComments();
 if (comments.Any())
 {
     //移除批注
     foreach (var cell in comments.Select(comment =>
         sheet.GetRow(comment.Key.Row).GetCell(comment.Key.Column)))
     {
         cell?.RemoveCellComment();
     }
 }