VBA 和宏

File Format Support (click to show)

XLSX does not support macros. The XLSM file format is nearly identical to XLSX and supports macros.

Formats	Basic	Storage Representation
XLSM	✔	vbaProject.bin file in container
XLSX	✕	Not supported in format (use XLSM)
XLSB	✔	vbaProject.bin file in container
XLS	✔	Intercalated in CFB container

X (✕) marks features that are not supported by the file formats. There is no way to embed VBA in the XLSX format.

Visual Basic for Applications (VBA) 是嵌入在 Excel 中的脚本编写平台。用户可以在电子表格中包含用户定义的函数和宏代码。

¥Visual Basic for Applications (VBA) is a scripting platform embedded in Excel. Users can include user-defined functions and macro code within spreadsheets.

SheetJS 工作簿对象的 vbaraw 属性是一个编码数据 blob，其中包括 VBA 宏和其他元数据。

¥The vbaraw property of the SheetJS workbook object is an encoded data blob which includes the VBA macros and other metadata.

默认情况下，SheetJS read 和 readFile 方法不提取 VBA 元数据。如果 bookVBA 选项设置为 true，则创建 vbaraw blob。

¥The SheetJS read and readFile methods do not pull VBA metadata by default. If the bookVBA option is set to true, the vbaraw blob is created.

var workbook = XLSX.read(data, { bookVBA: true });
var encoded_vba_blob = workbook.vbaraw;

如果 vbaraw blob 存在于工作簿对象中并且输出文件格式支持宏，则 SheetJS write 和 writeFile 方法将保存 vbaraw blob。

¥The SheetJS write and writeFile methods will save the vbaraw blob if it is present in the workbook object and if the output file format supports macros.

workbook.vbaraw = encoded_vba_blob;
XLSX.writeFile(workbook, "SheetJSNewMacro.xlsm");

较新版本的 Excel 支持用于编写用户定义函数的新 JavaScript API。这些插件不存储在电子表格文件中。

¥Newer versions of Excel support a new JavaScript API for writing user-defined functions. Those addins are not stored in the spreadsheet files.

"Excel JavaScript API" 演示 涵盖了 API 中 SheetJS 库的使用。

¥The "Excel JavaScript API" demo covers usage of SheetJS libraries within the API.

vbaraw 属性存储原始字节。SheetJS 专业版 提供了一个特殊的组件，用于从 VBA blob 中提取宏文本、编辑 VBA 项目以及导出新的 VBA blob。

¥The vbaraw property stores raw bytes. SheetJS Pro offers a special component for extracting macro text from the VBA blob, editing the VBA project, and exporting new VBA blobs.

演示

¥Demos

导出演示重点关注 一个例子，其中包括以下用户定义函数：

¥The export demos focus on an example that includes the following user-defined functions:

Function GetFormulaA1(Cell As Range) As String
  GetFormulaA1 = Cell.Formula
End Function

Function GetFormulaRC(Cell As Range) As String
  GetFormulaRC = Cell.Formula2R1C1
End Function

复制宏

¥Copying Macros

下载示例文件后，演示会提取 VBA blob 并创建一个包含 VBA blob 的新工作簿。单击按钮创建文件并在支持 VBA 的电子表格编辑器中打开：

¥After downloading the sample file, the demo extracts the VBA blob and creates a new workbook including the VBA blob. Click the button to create the file and open in a spreadsheet editor that supports VBA:

Web Browser

Result

Loading...

Live Editor

