剪贴板数据

Excel 等电子表格软件通常支持复制和粘贴单元格和数据。这是通过剪贴板（MacOS 中的 "纸板"）实现的。

¥Spreadsheet software like Excel typically support copying and pasting cells and data. This is implemented through the Clipboard ("Pasteboard" in MacOS).

复制选定的单元格时，Excel for Windows 将所选单元格的屏幕截图存储为图片。它还创建和存储各种格式的许多字符串和文件，包括 TSV、CSV、HTML、RTF、SYLK、DIF、XLSB、XLS（均为 ）和 SpreadsheetML 2003。

¥When copying a selection of cells, Excel for Windows stores a screenshot of the selected cells as an image. It also creates and stores a number of strings and files for the various formats, including TSV, CSV, HTML, RTF, SYLK, DIF, XLSB, XLS (both '97-2004 and '95), and SpreadsheetML 2003.

并非所有剪贴板 API 都提供对所有剪贴板类型的访问。

¥Not all Clipboard APIs offer access to all clipboard types.

测试部署

每个浏览器演示都在以下环境中进行了测试：

¥Each browser demo was tested in the following environments:

浏览器	日期	注意
Chrome 126	2024-06-30
Safari 17.3	2024-06-30	不支持 text/rtf

浏览器读取（粘贴）

¥Browser Reading (paste)

可以从 paste 事件读取剪贴板数据。

¥Clipboard data can be read from a paste event.

事件 clipboardData 属性有一个 getData 方法，该方法返回与 SheetJS read 方法 [^1] 中的 "string" 类型兼容的字符串。

¥The event clipboardData property has a getData method which returns a string compatible with the "string" type in the SheetJS read method[^1].

以下示例从 HTML 剪贴板读取并使用 SheetJS writeFile 方法 [^2] 生成 XLSX 文件：

¥The following example reads from the HTML clipboard and generates a XLSX file using the SheetJS writeFile method[^2]:

document.onpaste = function(e) {
  /* get HTML */
  var str = e.clipboardData.getData('text/html');
  /* parse */
  var wb = XLSX.read(str, {type: "string"});
  /* DO SOMETHING WITH wb HERE */
};

getData 接受一个参数：所需的 MIME 类型。经测试的浏览器支持：

¥getData accepts one argument: the desired MIME type. Tested browsers support:

MIME 类型	数据格式
text/plain	TSV（制表符分隔值）
text/html	HTML
text/rtf	RTF（富文本格式）

在线演示

¥Live Demo

在 Excel 中打开文件，复制一些单元格，然后返回到此窗口。单击下面的 "RESULT" 并粘贴（Windows 为 Control+V，Mac 为 Command+V）。

¥Open a file in Excel, copy some cells, then come back to this window. Click on "RESULT" below and paste (Control+V for Windows, Command+V for Mac).

Result

Loading...

Live Editor

function ClipboardRead() {
  const [csvs, setCSVs] = React.useState([ "", "", "" ]);

  /* Set up paste handler */
  const paste = React.useCallback((e) => {
    /* this demo will read 3 different clipboard data types */
    var mime_arr = [ 'text/plain', 'text/html', 'text/rtf' ];
    /* get clipboard data for each type */
    var data_arr = mime_arr.map(mime => e.clipboardData.getData(mime));
    /* parse each data string into a workbook */
    var wb_arr = data_arr.map(str => XLSX.read(str, {type: "string"}));
    /* get first worksheet from each workbook */
    var ws_arr = wb_arr.map(wb => wb.Sheets[wb.SheetNames[0]]);
    /* generate CSV for each "first worksheet" */
    var result = ws_arr.map(ws => XLSX.utils.sheet_to_csv(ws));
    setCSVs(result);
  }, []);

  return ( <>
      {csvs[0] && (<pre><b>Data from clipboard TSV  (text/plain)</b><br/>{csvs[0]}</pre>)}
      {csvs[1] && (<pre><b>Data from clipboard HTML (text/html)</b><br/>{csvs[1]}</pre>)}
      {csvs[2] && (<pre><b>Data from clipboard RTF  (text/rtf)</b><br/>{csvs[2]}</pre>)}
      {csvs.every(x => !x) && <b onPaste={paste}>Copy data in Excel, click here, and paste (Control+V)</b>}
  </> );
}

读取文件

¥Reading Files

现代浏览器支持读取用户复制到剪贴板中的文件。

¥Modern browsers support reading files that users have copied into the clipboard.

由于浏览器 API 限制，应使用系统文件浏览器来选择电子表格并将其复制到剪贴板中。

¥Due to browser API limitations, the system file browser should be used to select and copy spreadsheets into the clipboard.

事件 clipboardData.files 属性（如果已设置）是文件列表。

¥The event clipboardData.files property, if it is set, is a list of files.

Result

Loading...

Live Editor

