行属性

File Format Support (click to show)

By default, all rows in a workbook are "Visible" and have a standard height.

Formats	Height	Hidden Rows	Outline Level
XLSX/XLSM	✔	✔	✔
XLSB	✔	✔	✔
XLML	✔	✔	✕
BIFF8 XLS	R	R	R
BIFF5 XLS	R	R	R
SYLK	✔	*	✕
ODS / FODS / UOS	+	+	+

Asterisks (*) mark formats that represent hidden rows with zero height. For example, there is no way to specify a custom row height and mark that the row is hidden in the SYLK format.

Plus (+) marks formats with limited support. ODS supports specifying row heights in many units of measure. SheetJS supports some but not all ODS units.

X (✕) marks features that are not supported by the file formats. For example, the SpreadsheetML 2003 (XLML) file format does not support outline levels.

许多电子表格工具支持调整行高以适应多行数据或不同的文本大小。

¥Many spreadsheet tools support adjusting row heights to accommodate multiple lines of data or varying text sizes.

有些工具还支持行分组或 "outlining"。Excel 在网格左侧显示行轮廓级别。

¥Some tools additionally support row grouping or "outlining". Excel displays row outline levels to the left of the grid.

SheetJS 工作表对象将行属性存储在 !rows 字段中。它应该是行元数据对象的数组。

¥SheetJS worksheet objects store row properties in the !rows field. It is expected to be an array of row metadata objects.

演示

¥Demo

此示例创建一个工作簿，其中包括自定义行高、隐藏行和行大纲级别。

¥This example creates a workbook that includes custom row heights, hidden rows, and row outline levels.

Excel for Windows	Excel for Mac

Export Demo (click to show)

The table lists the assigned heights, outline levels and visibility settings.

Result

Loading...

Live Editor

function SheetJSRowProps() {
  const [ws, setWS] = React.useState();
  const [__html, setHTML] = React.useState("");
  const fmt = React.useRef(null);

  /* when the page is loaded, create worksheet and show table */
  React.useEffect(() => {
    /* Create worksheet from simple data */
    const data = [
      { Height: 20, Unit: "px", Level: 0 }, { Height: 25, Unit: "pt", Level: 1 },
      { Height: 30, Unit: "px", Level: 2 }, { Height: 35, Unit: "pt", Level: 3 },
      { Height: 25, Unit: "pt", Level: 3 }, { Height: 15, Unit: "px", Level: 1 },
      { Height: 10, Unit: "pt", Level: 0 }, { Hidden: true }
    ];
    const ws = XLSX.utils.json_to_sheet(data);
    /* set row metadata */
    ws["!rows"] = [];
    data.forEach((row, i) => {
      const r = {};
      if(row.Level) (ws["!rows"][i+1] = r).level = row.Level;
      if(row.Unit == "px") (ws["!rows"][i+1] = r).hpx = row.Height || 0;
      if(row.Unit == "pt") (ws["!rows"][i+1] = r).hpt = row.Height || 0;
      if(row.Hidden) (ws["!rows"][i+1] = r).hidden = true;
    });

    /* save worksheet object for the export */
    setWS(ws);
    /* generate the HTML table */
    setHTML(XLSX.utils.sheet_to_html(ws));
  }, []);

  const xport = (fmt) => {
    /* Export to file (start a download) */
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Formats");
    XLSX.writeFile(wb, `SheetJSRowProps.${fmt}`, {cellStyles: true});
  };

  const fmts = ["xlsx", "xlsb", "xls", "slk", "ods"];
  return ( <>
    <b>File format: </b>
    <select ref={fmt}>{fmts.map(f=>(<option value={f}>{f}</option>))}</select>
    <br/><button onClick={()=>xport(fmt.current.value)}><b>Export!</b></button>
    <div dangerouslySetInnerHTML={{__html}}/>
  </> );
}

函数

¥Functions

必须显式启用行处理！

¥Row processing must be explicitly enabled!

创建工作表对象的函数不保证生成 !rows 数组。不保证写入者导出行元数据。

¥Functions creating worksheet objects are not guaranteed to generate the !rows array. Writers are not guaranteed to export row metadata.

读取文件

¥Reading Files

read 和 readFile 接受选项参数。cellStyles 选项必须设置为 true 才能生成行属性：

¥read and readFile accept an options argument. The cellStyles option must be set to true to generate row properties:

var wb = XLSX.read(data, {/* ... other options , */ cellStyles: true});

写入文件

¥Writing Files

write 和 writeFile 接受选项参数。cellStyles 选项必须设置为 true 才能导出行属性：

¥write and writeFile accept an options argument. The cellStyles option must be set to true to export row properties:

XLSX.writeFile(wb, "SheetJSRowProps.xlsx", {/* ...opts , */ cellStyles: true});

导入 HTML 表格

¥Importing HTML Tables

table_to_book 和 table_to_sheet 处理 HTML DOM TABLE 元素。

¥table_to_book and table_to_sheet process HTML DOM TABLE elements.

通过将 CSS display 属性设置为 none，可以将各个表行（TR 元素）标记为隐藏。

¥Individual table rows (TR elements) can be marked as hidden by setting the CSS display property to none.

默认情况下，隐藏行被导入并适当地标记为隐藏：

¥By default, hidden rows are imported and appropriately marked as hidden:

/* generate worksheet from first table, preserving hidden rows */
var tbl = document.getElementsByTagName("TABLE")[0];
var ws = XLSX.utils.table_to_sheet(tbl);

如果 display 选项设置为 true，隐藏行将被跳过：

¥If the display option is set to true, hidden rows will be skipped:

/* generate worksheet from first table, omitting hidden rows */
var tbl = document.getElementsByTagName("TABLE")[0];
var ws = XLSX.utils.table_to_sheet(tbl, {display: true})

导出数据

¥Exporting Data

sheet_to_csv 和 sheet_to_json 接受选项。如果 skipHidden 选项设置为 true，则不会导出隐藏行：

¥sheet_to_csv and sheet_to_json accept options. If the skipHidden option is set to true, hidden rows will not be exported:

var ws = wb.Sheets[wb.SheetNames[0]]; // first worksheet
var csv = XLSX.utils.sheet_to_csv(ws, {/* ...opts, */ skipHidden: true});

贮存

¥Storage

工作表对象中的 !rows 属性存储行级元数据。如果存在，则它应该是行对象的数组。

¥The !rows property in a sheet object stores row-level metadata. If present, it is expected to be an array of row objects.

如 "地址和范围" 中所述，SheetJS 使用零索引行。Excel 第 20 行的行元数据存储在 !rows 数组的索引 19 处。

¥As explained in "Addresses and Ranges", SheetJS uses zero-indexed rows. The row metadata for Excel row 20 is stored at index 19 of the !rows array.

执行操作时，强烈建议测试行结构是否存在。

¥When performing operations, it is strongly recommended to test for the existence of the row structure.

此代码片段在设置第三行的 hidden 属性之前检查 !rows 数组和特定的行对象，如果它们不存在则创建它们：

¥This snippet checks the !rows array and the specific row object, creating them if they do not exist, before setting the hidden property of the third row:

/* Excel third row -> SheetJS row index 3 - 1 = 2 */
var ROW_INDEX = 2;

/* create !rows array if it does not exist */
if(!ws["!rows"]) ws["!rows"] = [];

/* create row metadata object if it does not exist */
if(!ws["!rows"][ROW_INDEX]) ws["!rows"][ROW_INDEX] = {hpx: 20};

/* set row to hidden */
ws["!rows"][ROW_INDEX].hidden = true;

行高

¥Row Heights

行高可以通过两种方式指定：

¥Row heights can be specified in two ways:

属性	描述
hpx	屏幕像素高度
hpt	高度（以磅为单位）

以下代码片段将第三行的高度设置为 50 像素：

¥The following snippet sets the height of the third row to 50 pixels:

const ROW_HEIGHT = 50;

/* Excel third row -> SheetJS row index 3 - 1 = 2 */
const ROW_INDEX = 2;

/* create !rows array if it does not exist */
if(!ws["!rows"]) ws["!rows"] = [];

/* create row metadata object if it does not exist */
if(!ws["!rows"][ROW_INDEX]) ws["!rows"][ROW_INDEX] = {hpx: ROW_HEIGHT};

/* set row height */
ws["!rows"][ROW_INDEX].hpx = ROW_HEIGHT;

行可见性

¥Row Visibility

hidden 属性控制可见性。

¥The hidden property controls visibility.

以下代码片段隐藏第四行：

¥The following snippet hides the fourth row:

/* Excel fourth row -> SheetJS row index 4 - 1 = 3 */
var ROW_INDEX = 3;

/* create !rows array if it does not exist */
if(!ws["!rows"]) ws["!rows"] = [];

/* create row metadata object if it does not exist */
if(!ws["!rows"][ROW_INDEX]) ws["!rows"][ROW_INDEX] = {hpx: 20};

/* set row to hidden */
ws["!rows"][ROW_INDEX].hidden = true;

大纲级别

¥Outline Levels

level 属性控制大纲级别/分组。预计为 0 和 7 之间的数字（含 0 和 7）。

¥The level property controls outline level / grouping. It is expected to be a number between 0 and 7 inclusive.

Excel UI 在列标签旁边显示大纲级别。应用中显示的基准级别为 1。

¥The Excel UI displays outline levels next to the column labels. The base level shown in the application is 1.

SheetJS 是零索引的：默认（基础）级别为 0。

¥SheetJS is zero-indexed: the default (base) level is 0.

以下代码片段将第六行的级别设置为 Excel 2 / SheetJS 1：

¥The following snippet sets the level of the sixth row to Excel 2 / SheetJS 1:

/* Excel level 2 -> SheetJS level 2 - 1 = 1 */
var LEVEL = 1;

/* Excel sixth row -> SheetJS row index 6 - 1 = 5 */
var ROW_INDEX = 2;

/* create !rows array if it does not exist */
if(!ws["!rows"]) ws["!rows"] = [];

/* create row metadata object if it does not exist */
if(!ws["!rows"][ROW_INDEX]) ws["!rows"][ROW_INDEX] = {hpx: 20};

/* set level */
ws["!rows"][ROW_INDEX].level = LEVEL;

对行进行分组

¥Grouping Rows

应用将具有相同级别的连续行视为 "group" 的一部分。

¥Applications treat consecutive rows with the same level as part of a "group".

"群组" 命令通常会增加范围内每行的级别：

¥The "Group" command typically increments the level of each row in the range:

/* start_row and end_row are SheetJS 0-indexed row indices */
function gruppieren(ws, start_row, end_row) {
  /* create !rows array if it does not exist */
  if(!ws["!rows"]) ws["!rows"] = [];
  /* loop over every row index */
  for(var i = start_row; i <= end_row; ++i) {
    /* create row metadata object if it does not exist */
    if(!ws["!rows"][i]) ws["!rows"][i] = {hpx: 20};
    /* increment level */
    ws["!rows"][i].level = 1 + (ws["!rows"][i].level || 0);
  }
}

"取消分组" 命令通常会递减范围内每行的级别：

¥The "Ungroup" command typically decrements the level of each row in the range:

/* start_row and end_row are SheetJS 0-indexed row indices */
function dissocier(ws, start_row, end_row) {
  /* create !rows array if it does not exist */
  if(!ws["!rows"]) ws["!rows"] = [];
  /* loop over every row index */
  for(var i = start_row; i <= end_row; ++i) {
    /* if row metadata does not exist, the level is zero -> skip */
    if(!ws["!rows"][i]) continue;
    /* if row level is not specified, the level is zero -> skip */
    if(!ws["!rows"][i].level) continue;
    /* decrement level */
    --ws["!rows"][i].level;
  }
}

分组符号

¥Grouping Symbol

默认情况下，Excel 在数据后面的行上显示组折叠按钮。在 UI 中，这是通过选项 "详细信息下方的摘要行" 进行调整的。

¥By default, Excel displays the group collapse button on the row after the data. In the UI, this is adjusted by the option "Summary rows below detail".

SheetJS 在工作表对象的 "!outline" 属性的 above 属性中公开此选项。将此属性设置为 true 可以有效地 "unchecks" Excel 中的 "详细信息下方的摘要行" 选项：

¥SheetJS exposes this option in the above property of the "!outline" property of worksheet objects. Setting this property to true effectively "unchecks" the "Summary rows below detail" option in Excel:

if(!ws["outline"]) ws["!outline"] = {};
ws["!outline"].above = true; // show summary rows above detail

实现细节

¥Implementation Details

Details (click to show)

Excel internally stores row heights in points. The default resolution is 72 DPI or 96 PPI, so the pixel and point size should agree. For different resolutions they may not agree, so the library separates the concepts.

Even though all of the information is made available, writers are expected to follow the priority order:

1. use hpx pixel height if available

2. use hpt point height if available