DanfoJS 中的工作表

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

DanfoJS 是一个用于处理结构化数据的库。它在底层使用 SheetJS 来读取和写入电子表格。

¥DanfoJS is a library for processing structured data. It uses SheetJS under the hood for reading and writing spreadsheets.

该演示涵盖了 DanfoJS 官方文档中省略的详细信息。

¥This demo covers details elided in the official DanfoJS documentation.

测试部署

此示例上次于 2024 年 4 月 25 日针对 DanfoJS 1.1.2 进行了测试。

¥This example was last tested on 2024 April 25 against DanfoJS 1.1.2.

浏览器集成

此页面上的在线演示包括 DanfoJS 浏览器打包包：

¥The live demos on this page include the DanfoJS browser bundle:

  <script src="https://cdn.jsdelivr.net/npm/danfojs@1.1.2/lib/bundle.min.js"></script>

文档生成器存在已知问题。如果演示明确打印 "RELOAD THIS PAGE"，请重新加载或刷新页面。

¥There are known issues with the documentation generator. If a demo explicitly prints "RELOAD THIS PAGE", please reload or refresh the page.

数据框和工作表

¥DataFrames and Worksheets

DanfoJS DataFrame[^1] 表示二维表格数据。它是大多数 DanfoJS 数据处理任务的起点。DataFrame 通常对应于一个 SheetJS 工作表 [^2]。

¥The DanfoJS DataFrame[^1] represents two-dimensional tabular data. It is the starting point for most DanfoJS data processing tasks. A DataFrame typically corresponds to one SheetJS worksheet[^2].

Spreadsheet	DanfoJS DataFrame
	╔════╤═══════════════╤═══════╗ ║ │ Name │ Index ║ ╟────┼───────────────┼───────╢ ║ 0 │ Bill Clinton │ 42 ║ ╟────┼───────────────┼───────╢ ║ 1 │ GeorgeW Bush │ 43 ║ ╟────┼───────────────┼───────╢ ║ 2 │ Barack Obama │ 44 ║ ╟────┼───────────────┼───────╢ ║ 3 │ Donald Trump │ 45 ║ ╟────┼───────────────┼───────╢ ║ 4 │ Joseph Biden │ 46 ║ ╚════╧═══════════════╧═══════╝

DanfoJS SheetJS 集成

¥DanfoJS SheetJS Integration

官方文档对库对象 danfo 和 dfd 的命名不一致。由于 dfd 是浏览器全局的，因此演示使用名称 dfd。

¥The official documentation inconsistently names the library object danfo and dfd. Since dfd is the browser global, the demos use the name dfd.

读取和写入电子表格的方法附加到主 dfd 对象。

¥Methods to read and write spreadsheets are attached to the main dfd object.

导入数据框

¥Importing DataFrames

readExcel[^3] 接受两个参数：源数据和选项。

¥readExcel[^3] accepts two arguments: source data and options.

源数据必须是 string 或 File 对象。字符串被解释为 URL，而 File 对象被视为数据。

¥The source data must be a string or File object. Strings are interpreted as URLs while File objects are treated as data.

选择工作表

¥Selecting a Worksheet

DanfoJS 将从一个工作表生成一个数据框。解析器通常使用第一个工作表。选项对象的 sheet 属性控制选定的工作表。它预计是一个零索引数字：

¥DanfoJS will generate a dataframe from one worksheet. The parser normally uses the first worksheet. The sheet property of the options object controls the selected worksheet. It is expected to be a zero-indexed number:

const first_sheet  = await dfd.readExcel(url, {sheet: 0});
const second_sheet = await dfd.readExcel(url, {sheet: 1});

更多解析选项

¥More Parsing Options

options 参数的 parsingOptions 属性直接传递给 SheetJS read 方法 [^4]。

¥The parsingOptions property of the options argument is passed directly to the SheetJS read method[^4].

例如，sheetRows 属性控制从较大工作表中提取的行数。要提取 3 行数据，sheetRows 必须设置为 4：

¥For example, the sheetRows property controls how many rows are extracted from larger worksheets. To pull 3 data rows, sheetRows must be set to 4:

const first_three_rows = await dfd.readExcel(url, { parsingOptions: {
  // see https://xlsx.nodejs.cn/docs/api/parse-options for details
  sheetRows: 4
} });

