Intensive reading "Excel JS API"

Excel can now use js to generate charts and tables based on cell data, or to extend built-in Excel expressions through js to extend custom functions.

Let's learn how the Excel js API open is designed, and learn some open API design experience from it.

API Documentation: Excel JavaScript API overview

intensive reading

Excel will use the JS API to open up a lot of capabilities, including what users can easily do through the interface, and also what cannot be done through the interface operation.

Why do you need to open JS API

Excel already has good ease of use, as well as the powerful formula formula In the previous intensive reading of "Microsoft Power Fx" mentioned that formula is the Power FX in Excel, which belongs to the canvas low-code language, but it is more appropriate to call it "formula" in Excel.

With so many capabilities, why do you need a JS API? In one sentence, formula can be used in JS API, that is, JS API is a superset of formula capabilities. It includes additions, deletions, changes and inspections of Excel workbooks, data restrictions, RangeAreas operations, charts, pivot tables, and even automatic Define the formula function.

That is to say, the JS API makes Excel "programmable", that is, the secondary expansion of Excel from the developer's perspective, including the secondary expansion of formulas, allows Excel to cover more scenarios.

Where JS APIs can be used

From the beginning of the workbook and worksheet in the Excel process, to the most detailed cell data verification, all can be supported by the JS API. At present, it seems that the Excel JS API does not set a capability boundary, and it will continue to improve. Excel All programmable parts in the whole life cycle are opened up.

The first is the operation of the workbook, the worksheet, and the monitoring of the user operation of the worksheet, or the read-only setting of the worksheet. The purpose of this type of API is to programmatically manipulate Excel as a whole.

The second step is to operate on the cell level, such as selecting a cell range, obtaining the selected range, or setting cell properties, colors, or verifying cell data. Custom formulas are also in this link, because the cell value can be a formula, and the formula can be extended using the JS API.

The last step is to expand the behavior, that is, to introduce chart and pivot table expansion on the basis of cells. Although these functions can also be operated on the UI buttons, the JS API can implement logic that cannot be configured on the UI interface. For very complex logical behaviors, even if the UI can be configured, the readability is far less than that of the code. In addition to table pivot tables, you can also create some custom shapes, basic geometric shapes, images and SVG are all supported.

JS API Design

Interestingly enough, Excel doesn't have an abstract "cell" object, even though we all think cells are what Excel stands for.

This is done for reasons of API design, since Excel uses the concept of Range to represent contiguous cells. for example:

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

It can be found that Range makes Excel focus on the batch cell API, that is, the cell is regarded as a range, and the overall API can be designed around a range. The advantage of this design concept is that by limiting the scope to a single cell, the concept of Cell can be covered, and when focusing on multiple cells, it is very convenient to create analytical graphics such as tables and line charts based on two-dimensional data structures. Because two-dimensional structured data is structured data.

In other words, structured data is the core concept of Excel, and cells cannot reflect structure. The advantage of structured data is that a worksheet is a data set that can be used for analysis. Whether it is cell-based conditional formatting or creating analysis charts, it is a secondary data analysis behavior. All benefit from structured data, so the Excel JS API must be abstracted around structured data.

From the API syntax point of view, except that the API at the workbook level adopts Excel.createWorkbook(); , most of the other APIs are in the following form:

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

The outermost function Excel.run is used to inject context , and it can also ensure that the Excel context is ready when it is executed. And context.sync() is a synchronous operation, even if the current operation on the context takes effect. Therefore, the Excel JS API is imperative, and it will not do two-way binding similar to MVVM, so the data and Excel state will not change during the operation until context.sync() executed.

After noticing this, you can understand why some code is written in context.sync().then , such as:

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

The acquired data from a PivotTable example, only the execution context.sync() after to get grandTotalRange.address .

Summarize

ScriptLab function in the Office suites Excel, Outlook, and Word, which allows you to write Excel JS APIs in Excel's ScriptLab.

On top of the Excel JS API, there is also a general API , which is defined as a general API across applications, so that the Excel JS API can focus on the capabilities of the Excel product itself.

The discussion address is: Intensive Reading "Excel JS API" Issue #387 dt-fe/weekly

If you want to participate in the discussion, please click here , there are new topics every week, released on weekends or Mondays. Front-end intensive reading - help you filter reliable content.

attention to 161dcd69f871a5 front-end intensive reading WeChat public

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

Copyright notice: Free to reprint - non-commercial - non-derivative - keep attribution ( Creative Commons 3.0 License )