精读《Excel JS API》

Excel 现在可利用 js 根据单元格数据生成图表、表格，或通过 js 拓展自定义函数拓展内置 Excel 表达式。

我们来学习一下 Excel js API 开放是如何设计的，从中学习到一些开放 API 设计经验。

API 文档：Excel JavaScript API overview

精读

Excel 将利用 JS API 开放了大量能力，包括用户能通过界面轻松做到的，也包括无法通过界面操作做到的。

为什么需要开放 JS API

Excel 已经具备了良好的易用性，以及 formula 这个强大的公式。在之前 精读《Microsoft Power Fx》 提到过，formula 就是 Excel 里的 Power FX，属于画布低代码语言，不过在 Excel 里叫做 “公式” 更合适。

已经具备这么多能力，为何还需要 JS API 呢？一句话概括就是，在 JS API 内可以使用 formula，即 JS API 是公式能力的超集，它包含了对 Excel 工作簿的增删改查、数据的限制、RangeAreas 操作、图表、透视表，甚至可以自定义 formula 函数。

也就是说，JS API 让 Excel “可编程化”，即以开发者视角对 Excel 进行二次拓展，包括对公式进行二次拓展，使 Excel 覆盖更多场景。

JS API 可以用在哪些地方

从 Excel 流程中最开始的工作薄、工作表环节，到最细节的单元格数据校验都可通过 JS API 支持，目前看来 Excel JS API 并没有设置能力边界，而且还会不断完善，将 Excel 全生命周期中一切可编程的地方开放出来。

首先是对工作薄、工作表的操作，以及对工作表用户操作的监听，或者对工作表进行只读设置。这一类 API 的目的是对 Excel 这个整体进行编程操作。

第二步就是对单元格级别进行操作，比如对单元格进行区域选中，获取选中区域，或者设置单元格属性、颜色，或者对单元格数据进行校验。自定义公式也在这个环节，因为单元格的值可以是公式，而公式可以利用 JS API 拓展。

最后一步是拓展行为，即在单元格基础上引入图表、透视表拓展。虽然这些功能在 UI 按钮上也可以操作出来，但 JS API 可以实现 UI 界面配置不出来的逻辑，对于非常复杂的逻辑行为，即便 UI 可以配置出来，可读性也远没有代码高。除了表格透视表外、还可以创建一些自定义形状，基本的几何图形、图片和 SVG 都支持。

JS API 设计

比较有趣的是，Excel 并没有抽象 “单元格” 对象，即便我们所有人都认为单元格就是 Excel 的代表。

这么做是出于 API 设计的合理性，因为 Excel 使用 Range 概念表示连续单元格。比如：

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getActiveWorksheet();

    var headers = [
      ["Product", "Quantity", "Unit Price", "Totals"]
    ];
    var headerRange = sheet.getRange("B2:E2");
    headerRange.values = headers;
    headerRange.format.fill.color = "#4472C4";
    headerRange.format.font.color = "white";

    return context.sync();
});

可以发现，Range 让 Excel 聚焦在批量单元格 API，即把单元格看做一个范围，整体 API 都可以围绕一个范围去设计。这种设计理念的好处是，把范围局限在单格单元格，就可以覆盖 Cell 概念，而聚焦在多个单元格时，可以很方便的基于二维数据结构创建表格、折线图等分析图形，因为二维结构的数据才是结构化数据。

或者可以说，结构化数据是 Excel 最核心的概念，而单元格无法体现结构化。结构化数据的好处是，一张工作表就是一个可以用来分析的数据集，在其之上无论是基于单元格的条件格式，还是创建分析图表，都是一种数据二次分析行为，这都得益于结构化数据，所以 Excel JS API 必然围绕结构化数据进行抽象。

再从 API 语法来看，除了工作薄这个级别的 API 采用了 Excel.createWorkbook(); 之外，其他大部分 API 都是以下形式：

Excel.run(function (context) {
    // var sheet = context.workbook.worksheets.getItem("Sample");
    // 对 sheet 操作 ..
    return context.sync();
});

最外层的函数 Excel.run 是注入 context 用的，而且也可以保证执行的时候 Excel context 已经准备好了。而 context.sync() 是同步操作，即使当前对 context 的操作生效。所以 Excel JS API 是命令式的，也不会做类似 MVVM 的双向绑定，所以在操作过程中数据和 Excel 状态不会发生变化，直到执行 context.sync()。

注意到这点后，就可以理解为什么要把某些代码写在 context.sync().then 里了，比如：

Excel.run(function (ctx) {
  var pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

  // Get the totals for each data hierarchy from the layout.
  var range = pivotTable.layout.getDataBodyRange();
  var grandTotalRange = range.getLastRow();
  grandTotalRange.load("address");
  return context.sync().then(function () {
    // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
    var masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
    masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
  });
}).catch(errorHandlerFunction);

这个从透视表获取数据的例子，只有执行 context.sync() 后才能拿到 grandTotalRange.address。

总结

微软还在 Office 套件 Excel、Outlook、Word 中推出了 ScriptLab 功能，就可以在 Excel 的 ScriptLab 里编写 Excel JS API。

在 Excel JS API 之上，还有一个 通用 API，定义为跨应用的通用 API，这样 Excel JS API 就可以把精力聚焦在 Excel 产品本身能力上。

讨论地址是：精读《Excel JS API》· Issue #387 · dt-fe/weekly

版权声明：自由转载-非商用-非衍生-保持署名（创意共享 3.0 许可证）