使用 Jurassic 在 .NET 中创建工作表

Jurassic[^1] 是 .NET 的 JavaScript 编译器，与其他引擎不同，Jurassic 生成 .NET 字节码。

¥Jurassic[^1] is a JavaScript compiler for .NET, In contrast to other engines, Jurassic generates .NET bytecode.

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

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

此演示使用 Jurassic 和 SheetJS 读取和写入电子表格。我们将探索如何在 Jurassic 引擎中加载 SheetJS、与 C# 程序交换二进制数据以及处理电子表格和结构化数据。

¥This demo uses Jurassic and SheetJS to read and write spreadsheets. We'll explore how to load SheetJS in the Jurassic engine, exchange binary data with a C# program, and process spreadsheets and structured data.

"集成示例" 部分包含一个完整的命令行工具，用于读取任意工作簿并将数据写入 ODS（开放文档电子表格）工作簿。

¥The "Integration Example" section includes a complete command-line tool for reading arbitrary workbooks and writing data to ODS (OpenDocument Spreadsheet) workbooks.

遥测

dotnet 命令嵌入遥测技术。

¥The dotnet command embeds telemetry.

DOTNET_CLI_TELEMETRY_OPTOUT 环境变量应设置为 1。

¥The DOTNET_CLI_TELEMETRY_OPTOUT environment variable should be set to 1.

"平台配置" 包含有关在支持的平台上设置环境变量的说明。

¥"Platform Configuration" includes instructions for setting the environment variable on supported platforms.

集成详情

¥Integration Details

SheetJS "mini" 脚本可以在 Jurassic 引擎实例中解析和评估。

¥The SheetJS "mini" script can be parsed and evaluated in a Jurassic engine instance.

Jurassic 在处理 "full" 脚本（xlsx.full.min.js）时会抛出错误：

¥Jurassic throws errors when processing the "full" script (xlsx.full.min.js):

Unhandled exception. Jurassic.JavaScriptException: Error: Maximum number of named properties reached.

必须使用 xlsx.mini.min.js 脚本！

¥The xlsx.mini.min.js script must be used!

迷你版本有许多限制，如安装指南中所述。

¥The mini build has a number of limitations, as noted in the installation guide.

建议在 C# 代码和脚本引擎之间传递 Base64 字符串。

¥It is recommended to pass Base64 strings between C# code and the script engine.

初始化 Jurassic

¥Initialize Jurassic

可以在一行中创建 Jurassic.ScriptEngine 对象：

¥A Jurassic.ScriptEngine object can be created in one line:

var engine = new Jurassic.ScriptEngine();

Jurassic 不公开 NodeJS global。可以合成：

¥Jurassic does not expose the NodeJS global. It can be synthesized:

目标是运行以下 JavaScript 代码：

¥The goal is to run the following JavaScript code:

var global = (function(){ return this; }).call(null);

engine.Evaluate("var global = (function(){ return this; }).call(null);");

加载 SheetJS 脚本

¥Load SheetJS Scripts

Jurassic 引擎对象支持 ExecuteFile 方法评估脚本：

¥Jurassic engine objects support the ExecuteFile method for evaluating scripts:

/* read and evaluate the shim script */
engine.ExecuteFile("shim.min.js");
/* read and evaluate the main library */
engine.ExecuteFile("xlsx.mini.min.js");

要确认库已加载，可以检查 XLSX.version：

¥To confirm the library is loaded, XLSX.version can be inspected:

Console.WriteLine("SheetJS version {0}", engine.Evaluate("XLSX.version"));

读取文件

¥Reading Files

在 C# 中，System.IO.File.ReadAllBytes 将文件数据读取到 byte[] 字节数组中：

¥In C#, System.IO.File.ReadAllBytes reads file data into a byte[] byte array:

string filename = "pres.xlsx";
byte[] buf = File.ReadAllBytes(filename);

字节不能直接传递给 Jurassic。支持 Base64 字符串。可以使用 System.Convert.ToBase64String 创建编码的 Base64 字符串：

¥The bytes cannot be directly passed to Jurassic. Base64 strings are supported. An encoded Base64 string can be created with System.Convert.ToBase64String:

string b64 = System.Convert.ToBase64String(buf);

Jurassic.ScriptEngine#SetGlobalValue 将 C# 字符串分配给全局变量：

¥Jurassic.ScriptEngine#SetGlobalValue will assign the C# String to a global:

engine.SetGlobalValue("buf", b64);