function ClipboardReadFiles() {
  const [data, setData] = React.useState([]);

  /* Set up paste handler */
  const paste = React.useCallback(async(e)=>{
    const result = [];

    /* loop over files */
    const files = e.clipboardData.files || [];
    for(let i = 0; i < files.length; ++i) {
      const file = files.item(i);

      /* filter MIME type for spreadsheets */
      if(!file.type.match(/excel|sheet|csv/)) continue;

      /* read data */
      const wb = XLSX.read(await file.arrayBuffer());

      /* capture sheet names */
      result.push([file.name, wb.SheetNames]);
    }
    setData(result);
  }, []);

  return ( <>
    {data.map((f,idx) => (<pre key={idx}>
      <b>Sheet Names from {f[0]}</b><br/>{f[1].join("\n")}
    </pre>))}
    {!data.length && (<b onPaste={paste}>Copy files, click here, and paste (Control+V)</b>)}
  </> );
}

浏览器编写（副本）

¥Browser Writing (copy)

可以从 copy 事件写入剪贴板数据。

¥Clipboard data can be written from a copy event.

事件 clipboardData 属性有一个 setData 方法，该方法接受可以使用 SheetJS write 方法 [^3] 中的 type: "string" 生成的字符串。

¥The event clipboardData property has a setData method which accepts a string that can be generated using type: "string" in the SheetJS write method[^3].

以下示例从工作簿对象的第一张工作表生成 HTML 字符串，并将该字符串加载到 HTML 剪贴板中：

¥The following example generates a HTML string from the first sheet of a workbook object and loads the string into the HTML clipboard:

document.oncopy = function(e) {
  /* get HTML of first worksheet in workbook */
  var str = XLSX.write(wb, {type: "string", bookType: "html"});
  /* set HTML clipboard data */
  e.clipboardData.setData('text/html', str);

  /* prevent the browser from copying the normal data */
  e.preventDefault();
};

setData 接受两个参数：MIME 类型和新数据。

¥setData accepts two arguments: MIME type and new data.

下表列出了支持的 MIME 类型以及必须传递给 SheetJS write 方法的 bookType[^4] 值：

¥The following table lists the supported MIME types and the bookType[^4] value that must be passed to the SheetJS write method:

MIME 类型	数据格式	bookType
text/plain	TSV（制表符分隔值）	txt
text/html	HTML	html

浏览器当前不支持分配给 text/rtf 剪贴板类型。

¥Browsers do not currently support assigning to the text/rtf clipboard type.

在线演示

¥Live Demo

该演示根据以下 HTML 表创建一个简单的工作簿：

¥This demo creates a simple workbook from the following HTML table:

SheetJS	剪贴板	演示
bookType	RTF
source	HTML 表格

在 Excel 中创建一个新文件，然后返回到此窗口。选择下面的文本并复制（Windows 为 Control+C，Mac 为 Command+C）。返回 Excel 文件，选择单元格 A1，然后粘贴。

¥Create a new file in Excel then come back to this window. Select the text below and copy (Control+C for Windows, Command+C for Mac). Go back to the Excel file, select cell A1, and paste.

Result

Loading...

Live Editor

function ClipboardWrite() {
  /* Set up copy handler */
  const copy = React.useCallback((e) => {
    /* generate workbook from table */
    var wb = XLSX.utils.table_to_book(document.getElementById("srcdata"));
    /* get HTML of first worksheet in workbook */
    var str = XLSX.write(wb, {type: "string", bookType: "html"});
    /* set HTML clipboard data */
    e.clipboardData.setData('text/html', str);
    /* prevent the browser from copying the normal data */
    e.preventDefault();
  }, []);

  return (
    <b onCopy={copy}>Select this text, copy (Control+C), and paste in Excel</b>
  );
}

Electron

Electron Clipboard API 支持 HTML 和 RTF 剪贴板。

¥Electron Clipboard API supports HTML and RTF clipboards.

对于特定的剪贴板类型有特殊的方法：

¥There are special methods for specific clipboard types:

文件类型	读取剪贴板数据	写入剪贴板数据
TSV	clipboard.readText	clipboard.writeText
HTML	clipboard.readHTML	clipboard.writeHTML
RTF	clipboard.readRTF	clipboard.writeRTF

每个方法都对 JS 字符串进行操作。

¥Each method operates on JS strings.

clipboard.write 可以分配给多种剪贴板类型：

¥clipboard.write can assign to multiple clipboard types:

const { clipboard } = require('electron');
const XLSX = require('xlsx');

function copy_first_sheet_to_clipboard(workbook) {
  clipboard.write({
    text: XLSX.write(wb, {type: "string", bookType: "txt"}),
    rtf:  XLSX.write(wb, {type: "string", bookType: "rtf"}),
    html: XLSX.write(wb, {type: "string", bookType: "html"})
  });
}

[^1]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^2]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"

[^3]: 见 write 于 "写入文件"

¥See write in "Writing Files"

[^4]: 有关 bookType 的详细信息，请参阅 "支持的输出格式" 于 "写入文件"

¥See "Supported Output Formats" in "Writing Files" for details on bookType