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 = https://www.cnblogs.com/hippieZhou/archive/2021/11/11/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();
     }
 }

標籤：.NET技术

上一篇：10分鐘簡單學習net core集成jwt權限認證，快速接入專案落地使用

下一篇：記一次WPF應用在用戶電腦無法啟動