CSV 和文本

CSV 是数据交换的常用格式。Excel 等电子表格应用还支持其他分隔符分隔格式，包括 "文本"（使用制表符作为字段分隔符）。

¥CSV is a common format for data interchange. Spreadsheet applications such as Excel also support other delimiter-separated formats including "Text" (using a tab character as the field separator).

通用写入函数（XLSX.write 和 XLSX.writeFile）支持 csv 和 txt（分别用于 CSV 和制表符分隔值），但特定实用程序函数会生成原始 JS 字符串以供进一步处理。

¥The general write functions (XLSX.write and XLSX.writeFile) support csv and txt (for CSV and tab-separated values respectively), but the specific utility functions generate raw JS strings for further processing.

在线演示

¥Live Demo

选择文件后，演示将打印每个工作表中的数据：

¥After choosing a file, the demo will print the data from each worksheet:

Result

Live Editor

function SheetJSPreCSView() {
  const [__html, setHTML] = React.useState("Select a file");

  const process = (ab) => {
    const wb = XLSX.read(ab);
    var res = "";
    wb.SheetNames.forEach((n, idx) => {
      const ws = wb.Sheets[n];
      res += `<b>Sheet #${idx+1} (${n})</b>\n`;
      res += XLSX.utils.sheet_to_csv(ws) + "\n\n";
    });
    setHTML(res);
  };
  React.useEffect(() => { (async() => {
    const url = "https://xlsx.nodejs.cn/pres.numbers";
    process(await (await fetch(url)).arrayBuffer());
  })(); }, []);

  return ( <>
    <input type="file" onChange={async(e) => {
      process(await e.target.files[0].arrayBuffer());
    }}/>
    <pre dangerouslySetInnerHTML={{ __html }}/>
  </> );
}

分隔符分隔的输出

¥Delimiter-Separated Output

以 CSV、TSV 或其他分隔符分隔格式导出工作表数据

¥Export worksheet data in CSV, TSV, or other delimiter-separated format

var csv = XLSX.utils.sheet_to_csv(ws, opts);

作为 writeFile CSV 类型的替代方案，XLSX.utils.sheet_to_csv 也生成 CSV 输出。该函数接受一个选项参数：

¥As an alternative to the writeFile CSV type, XLSX.utils.sheet_to_csv also produces CSV output. The function takes an options argument:

选项名称	默认	描述
`FS`	`","`	字段之间的 "字段分隔符" 分隔符
`RS`	`"\n"`	行之间的 "记录分隔符" 分隔符
`dateNF`	菌群移植 14	在字符串输出中使用指定的日期格式
`strip`	false	删除每条记录中的尾随字段分隔符 **
`blankrows`	true	在 CSV 输出中包含空行
`skipHidden`	`false`	跳过隐藏数据
`forceQuotes`	false	在字段周围强制引号

strip 将在默认 FS/RS 下删除每行的尾随逗号

¥strip will remove trailing commas from each line under default FS/RS
blankrows 必须设置为 false 以跳过空行。

¥blankrows must be set to false to skip blank lines.
包含记录或字段分隔符的字段将自动用双引号引起来；forceQuotes 强制所有单元格用引号引起来。

¥Fields containing the record or field separator will automatically be wrapped in double quotes; forceQuotes forces all cells to be wrapped in quotes.

以下示例显示 FS 和 RS 选项：

¥The following example shows FS and RS options:

Result

Live Editor

function SheetJSCSVTest() {
  var ws = XLSX.utils.aoa_to_sheet([
    ["S", "h", "e", "e", "t", "J", "S"],
    [  1,   2,    ,    ,   5,   6,   7],
    [  2,   3,    ,    ,   6,   7,   8],
    [  3,   4,    ,    ,   7,   8,   9],
    [  4,   5,   6,   7,   8,   9,   0]
  ]);
  return ( <pre>
    <b>Worksheet (as HTML)</b>
    <div dangerouslySetInnerHTML={{__html: XLSX.utils.sheet_to_html(ws)}}/>
    <b>XLSX.utils.sheet_to_csv(ws)</b><br/>
    {XLSX.utils.sheet_to_csv(ws)}<br/><br/>
    <b>XLSX.utils.sheet_to_csv(ws, {'{'} FS: "\t" {'}'})</b><br/>
    {XLSX.utils.sheet_to_csv(ws, { FS: "\t" })}<br/><br/>
    <b>XLSX.utils.sheet_to_csv(ws, {'{'} FS: ":", RS: "|" {'}'})</b><br/>
    {XLSX.utils.sheet_to_csv(ws, { FS: ":", RS: "|" })}<br/>
  </pre> );
}

