Azure 云服务

Azure 云服务是一个云服务平台，包括传统虚拟机支持、"无服务器功能" 和云存储。

¥Azure Cloud Services is a cloud services platform which includes traditional virtual machine support, "Serverless Functions" and cloud storage.

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

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

该演示探讨了两个关键的 AWS 产品：

¥This demo explores two key AWS offerings:

"Azure 函数" ("Lambda") 探索无服务器计算产品。该演示创建了一个 JavaScript 函数，可以处理用户提交的文件并生成电子表格。

¥"Azure Functions" ("Lambda") explores the serverless computing offering. The demo creates a JavaScript function that can process user-submitted files and generate spreadsheets.
"Blob 存储" 探索云存储产品。该演示使用 NodeJS 连接库从存储读取电子表格并将电子表格写回云存储。

¥"Blob Storage" explores the cloud storage offering. The demo uses the NodeJS connection library to read spreadsheets from storage and write spreadsheets back to cloud storage.

Azure 迭代速度很快，无法保证所引用的服务将来可用。

¥Azure iterates quickly and there is no guarantee that the referenced services will be available in the future.

测试部署

此演示最后一次于 2024 年 6 月 12 日进行测试。

¥This demo was last tested on 2024 June 12.

遥测

¥Telemetry

遥测

每个与 Azure 相关的命令行工具都嵌入了遥测技术。

¥Each command-line tool related to Azure embeds telemetry.

Azure 工具嵌入遥测技术，但没有适当的免责声明。

¥Azure tools embed telemetry without proper disclaimer.

强烈建议在使用 Azure 之前禁用遥测。

¥It is strongly recommended to disable telemetry before working with Azure.

Azure Functions 核心工具

¥Azure Functions Core Tools

Azure Functions Core Tools (func) 遥测通过 FUNCTIONS_CORE_TOOLS_TELEMETRY_OPTOUT 环境变量进行控制。

¥Azure Functions Core Tools (func) telemetry is controlled through the FUNCTIONS_CORE_TOOLS_TELEMETRY_OPTOUT environment variable.

Linux/MacOS
Windows

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

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

export FUNCTIONS_CORE_TOOLS_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..."

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

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

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

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

Azure CLI

可以使用子命令（安装 CLI 工具后）[^1] 禁用 Azure CLI (az) 遥测：

¥Azure CLI (az) telemetry can be disabled using a subcommand (after installing the CLI tool)[^1]:

az configure -d collect_telemetry=false

Azure 函数

¥Azure Functions

使用 NodeJS 运行时的 Azure Functions 可能需要 SheetJS NodeJS 模块。

¥The SheetJS NodeJS module can be required in Azure Functions that use the NodeJS runtime.

本讨论重点讨论 "HTTP 触发器" 函数类型。

¥This discussion focuses on the "HTTP Trigger" function type.

在早期的测试中，为了启用二进制数据处理，function.json 需要 dataType 选项：

¥In earlier tests, to enable binary data processing, function.json required a dataType option:

