PouchDB 中的工作表

PouchDB 是一个纯 JavaScript 数据库，具有内置同步功能和离线支持。

¥PouchDB is a pure JavaScript database with built-in synchronization features and offline support.

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

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

该演示使用 PouchDB 和 SheetJS 将数据库快照导出到电子表格并从工作簿导入批量数据。我们将探索处理对象数组以与这两个库啮合的微妙之处。

¥This demo uses PouchDB and SheetJS to export database snapshots to spreadsheets and import bulk data from workbooks. We'll explore the subtleties of processing arrays of objects to mesh with both libraries.

"完整示例" 部分为官方 "托多斯" 演示提供了将列表导出到 XLSX 工作簿的功能。

¥The "Complete Example" section imbues the official "Todos" demo with the ability to export the list to XLSX workbooks.

测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

PouchDB	日期
`8.0.1`	2024-05-04
`7.3.1`	2024-05-04
`6.4.3`	2024-05-04
`5.4.5`	2024-05-04
`4.0.3`	2024-05-04
`3.6.0`	2024-05-04

集成详情

¥Integration Details

SheetJS CE 提供独立脚本、NodeJS 模块、ESM 模块和其他脚本。"安装" 部分涵盖了许多常见的部署场景。

¥SheetJS CE offers standalone scripts, NodeJS modules, ESM modules, and other scripts. The "Installation" section covers a number of common deployment scenarios.

PouchDB 附带供浏览器使用的独立脚本和供服务器端脚本使用的 NodeJS 模块 [^1]。

¥PouchDB ships with standalone scripts for browser use and NodeJS modules for use in server-side scripts[^1].

PouchDB 构造函数返回 Database 对象。

¥The PouchDB constructor returns a Database object.

导入数据

¥Importing Data

Database#bulkDocs[^2] 是批量数据导入的标准方法。该方法接受可以通过 SheetJS sheet_to_json[^3] 方法生成的 "对象数组"。

¥Database#bulkDocs[^2] is the standard approach for bulk data import. The method accepts "arrays of objects" that can be generated through the SheetJS sheet_to_json[^3] method.

如果行不包含 _id 参数，数据库将自动为每行分配一个 ID。强烈建议直接生成 _id。

¥If rows do not include the _id parameter, the database will automatically assign an ID per row. It is strongly recommended to generate the _id directly.

此方法从 SheetJS 工作簿对象 [^4] 开始，并使用第一个工作表中的数据。read 和 readFile[^5] 可以从文件生成工作簿对象。

¥This method starts from a SheetJS workbook object[^4] and uses data from the first sheet. read and readFile[^5] can generate workbook objects from files.

async function push_first_sheet_to_pouchdb(db, wb, _id_) {
  /* get first worksheet */
  const ws = wb.Sheets[wb.SheetNames[0]];

  /* generate array of objects */
  const aoo = XLSX.utils.sheet_to_json(ws);

  /* if a prefix is specified, add a unique _id to each row based on index */
  if(typeof _id_ == "string") aoo.forEach((row, idx) => row._id = _id_ + idx);

  /* perform query */
  return await db.bulkDocs(aoo);
}

可用 Database#destroy 擦除现有数据。

¥Existing data can be erased with Database#destroy.

导出数据

¥Exporting Data

Database#allDocs[^6] 是批量数据导出的标准方法。生成的行对象具有应删除的附加 _id 和 _rev 键。

¥Database#allDocs[^6] is the standard approach for bulk data export. Generated row objects have additional _id and _rev keys that should be removed.

删除 PouchDB 内部字段后，SheetJS json_to_sheet[^7] 方法可以生成工作表。其他实用函数 [^8] 可以构造工作簿。可以使用 SheetJS writeFile[^9] 方法导出工作簿：

¥After removing the PouchDB internal fields, the SheetJS json_to_sheet[^7] method can generate a worksheet. Other utility functions[^8] can construct a workbook. The workbook can be exported with the SheetJS writeFile[^9] method:

function export_pouchdb_to_xlsx(db) {
  /* fetch all rows, including the underlying data */
  db.allDocs({include_docs: true}, function(err, doc) {

    /* pull the individual data rows */
    const aoo = doc.rows.map(r => {
      /* `rest` will include every field from `r` except for _id and _rev */
      const { _id, _rev, ...rest } = r;
      return rest;
    });

    /* generate worksheet */
    const ws = XLSX.utils.json_to_sheet(aoo);

    /* generate workbook and export */
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSPouch.xlsx");
  });
}

json_to_sheet 需要一个 "flattened" 对象数组，其中每个值都是可以存储在电子表格单元格中的简单数据类型。如果文档对象具有嵌套结构，集成代码应对数据进行后处理。

¥json_to_sheet expects an array of "flattened" objects where each value is a simple data type that can be stored in a spreadsheet cell. If document objects have a nested structure, integration code should post-process the data.

"导出教程" 处理来自 API 的数据并根据嵌套数据计算一些文本值。

¥"Export Tutorial" processes data from an API and computes a few text values from the nested data.

完整示例

¥Complete Example

从入门指南下载 "工作版本"。