网址来源

¥URL source

以下示例获取 测试文件，使用 SheetJS 进行解析并生成 DanfoJS 数据帧。

¥The following example fetches a test file, parses with SheetJS and generates a DanfoJS dataframe.

Result

Loading...

Live Editor

function DanfoReadExcelURL() {
  const [text, setText] = React.useState("");
  React.useEffect(() => { (async() => {
    if(typeof dfd === "undefined") return setText("RELOAD THIS PAGE!");
    const df = await dfd.readExcel("https://xlsx.nodejs.cn/pres.xlsx");
    setText("" + df.head());
  })(); }, []);
  return (<pre>{text}</pre>);
}

文件来源

¥File source

以下示例使用文件输入元素。"本地文件访问" 演示的 "文件接口"[^5] 部分更详细地介绍了浏览器 API。

¥The following example uses a file input element. The "File API"[^5] section of the "Local File Access" demo covers the browser API in more detail.

Result

Loading...

Live Editor

function DanfoReadExcelFile() {
  const [text, setText] = React.useState("Select a spreadsheet");

  return (<><pre>{text}</pre><input type="file" onChange={async(e) => {
    if(typeof dfd === "undefined") return setText("RELOAD THIS PAGE!");

    /* get first file */
    const file = e.target.files[0];

    /* create dataframe and pretty-print the first 10 rows */
    const df = await dfd.readExcel(file);
    setText("" + df.head());
  }}/></>);
}

导出数据框

¥Exporting DataFrames

toExcel[^6] 接受两个参数：数据框和选项。在底层，它使用 SheetJS writeFile 方法 [^7]。

¥toExcel[^6] accepts two arguments: dataframe and options. Under the hood, it uses the SheetJS writeFile method[^7].

导出的文件名

¥Exported File Name

文件名的相关属性取决于平台：

¥The relevant property for the file name depends on the platform:

平台	属性
NodeJS	filePath
浏览器	fileName

导出器将从文件扩展名推断出所需的文件格式。

¥The exporter will deduce the desired file format from the file extension.

工作表名称

¥Worksheet Name

sheetName 属性指定工作簿中工作表的名称：

¥The sheetName property specifies the name of the worksheet in the workbook:

dfd.toExcel(df, {
  fileName: "test.xlsx", // generate `test.xlsx`
  sheetName: "Export"    // The name of the worksheet will be "Export"
});

DanfoJS 集成强制使用 .xlsx 文件扩展名。导出为其他文件格式将需要 底层操作。

¥The DanfoJS integration forces the .xlsx file extension. Exporting to other file formats will require low-level operations.

更多写作选择

¥More Writing Options

options 参数的 writingOptions 属性直接传递给 SheetJS writeFile 方法 [^8]。

¥The writingOptions property of the options argument is passed directly to the SheetJS writeFile method[^8].

例如，compression 属性启用 XLSX 和其他格式的 ZIP 压缩：

¥For example, the compression property enables ZIP compression for XLSX and other formats:

dfd.toExcel(df, {fileName: "export.xlsx", writingOptions: {
  // see https://xlsx.nodejs.cn/docs/api/write-options for details
  compression: true
}});

导出到文件

¥Export to File

以下示例将示例数据帧导出到 XLSX 电子表格。

¥The following example exports a sample dataframe to a XLSX spreadsheet.

Result

Loading...

Live Editor

function DanfoToExcel() {
  if(typeof dfd === "undefined") return (<b>RELOAD THIS PAGE</b>);
  /* sample dataframe */
  const df = new dfd.DataFrame([{Sheet:1,JS:2},{Sheet:3,JS:4}]);
  return ( <><button onClick={async() => {
    /* dfd.toExcel calls the SheetJS `writeFile` method */
    dfd.toExcel(df, {fileName: "SheetJSDanfoJS.xlsx", writingOptions: {
      compression: true
    }});
  }}>Click to Export</button><pre>{"Data:\n"+df.head()}</pre></> );
}

底层操作

¥Low-Level Operations

DanfoJS 和 SheetJS 提供了处理对象数组的方法。

¥DanfoJS and SheetJS provide methods for processing arrays of objects.

创建 DataFrame

¥Creating DataFrames