可以使用 SheetJS read 方法 [^2] 从 JS 中解析 buf 变量：

¥The buf variable can be parsed from JS with the SheetJS read method[^2]:

将评估以下脚本：

¥The following script will be evaluated:

var wb = XLSX.read(buf, {type:'base64'});

engine.Evaluate("var wb = XLSX.read(buf, {type:'base64'});");

wb 是一个 SheetJS 工作簿对象。"SheetJS 数据模型" 部分描述了对象结构，"API 参考" 部分描述了各种辅助函数。

¥wb is a SheetJS workbook object. The "SheetJS Data Model" section describes the object structure and the "API Reference" section describes various helper functions.

写入文件

¥Writing Files

SheetJS write 方法 [^3] 可以编写工作簿。选项 type: "base64" 指示库生成 Base64 字符串。bookType 选项 [^4] 控制输出文件格式。

¥The SheetJS write method[^3] can write workbooks. The option type: "base64" instructs the library to generate Base64 strings. The bookType option[^4] controls the output file format.

将评估以下表达式：

¥The following expression will be evaluated:

XLSX.write(wb, {bookType: 'ods', type: 'base64'})

结果将传回 C# 代码。

¥The result will be passed back to C# code.

string ods = engine.Evaluate("XLSX.write(wb, {bookType: 'ods', type: 'base64'})") as string;

ods 是一个 Base64 字符串。System.Convert.FromBase64String 可以将字符串解码为可以写入文件的 byte[]：

¥ods is a Base64 string. System.Convert.FromBase64String can decode the string into a byte[] which can be written to file:

File.WriteAllBytes("SheetJSJurassic.ods", System.Convert.FromBase64String(ods));

集成示例

¥Integration Example

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

架构	侏罗纪	日期
`darwin-x64`	`3.2.7`	2024-06-15
`darwin-arm`	`3.2.7`	2024-06-15
`win10-x64`	`3.2.7`	2024-06-21
`win11-arm`	`3.2.7`	2024-07-14
`linux-x64`	`3.2.7`	2024-06-20
`linux-arm`	`3.2.7`	2024-06-20

平台配置

¥Platform Configuration

将 DOTNET_CLI_TELEMETRY_OPTOUT 环境变量设置为 1。

¥Set the DOTNET_CLI_TELEMETRY_OPTOUT environment variable to 1.

How to disable telemetry (click to hide)

Linux/MacOS
Windows

将以下行添加到 .profile、.bashrc 和 .zshrc：

¥Add the following line to .profile, .bashrc and .zshrc:

(add to .profile , .bashrc , and .zshrc)
export DOTNET_CLI_TELEMETRY_OPTOUT=1

关闭并重新启动终端以加载更改。

¥Close and restart the Terminal to load the changes.

在搜索栏中输入 env，然后选择 "编辑系统环境变量"。

¥Type env in the search bar and select "Edit the system environment variables".

在新窗口中，单击 "环境变量..." 按钮。

¥In the new window, click the "Environment Variables..." button.

在新窗口中，查找 "系统变量" 部分并单击 "新的..."

¥In the new window, look for the "System variables" section and click "New..."

将 "变量名" 设置为 DOTNET_CLI_TELEMETRY_OPTOUT，并将值设置为 1。

¥Set the "Variable name" to DOTNET_CLI_TELEMETRY_OPTOUT and the value to 1.

在每个窗口（3 个窗口）中单击 "OK"，然后重新启动计算机。

¥Click "OK" in each window (3 windows) and restart your computer.

安装.NET

¥Install .NET

Installation Notes (click to show)

For macOS x64 and ARM64, install the dotnet-sdk Cask with Homebrew:

brew install --cask dotnet-sdk

For Steam Deck Holo and other Arch Linux x64 distributions, the dotnet-sdk and dotnet-runtime packages should be installed using pacman:

sudo pacman -Syu dotnet-sdk dotnet-runtime

https://dotnet.microsoft.com/en-us/download/dotnet/6.0 is the official source for Windows and ARM64 Linux versions.

在 macOS 中打开新的终端窗口或在 Windows 中打开 PowerShell 窗口。

¥Open a new Terminal window in macOS or PowerShell window in Windows.

基础项目

¥Base Project

创建一个新的文件夹 SheetJSJurassic 和一个新的 dotnet 项目：

¥Create a new folder SheetJSJurassic and a new dotnet project:

