合并单元格

File Format Support (click to show)

By default, no cells are merged. Merge metadata is ignored when exporting to a file format that does not support merge cells.

Formats store the actual contents of a merged cell in the "top-left" corner (first row and first column of the merge range). Some formats can hold data for cells that are covered by the merge range.

Formats	Merge	Covered
XLSX/XLSM	✔	✔
XLSB	✔	✔
XLML	✔	✔
BIFF8 XLS	✔	✔
ODS/FODS	✔	✔
NUMBERS	✔	✔
HTML	✔

There are multiple representations of merge cells in the NUMBERS file format. Writers use the simplified .TST.MergeRegionMapArchive representation. Parsers understand the classic form and the modern .TST.MergeOwnerArchive form.

现代电子表格软件通常允许用户将单元格块组合成一个单元。此单元可以跨越多列和多行。如下表所示，HTML TH 和 TD 元素使用 colspan 和 rowspan 属性来实现合并：

¥Modern spreadsheet software typically allow users to combine blocks of cells into a single unit. This unit can span multiple columns and rows. As shown in the following table, HTML TH and TD elements use colspan and rowspan attributes to effectuate merging:

This title spans four columns
SheetJS	supports	merge	cells

此功能在版本 0.20.3 中进行了扩展。强烈推荐给升级到最新版本。

¥This feature was expanded in version 0.20.3. It is strongly recommended to upgrade to the latest version.

贮存

¥Storage

工作表对象的 !merges 属性应为 SheetJS 范围对象的数组。每个范围对象对应于工作表中的合并范围。

¥The !merges property of the worksheet object is expected to be an array of SheetJS range objects. Each range object corresponds to a merged range in the worksheet.

以下代码片段创建跨越 A1:B2 的合并范围：

¥The following snippet creates a merge range spanning A1:B2 :

Merge the range A1:B2 in a worksheet
ws["!merges"] = [
  { s: { c: 0, r: 0 }, e: { c: 1, r: 1 } }  // A1:B2
];

不会自动检测到重叠合并！

¥Overlapping merges are not automatically detected!

范围

¥Range

decode_range 方法从 A1 样式范围字符串创建范围对象。

¥The decode_range method creates range objects from A1-style range strings.

以下代码片段创建跨越 A1:B2 的合并范围：

¥The following snippet creates a merge range spanning A1:B2 :

Merge the range A1:B2 in a worksheet
ws["!merges"] = [
  XLSX.utils.decode_range("A1:B2")
];

重叠

¥Overlap

将合并添加到现有工作簿时，强烈建议扫描合并数组并测试冲突：

¥When adding merges to an existing workbook, it is strongly recommended to scan the merges array and test for collisions:

Add a merged range to a worksheet
function sheet_add_merge(ws, range) {
  /* if `range` is a string, parse into a range object */
  var merge = typeof range == "string" ? XLSX.utils.decode_range(range) : range;

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

  /* check if the new merge collides with any existing merge */
  ws["!merges"].forEach(function(range) {
    if(merge.e.r < range.s.r) return;
    if(range.e.r < merge.s.r) return;
    if(merge.e.c < range.s.c) return;
    if(range.e.c < merge.s.c) return;
    throw new Error(XLSX.utils.encode_range(merge)+" overlaps "+XLSX.utils.encode_range(range));
  });

  /* add merge */
  ws["!merges"].push(merge);
}

单元格

¥Cells

电子表格工具将存储并使用合并范围的左上角单元格。例如，如果合并了范围 B2:C5，则与该范围对应的单元格将存储在工作表中的单元格 B2 中。

¥Spreadsheet tools will store and use the top-left cell of a merge range. For example, if the range B2:C5 is merged, the cell corresponding to the range will be stored in the worksheet in cell B2.

覆盖单元格

¥Covered Cells

电子表格工具可以存储合并单元格覆盖的单元格。

¥Spreadsheet tools can store cells that are covered by a merged cell.

SheetJS 工作表对象可以存储覆盖的单元格。API 函数可能会省略或包含覆盖的单元格。

¥The SheetJS worksheet object can store covered cells. API Functions may omit or include covered cells.

在线演示

¥Live Demo

此示例生成与以下屏幕截图匹配的工作表：

¥This example generates a worksheet that matches the following screenshot:

screenshot

合并范围为 A1:B2、C1:C2、A3:B3、D1:D2 和 A4:B4。

¥The merge ranges are A1:B2, C1:C2, A3:B3, D1:D2, and A4:B4.

Result

Live Editor