DataFrame 构造函数 [^9] 从对象数组创建 DataFrame 对象。给定一个 SheetJS 工作表对象，sheet_to_json 方法 [^10] 生成兼容的对象数组：

¥The DataFrame constructor[^9] creates DataFrame objects from arrays of objects. Given a SheetJS worksheet object, the sheet_to_json method[^10] generates compatible arrays of objects:

function ws_to_df(ws) {
  const aoo = XLSX.utils.sheet_to_json(ws);
  return new dfd.DataFrame(aoo);
}

生成文件

¥Generating Files

toJSON[^11] 接受两个参数：数据框和选项。

¥toJSON[^11] accepts two arguments: dataframe and options.

options 参数的 format 键决定结果布局。column 布局生成按行优先顺序的对象数组。SheetJS json_to_sheet[^12] 方法可以从结果生成一个工作表对象：

¥The format key of the options argument dictates the result layout. The column layout generates an array of objects in row-major order. The SheetJS json_to_sheet[^12] method can generate a worksheet object from the result:

function df_to_ws(df) {
  const aoo = dfd.toJSON(df, { format: "column" });
  return XLSX.utils.json_to_sheet(aoo);
}

SheetJS book_new 方法从工作表 [^13] 创建一个工作簿对象，writeFile 方法 [^14] 将生成该文件：

¥The SheetJS book_new method creates a workbook object from the worksheet[^13] and the writeFile method[^14] will generate the file:

const ws = df_to_ws(df);
const wb = XLSX.utils.book_new(ws, "Export");
XLSX.writeFile(wb, "SheetJSDanfoJS.xlsb", { compression: true });

以下演示将示例数据帧导出到 XLSB。DanfoJS toExcel 方法不支持此操作，因为该方法强制执行 XLSX。

¥The following demo exports a sample dataframe to XLSB. This operation is not supported by the DanfoJS toExcel method since that method enforces XLSX.

Result

Loading...

Live Editor

function DanfoToXLS() {
  if(typeof dfd === "undefined") return (<b>RELOAD THIS PAGE</b>);
  /* sample dataframe */
  const df = new dfd.DataFrame([{Sheet:1,JS:2},{Sheet:3,JS:4}]);
  return ( <><button onClick={async() => {
    /* generate worksheet */
    const aoo = dfd.toJSON(df, { format: "column" });
    const ws = XLSX.utils.json_to_sheet(aoo);

    /* generate workbook */
    const wb = XLSX.utils.book_new(ws, "Export");

    /* write to XLS */
    XLSX.writeFile(wb, "SheetJSDanfoJS.xlsb", { compression: true });
  }}>Click to Export</button><pre>{"Data:\n"+df.head()}</pre></> );
}

[^1]: 请参阅 DanfoJS 文档中的 "数据框"

¥See "Dataframe" in the DanfoJS documentation

[^2]: 见 "Sheet 对象"

¥See "Sheet Objects"

[^3]: 请参阅 DanfoJS 文档中的 "danfo.readExcel"。

¥See "danfo.readExcel" in the DanfoJS documentation.

[^4]: 有关解析选项的完整列表，请参阅 "读取文件"。

¥See "Reading Files" for the full list of parsing options.

[^5]: 详细信息请参见 "文件接口" 于 "本地文件访问"。

¥See "File API" in "Local File Access" for more details.

[^6]: 请参阅 DanfoJS 文档中的 "danfo.toExcel"。

¥See "danfo.toExcel" in the DanfoJS documentation.

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

¥See writeFile in "Writing Files"

[^8]: 有关写入选项的完整列表，请参阅 "写入文件"。

¥See "Writing Files" for the full list of writing options.

[^9]: 请参阅 DanfoJS 文档中的 "创建数据框"。

¥See "Creating a DataFrame" in the DanfoJS documentation.

[^10]: 见 sheet_to_json 于 "实用工具"

¥See sheet_to_json in "Utilities"

[^11]: 请参阅 DanfoJS 文档中的 "danfo.toJSON"。

¥See "danfo.toJSON" in the DanfoJS documentation.

[^12]: 见 json_to_sheet 于 "实用工具"

¥See json_to_sheet in "Utilities"

[^13]: 见 book_new 于 "实用工具"

¥See book_new in "Utilities"

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

¥See writeFile in "Writing Files"