mkdir SheetJSJurassic
cd SheetJSJurassic
dotnet new console
dotnet run

使用 NuGet 工具添加 Jurassic：

¥Add Jurassic using the NuGet tool:

dotnet add package Jurassic --version 3.2.7

要验证 Jurassic 是否已安装，请将 Program.cs 替换为以下内容：

¥To verify Jurassic is installed, replace Program.cs with the following:

Program.cs
var engine = new Jurassic.ScriptEngine();
Console.WriteLine("Hello {0}", engine.Evaluate("'Sheet' + 'JS'"));

保存后运行程序：

¥After saving, run the program:

dotnet run

终端应显示 Hello SheetJS

¥The terminal should display Hello SheetJS

添加 SheetJS

¥Add SheetJS

下载 SheetJS 小脚本、shim 脚本和测试文件。将所有三个文件移动到项目目录：

¥Download the SheetJS mini script, shim script and test file. Move all three files to the project directory:

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

将 Program.cs 替换为以下内容：

¥Replace Program.cs with the following:

Program.cs
var engine = new Jurassic.ScriptEngine();
engine.Evaluate("var global = (function(){ return this; }).call(null);");
engine.Evaluate(File.ReadAllText("shim.min.js"));
engine.Evaluate(File.ReadAllText("xlsx.mini.min.js"));
Console.WriteLine("SheetJS version {0}", engine.Evaluate("XLSX.version"));

保存后运行程序：

¥After saving, run the program:

dotnet run

The terminal should display SheetJS version 0.20.3

读写文件

¥Read and Write Files

将 Program.cs 替换为以下内容：

¥Replace Program.cs with the following:

Program.cs
var engine = new Jurassic.ScriptEngine();

/* Initialize Jurassic */
engine.Evaluate("var global = (function(){ return this; }).call(null);");

/* Load SheetJS Scripts */
engine.ExecuteFile("shim.min.js");
engine.ExecuteFile("xlsx.mini.min.js");
Console.WriteLine("SheetJS version {0}", engine.Evaluate("XLSX.version"));

/* Read and Parse File */
byte[] filedata = File.ReadAllBytes(args[0]);
string b64 = System.Convert.ToBase64String(filedata);
engine.SetGlobalValue("buf", b64);
engine.Evaluate("var wb = XLSX.read(buf, {type:'base64'});");

/* Print CSV of first worksheet*/
engine.Evaluate("var ws = wb.Sheets[wb.SheetNames[0]];");
object csv = engine.Evaluate("XLSX.utils.sheet_to_csv(ws)");
Console.Write(csv);

/* Generate XLSB file and save to SheetJSJurassic.ods */
string ods = engine.Evaluate("XLSX.write(wb, {bookType: 'ods', type: 'base64'})") as string;
File.WriteAllBytes("SheetJSJurassic.ods", System.Convert.FromBase64String(ods));

保存后，运行程序并将测试文件名作为参数传递：

¥After saving, run the program and pass the test file name as an argument:

dotnet run pres.xlsx

如果成功，程序会将第一张表的内容打印为 CSV 行。它还将创建 SheetJSJurassic.ods，一个可以在 Excel 或其他电子表格编辑器中打开的 OpenDocument 电子表格。

¥If successful, the program will print the contents of the first sheet as CSV rows. It will also create SheetJSJurassic.ods, an OpenDocument spreadsheet that can be opened in Excel or another spreadsheet editor.

在不带文件名参数的情况下运行 dotnet run 将显示错误：

¥Running dotnet run without the filename argument will show an error:

Unhandled exception. System.IndexOutOfRangeException: Index was outside the bounds of the array.
   at Program.<Main>$(String[] args) in C:\Users\Me\SheetJSJurassic\Program.cs:line 15

该命令必须使用指定工作簿名称的参数运行：

¥The command must be run with an argument specifying the name of the workbook:

dotnet run pres.xlsx

[^1]: 该项目没有网站。该库托管在 NuGet 上。

¥The project does not have a website. The library is hosted on NuGet.

[^2]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^3]: 见 write 于 "写入文件"

¥See write in "Writing Files"

[^4]: 有关 bookType 的详细信息，请参阅 "支持的输出格式" 于 "写入文件"

¥See "Supported Output Formats" in "Writing Files" for details on bookType

集成详情​

初始化 Jurassic​

加载 SheetJS 脚本​

读取文件​

写入文件​

集成示例​

平台配置​

基础项目​

添加 SheetJS​

读写文件​