function SheetJSMergeCellsExport() { return (<button onClick={() => {
  /* write data to the top-left corner of each range */
  var ws = XLSX.utils.aoa_to_sheet([
    ["A1:B2",  /* B1 */, "C1:C2",  "Separate blocks"], // row 1
    [],                                                // row 2
    ["A3:B3",  /* B3 */, "C3"],                        // row 3
    ["... are merged separately"],                     // row 4
  ]);
  /* add merges */
  ws["!merges"] = [
    { s: { c: 0, r: 0 }, e: { c: 1, r: 1 } },  // A1:B2
    { s: { c: 2, r: 0 }, e: { c: 2, r: 1 } },  // C1:C2
    { s: { c: 0, r: 2 }, e: { c: 1, r: 2 } },  // A3:B3
    { s: { c: 3, r: 0 }, e: { c: 3, r: 1 } },  // D1:D2
    { s: { c: 0, r: 3 }, e: { c: 1, r: 3 } }   // A4:B4
  ];
  /* export to XLSX */
  var wb = XLSX.utils.book_new(ws, "Merges");
  XLSX.writeFile(wb, "SheetJSMergeCells.xlsx");
}}><b>Click here to Export</b></button>); }

函数

¥Functions

HTML

table_to_sheet 和 table_to_book 将生成包含合并范围的工作表：

¥table_to_sheet and table_to_book will generate worksheets that include merged ranges:

Live Demo (click to show)

Result

Live Editor

function SheetJSDOMMergedCells() {
  const ref = React.useRef(null);
  const [ merges, setMerges ] = React.useState([]);

  React.useEffect(() => {
    if(ref.current) {
      const tbl = ref.current.getElementsByTagName("TABLE");
      if(!tbl || !tbl[0]) return;
      const ws = XLSX.utils.table_to_sheet(tbl[0]);
      console.log(ws["!merges"])
      setMerges(ws["!merges"] || []);
    }
  }, [ref])
  const ws = XLSX.utils.aoa_to_sheet([
    ["A1:B1 is merged", "This cell is covered" ],
    ["A2 is not merged", "B2 is not merged"]
  ]);
  ws["!merges"] = [XLSX.utils.decode_range("A1:B1")];
  const __html = XLSX.utils.sheet_to_html(ws);

  return ( <>
    <b>Table:</b>
    <div ref={ref} dangerouslySetInnerHTML={{__html}}/>
    <b>Merges:</b>
    <pre>{merges ? merges.map(m =>  XLSX.utils.encode_range(m)).join("\n") : ""}</pre>
  </> );
}

sheet_to_html 将生成使用 colspan 和 rowspan 进行合并范围的 HTML 字符串：

¥sheet_to_html will generate HTML strings that use colspan and rowspan for merged ranges:

Live Demo (click to show)

Result

Live Editor

function SheetJSHTMLMergedCells() {
  const ws = XLSX.utils.aoa_to_sheet([
    ["A1:B1 is merged", "This cell is covered" ],
    ["A2 is not merged", "B2 is not merged"]
  ]);
  ws["!merges"] = [XLSX.utils.decode_range("A1:B1")];
  const __html = XLSX.utils.sheet_to_html(ws);
  return ( <div dangerouslySetInnerHTML={{__html}}/> );
}

读取文件

¥Reading Files

read 和 readFile 将从支持的文件中提取合并元数据。

¥read and readFile will extract merge metadata from supported files.

写入文件

¥Writing Files

write 和 writeFile 将在导出到支持合并范围的文件格式时尝试写入合并元数据。

¥write and writeFile will attempt to write merge metadata when exporting to file formats that support merged ranges.

写入不支持合并范围的 CSV 和其他格式时，范围中的每个单元格都将被导出。这包括覆盖的单元格！

¥When writing to CSV and other formats that do not support merged ranges, every cell in the range will be exported. This includes covered cells!

Live Demo (click to show)

Result

Live Editor

function SheetJSCSVMergedCells() {
  const ws = XLSX.utils.aoa_to_sheet([
    ["A1:B1 is merged", "This cell is covered" ],
    ["A2 is not merged", "B2 is not merged"]
  ]);
  ws["!merges"] = [XLSX.utils.decode_range("A1:B1")];
  const wb = XLSX.utils.book_new(ws, "Sheet1");
  const csv = XLSX.write(wb, { type: "string", bookType: "csv"});
  return ( <pre>{csv}</pre> );
}

导出数据

¥Exporting Data

sheet_to_csv 和 sheet_to_json 不支持合并范围。导出将包括覆盖的单元格：

¥sheet_to_csv and sheet_to_json do not support merged ranges. The exports will include covered cells:

Live Demo (click to show)

!!!IG7!!!

贮存​

范围​

重叠​

单元格​

覆盖单元格​

在线演示​

函数​

HTML​

读取文件​

写入文件​

导出数据​

贮存

范围

重叠

单元格

覆盖单元格

在线演示

函数

HTML

读取文件

写入文件

导出数据