Column Properties列属性

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

By default, all columns in a workbook are "Visible" and have a standard width.默认情况下，工作簿中的所有列都是“可见”的，并具有标准宽度。

Formats	Width	Hidden Cols	Outline Level
XLSX/XLSM	✔	✔	✔
XLSB	✔	✔	✔
XLML	✔	✔	✕
BIFF8 XLS	✔	✔	✔
BIFF5 XLS	R	R	R
SYLK	✔	*	✕

Asterisks (*) mark formats that represent hidden columns with zero width. For example, there is no way to specify a custom column width and mark the column as hidden in the SYLK format.星号（*）标记表示零宽度隐藏列的格式。例如，无法指定自定义列宽并将该列标记为SYLK格式中的隐藏列。

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 column widths to accommodate longer formatted data or varying text sizes.许多电子表格工具支持调整列宽以适应较长格式的数据或不同的文本大小。

Some tools additionally support column grouping or "outlining". Excel displays outline levels above the grid.一些工具还支持列分组或“概述”。Excel在网格上方显示大纲级别。

SheetJS worksheet objects store column properties in the !cols field. It is expected to be an array of column metadata objects.SheetJS工作表对象将列属性存储在!cols字段中。它应该是列元数据对象的数组。

Excel Bugs

For most common formats (XLSX, XLS), widths are tied to font metrics, which are tied to Windows Scaling settings. In Windows 11, the Scale factor settings are found in "System" > "Display" > "Scale"对于大多数常见的格式（XLSX、XLS），宽度与字体度量挂钩，字体度量与Windows缩放设置挂钩。在Windows 11中，比例因子设置位于“系统”>“显示”>“缩放”

Column widths may appear different on other machines due to scaling.由于缩放，其他机器上的列宽可能会有所不同。

This is an issue with Excel.这是Excel的问题。

Demo演示

This example creates a workbook that includes custom column widths, hidden columns, and column outline levels.此示例创建一个工作簿，其中包括自定义列宽、隐藏列和列大纲级别。

Excel for Windows	Excel for Mac

Export Demo (click to show)

The table lists the assigned widths, outline levels and visibility settings.该表列出了指定的宽度、轮廓级别和可见性设置。

Result

Loading...

Live Editor

function SheetJColProps() { 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 = [ [ "Width" , 10, 20, 30, 40, 50, 20, 20, ], [ "Level" , 0, 1, 2, 3, 3, 1, 0, ], [ "Hidden" , 0, 0, 0, 0, 0, 0, 0, 1 ] ]; const ws = XLSX.utils.aoa_to_sheet(data); /* set column metadata */ ws["!cols"] = []; for(let i = 1; i <= 8; ++i) { const r = {}; if(data[0][i] != null) (ws["!cols"][i] = r).wpx = data[0][i]; if(data[1][i] != null) (ws["!cols"][i] = r).level = data[1][i]; if(data[2][i] != null) (ws["!cols"][i] = r).hidden = data[2][i]; } /* 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, `SheetJSColProps.${fmt}`, {cellStyles: true}); }; const fmts = ["xlsx", "xlsb", "xls", "slk"]; 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 SheetJColProps() {
  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 = [
      [ "Width"  , 10, 20, 30, 40, 50, 20, 20,   ],
      [ "Level"  ,  0,  1,  2,  3,  3,  1,  0,   ],
      [ "Hidden" ,  0,  0,  0,  0,  0,  0,  0, 1 ]
    ];
    const ws = XLSX.utils.aoa_to_sheet(data);
    /* set column metadata设置列元数据 */
    ws["!cols"] = [];
    for(let i = 1; i <= 8; ++i) {
      const r = {};
      if(data[0][i] != null) (ws["!cols"][i] = r).wpx = data[0][i];
      if(data[1][i] != null) (ws["!cols"][i] = r).level = data[1][i];
      if(data[2][i] != null) (ws["!cols"][i] = r).hidden = data[2][i];
    }

    /* 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, `SheetJSColProps.${fmt}`, {cellStyles: true});
  };

  const fmts = ["xlsx", "xlsb", "xls", "slk"];
  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函数

Column processing must be explicitly enabled!必须显式启用列处理！

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

Reading Files读取文件

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

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

Writing Files写入文件

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

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

Exporting Data导出数据

sheet_to_csv and 和sheet_to_json accept options. 接受选项。If the skipHidden option is set to true, hidden columns 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 !cols property in a sheet object stores column-level metadata. If present, it is expected to be an array of column objects.这个工作表对象中的!cols属性存储列级元数据。如果存在，则应为列对象的数组。

As explained in "Addresses and Ranges", SheetJS uses zero-indexed columns. 如“地址和范围”中所述，SheetJS使用零索引列。The column metadata for Excel column "T" is stored at index 19 of the !cols array.Excel列“T”的列元数据存储在!cols数组的索引19中。

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

This snippet checks the !cols array and the specific column object, creating them if they do not exist, before setting the hidden property of column "C":此片段检查!cols数组和特定的列对象，如果它们不存在，则创建它们，然后设置列“C”的hidden属性：

/* Excel column "C" -> SheetJS column index 2 == XLSX.utils.decode_col("C") */
var COL_INDEX = 2;

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

/* create column metadata object if it does not exist */
if(!ws["!cols"][COL_INDEX]) ws["!cols"][COL_INDEX] = {wch: 8};

/* set column to hidden */
ws["!cols"][COL_INDEX].hidden = true;

Column Widths列宽

Column widths can be specified in three ways:列宽可以通过三种方式指定：

Property	Description描述	Excel UI
wpx	Width in screen pixels屏幕像素宽度	Pixels
wch	"inner width" in MDW **	Width
width	"outer width" in MDW **

When resizing a column, Excel will show a tooltip:调整列大小时，Excel将显示工具提示：

wpx stores the "pixels" field (65 in the diagram) for certain computer and font settings.wpx为某些计算机和字体设置存储“像素”字段（图中为65）。

MDW (Max Digit Width) (click to show)

MDW

"MDW" stands for "Max Digit Width", the maximum width of the numeric characters (0, 1, ..., 9) using the first font specified in the file. “MDW”代表“最大数字宽度”，即使用文件中指定的第一种字体的数字字符（0、1、…、9）的最大宽度。For most common fonts and text scaling settings, this is the width of 0 measured in pixels.对于大多数常见的字体和文本缩放设置，这是以像素为单位测量的0的宽度。

Parsers will save the estimated pixel width of the 0 digit to the MDW property of the column object. It is always a positive integer.解析器将0位的估计像素宽度保存到列对象的MDW属性中。它总是一个正整数。

width

width is the distance from "gridline before the current column" to "gridline before the next column" divided by MDW and rounded to the nearest 1/256.是从“当前列之前的网格线”到“下一列之前的栅格线”的距离除以MDW并四舍五入到最接近的1/256。

wch

Table cells in Excel include 2 pixels of padding on each side. The vertical gridline is one pixel wide. In total, the width includes 5 pixels of padding.Excel中的表格单元格每侧包含2个像素的填充。垂直网格线为一个像素宽。总的来说，width包括5个像素的填充。

wch is the "inner width", calculated by subtracting the 5 pixels from width. wch is also measured in MDW units rounded to the nearest 1/256.是“内部宽度”，通过从width中减去5个像素来计算。wch也以MDW单位测量，四舍五入到最接近的1/256。

Diagram

The following diagram depicts the Excel box model and the relationship between width, wpx, MDW and the displayed grid:下图描述了Excel框模型以及width、wpx、MDW和显示网格之间的关系：

The distance between the two red lines is width * MDW = 15 pixels. That span includes one gridline width (1 pixel) and two padding blocks (2 pixels each).两条红线之间的距离为width * MDW = 15个像素。该跨度包括一个网格线宽度（1个像素）和两个填充块（每个2个像素）。

The space available for content is wch * MDW = 15 - 5 = 10 pixels.可用于内容的空间为wch * MDW = 15 - 5 = 10像素。

The following snippet sets the width of column "C" to 50 pixels:以下代码段将列“C”的宽度设置为50像素：

const COL_WIDTH = 50;

/* Excel column "C" -> SheetJS column index 2 == XLSX.utils.decode_col("C") */
var COL_INDEX = 2;

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

/* create column metadata object if it does not exist */
if(!ws["!cols"][COL_INDEX]) ws["!cols"][COL_INDEX] = {wch: 8};

/* set column width */
ws["!cols"][COL_INDEX].wpx = COL_WIDTH;

Column Visibility列可见性

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

The following snippet hides column "D":以下代码段隐藏列“D”：

/* Excel column "D" -> SheetJS column index 3 == XLSX.utils.decode_col("D") */
var COL_INDEX = 3;

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

/* create column metadata object if it does not exist */
if(!ws["!cols"][COL_INDEX]) ws["!cols"][COL_INDEX] = {wch: 8};

/* set column to hidden */
ws["!cols"][COL_INDEX].hidden = true;

Outline Levels大纲级别

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

The Excel UI displays outline levels above the row 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 column "F" to Excel 2 / SheetJS 1:以下代码段将列“F”的级别设置为Excel 2/SheetJS 1：

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

/* Excel column "F" -> SheetJS column index 5 == XLSX.utils.decode_col("F") */
var COL_INDEX = 5;

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

/* create column metadata object if it does not exist */
if(!ws["!cols"][COL_INDEX]) ws["!cols"][COL_INDEX] = {wch: 8};

/* set level */
ws["!cols"][COL_INDEX].level = LEVEL;

Grouping Columns对列进行分组

Applications treat consecutive columns with the same level as part of a "group".应用程序将具有相同级别的连续列视为“组”的一部分。

The "Group" command typically increments the level of each column in the range:“组”命令通常会增加范围内每列的级别：

/* start_col and end_col are SheetJS 0-indexed column indices */
function grouper(ws, start_col, end_col) {
  /* create !cols array if it does not exist */
  if(!ws["!cols"]) ws["!cols"] = [];
  /* loop over every column index */
  for(var i = start_col; i <= end_col; ++i) {
    /* create column metadata object if it does not exist */
    if(!ws["!cols"][i]) ws["!cols"][i] = {wch: 8};
    /* increment level */
    ws["!cols"][i].level = 1 + (ws["!cols"][i].level || 0);
  }
}

The "Ungroup" command typically decrements the level of each column in the range:“解组”命令通常会降低范围内每列的级别：

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

Grouping Symbol分组符号

By default, Excel displays the group collapse button on the column after the data. In the UI, this option is named "Summary columns to right of detail".默认情况下，Excel在数据后面的列上显示组折叠按钮。在UI中，此选项命名为“细节右侧的摘要列”。

SheetJS exposes this option in the left property of the "!outline" property of worksheet objects. SheetJS在工作表对象的"!outline"属性的left属性中公开此选项。Setting this property to true effectively "unchecks" the "Summary columns to right of detail" option in Excel:将此属性设置为true可以有效地“取消选中”Excel中的“明细右侧的摘要列”选项：

if(!ws["outline"]) ws["!outline"] = {};
ws["!outline"].left = true; // show summary to left of detail

Implementation Details实施细节

Details (click to show)

Three Width Types三种宽度类型

There are three different width types corresponding to the three different ways spreadsheets store column widths:有三种不同的宽度类型，对应于电子表格存储列宽的三种不同方式：

SYLK and other plain text formats use raw character count. Contemporaneous tools like Visicalc and Multiplan were character based. Since the characters had the same width, it sufficed to store a count. This tradition was continued into the BIFF formats.SYLK和其他纯文本格式使用原始字符计数。像Visicalc和Multiplan这样的当代工具都是基于角色的。由于字符具有相同的宽度，因此存储一个计数就足够了。这一传统延续到BIFF格式中。

SpreadsheetML (2003) tried to align with HTML by standardizing on screen pixel count throughout the file. Column widths, row heights, and other measures use pixels. When the pixel and character counts do not align, Excel rounds values.SpreadsheetML(2003)试图通过标准化整个文件的屏幕像素数来与HTML对齐。列宽、行高和其他度量值使用像素。当像素和字符计数不对齐时，Excel会舍入值。

XLSX internally stores column widths in a nebulous "Max Digit Width" form. The Max Digit Width is the width of the largest digit when rendered (generally the "0" character is the widest). The internal width must be an integer multiple of the width divided by 256. XLSX内部以模糊的“最大数字宽度”形式存储列宽。最大数字宽度是渲染时最大数字的宽度（通常“0”字符最宽）。内部宽度必须是宽度除以256的整数倍。ECMA-376 describes a formula for converting between pixels and the internal width. This represents a hybrid approach.ECMA-376描述了用于在像素和内部宽度之间进行转换的公式。这代表了一种混合方法。

Read functions attempt to populate all three properties. Write functions will try to cycle specified values to the desired type. 读取函数试图填充所有三个属性。写入函数将尝试将指定的值循环到所需的类型。In order to avoid potential conflicts, manipulation should delete the other properties first. 为了避免潜在的冲突，操作应该首先删除其他属性。For example, when changing the pixel width, delete the wch and width properties.例如，在更改像素宽度时，请删除wch和width属性。

Column Width Priority列宽优先级

Even though all of the information is made available, writers are expected to follow the priority order:尽管所有信息都已提供，但作者仍应遵循以下优先顺序：

1) use width field if available如果可用，请使用width字段

2) use wpx pixel width if available如果可用，请使用wpx像素宽度

3) use wch character count if available如果可用，请使用wch字符计数