NPOI 扩展库 - 入门指南

NPOI 是一个开源的 .NET 组件，可在不依赖 Microsoft Office 的环境下操作 Excel，Word 等文件。aardio 的 NPOI 扩展库默认内存加载 NPOI 的所有依赖程序集，可生成独立 EXE 文件。

参考链接： 🅰 aardio 文档 - .NET 调用指南 📄 NPOI 文档增强检索

本文档根据 NPOI 官方文档范例翻译为 aardio 版本，适用于 aardio v40.7 以上，NPOI 扩展库 v2.7.3 以上版本。

如何开始使用 NPOI

在 aardio 中打开 工具 » 扩展库 搜索并勾选 `NPOI 扩展库，
点击 安装 / 更新 按钮。

✅ 在 aardio 中直接运行包含 import NPOI 语句的代码也会自动安装扩展库。

Excel 基础概念与术语说明

本教程包含一些专业术语，我们先来明确这些概念：

电子表格（Spreadsheet）：整个 Excel 文档。
工作表（Sheet）：电子表格中的单个页面，显示在表格左下角的标签页。
行（Row）：工作表中的水平数据集合。
单元格（Cell）：行中的数据单元。单元格可以包含静态值（文本、数字、日期等）或计算公式。

使用 NPOI 创建第一个 Excel 表格

NPOI 的类和接口模拟了 Excel 表格的组件。例如：

Workbook 接口定义了电子表格的必要属性和方法
Sheet、Row 和 Cell 接口分别定义了工作表、行和单元格的属性和方法

创建 Excel 表格分为两个主要步骤：

构建表格模型：创建必要的对象并设置其属性
将 NPOI 表示的表格转换为实际的 Excel 表格，可以保存到文件系统或直接发送到客户端

让我们通过创建一个简单的用户账户表格来说明这个过程。这个表格列出了系统中每个用户的用户名、邮箱、加入日期、最后登录时间、是否批准和备注信息。

1. 导入必要的命名空间

import NPOI;

2. 创建工作簿和工作表

NPOI 的 HSSFWorkbook 类模拟 Excel 表格，我们首先创建一个工作簿对象，然后添加一个名为"User Accounts"的工作表。

// 创建 XLS 格式工作簿（Excel 97-2003）
var workbook = NPOI.HSSF.UserModel.HSSFWorkbook();

// 创建 XLSX 格式工作簿（Excel 2007+）
var workbook = NPOI.XSSF.UserModel.XSSFWorkbook();

// 创建名为 "Sheet1" 的工作表
var sheet = workbook.CreateSheet("Sheet1");

// 获取或创建工作表（如果不存在则创建）
var sheet = workbook.GetSheet("Sheet1") || workbook.CreateSheet("Sheet1");

// 通过索引删除删除工作表
workbook.RemoveSheetAt(0);

// 通过名称删除
var sheetIndex = workbook.GetSheetIndex("Sheet1");
if(sheetIndex >= 0) {
    workbook.RemoveSheetAt(sheetIndex);
}

3. 从文件加载工作簿

// 方法1：直接通过文件路径打开
var workbook = NPOI.XSSF.UserModel.XSSFWorkbook("/path/to/file.xlsx");

// 方法2：通过文件流打开
var fs = System.IO.FileStream("/path/to/file.xlsx", "r+");
var workbook = NPOI.XSSF.UserModel.XSSFWorkbook(fs);
fs.Close();

// 方法3：自动检测格式打开
var workbook = NPOI.SS.UserModel.WorkbookFactory.Create("/path/to/file");

// 方法4：使用简化写法
var workbook = NPOI.Workbook.Create("/path/to/file");  // Workbook是WorkbookFactory的别名

4. 创建标题行

标题行标记了每列显示的数据类型（用户名、邮箱等）。我们使用 CreateRow 方法添加行，CreateCell 方法添加单元格，SetCellValue 方法设置单元格值。

// 添加标题标签
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("用户名");
row.CreateCell(1).SetCellValue("邮箱");
row.CreateCell(2).SetCellValue("加入时间");
row.CreateCell(3).SetCellValue("最后登录");
row.CreateCell(4).SetCellValue("是否批准");
row.CreateCell(5).SetCellValue("备注");
rowIndex++;

注意：CreateRow 和 CreateCell 的索引从0开始。第一行（Excel中显示为行1）对应 CreateRow(0)。

5. 添加数据行

遍历用户账户集合，为每个用户添加一行数据：

//模拟用户数据
var userAccounts = [
    {
        userName = "张三",
        email = "test@example.com",
        creationDate = time(),
        lastLoginDate = time(),
        isApproved = true,
        comment = "很好"
    }
]

// 添加数据行
for idx,account in userAccounts {
    row = sheet.CreateRow(rowIndex);
    row.CreateCell(0).SetCellValue(account.userName);
    row.CreateCell(1).SetCellValue(account.email);
    row.CreateCell(2).SetCellValue(tostring(account.creationDate));
    row.CreateCell(3).SetCellValue(tostring(account.lastLoginDate));
    row.CreateCell(4).SetCellValue(account.isApproved);
    row.CreateCell(5).SetCellValue(account.comment);
    rowIndex++;
}

6. 保存表格

现在我们需要决定如何处理这个内存中的表格表示。我们可以将其保存到服务器文件系统或直接发送给访问者。

保存到文件

// 将Excel表格保存到服务器文件系统 
workbook.Write("/test.xlsx");

发送到 HTTP 客户端

如果在 HTTP 服务器上运行 aardio 代码并且要将 Excel 表格发送到客户端浏览器，我们需要：

告诉浏览器我们发送的是 Microsoft Excel 表格（而不是 HTML 文档）
指示浏览器将其作为附件处理，打开"查看/另存为"对话框
将表格内容发送到客户端响应中

// 将 Excel 表格保存到 MemoryStream 并返回给客户端
var exportData = System.IO.MemoryStream()
workbook.Write(exportData);

//重置流的位置到开头
exportData.Seek(0, System.IO.SeekOrigin.Begin);

//读取全部数据到字节数组
var buffer = exportData.GetBuffer()

// 设置HTTP响应头
response.contentType = "application/vnd.ms-excel"
response.headers["Content-Disposition"] = time().format("attachment;filename=MembershipExport-%Y-%m-%d.xls")

多工作表、样式和公式应用

前面的会员报告表格比较简单，只有一个工作表，没有格式化和公式。让我们看一个更有趣的例子。

下载的演示包含来自Northwind数据库的销售数据报告。用户可以选择感兴趣的年份，生成的Excel表格包含两个工作表：

Summary：列出全年所有产品的总销售额，并按产品细分
Details：列出该年度的每一笔销售记录，按产品分组

这个表格还包含多种样式设置：

对齐方式
边框
格式化
字体粗细

以及公式应用。例如图1中第20行的文本加粗并有上下边框，D20、E20和F20单元格使用Excel的SUM公式计算各列数据，D和F列的数字格式化为货币值。

创建和使用单元格样式

为单元格应用样式需要以下步骤：

创建实现 CellStyle 接口的对象
设置对象的各种属性
将对象赋给单元格的 CellStyle 属性

创建小计行样式

// 创建样式对象
var detailSubtotalCellStyle = workbook.CreateCellStyle();

// 定义单元格上下边框
detailSubtotalCellStyle.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;

// 创建字体对象并设置为粗体
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = NPOI.SS.UserModel.FontBoldWeight.Bold;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);

// 为小计行添加单元格并应用样式
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("总计:");
cell.CellStyle = detailSubtotalCellStyle;
cell = row.CreateCell(1);
cell.CellStyle = detailSubtotalCellStyle;

重要提示：每种样式只需创建一次，然后可以多次应用。不要为每个单元格创建相同的新样式对象，因为Excel对支持的样式数量有限制。

创建货币格式样式

// 创建样式对象
var currencyCellStyle = workbook.CreateCellStyle();
// 货币值右对齐
currencyCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
// 获取/创建数据格式字符串
var formatId = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1) {
    var newDataFormat = workbook.CreateDataFormat();
    currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
} else {
    currencyCellStyle.DataFormat = formatId;
}

使用公式计算单元格值

前面的例子使用 SetCellValue 方法为单元格赋值。但Excel的强大之处在于可以使用公式自动计算值。

要为单元格设置公式：

使用 SetCellType 方法将单元格类型设为 FORMULA
通过 CellFormula 属性指定公式

var startRowIndexForProductDetails = 1;

var cell = row.CreateCell(3);
cell.SetCellType(NPOI.SS.UserModel.CellType.Formula);
cell.CellFormula = string.format("SUM(D%d:D%d)", 
    startRowIndexForProductDetails + 1, rowIndex);

这样当用户手动修改D、E或F列中的详细值时，小计值会自动更新。

Markdown 格式