function SheetJSVBAFormula() { return ( <button onClick={async () => {
  /* Extract VBA Blob from test file */
  const url = "/vba/SheetJSVBAFormula.xlsm";
  const raw_data = await (await fetch(url)).arrayBuffer();
  const blob = XLSX.read(raw_data, {bookVBA: true}).vbaraw;

  /* generate worksheet and workbook */
  const worksheet = XLSX.utils.aoa_to_sheet([
    ["Cell", "A1", "RC"],
    [
      {t:"n", f:"LEN(A1)"},      // A2
      {t:"s", f:"GetFormulaA1(A2)"},  // B2
      {t:"s", f:"GetFormulaRC(A2)"}   // C2
    ]
  ]);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

  /* add VBA blob to new workbook */
  workbook.vbaraw = blob;

  /* create an XLSM file and try to save to SheetJSVBANeu.xlsm */
  XLSX.writeFile(workbook, "SheetJSVBANeu.xlsm");
}}><b>Click to Generate file!</b></button> ); }

NodeJS

0. 安装依赖：

   ¥Install the dependencies:

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

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

   ¥Save the following script to generate_file.js:

generate_file.js

const XLSX = require("xlsx");
(async() => {
/* Extract VBA Blob from test file */
const url = "https://xlsx.nodejs.cn/vba/SheetJSVBAFormula.xlsm";
const raw_data = await (await fetch(url)).arrayBuffer();
const blob = XLSX.read(raw_data, {bookVBA: true}).vbaraw;

/* generate worksheet and workbook */
const worksheet = XLSX.utils.aoa_to_sheet([
  ["Cell", "A1", "RC"],
  [
    {t:"n", f:"LEN(A1)"},      // A2
    {t:"s", f:"GetFormulaA1(A2)"},  // B2
    {t:"s", f:"GetFormulaRC(A2)"}   // C2
  ]
]);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

/* add VBA blob to new workbook */
workbook.vbaraw = blob;

/* create an XLSM file and try to save to SheetJSVBANeu.xlsm */
XLSX.writeFile(workbook, "SheetJSVBANeu.xlsm");
})();

2. 运行脚本：

   ¥Run the script:

node generate_file.js

该脚本将生成 SheetJSVBANeu.xlsm。

¥This script will generate SheetJSVBANeu.xlsm.

提取 VBA Blob

¥Extracting VBA Blobs

要提取 blob，必须在 read 或 readFile 调用中设置 bookVBA: true。

¥To extract blobs, bookVBA: true must be set in the read or readFile call.

以下示例提取工作簿中嵌入的 VBA Blob：

¥The following example extracts the embedded VBA blob in a workbook:

Web Browser

Result

Loading...

Live Editor

function SheetJSExtractVBA(props) {
  const [msg, setMsg] = React.useState("Select a macro-enabled file");
  return ( <>
    <b>{msg}</b><br/>
    <input type="file" onChange={async(e) => {
      /* parse workbook with bookVBA: true */
      const wb = XLSX.read(await e.target.files[0].arrayBuffer(), {bookVBA: true});

      /* get vba blob */
      if(!wb.vbaraw) return setMsg("No VBA found!");
      const blob = wb.vbaraw;

      /* download to vbaProject.bin */
      setMsg("Attempting to download vbaProject.bin");
      const url = URL.createObjectURL(new Blob([blob]));
      const a = document.createElement("a");
      a.download = "vbaProject.bin"; a.href = url;
      document.body.appendChild(a); a.click();
      document.body.removeChild(a);
    }}/>
  </> );
}

NodeJS

0. 安装依赖：

   ¥Install the dependencies:

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

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

   ¥Save the following script to extract_vba.js:

extract_vba.js

const fs = require("fs"), XLSX = require("xlsx");
const wb = XLSX.readFile(process.argv[2], { bookVBA: true });
if(!wb.vbaraw) throw new Error("Could not find VBA blob!");
fs.writeFileSync("vbaProject.bin", wb.vbaraw);

2. 运行脚本：

   ¥Run the script:

node extract_vba.js SheetJSMacroEnabled.xlsm

该脚本将生成 vbaProject.bin。可以将其添加到新工作簿中。

¥This script will generate vbaProject.bin. It can be added to a new workbook.

导出 Blob

¥Exporting Blobs

