Skip to main content

单元格注释和注释

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 '97 - 2019 "Comments" float over the sheet and support styling.

Excel 365 introduced "Threaded Comments" which do not support rich text but do allow users to "reply". The original "Comments" were renamed to "Notes".

FormatsNotesCommentThreaded
XLSX / XLSM✕✔✔
XLSB✕RR
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.

The letter R (R) marks features parsed but not written in the format.

SheetJS Pro supports comment rich text and styling.

注释和注释是单元格注释。带有注释或注释的单元格在右上角标有小三角形或 ¬。

¥Comments and notes are cell annotations. Cells with comments or notes are marked with a small triangle or ¬ in the upper-right corner.

Excel 注释是独立的文本框，具有可调整的背景颜色并支持富文本。历史上，人们通过在现有评论的末尾添加文本来进行评论。

¥Excel notes are standalone text boxes with adjustable background colors and support for rich text. Historically people "replied" to comments by adding text to the end of existing comments.

Excel 注释是简单的文本框，允许用户输入纯文本。用户可以回复评论。

¥Excel comments are simple text boxes that allow users to enter plain text. Users can reply to comments.

以下屏幕截图显示了带有评论和注释的电子表格。

¥The following screenshot shows a spreadsheet with comments and a note.

  • 该注释与单元格 A1（带有红色三角形的单元格）关联。它具有绿色渐变背景填充。

    ¥The note is associated with cell A1 (the cell with the red triangle). It has a green gradient background fill.

  • 注释与单元格 A2（带有蓝色 ¬ 的单元格）相关联。有 2 条来自不同作者的评论。线程下方会出现一个 "回复" 框。

    ¥The comments are associated with cell A2 (the cell with the blue ¬). There are 2 comments from different authors. A "Reply" box appears below the thread.

Excel comments and notes

Google Sheets "notes" 目前不支持富文本或背景颜色。

¥Google Sheets "notes" do not currently support rich text or background colors.

Apple Numbers 支持 "comments"，但不支持 "notes"。

¥Apple Numbers supports "comments" but does not support "notes".

基本结构​

¥Basic Structure

单元格注释是存储在单元格对象的 c 数组中的对象。

¥Cell comments are objects stored in the c array of cell objects.

注释内容根据注释作者分为几部分。

¥The comment content is split into parts based on the comment author.

每个注释部分的 a 字段是注释的作者，t 字段是纯文本表示。

¥The a field of each comment part is the author of the comment and the t field is the plain text representation.

例如，以下代码片段将单元格注释附加到单元格 A1 中：

¥For example, the following snippet appends a cell comment into cell A1:

/* get cell A1, creating an empty cell if necessary */
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 作者限制

XLSB 对作者名称强制实现 54 个字符的限制。名称长度超过 54 个字符可能会导致其他格式出现问题。

¥XLSB enforces a 54 character limit on the Author name. Names longer than 54 characters may cause issues with other formats.

演示​

¥Demos

导出​

¥Export

Live Export Example (click to hide)

此示例创建一个小工作表，并在单元格 A1 中添加注释：

¥This example creates a small worksheet with a comment in cell 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>);
}

导入​

¥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 }}/>
  </> );
}

可见性​

¥Visibility

注释块的 hidden 属性指示注释可见性。如果设置为 true，则只有用户将鼠标悬停在注释上，注释才会可见。

¥The hidden property of the comment block indicates comment visibility. If set to true, the comment will not be visible until users hover over the comment.

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.

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>);
}

线索注释​

¥Threaded Comments

线索注释是带有作者元数据和父参考的纯文本注释片段。XLSX、XLSB 和 NUMBERS 文件支持它们。

¥Threaded comments are plain text comment snippets with author metadata and parent references. They are supported in XLSX, XLSB, and NUMBERS files.

要将注释标记为线索，每个注释部分必须具有真正的 T 属性：

¥To mark a comment as threaded, each comment part must have a true T property:

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});

没有与作者关联的 Active Directory 或 Office 365 元数据。

¥There is no Active Directory or Office 365 metadata associated with authors.

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> );
}