Defined Names已定义的名称

File Format Support (click to show)

Defined names have evolved over the decades, with new features added over time:定义名称经过几十年的演变，随着时间的推移添加了新功能：

• "English" refers to defined names with English letters and numbers (ASCII)“英文”是指具有英文字母和数字（ASCII）的已定义名称
• "Unicode" refers to defined names with non-English characters.“Unicode”是指具有非英语字符的已定义名称。
• "Comment" refers to comments that can be attached to defined names.“注释”是指可以附加到已定义名称的注释。

Formats	English	Unicode	Comment
XLSX / XLSM	✔	✔	✔
XLSB	✔	✔	✔
XLS	✔	✔	✔
XLML	✔	✔
SYLK	✔	✕
ODS / FODS / UOS	✔	✔

X (✕) marks features that are not supported by the file formats. There is no way to specify a Unicode defined name in the SYLK format.标记文件格式不支持的功能。无法在SYLK格式中指定Unicode定义的名称。

wb.Workbook.Names is an array of defined name objects which have the keys:是已定义名称对象的数组，这些对象具有以下键：

Key	Name in app应用程序中的名称	Description描述
Sheet	"Scope"	Sheet Index (0 = first sheet) or null (Workbook)工作表索引（0=第一张工作表）或为空（工作簿）
Name	"Name"	Case-sensitive name. Standard rules apply区分大小写的名称。适用标准规则
Ref	"Refers To"	A1-Style Reference A1样式参考("Sheet1!$A$1:$D$20")
Comment	"Comment"	Comment (for supported file formats)注释（对于支持的文件格式）

Parsers do not always create the Names structure. Parsing and writing code should test for the existence of the defined names array before use:解析程序并不总是创建Names结构。在使用之前，分析和编写代码应测试是否存在已定义的名称数组：

/* ensure the workbook structure exists确保工作簿结构存在 */
if(!wb.Workbook) wb.Workbook = {};
if(!wb.Workbook.Names) wb.Workbook.Names = [];

/* add a new defined name添加新定义的名称 */
wb.Workbook.Names.push({ Name: "MyData", Ref: "Sheet1!$A$1:$A$2" });

Ranges范围

Defined name references in formulae are internally shifted to the cell address. For example, given the defined name公式中定义的名称引用在内部移动到单元格地址。例如，给定定义的名称

{ Name: "MyData", Ref: "Sheet1!A1:A2" } // no $ means relative reference

If D4 is set to =SUM(MyData):如果D4设置为=SUM(MyData)：

ws["D4"].f = "SUM(MyData)";

Spreadsheet software will translate the defined name range down to the cell. 电子表格软件将把定义的名称范围向下转换到单元格。Excel will try to calculate SUM(D4:D5) and assign to cell D4. This will elicit a circular reference error.Excel将尝试计算SUM(D4:D5)并分配给单元格D4。这将引发循环引用错误。

The recommended approach is to fix the rows and columns of the reference:建议的方法是固定引用的行和列：

{ Name: "MyData", Ref: "Sheet1!$A$1:$A$2" } // absolute reference

Scoped Defined Names作用域定义的名称

Excel allows two sheet-scoped defined names to share the same name. However, a sheet-scoped name cannot collide with a workbook-scope name. Workbook writers may not enforce this constraint.Excel允许两个工作表范围内定义的名称共享相同的名称。但是，工作表范围的名称不能与工作簿范围的名称冲突。工作簿编写者可能无法强制执行此约束。

The following snippet creates a worksheet-level defined name "Global" and a local defined name "Local" with distinct values for first and second sheets:以下代码段为第一张和第二张工作表创建了一个工作表级别定义的名称"Global"和一个本地定义的名称（具有不同值）"Local"：

/* "Global" workbook-level “Global”工作簿级别-> Sheet1 A1:A2 */
wb.Workbook.Names.push({ Name: "Global", Ref: "Sheet1!$A$1:$A$2" });

/* "Local" scoped to the first worksheet -> Sheet1 B1:B2 */
wb.Workbook.Names.push({ Name: "Local",  Ref: "Sheet1!$B$1:$B$2", Sheet: 0 });

/* "Local" scoped to the second worksheet“Local”范围为第二个工作表 -> Sheet1 C1:C2 */
wb.Workbook.Names.push({ Name: "Local",  Ref: "Sheet1!$C$1:$C$2", Sheet: 1 });

Live Demo现场演示

The following example creates 3 defined names:以下示例创建了3个定义的名称：

• "Global" is a workbook-level name that references Sheet1!$A$1:$A$2“Global”是一个工作簿级别的名称，它引用了Sheet1$A$1:$A$2
• "Local" in the first worksheet references Sheet1!$B$1:$B$2第一个工作表中的“Local”引用了Sheet1$B$1:$B$2
• "Local" in the second worksheet references Sheet1!$C$1:$C$2第二个工作表中的“Local”引用了Sheet1$C$1:$C$2