¥Download the "Working Version" from the Getting Started guide.

ZIP 文件应具有 MD5 校验和 ac4da7cb0cade1be293ba222462f109c：

¥The ZIP file should have MD5 checksum ac4da7cb0cade1be293ba222462f109c:

curl -LO https://github.com/nickcolley/getting-started-todo/archive/master.zip
md5sum master.zip || md5 master.zip

如果无法下载，则可以在 https://xlsx.nodejs.cn/pouchdb/master.zip 找到镜像：

¥If the download is unavailable, a mirror is available at https://xlsx.nodejs.cn/pouchdb/master.zip :

curl -LO https://xlsx.nodejs.cn/pouchdb/master.zip
md5sum master.zip || md5 master.zip

第二个命令将显示校验和：

¥The second command will display the checksum:

ac4da7cb0cade1be293ba222462f109c  master.zip

解压 master.zip 文件并进入文件夹：

¥Unzip the master.zip file and enter the folder:

unzip master.zip
cd getting-started-todo-master

编辑 index.html 以引用 SheetJS 库并添加一个按钮：

¥Edit index.html to reference the SheetJS library and add a button:

index.html (add highlighted lines)
  <body>
    <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
    <button id="xport">Export!</button>
    <section id="todoapp">

在 index.html 快结束时，查找引用 CDN 的脚本标记：

¥Near the end of index.html, look for a script tag referencing a CDN:

index.html (find line)
    <script src="//cdn.jsdelivr.net/pouchdb/3.2.0/pouchdb.min.js"></script>

通过将 src 属性更改为生产 build[^10] 来升级 PouchDB：

¥Upgrade PouchDB by changing the src attribute to the production build[^10]:

index.html (replace line)
    <script src="//cdn.jsdelivr.net/npm/pouchdb@8.0.1/dist/pouchdb.min.js"></script>

在 js/app.js 结束之前，添加 click 事件监听器：

¥Just before the end of js/app.js, add a click event listener:

js/app.js (add highlighted lines)
  if (remoteCouch) {
    sync();
  }

  document.getElementById("xport").addEventListener("click", function() {
    db.allDocs({include_docs: true, descending: true}, function(err, doc) {
      const aoo = doc.rows.map(r => {
        const { _id, _rev, ... rest } = r.doc;
        return rest;
      });
      const ws = XLSX.utils.json_to_sheet(aoo);
      const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
      XLSX.writeFile(wb, "SheetJSPouch.xlsx");
    });
  });
})();

演示 UI 按降序读取待办事项：

¥The demo UI reads the todo items in descending order:

js/app.js
    //------------------------------VVVVVVVVVVVVVVVV (descending order)
    db.allDocs({include_docs: true, descending: true}, function(err, doc) {
      redrawTodosUI(doc.rows);
    });

新的回调函数还指定 descending: true，以确保导出中待办事项的顺序与网页中显示的列表匹配。

¥The new callback function also specifies descending: true to ensure that the order of todo items in the export matches the list displayed in the webpage.

启动本地网络服务器：

¥Start a local web server:

npx http-server .

该命令将显示一个可以在 Web 浏览器中打开的 URL（通常为 http://localhost:8080）。

¥The command will display a URL (typically http://localhost:8080) which can be opened in a web browser.

测试

¥Testing

使用 Web 浏览器访问步骤 5 中的 URL。

¥Access the URL from step 5 with a web browser.
添加两项 "js" 和 "床单"。将 "床单" 标记为已完成。该页面应类似于以下屏幕截图：

¥Add two items "js" and "Sheet". Mark "Sheet" as completed. The page should look like the following screenshot:

todos screenshot

单击页面顶部的 "导出！" 文本。该站点应创建一个名为 "SheetJSPouch.xlsx" 的导出

¥Click the "Export!" text at the top of the page. The site should create an export named "SheetJSPouch.xlsx"
在电子表格编辑器中打开该文件。它应该与下表匹配：

¥Open the file in a spreadsheet editor. It should match the following table:

title	completed
床单	TRUE
js	FALSE

[^1]: 请参阅 PouchDB 文档中的 "设置 PouchDB"。

¥See "Setting up PouchDB" in the PouchDB documentation.

[^2]: 请参阅 PouchDB API 文档中的 "创建/更新一批文档"

¥See "Create/update a batch of documents" in the PouchDB API documentation

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

¥See sheet_to_json in "Utilities"

[^4]: 见 "SheetJS 数据模型"

¥See "SheetJS Data Model"

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

¥See read in "Reading Files"

[^6]: 请参阅 PouchDB API 文档中的 "获取一批文档"

¥See "Fetch a batch of documents" in the PouchDB API documentation

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

¥See json_to_sheet in "Utilities"

[^8]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

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

¥See writeFile in "Writing Files"

[^10]: PouchDB 网站中的 "下载" 的 "快速开始" 部分描述了 PouchDB 脚本推荐的 CDN。

¥The "Quick Start" section of "Download" in the PouchDB website describes the recommended CDN for PouchDB scripts.

集成详情​

导入数据​

导出数据​

完整示例​

集成详情

导入数据

导出数据

完整示例