AppleScript 和 OSA

开放脚本架构 (OSA)[^1] 通过脚本实现 macOS 应用自动化。OSA 最初支持 "AppleScript" 语言。现代 macOS 版本（OSX 10.10 及更高版本）本身支持使用 "JXA"[^2] 的 JavaScript 脚本。

¥Open Scripting Architecture (OSA)[^1] enables macOS app automation with scripts. OSA originally supported the "AppleScript" language. Modern macOS releases (OSX 10.10 and later) natively support JavaScript scripts using "JXA"[^2].

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

此演示使用 OSA Scripts 中的 SheetJS 从电子表格中提取数据。我们将探索如何在 AppleScript 和 JavaScript 脚本中使用 SheetJS 库。"完整演示" 解析工作簿并生成 CSV 行。

¥This demo uses SheetJS in OSA Scripts to pull data from a spreadsheet. We'll explore how to use SheetJS libraries in AppleScript and JavaScript scripts. The "Complete Demo" parses workbooks and generates CSV rows.

测试环境

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

苹果系统	语言	日期
14.5	AppleScript (OSA)	2024-06-30
14.5	JavaScript (JXA)	2024-06-30

集成细节

¥Integration details

SheetJS 独立脚本 可以从 JS 引擎解析和评估。评估后，将定义 XLSX 全局。JS 存根可以公开 AppleScript 脚本中的方法。

¥The SheetJS Standalone scripts can be parsed and evaluated from the JS engine. Once evaluated, the XLSX global will be defined. A JS stub can expose methods from AppleScript scripts.

JavaScript

以下代码片段将文件读取为二进制字符串：

¥The following snippet reads a file into a binary string:

ObjC.import("Foundation");
function get_bstr(path) {
  /* create NSString from the file contents using a binary encoding */
  var str = $.NSString.stringWithContentsOfFileEncodingError(path, $.NSISOLatin1StringEncoding, null);
  /* return the value as a JS object */
  return ObjC.unwrap(str);
}

加载库

¥Loading the Library

假设独立库与源文件位于同一目录中，则可以使用 eval 评估脚本：

¥Assuming the standalone library is in the same directory as the source file, the script can be evaluated with eval:

var src = get_bstr("./xlsx.full.min.js");
eval(src);

解析文件

¥Parsing Files

同样的方法可以用来读取二进制字符串并用 type: "binary" 解析：

¥The same method can be used to read binary strings and parse with type: "binary":

var file = get_bstr("./pres.numbers");
var wb = XLSX.read(file);

AppleScript

核心思想是将处理逻辑推送到一个存根 JS 文件中。

¥The core idea is to push the processing logic to a stub JS file.

JS 存根

¥JS Stub

JS 存根将在 JavaScript 上下文中进行评估。JavaScript 部分中的相同技术也适用于存根：

¥The JS stub will be evaluated in the JavaScript context. The same technique from the JavaScript section works in the stub:

ObjC.import("Foundation");

function get_bstr(path) {
  var str = $.NSString.stringWithContentsOfFileEncodingError(path,  $.NSISOLatin1StringEncoding, null);
  return ObjC.unwrap(str);
}

/* this will be called when AppleScript initializes the JS engine */
eval(get_bstr("./xlsx.full.min.js"));

将尽可能多的工作卸载到存根中会更有效。例如，此函数从文件系统解析工作簿文件并生成 CSV，而不将中间值传递回 AppleScript：

¥It is more efficient to offload as much work as possible into the stub. For example, this function parses a workbook file from the filesystem and generates a CSV without passing intermediate values back to AppleScript:

/* this method will be exposed as `wb_to_csv` */
function wb_to_csv(path) {
  /* read file */
  var filedata = get_bstr(path);
  var wb = XLSX.read(filedata, { type: "binary" });
  return XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
}

加载存根

¥Loading the Stub

假设存根保存到 xlsx.stub.js，以下处理程序将创建一个上下文并评估独立库：

¥Assuming the stub is saved to xlsx.stub.js, the following handler creates a context and evaluates the standalone library:

on getContext()
  -- get contents of xlsx.stub.js
  set UnixPath to POSIX path of ((path to me as text) & "::")
  set libpath to POSIX path of (UnixPath & "xlsx.stub.js")
  set {src, err} to current application's NSString's stringWithContentsOfFile:libpath encoding:(current application's NSISOLatin1StringEncoding) |error|:(reference)
  if src is missing value then error (err's localizedDescription()) as text

  -- create scripting context and evaluate the stub
  set lang to current application's OSALanguage's languageForName:"JavaScript"
  set osa to current application's OSAScript's alloc()'s initWithSource:src language:lang
  return osa
end getContext

评估 JS 代码

¥Evaluating JS Code

调用函数时，结果是一个数组，其第一项是计算代码的值。一个小辅助函数提取原始结果：

¥When calling a function, the result is an array whose first item is the value of the evaluated code. A small helper function extracts the raw result:

on extractResult(res)
  return item 1 of ((current application's NSArray's arrayWithObject:res) as list)
end extractResult

定义完所有内容后，executeHandlerWithName 将运行存根中定义的函数。例如：

¥With everything defined, executeHandlerWithName will run functions defined in the stub. For example:

set osa to getContext()
set {res, err} to osa's executeHandlerWithName:"wb_to_csv" arguments:{"pres.numbers"} |error|:(reference)
extractResult(res)

完整演示

¥Complete Demo

此示例将从指定的文件名中读取并以 CSV 格式打印第一个工作表数据。

¥This example will read from a specified filename and print the first worksheet data in CSV format.

0. 下载 SheetJS 独立脚本和测试文件。将这两个文件移动到项目目录：

   ¥Download the SheetJS Standalone script and test file. Move both files to the project directory:

• xlsx.full.min.js
• pres.numbers

curl -LO https://xlsx.nodejs.cn/pres.numbers
curl -LO https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js

JavaScript

1. 将以下脚本保存到 sheetosa.js：

   ¥Save the following script to sheetosa.js:

sheetosa.js

#!/usr/bin/env osascript -l JavaScript

ObjC.import("Foundation");
function get_bstr(path) {
  var str = $.NSString.stringWithContentsOfFileEncodingError(path,  $.NSISOLatin1StringEncoding, null);
  return ObjC.unwrap(str);
}
eval(get_bstr("./xlsx.full.min.js"));

function run(argv) {
  var filedata = get_bstr(argv[0]);
  var wb = XLSX.read(filedata, { type: "binary" });
  console.log(XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));
}

2. 使脚本可执行：

   ¥Make the script executable:

chmod +x sheetosa.js

3. 运行脚本，将测试文件的路径作为参数传递：

   ¥Run the script, passing the path to the test file as an argument:

./sheetosa.js pres.numbers

AppleScript

1. 将以下脚本保存到 xlsx.stub.js：

   ¥Save the following script to xlsx.stub.js:

xlsx.stub.js

ObjC.import("Foundation");
function get_bstr(path) {
  var str = $.NSString.stringWithContentsOfFileEncodingError(path,  $.NSISOLatin1StringEncoding, null);
  return ObjC.unwrap(str);
}
eval(get_bstr("./xlsx.full.min.js"));

function wb_to_csv(path) {
  var filedata = get_bstr(path);
  var wb = XLSX.read(filedata, { type: "binary" });
  return XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]);
}

2. 将以下脚本保存到 sheetosa.scpt：

   ¥Save the following script to sheetosa.scpt:

sheetosa.scpt

#!/usr/bin/env osascript
use AppleScript version "2.7"
use scripting additions
use framework "Foundation"
use framework "OSAKit"

set osa to getContext()
set {res, err} to osa's executeHandlerWithName:"wb_to_csv" arguments:{"pres.numbers"} |error|:(reference)
extractResult(res)

on getContext()
  set UnixPath to POSIX path of ((path to me as text) & "::")
  set libpath to POSIX path of (UnixPath & "xlsx.stub.js")
  set {src, err} to current application's NSString's stringWithContentsOfFile:libpath encoding:(current application's NSISOLatin1StringEncoding) |error|:(reference)

  set lang to current application's OSALanguage's languageForName:"JavaScript"
  set osa to current application's OSAScript's alloc()'s initWithSource:src language:lang
  return osa
end getContext

on extractResult(res)
  return item 1 of ((current application's NSArray's arrayWithObject:res) as list)
end extractResult

3. 使脚本可执行：

   ¥Make the script executable:

chmod +x sheetosa.scpt

4. 运行脚本（它被硬编码为读取 pres.numbers）：

   ¥Run the script (it is hardcoded to read pres.numbers):

./sheetosa.scpt

如果成功，将打印第一个工作表中的 CSV 行：

¥If successful, CSV rows from the first worksheet will be printed:

Name,Index
Bill Clinton,42
GeorgeW Bush,43
Barack Obama,44
Donald Trump,45
Joseph Biden,46

[^1]: 有关更多详细信息，请参阅 Apple 开发者文档中的 "AppleScript 简介概述"。

¥See "Introduction to AppleScript Overview" in the Apple Developer documentation for more details.

[^2]: 有关更多详细信息，请参阅 Apple 开发者文档中的 "用于自动化的 JavaScript 简介发行说明"。

¥See "Introduction to JavaScript for Automation Release Notes" in the Apple Developer documentation for more details.