Row Properties行属性

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.星号（*）标记表示零高度隐藏行的格式。例如，无法指定自定义行高度并标记该行以SYLK格式隐藏。

Plus (+) marks formats with limited support. ODS supports specifying row heights in many units of measure. SheetJS supports some but not all ODS units.加号（+）标记支持有限的格式。ODS支持以许多度量单位指定行高度。SheetJS支持一些但不是所有的ODS单元。

X (✕) marks features that are not supported by the file formats. For example, the SpreadsheetML 2003 (XLML) file format does not support outline levels.标记文件格式不支持的功能。例如，SpreadsheetML 2003(XLML)文件格式不支持大纲级别。

Many spreadsheet tools support adjusting row heights to accommodate multiple lines of data or varying text sizes.许多电子表格工具支持调整行高度以适应多行数据或不同的文本大小。

Some tools additionally support row grouping or "outlining". Excel displays row outline levels to the left of the grid.一些工具还支持行分组或“概述”。Excel在网格左侧显示行轮廓级别。

SheetJS worksheet objects store row properties in the !rows field. SheetJS工作表对象将行属性存储在中!rows字段。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 MacExcel 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); /* <del>set row metadata</del><ins>设置行元数据</ins> */ 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}}/> </> ); }

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生成HTML表 */
    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!必须显式启用行处理！

Functions creating worksheet objects are not guaranteed to generate the !rows array. Writers are not guaranteed to export row metadata.创建工作表对象的函数不能保证生成!rows数组。写入程序不能保证导出行元数据。

Reading Files读取文件

read and readFile accept an options argument. read和readFile接受一个options参数。The cellStyles option must be set to true to generate row properties:cellStyles选项必须设置为true才能生成行属性：

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

Writing Files写入文件

write and 和writeFile accept an options argument. 接受一个options参数。The cellStyles option must be set to true to export row properties:cellStyles选项必须设置为true才能导出行属性：

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

Importing HTML Tables导入HTML表

table_to_book and 和table_to_sheet process HTML DOM TABLE elements.处理HTML DOM TABLE元素。

Individual table rows (TR elements) can be marked as hidden by setting the CSS display property to none.通过将CSSdisplay属性设置为none，可以将单个表行（TR元素）标记为隐藏。

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

If the display option is set to true, hidden rows will be skipped:如果display选项设置为true，将跳过隐藏行：

/* 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 and sheet_to_json accept options. sheet_to_csv和sheet_to_json接受选项。If the skipHidden option is set to true, hidden rows will not be exported:如果skipHidden选项设置为true，则不会导出隐藏行：

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

Storage存储

The !rows property in a sheet object stores row-level metadata. 这个工作表对象中的!rows属性存储行级元数据。If present, it is expected to be an array of row objects.如果存在，则应为行对象的数组。

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.如“地址和范围”中所述，SheetJS使用零索引行。Excel行20的行元数据存储在的索引19中！行数组。

When performing operations, it is strongly recommended to test for the existence of the row structure.执行操作时，强烈建议测试行结构是否存在。

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:此片段检查!rows数组和特定的行对象，如果它们不存在，则创建它们，然后设置第三行的hidden属性：

/* 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:行高可以通过两种方式指定：

Property所有物	Description描述
hpx	Height in screen pixels以屏幕像素为单位的高度
hpt	Height in points高度（以点为单位）

The following snippet sets the height of the third row to 50 pixels:以下代码段将第三行的高度设置为50像素：

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行可见性

The hidden property controls visibility.hidden属性控制可见性。

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大纲级别

The level property controls outline level / grouping. level属性控制大纲级别/分组。It is expected to be a number between 0 and 7 inclusive.它应该是一个介于0和7之间（包括0和7）的数字。

The Excel UI displays outline levels next to the column labels. The base level shown in the application is 1.Excel UI在列标签旁边显示大纲级别。应用程序中显示的基本级别为1。

SheetJS is zero-indexed: the default (base) level is 0.SheetJS为零索引：默认（基本）级别为0。

The following snippet sets the level of the sixth row to Excel 2 / SheetJS 1:以下代码段将第六行的级别设置为Excel2/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分组行

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 indicesstart_row和end_row是SheetJS 0索引行索引 */
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 indicesstart_row和end_row是SheetJS 0索引行索引 */
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分组符号

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".默认情况下，Excel在数据后面的行上显示组折叠按钮。在用户界面中，通过“明细下方的汇总行”选项进行调整。

SheetJS exposes this option in the above property of the "!outline" property of worksheet objects. SheetJS在工作表对象的"!outline"属性的above属性中公开此选项。Setting this property to true effectively "unchecks" the "Summary rows below detail" option in Excel:将此属性设置为true有效地“取消选中”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. Excel在内部以点形式存储行高度。默认分辨率为72 DPI或96 PPI，因此像素和点的大小应该一致。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如果可用，请使用hpx像素高度

2) use hpt point height if available使用hpt点高度（如果可用）