Cell Comments单元格注释

File Format Support文件格式支持 (click to show)

Comments and Notes have evolved over the years.多年来，注释和注释不断演变。

Excel 2.0 - '95 "Notes" were displayed in a master list.Excel 2.0-'95“备注”显示在主列表中。

Excel '97 - 2019 "Comments" float over the sheet and support styling.Excel’97-2019“注释”浮动在工作表上并支持样式。

Excel 365 introduced "Threaded Comments" which do not support rich text but do allow users to "reply". The original "Comments" were renamed to "Notes".Excel 365引入了“线程注释”，它不支持富格文本，但允许用户“回复”。原来的“注释”被重命名为“注释”。

Formats	Notes	Comment	Threaded
XLSX / XLSM	✕	✔	✔
XLSB	✕	R	R
NUMBERS	✕	✕	✔
XLS (BIFF8)	✕	✔	✕
XLML	✕	✔	✕
ODS / FODS / UOS	✕	✔	✕
SYLK	✔	✕	✕
XLS (BIFF5)	✔	✕	✕
XLS (BIFF 2/3/4)	✔	✕	✕

X (✕) marks features that are not supported by the file formats. For example, the NUMBERS file format supports plaintext threaded comments but does not support Excel styled comments or Excel legacy notes.标记文件格式不支持的功能。例如，NUMBERS文件格式支持明文螺纹注释，但不支持Excel样式的注释或Excel旧注释。

The letter R (R) marks features parsed but not written in the format.字母R(R)标记已解析但未按格式编写的特征。

SheetJS Pro supports comment rich text and styling.支持注释丰富的文本和样式。

Basic Structure基本结构

Cell comments are objects stored in the c array of cell objects.单元格注释是存储在单元格对象的c数组中的对象。

The comment content is split into parts based on the comment author.注释内容根据注释作者分为多个部分。

The a field of each comment part is the author of the comment and the t field is the plain text representation.每个注释部分的a字段是注释的作者，t字段是纯文本表示。

For example, the following snippet appends a cell comment into cell A1:例如，以下代码段将单元格注释附加到单元格A1中：

/* get cell A1, creating an empty cell if necessary获取单元格A1，必要时创建一个空单元格 */
var cell = ws["A1"];
if(!ws["A1"]) ws["A1"] = { t: "z" };

/* create comment array if it does not exist如果注释数组不存在，则创建该数组 */
if(!cell.c) cell.c = [];

/* create a comment part创建注释部分 */
var comment_part = {
  a: "SheetJS",
  t: "I'm a little comment, short and stout!"
};

/* Add comment part to the comment array将注释部分添加到注释数组 */
cell.c.push(comment_part);

XLSB Author limits

XLSB enforces a 54 character limit on the Author name. Names longer than 54 characters may cause issues with other formats.XLSB对作者名称强制执行54个字符的限制。超过54个字符的名称可能会导致其他格式出现问题。

Demos演示文档

Export

Live Export Example (click to hide)

This example creates a small worksheet with a comment in cell A1:本例在单元格A1中创建一个带有注释的小工作表：

Result

Loading...

Live Editor

