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();
}
}