Both worksheets include formulae referencing "Local" and "Global". Since the referenced ranges are different, the expressions using "Local" will differ.这两个工作表都包含引用“本地”和“全局”的公式。由于引用的范围不同，使用“Local”的表达式也会不同。

Result

Loading...

Live Editor

/* The live editor requires this function wrapper */ function DefinedNameExport() { return ( <button onClick={() => { /* Create empty workbook */ var wb = XLSX.utils.book_new(); /* Create worksheet Sheet1 */ var ws1 = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6],["Global",0],["Local",0]]); XLSX.utils.book_append_sheet(wb, ws1, "Sheet1"); /* Create worksheet Sheet2 */ var ws2 = XLSX.utils.aoa_to_sheet([["Global",0],["Local",0]]); XLSX.utils.book_append_sheet(wb, ws2, "Sheet2"); /* Create defined names */ if(!wb.Workbook) wb.Workbook = {}; if(!wb.Workbook.Names) wb.Workbook.Names = []; /* "Global" workbook-level -> Sheet1 A1:A2 */ wb.Workbook.Names.push({ Name: "Global", Ref: "Sheet1!$A$1:$A$2" }); /* "Local" scoped to the first worksheet -> Sheet1 B1:B2 */ wb.Workbook.Names.push({ Name: "Local", Sheet: 0, Ref: "Sheet1!$B$1:$B$2" }); /* "Local" scoped to the second worksheet -> Sheet1 C1:C2 */ wb.Workbook.Names.push({ Name: "Local", Sheet: 1, Ref: "Sheet1!$C$1:$C$2" }); /* Create formulae */ ws1["B3"].f = "SUM(Global)"; // Sheet1 B3 =SUM(Global) 1 + 4 = 5 ws1["B4"].f = "SUM(Local)"; // Sheet1 B4 =SUM(Local) 2 + 5 = 7 ws2["B1"].f = "SUM(Global)"; // Sheet2 B1 =SUM(Global) 1 + 4 = 5 ws2["B2"].f = "SUM(Local)"; // Sheet2 B2 =SUM(Local) 3 + 6 = 9 /* Export to file (start a download) */ XLSX.writeFile(wb, "SheetJSDNExport.xlsx"); }}><b>Export XLSX!</b></button> ); }

/* The live editor requires this function wrapper实时编辑器需要此函数包装器 */
function DefinedNameExport() { return ( <button onClick={() => {
  /* Create empty workbook创建空工作簿 */
  var wb = XLSX.utils.book_new();

  /* Create worksheet Sheet1创建工作表Sheet1 */
  var ws1 = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6],["Global",0],["Local",0]]);
  XLSX.utils.book_append_sheet(wb, ws1, "Sheet1");

  /* Create worksheet Sheet2创建工作表Sheet2 */
  var ws2 = XLSX.utils.aoa_to_sheet([["Global",0],["Local",0]]);
  XLSX.utils.book_append_sheet(wb, ws2, "Sheet2");

  /* Create defined names创建定义的名称 */
  if(!wb.Workbook) wb.Workbook = {};
  if(!wb.Workbook.Names) wb.Workbook.Names = [];
  /* "Global" workbook-level -> Sheet1 A1:A2 */
  wb.Workbook.Names.push({ Name: "Global", Ref: "Sheet1!$A$1:$A$2" });
  /* "Local" scoped to the first worksheet -> Sheet1 B1:B2“Local”范围到第一个工作表->Sheet1 B1:B2 */
  wb.Workbook.Names.push({ Name: "Local", Sheet: 0, Ref: "Sheet1!$B$1:$B$2" });
  /* "Local" scoped to the second worksheet“本地”范围为第二个工作表 -> Sheet1 C1:C2 */
  wb.Workbook.Names.push({ Name: "Local", Sheet: 1, Ref: "Sheet1!$C$1:$C$2" });

  /* Create formulae创建公式 */
  ws1["B3"].f = "SUM(Global)"; // Sheet1 B3 =SUM(Global)  1 + 4 = 5
  ws1["B4"].f = "SUM(Local)";  // Sheet1 B4 =SUM(Local)   2 + 5 = 7
  ws2["B1"].f = "SUM(Global)"; // Sheet2 B1 =SUM(Global)  1 + 4 = 5
  ws2["B2"].f = "SUM(Local)";  // Sheet2 B2 =SUM(Local)   3 + 6 = 9

  /* Export to file (start a download)导出到文件（开始下载） */
  XLSX.writeFile(wb, "SheetJSDNExport.xlsx");
}}><b>Export XLSX!</b></button> ); }