function SheetJSComments1() { return (<button onClick={() => { var ws = XLSX.utils.aoa_to_sheet([["SheetJS"]]); if(!ws.A1.c) ws.A1.c = []; ws.A1.c.push({a:"SheetJS", t:"I'm a little comment, short and stout!"}); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSComments1.xlsx"); }}>Click me to generate a sample file</button>); }

function SheetJSComments1() {
  return (<button onClick={() => {
    var ws = XLSX.utils.aoa_to_sheet([["SheetJS"]]);

    if(!ws.A1.c) ws.A1.c = [];
    ws.A1.c.push({a:"SheetJS", t:"I'm a little comment, short and stout!"});

    var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSComments1.xlsx");
  }}>Click me to generate a sample file</button>);
}

Import进口

Live Import Example (click to show)

This example displays every comment in the workbook:此示例显示工作簿中的每个注释：

Result

Loading...

Live Editor

function SheetJSParseComments(props) { const [__html, setHTML] = React.useState(""); return ( <> <input type="file" onChange={async(e) => { /* parse workbook */ const file = e.target.files[0]; const data = await file.arrayBuffer(); const wb = XLSX.read(data); const html = []; wb.SheetNames.forEach(n => { var ws = wb.Sheets[n]; if(!ws) return; var ref = XLSX.utils.decode_range(ws["!ref"]); for(var R = 0; R <= ref.e.r; ++R) for(var C = 0; C <= ref.e.c; ++C) { var addr = XLSX.utils.encode_cell({r:R,c:C}); if(!ws[addr] || !ws[addr].c) continue; var comments = ws[addr].c; if(!comments.length) continue; var threaded = !!comments[0].T; var msg = comments.map(c => c.t).join(threaded ? "\n" : ""); console.log(comments); html.push(`${n}:${addr}:${+!!threaded}:${msg}`); } }); setHTML(html.join("\n")); }}/> <pre dangerouslySetInnerHTML={{ __html }}/> </> ); }

function SheetJSParseComments(props) {
  const [__html, setHTML] = React.useState("");

  return ( <>
    <input type="file" onChange={async(e) => {
      /* parse workbook */
      const file = e.target.files[0];
      const data = await file.arrayBuffer();
      const wb = XLSX.read(data);

      const html = [];
      wb.SheetNames.forEach(n => {
        var ws = wb.Sheets[n]; if(!ws) return;
        var ref = XLSX.utils.decode_range(ws["!ref"]);
        for(var R = 0; R <= ref.e.r; ++R) for(var C = 0; C <= ref.e.c; ++C) {
          var addr = XLSX.utils.encode_cell({r:R,c:C});
          if(!ws[addr] || !ws[addr].c) continue;
          var comments = ws[addr].c;
          if(!comments.length) continue;
          var threaded = !!comments[0].T;
          var msg = comments.map(c => c.t).join(threaded ? "\n" : "");
          console.log(comments);
          html.push(`${n}:${addr}:${+!!threaded}:${msg}`);
        }
      });
      setHTML(html.join("\n"));
    }}/>
    <pre dangerouslySetInnerHTML={{ __html }}/>
  </> );
}

Visibility可见性

The hidden property of the comment block indicates comment visibility. 注释块的hidden属性表示注释可见性。If set to true, the comment will not be visible until users hover over the comment.如果设置为true，则在用户将鼠标悬停在注释上之前，该注释将不可见。

if(!cell.c) cell.c = [];
cell.c.hidden = true;
cell.c.push({a:"SheetJS", t:"This comment will be hidden"});

Live Example (click to show)

The following demo creates a worksheet with two comments. The comment in cell A1 will be visibile and the comment in cell A2 will be hidden.下面的演示创建了一个包含两条注释的工作表。单元格A1中的注释是可见的，单元格A2中的注释将被隐藏。

Result

Loading...

Live Editor

function SheetJSComments2() { return (<button onClick={() => { var ws = XLSX.utils.aoa_to_sheet([["SheetJS"], [5433795]]); if(!ws.A1.c) ws.A1.c = []; ws.A1.c.push({a:"SheetJS", t:"This comment is visible"}); if(!ws.A2.c) ws.A2.c = []; ws.A2.c.hidden = true; ws.A2.c.push({a:"SheetJS", t:"This comment will be hidden"}); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSComments2.xlsx"); }}>Click me to generate a sample file</button>); }

function SheetJSComments2() {
  return (<button onClick={() => {
    var ws = XLSX.utils.aoa_to_sheet([["SheetJS"], [5433795]]);

    if(!ws.A1.c) ws.A1.c = [];
    ws.A1.c.push({a:"SheetJS", t:"This comment is visible"});

    if(!ws.A2.c) ws.A2.c = [];
    ws.A2.c.hidden = true;
    ws.A2.c.push({a:"SheetJS", t:"This comment will be hidden"});

    var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSComments2.xlsx");
  }}>Click me to generate a sample file</button>);
}

Threaded Comments线程注释

Threaded comments are plain text comment snippets with author metadata and parent references. They are supported in XLSX, XLSB, and NUMBERS files.线程注释是带有作者元数据和父引用的纯文本注释片段。XLSX、XLSB和NUMBERS文件支持它们。

To mark a comment as threaded, each comment part must have a true T property:要将注释标记为线程化，每个注释部分都必须具有一个true T属性：

if(!cell.c) cell.c = [];

var part1 = {
  a:"SheetJS",
  t:"This is threaded",
  T: true
};
cell.c.push(part1);

var part2 = {
  a:"JSSheet",
  t:"This is also threaded",
};
// The next line uses Object Spread syntax to add T: true
cell.c.push({ ...part2, T: true});

There is no Active Directory or Office 365 metadata associated with authors.没有与作者关联的活动目录或Office 365元数据。

Live Example (click to hide)

Result

Loading...

Live Editor

function SheetJSThreadedComments() { return ( <button onClick={() => { var ws = XLSX.utils.aoa_to_sheet([["SheetJS"], [5433795]]); /* normal comment */ if(!ws.A1.c) ws.A1.c = []; ws.A1.c.push({a:"SheetJS", t:"This is not threaded"}); /* threaded comment */ if(!ws.A2.c) ws.A2.c = []; /* add parts */ ws.A2.c.push({a:"SheetJS", t:"This is threaded", T: true}); var part = {a:"JSSheet", t:"This is also threaded"}; ws.A2.c.push({...part, T: true}); /* create workbook and export */ var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSThreadedComments.xlsx"); }}>Click me to generate a sample file</button> ); }

function SheetJSThreadedComments() {
  return ( <button onClick={() => {
    var ws = XLSX.utils.aoa_to_sheet([["SheetJS"], [5433795]]);

    /* normal comment */
    if(!ws.A1.c) ws.A1.c = [];
    ws.A1.c.push({a:"SheetJS", t:"This is not threaded"});

    /* threaded comment */
    if(!ws.A2.c) ws.A2.c = [];

    /* add parts */
    ws.A2.c.push({a:"SheetJS", t:"This is threaded", T: true});

    var part = {a:"JSSheet", t:"This is also threaded"};
    ws.A2.c.push({...part, T: true});

    /* create workbook and export */
    var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSThreadedComments.xlsx");
  }}>Click me to generate a sample file</button> );
}