为了确保编写者导出 VBA blob：

¥To ensure the writers export the VBA blob:

• 输出格式必须支持 VBA（xlsm 或 xlsb 或 xls 或 biff8）

  ¥The output format must support VBA (xlsm or xlsb or xls or biff8)

• 工作簿对象必须具有有效的 vbaraw 字段

  ¥The workbook object must have a valid vbaraw field

此示例使用 样本文件 中的 vbaProject.bin：

¥This example uses vbaProject.bin from the sample file:

Result

Loading...

Live Editor

function SheetJSVBAPrepared() { return ( <button onClick={async () => {
  /* Download prepared VBA blob */
  const url = "/vba/vbaProject.bin";
  const blob = new Uint8Array(await (await fetch(url)).arrayBuffer());

  /* generate worksheet and workbook */
  const worksheet = XLSX.utils.aoa_to_sheet([
    ["Cell", "A1", "RC"],
    [
      {t:"n", f:"LEN(A1)"},      // A2
      {t:"s", f:"GetFormulaA1(A2)"},  // B2
      {t:"s", f:"GetFormulaRC(A2)"}   // C2
    ]
  ]);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");

  /* add VBA blob to new workbook */
  workbook.vbaraw = blob;

  /* create an XLSM file and try to save to SheetJSVBAPreparedNeu.xlsm */
  XLSX.writeFile(workbook, "SheetJSVBAPreparedNeu.xlsm");
}}><b>Click to Generate file!</b></button> ); }

细节

¥Details

代号

¥Code Names

Excel 将使用 ThisWorkbook（或类似 DieseArbeitsmappe 的翻译）作为工作簿的默认代码名称。即使工作表名称已更改，每个工作表也将使用默认的 Sheet# 命名模式进行标识。

¥Excel will use ThisWorkbook (or a translation like DieseArbeitsmappe) as the default Code Name for the workbook. Each worksheet will be identified using the default Sheet# naming pattern even if the worksheet names have changed.

自定义工作簿代码名称将存储在 wb.Workbook.WBProps.CodeName 中。对于导出，分配属性将覆盖默认值。

¥A custom workbook code name will be stored in wb.Workbook.WBProps.CodeName. For exports, assigning the property will override the default value.

工作表和图表代码名称位于 wb.Workbook.Sheets[i].CodeName 处的工作表属性对象中。宏表和对话框表将被忽略。

¥Worksheet and Chartsheet code names are in the worksheet properties object at wb.Workbook.Sheets[i].CodeName. Macrosheets and Dialogsheets are ignored.

读取器和写入器保留代码名称，但在将 VBA blob 添加到不同的工作簿时必须手动设置它们。

¥The readers and writers preserve the code names, but they have to be manually set when adding a VBA blob to a different workbook.

宏表

¥Macrosheets

旧版本的 Excel 还支持存储自动化命令的非 VBA "macrosheet" 工作表类型。这些在 !type 属性设置为 "macro" 的对象中公开。

¥Older versions of Excel also supported a non-VBA "macrosheet" sheet type that stored automation commands. These are exposed in objects with the !type property set to "macro".

在幕后，Excel 将宏表视为普通工作表，并对函数表达式进行特殊解释。

¥Under the hood, Excel treats Macrosheets as normal worksheets with special interpretation of the function expressions.

检测工作簿中的宏

¥Detecting Macros in Workbooks

仅当存在宏时才会设置 vbaraw 字段。宏表将被显式标记。结合这两个检查会产生一个简单的函数：

¥The vbaraw field will only be set if macros are present. Macrosheets will be explicitly flagged. Combining the two checks yields a simple function:

function wb_has_macro(wb/*:workbook*/)/*:boolean*/ {
  if(!!wb.vbaraw) return true;
  const sheets = wb.SheetNames.map((n) => wb.Sheets[n]);
  return sheets.some((ws) => !!ws && ws['!type']=='macro');
}