function.json
{
  "bindings": [
    {
      "type": "httpTrigger",
      "direction": "in",
      "dataType": "binary",
      "name": "req",

在最近的测试中，模板没有创建 function.json，并且不需要该选项。

¥In the most recent test, the template did not create a function.json and the option was not required.

读取数据

¥Reading Data

使用 @azure/functions，处理程序回调接收 Request 对象。上传的文件可以拉入 ArrayBuffer 个对象。

¥Using @azure/functions, the handler callback receives a Request object. Uploaded files can be pulled into ArrayBuffer objects.

Code Snippet (click to show)

This function returns a promise that resolves to an ArrayBuffer object:

const { Blob } = require('buffer');

async function get_file_from_request(request, form_field_name) {
  /* parse the request body */
  const formData = await request.formData();

  /* pull the specified field */
  const file = formData.get(form_field_name);

  /* if a file was submitted, `file` will be a Blob */
  if(!(file instanceof Blob)) throw new Error(`File is missing!`);

  /* pull data into an ArrayBuffer object */
  const ab = await file.arrayBuffer();
  return ab;
}

SheetJS read 方法 [^2] 可以读取 ArrayBuffer 对象并生成 SheetJS 工作簿对象 [^3]，可以使用其他 API 函数进行处理。

¥The SheetJS read method[^2] can read the ArrayBuffer objects and generate SheetJS workbook objects[^3] which can be processed with other API functions.

例如，处理程序可以使用 sheet_to_csv[^4] 从用户提交的电子表格生成 CSV 文本：

¥For example, a handler can use sheet_to_csv[^4] to generate CSV text from user-submitted spreadsheets:

const { Blob } = require('buffer');
const { app } = require('@azure/functions');
const XLSX = require('xlsx');

app.http('SheetJSAzure', {
  methods: ['POST'],
  handler: async (req, context) => {
    /* grab the file at form key `upload` */
    const formData = await req.formData();
    const f = formData.get("upload");

    /* if a file was submitted, `f` will be a Blob object */
    if(!(f instanceof Blob)) return { status: 400, body: "Must submit a file" };

    /* parse file */
    const ab = await f.arrayBuffer();
    const wb = XLSX.read(ab);

    /* generate CSV from first sheet */
    const ws = wb.Sheets[wb.SheetNames[0]];
    const csv = XLSX.utils.sheet_to_csv(ws);
    return { status: 200, body: csv };
  }
});

写入数据

¥Writing Data

带有选项 type: "buffer" 的 SheetJS write 方法 [^5] 将生成 NodeJS 缓冲区，这些缓冲区可以在回调处理程序响应中发送。

¥The SheetJS write method[^5] with the option type: "buffer" will generate NodeJS buffers which can be sent in the callback handler response.

以下示例使用 aoa_to_sheet[^6] 方法生成示例工作表，使用工作表辅助程序方法 [^7] 生成示例工作簿，将工作簿以 XLSX 格式写入缓冲区中，并在响应中发送缓冲区：

¥The following example generates a sample worksheet using the aoa_to_sheet[^6] method, generates a sample workbook using worksheet helper methods[^7], writes the workbook to XLSX format in a Buffer, and sends the Buffer in the response:

const { app } = require('@azure/functions');
const XLSX = require('xlsx');

app.http('SheetJSAzure', {
  methods: ['GET'],
  handler: async (req, context) => {
    /* generate sample worksheet */
    var ws = XLSX.utils.aoa_to_sheet(["SheetJS".split(""), [5, 4, 3, 3, 7, 9, 5]]);
    /* generate workbook */
    var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Data");
    /* write to XLSX, returning a NodeJS Buffer */
    var buf = XLSX.write(wb, { type: "buffer", bookType: "xlsx" });
    /* send Buffer to client */
    return {
      status: 200,
      /* Content-Disposition header */
      headers: { "Content-Disposition": `attachment; filename="SheetJSAzure.xlsx";` },
      /* data */
      body: buf
    };
  }
});

功能演示

¥Functions Demo

截至撰写本文时，Azure 免费套餐包含每月 100 万个免费请求的限额。

¥At the time of writing, the Azure Free Tier included an allowance of 1 million free requests per month.

如果你没有账户，请创建一个新的 Azure 免费套餐账户 [^8]。

¥If you do not have an account, create a new Azure free tier account[^8].

本地设置

¥Local Setup

禁用 Azure Functions 核心工具遥测。

¥Disable Azure Functions Core Tools Telemetry.
使用 npm 安装 CLI 工具：

¥Install the CLI tool using npm:

npm i -g azure-functions-core-tools@4 --unsafe-perm true

在 macOS 和 Linux 上，可能需要 sudo：

¥On macOS and Linux, sudo may be required:

sudo npm i -g azure-functions-core-tools@4 --unsafe-perm true

安装 Azure CLI

¥Install Azure CLI

Installation Notes (click to show)

On macOS, azure-cli can be installed from Homebrew:

brew install azure-cli

禁用 Azure CLI 遥测：

¥Disable Azure CLI telemetry:

az configure -d collect_telemetry=false

启动项目

¥Start Project

创建一个新的 JavaScript HTTP 触发器项目：

¥Create a new JavaScript HTTP Trigger project:

mkdir SheetJSAzure
cd SheetJSAzure
func new --template httpTrigger --language JavaScript --name SheetJSAzure

如果提示工作运行时，请选择 node（按 3）

¥If prompted for a worker runtime, select node (Press 3)

如果提示语言，请选择 javascript（按 1）

¥If prompted for a language, select javascript (Press 1)

上次测试演示时，现有的 TypeScript 模板无法工作。

¥When the demo was last tested, the stock TypeScript template did not work.

这是 Azure Functions 核心工具中的一个错误

¥This is a bug in the Azure Functions Core Tools

在错误得到解决之前，JavaScript 应该优先于 TypeScript。

¥Until the bugs are resolved, JavaScript should be preferred over TypeScript.

启动本地服务器：

¥Start the local server:

npm start

当服务器运行时，打开一个新的终端窗口并发送请求：

¥While the server is running, open a new terminal window and make a request:

curl -L http://localhost:7071/api/SheetJSAzure

终端应显示 Hello, world!

¥The terminal should display Hello, world!

添加 SheetJS

¥Add SheetJS

安装 SheetJS NodeJS 模块：

¥Install the SheetJS NodeJS module:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

下载示例脚本：

¥Download the sample script:

curl -L -o src/functions/SheetJSAzure.js https://xlsx.nodejs.cn/azure/index.js

本地测试

¥Local Test

停止并重新启动开发服务器：

¥Stop and restart the dev server:

npm start

在新的终端窗口中，下载 https://xlsx.nodejs.cn/pres.numbers 并向开发服务器发送 POST 请求：

¥In a new terminal window, download https://xlsx.nodejs.cn/pres.numbers and make a POST request to the dev server:

curl -LO https://xlsx.nodejs.cn/pres.numbers
curl -X POST -F "upload=@pres.numbers" http://localhost:7071/api/SheetJSAzure

如果测试成功，终端将从测试文件数据中打印 CSV 行。

¥If the test succeeded, the terminal will print CSV rows from the test file data.

打开网络浏览器并访问 http://localhost:7071/api/SheetJSAzure 。

¥Open a web browser and access http://localhost:7071/api/SheetJSAzure .

如果测试成功，浏览器将尝试下载 SheetJSAzure.xlsx。在 Excel 或其他电子表格编辑器中打开以确认文件有效。

¥If the test succeeded, the browser will attempt to download SheetJSAzure.xlsx. Open in Excel or another spreadsheet editor to confirm the file is valid.

创建远程函数

¥Create Remote Function

登录 Azure 门户

¥Sign into the Azure Portal
在顶部搜索框中输入 "功能应用"，然后单击 "功能应用"：

¥Type "Function App" in the top search box and click "Function App":

Function App

点击 "* 创造"

¥Click "+ Create"
选择以下选项：

¥Select the following options:

"选择托管选项"："消耗"

¥"Select a hosting option": "Consumption"
输入令人难忘的 "函数名称"（上次测试时为 "sheetjsazure"）

¥Type a memorable "Function Name" ("sheetjsazure" when last tested)
"操作系统"："Windows"

¥"Operating System": "Windows"
"运行时堆栈"：选择 Node.js

¥"Runtime stack": select Node.js

单击 "审核+创建"，然后单击 "创造" 以创建函数。

¥Click "Review + create", then click "Create" to create the function.

页面将显示状态消息

¥The page will display a status message

...部署正在进行中

¥... Deployment is in progress

配置资源后，状态将更改为

¥When the resources are configured, the status will change to

你的部署已完成

¥Your deployment is complete

单击 "转到资源"。

¥Click "Go to Resource".
记下 "基本信息" 表中的 URL。

¥Take note of the URL from the "Essentials" table.

部署到 Azure

¥Deploy to Azure

az login

登录流程在浏览器中恢复。

¥The login flow resumes in the browser.

部署到 Azure。将 FUNCTION_NAME 替换为步骤 16 中的名称：

¥Deploy to Azure. Replace FUNCTION_NAME with the name from Step 16:

func azure functionapp publish FUNCTION_NAME

发布后，进程将打印 "调用网址"：

¥After publishing, the process will print the "Invoke url":

Functions in sheetjsazure:
    SheetJSAzure - [httpTrigger]
        Invoke url: https://sheetjsazure.azurewebsites.net/api/sheetjsazure

记下该 URL。

¥Take note of that URL.

上次使用 "Linux" 操作系统测试此演示时，命令失败并出现支持错误：

¥When this demo was last tested using the "Linux" operating system, the command failed with a support error:

Azure Functions Core Tools does not support this deployment path. Please configure the app to deploy from a remote package using the steps here: https://aka.ms/deployfromurl

这是具有 Linux 功能的 Azure CLI 工具的限制！

¥This is a limitation of the Azure CLI tool with Linux functions!

确保所选操作系统为 "Windows"。

¥Ensure that the selected operating system is "Windows".

SheetJS 库在基于 Linux 的函数中运行。

¥SheetJS libraries run in Linux-based functions.

远程测试

¥Remote Test

在新的终端窗口中，下载 https://xlsx.nodejs.cn/pres.numbers 并向生产服务器发送 POST 请求。将 FUNCTION_URL 替换为步骤 21 中的调用 URL：

¥In a new terminal window, download https://xlsx.nodejs.cn/pres.numbers and make a POST request to the production server. Replace FUNCTION_URL with the Invoke URL from Step 21:

curl -LO https://xlsx.nodejs.cn/pres.numbers
curl -X POST -F "upload=@pres.numbers" FUNCTION_URL

如果测试成功，终端将从测试文件数据中打印 CSV 行。

¥If the test succeeded, the terminal will print CSV rows from the test file data.

打开 Web 浏览器并访问步骤 21 中的调用 URL。

¥Open a web browser and access the Invoke URL from Step 21.

如果测试成功，浏览器将尝试下载 SheetJSAzure.xlsx。在 Excel 或其他电子表格编辑器中打开以确认文件有效。

¥If the test succeeded, the browser will attempt to download SheetJSAzure.xlsx. Open in Excel or another spreadsheet editor to confirm the file is valid.

Blob 存储

¥Blob Storage

Azure Blob 存储的主要模块是 @azure/storage-blob。本示例使用 "连接字符串" 身份验证方法进行测试。这些字符串可在 Azure 门户中存储账户的 "访问键" 下找到。

¥The main module for Azure Blob Storage is @azure/storage-blob. This example was tested using the "Connection String" authentication method. The strings are found in the Azure Portal under "Access Keys" for the storage account.

下载数据

¥Downloading Data

BlobClient#download 方法返回一个 Stream。收集到 Buffer 中后，SheetJS read 方法 [^9] 可以将数据解析成工作簿 [^10]。

¥The BlobClient#download method returns a Stream. After collecting into a Buffer, the SheetJS read method[^9] can parse the data into a workbook[^10].

以下演示使用 sheet_to_csv[^11] 实用函数来显示 Azure Blob 存储中文件的内容：

¥The following demo uses the sheet_to_csv[^11] utility function to display the contents of a file in Azure Blob Storage:

SheetJSReadFromAzure.mjs
import { BlobServiceClient } from "@azure/storage-blob";
import { read, utils } from "xlsx";

/* replace these constants */
const connStr = "<REPLACE WITH CONNECTION STRING>";
const containerName = "<REPLACE WITH CONTAINER NAME>";

/* Blob name */
const blobName = "SheetJSBloblobber.xlsx";

/* get a readable stream*/
const blobServiceClient = BlobServiceClient.fromConnectionString(connStr);
const containerClient = blobServiceClient.getContainerClient(containerName);
const blobClient = containerClient.getBlobClient(blobName);
const response = (await blobClient.download()).readableStreamBody;

/* collect data into a Buffer */
const bufs = [];
for await(const buf of response) bufs.push(buf);
const downloaded = Buffer.concat(bufs);

/* parse downloaded buffer */
const wb = read(downloaded);
/* print first worksheet */
console.log(utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));

上传数据

¥Uploading Data

带有选项 type: "buffer" 的 SheetJS write method[^12] 将生成可以使用 BlockBlobClient#upload 上传的 NodeJS 缓冲区。

¥The SheetJS write method[^12] with the option type: "buffer" will generate NodeJS buffers which can be uploaded with BlockBlobClient#upload.

以下示例使用 aoa_to_sheet[^13] 方法生成示例工作表，使用工作表辅助程序方法 [^14] 生成示例工作簿，将工作簿以 XLSX 格式写入缓冲区中，并在响应中发送缓冲区：

¥The following example generates a sample worksheet using the aoa_to_sheet[^13] method, generates a sample workbook using worksheet helper methods[^14], writes the workbook to XLSX format in a Buffer, and sends the Buffer in the response:

SheetJSWriteToAzure.mjs
import { BlobServiceClient } from "@azure/storage-blob";
import { write, utils } from "xlsx";

/* replace these constants */
const connStr = "<REPLACE WITH CONNECTION STRING>";
const containerName = "<REPLACE WITH CONTAINER NAME>";

/* Blob name */
const blobName = "SheetJSBloblobber.xlsx";

/* Create a simple workbook and write XLSX to buffer */
const ws = utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
const wb = utils.book_new(); utils.book_append_sheet(wb, ws, "Sheet1");
const buf = write(wb, {type: "buffer", bookType: "xlsx"});

/* upload buffer */
const blobServiceClient = BlobServiceClient.fromConnectionString(connStr);
const containerClient = blobServiceClient.getContainerClient(containerName);
const blockBlobClient = containerClient.getBlockBlobClient(blobName);
const uploadBlobResponse = await blockBlobClient.upload(buf, buf.length);

Blob 演示

¥Blob Demo

截至撰写本文时，新的 Azure 账户已获得 12 个月的 Blob 存储试用期。该试用版包括 5GB 的 "本地冗余存储"，每月有 20,000 个读取请求和 2000 个写入请求。

¥At the time of writing, new Azure accounts were granted a 12-month trial of Blob Storage. The trial includes 5GB of "Locally-redundant storage" with 20,000 read requests and 2000 write requests per month.

如果你没有账户，请创建一个新的 Azure 免费套餐账户 [^15]。

¥If you do not have an account, create a new Azure free tier account[^15].

存储账户设置

¥Storage Account Setup

登录 Azure 门户

¥Sign into the Azure Portal
在顶部搜索框中输入 "贮存"，然后单击 "存储账户"：

¥Type "Storage" in the top search box and click "Storage accounts":

Storage Accounts

点击 "* 创造"

¥Click "+ Create"
选择以下选项：

¥Select the following options:

输入令人难忘的 "存储账户名称"（上次测试时为 "sheetjstorage"）

¥Type a memorable "Storage account name" ("sheetjstorage" when last tested)
"冗余"：选择 LRS（本地冗余存储）

¥"Redundancy": select LRS (Locally-redundant storage)

单击 "审查"，然后单击 "创造" 创建存储。

¥Click "Review", then click "Create" to create the storage.

页面将显示状态消息

¥The page will display a status message

...部署正在进行中

¥... Deployment is in progress

配置资源后，状态将更改为

¥When the resources are configured, the status will change to

你的部署已完成

¥Your deployment is complete

单击 "转到资源"。

¥Click "Go to Resource".

访问键

¥Access Keys

单击左侧边栏（"安全+网络" 下方）中的 "访问键"。

¥Click "Access keys" in the left sidebar (under "Security + networking").
在 "key1" 下查找 "连接字符串" 标题。在标题下方的行中，单击 "展示" 以显示密钥。单击复制图标或手动复制密钥，并将其存储在安全的地方。

¥Look for the "Connection string" title under "key1". In the row below the title, click "Show" to reveal the key. Click the copy icon or manually copy the key, storing it in a safe place.

Connection string "Show" button

容器设置

¥Container Setup

单击左侧边栏（"数据存储" 下方）中的 "容器"。

¥Click "Containers" in the left sidebar (under "Data storage").
点击 "* 容器"

¥Click "+ Container"
选择以下选项：

¥Select the following options:

输入令人难忘的 "名称"（上次测试时为 "sheetjs-container"）

¥Type a memorable "Name" ("sheetjs-container" when last tested)

单击 "创造" 创建容器。

¥Click "Create" to create the container.

项目设置

¥Project Setup

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

¥Create a new project folder:

mkdir SheetJSBlob
cd SheetJSBlob
npm init -y

安装依赖：

¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @azure/storage-blob

复制 SheetJSReadFromAzure.mjs code block 并保存到 SheetJSReadFromAzure.mjs。

¥Copy the SheetJSReadFromAzure.mjs code block and save to SheetJSReadFromAzure.mjs.
复制 SheetJSWriteToAzure.mjs code block 并保存到 SheetJSWriteToAzure.mjs。

¥Copy the SheetJSWriteToAzure.mjs code block and save to SheetJSWriteToAzure.mjs.
编辑 SheetJSReadFromAzure.mjs 和 SheetJSWriteToAzure.mjs：

¥Edit both SheetJSReadFromAzure.mjs and SheetJSWriteToAzure.mjs:

将 connStr 值替换为步骤 8 中的连接字符串

¥Replace the connStr value with the connection string from Step 8
将 containerName 值替换为步骤 11 中的容器名称

¥Replace the containerName value with the container name from Step 11

测试

¥Test

写入演示创建一个简单的工作簿，生成 NodeJS 缓冲区，并将缓冲区上传到 Azure Blob 存储上名为 SheetJSBloblobber.xlsx 的文件。

¥The write demo creates a simple workbook, generates a NodeJS buffer, and uploads the buffer to a file named SheetJSBloblobber.xlsx on Azure Blob Storage.

读取演示获取 SheetJSBloblobber.xlsx 并显示数据。

¥The read demo fetches SheetJSBloblobber.xlsx and displays the data.

   | A | B | C | D | E | F | G |
---+---|---|---|---|---|---|---|
 1 | S | h | e | e | t | J | S |
 2 | 5 | 4 | 3 | 3 | 7 | 9 | 5 |

运行写入测试：

¥Run the write test:

node SheetJSWriteToAzure.mjs

这会将文件 SheetJSBloblobber.xlsx 写入容器。

¥This will write the file SheetJSBloblobber.xlsx to the container.

运行读取测试：

¥Run the read test:

node SheetJSReadFromAzure.mjs

它将获取上一步中创建的文件并显示 CSV 行。

¥It will fetch the file created in the previous step and display CSV rows.

S,h,e,e,t,J,S
5,4,3,3,7,9,5

登录 Azure 门户

¥Sign into the Azure Portal
在顶部搜索框中输入 "贮存"，然后单击 "存储账户"

¥Type "Storage" in the top search box and click "Storage accounts"
单击存储名称

¥Click on the name of the storage
在左侧边栏中，单击 "容器"。它将在 "数据存储" 下。

¥In the left sidebar, click "Containers". It will be under "Data storage".
单击表中容器的名称

¥Click on the name of the container in the table
验证该表是否显示 SheetJSBloblobber.xlsx：

¥Verify that the table shows SheetJSBloblobber.xlsx: