列属性

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.

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 column widths to accommodate longer formatted data or varying text sizes.

有些工具还支持列分组或 "outlining"。Excel 在网格上方显示轮廓级别。

¥Some tools additionally support column grouping or "outlining". Excel displays outline levels above the grid.

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

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

Excel 错误

对于大多数常见格式（XLSX、XLS），宽度与字体规范相关，而字体规范又与 Windows 缩放设置相关。在 Windows 11 中，比例因子设置位于 "系统" > "展示" > "规模"

¥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"

由于缩放，列宽在其他计算机上可能会有所不同。

¥Column widths may appear different on other machines due to scaling.

这是 Excel 的问题。

¥This is an issue with 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

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

函数

¥Functions

必须显式启用列处理！

¥Column processing must be explicitly enabled!

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

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

读取文件

¥Reading Files

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

¥read and readFile accept an options argument. The cellStyles option must be set to true to generate column 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 column properties:

XLSX.writeFile(wb, "SheetSColProps.xlsx", {/* ...opts , */ cellStyles: 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 columns 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

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

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

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

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

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

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

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

¥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":

/* 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:

属性	描述	Excel 用户界面
`wpx`	屏幕像素宽度	像素
`wch`	MDW 中的 "内宽" **	宽度
`width`	MDW 中的 "外宽" **

调整列大小时，Excel 将显示工具提示：

¥When resizing a column, Excel will show a tooltip:

Resize tooltip

wpx 存储某些计算机和字体设置的 "pixels" 字段（图中的 65）。

¥wpx stores the "pixels" field (65 in the diagram) for certain computer and font settings.

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. For most common fonts and text scaling settings, this is the width of 0 measured in pixels.

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.

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.

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.

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.

Diagram

The following diagram depicts the Excel box model and the relationship between width, wpx, MDW and the displayed grid:

Box diagram

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

The space available for content is wch * MDW = 15 - 5 = 10 pixels.

以下代码片段将 "C" 列的宽度设置为 50 像素：

¥The following snippet sets the width of column "C" to 50 pixels:

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

hidden 属性控制可见性。

¥The hidden property controls visibility.

以下代码片段隐藏了 "D" 列：

¥The following snippet hides column "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

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 above the row labels. The base level shown in the application is 1.

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

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

以下代码片段将 "F" 列的级别设置为 Excel 2 / SheetJS 1：

¥The following snippet sets the level of column "F" to 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

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

¥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

默认情况下，Excel 在数据后面的列上显示组折叠按钮。在 UI 中，该选项名为 "详细信息右侧的摘要列"。

¥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".

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

¥SheetJS exposes this option in the left property of the "!outline" property of worksheet objects. Setting this property to true effectively "unchecks" the "Summary columns to right of detail" option in 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.

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.

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. ECMA-376 describes a formula for converting between pixels and the internal width. This represents a hybrid approach.

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.

Column Width Priority

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

use width field if available
use wpx pixel width if available
use wch character count if available

演示​

函数​

读取文件​

写入文件​

导出数据​

贮存​

列宽​

列可见性​

大纲级别​

对列进行分组​

分组符号​

实现细节​

演示

函数