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(
下载地址
)      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();      }  }