CSV 输出

¥CSV Output

以 "逗号分隔值" (CSV) 格式导出工作表数据

¥Export worksheet data in "Comma-Separated Values" (CSV)

var csv = XLSX.utils.sheet_to_csv(ws, opts);

sheet_to_csv 默认使用逗号字符作为字段分隔符。该实用函数将 csv 图书类型镜像到 XLSX.write 或 XLSX.writeFile 中。

¥sheet_to_csv uses the comma character as the field separator by default. This utility function mirrors the csv book type in XLSX.write or XLSX.writeFile.

sheet_to_csv 始终返回 JS 字符串并始终省略字节顺序标记。

¥sheet_to_csv always returns a JS string and always omits byte-order marks.

UTF-16 文本输出

¥UTF-16 Text Output

以 "UTF-16 文本" 或制表符分隔值 (TSV) 导出工作表数据

¥Export worksheet data in "UTF-16 Text" or Tab-Separated Values (TSV)

var txt = XLSX.utils.sheet_to_txt(ws, opts);

sheet_to_txt 使用制表符作为字段分隔符。该实用函数与 XLSX.write 或 XLSX.writeFile 中的 txt 图书类型相匹配。

¥sheet_to_txt uses the tab character as the field separator. This utility function matches the txt book type in XLSX.write or XLSX.writeFile.

如果编码支持可用，输出将以 CP1200 进行编码，并添加 UTF-16 BOM。如果编码支持不可用，输出将被编码为标准 string。

¥If encoding support is available, the output will be encoded in CP1200 and the UTF-16 BOM will be added. If encoding support is not available, the output will be encoded as a standard string.

XLSX.utils.sheet_to_txt 采用与 sheet_to_csv 相同的参数。

¥XLSX.utils.sheet_to_txt takes the same arguments as sheet_to_csv.

注意

¥Notes

隐藏行和列

¥Hidden Rows and Columns

默认情况下，所有行和列都会呈现。skipHidden 选项指示文本处理器跳过隐藏的行和列。

¥By default, all rows and columns are rendered. The skipHidden option instructs the text processor to skip hidden rows and columns.

工作表 !rows 数组存储行设置。!cols 数组存储列设置。

¥The worksheet !rows array stores row settings. The !cols array stores column settings.

默认情况下，read 和 readFile 方法不保存行/列设置。必须设置 cellStyles 选项。

¥By default, the read and readFile methods do not save row / column settings. The cellStyles option must be set.

以下演示展示了 skipHidden 的效果。第 2 行和第 5 行以及 F 列和 G 列标记为隐藏。隐藏的行和列默认呈现，但当 skipHidden 选项设置为 true 时会被省略。

¥The following demo shows the effect of skipHidden. Rows 2 and 5 and columns F and G are marked as hidden. The hidden rows and columns are rendered by default but omitted when the skipHidden option is set to true.

Result

Live Editor

function SheetJSCSVHiddenRows() {
  var ws = XLSX.utils.aoa_to_sheet([
    ["S", "h", "e", "e", "t", "J", "S", "Hidden (row)"],
    [  1,   2,    ,    ,   5,   6,   7, true],
    [  2,   3,    ,    ,   6,   7,   8, false],
    [  3,   4,    ,    ,   7,   8,   9, false],
    [  4,   5,   6,   7,   8,   9,   0, true],
    [  0,   0,   0,   0,   0,   1,   1, false, "Hidden (col)"]
  ]);

  ws["!rows"] = [];
  ws["!rows"][1] = { hidden: true, hpx: 16 }; // hide row 2
  ws["!rows"][4] = { hidden: true, hpx: 16 }; // hide row 5

  ws["!cols"] = [];
  ws["!cols"][5] = { wch: 8, hidden: true }; // hide column F
  ws["!cols"][6] = { wch: 8, hidden: true }; // hide column G

  return ( <pre>
    <b>Worksheet data (as HTML)</b>
    <div dangerouslySetInnerHTML={{__html: XLSX.utils.sheet_to_html(ws)}}/>
    <b>XLSX.utils.sheet_to_csv(ws, {'{'} skipHidden: true {'}'})</b><br/>
    {XLSX.utils.sheet_to_csv(ws, { skipHidden: true })}<br/>
  </pre> );
}

在线演示​

分隔符分隔的输出​

CSV 输出​

UTF-16 文本输出​

注意​

隐藏行和列​

在线演示

分隔符分隔的输出

CSV 输出

UTF-16 文本输出

注意

隐藏行和列