Skip to main content

使用 MongoDB 的表格

MongoDB 是一个面向文档的数据库引擎。

¥MongoDB is a document-oriented database engine.

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

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

该演示使用 SheetJS 在电子表格和 MongoDB 之间交换数据。我们将探讨如何使用 MongoDB 集合中的保存表到电子表格以及如何将电子表格中的数据添加到集合中。

¥This demo uses SheetJS to exchange data between spreadsheets and MongoDB. We'll explore how to use save tables from a MongoDB collection to spreadsheets and how to add data from spreadsheets into a collection.

测试部署

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

¥This demo was tested in the following environments:

服务器连接器库日期
雪貂 DB 1.21.0mongodb(5.9.2)2024-03-30
MongoDB CE 6.0.15mongodb(6.5.0)2024-05-01
MongoDB CE 7.0.8mongodb(6.5.0)2024-05-01

集成详情​

¥Integration Details

SheetJS NodeJS 模块 可以加载到使用 mongodb NodeJS 连接器库的 NodeJS 脚本中。

¥The SheetJS NodeJS module can be loaded in NodeJS scripts that use the mongodb NodeJS connector library.

将集合视为工作表非常简单。每个对象映射到表中的一行。

¥It is straightforward to treat collections as worksheets. Each object maps to a row in the table.

导入数据​

¥Importing Data

可以使用 Collection#insertMany[^1] 将存储在对象数组中的数据添加到 MongoDB 集合中。SheetJS sheet_to_json 方法 [^2] 可以从工作表生成数据：

¥Data stored in an array of objects can be added to MongoDB Collections using Collection#insertMany[^1]. The SheetJS sheet_to_json method[^2] can generate data from worksheets:

/* import data from a worksheet to a collection */
const aoo = XLSX.utils.sheet_to_json(ws);
await collection.insertMany(aoo, {ordered: true});

通常，工作表对象是从 SheetJS read 或 readFile 方法 [^4] 生成的工作簿对象 [^3] 中提取的。

¥Typically worksheet objects are extracted from workbook objects[^3] generated from the SheetJS read or readFile methods[^4].

导出数据​

¥Exporting Data

Collection#find[^5] 可以从 Mongo Collection 中提取对象数组。

¥Collection#find[^5] can pull an array of objects from a Mongo Collection.

SheetJS json_to_sheet 方法 [^6] 可以获取结果并生成工作表对象。

¥The SheetJS json_to_sheet method[^6] can take the result and generate a worksheet object.

通常该方法会向每个对象添加一个 _id 字段。删除该字段的推荐方法是使用 projection 来抑制 ID。

¥Normally the method adds a _id field to each object. The recommended way to remove the field is to use a projection to suppress the ID.

/* generate an array of objects from a collection */
const aoo = await collection.find({}, {projection:{_id:0}}).toArray();

/* generate a worksheet from a collection */
const ws = utils.json_to_sheet(aoo);

使用 book_new 和 book_append_sheet[^7]，可以创建工作簿对象。该工作簿通常导出到带有 writeFile[^8] 的文件系统。

¥Using book_new and book_append_sheet[^7], a workbook object can be created. This workbook is typically exported to the filesystem with writeFile[^8].

完整示例​

¥Complete Example

  1. 安装 MongoDB 兼容的服务器。选项包括 MongoDB CE[^9] 和 FerretDB[^10]

    ¥Install a MongoDB-compatible server. Options include MongoDB CE[^9] and FerretDB[^10]

  2. 在 localhost 上启动服务器（遵循官方说明）。

    ¥Start a server on localhost (follow official instructions).

MongoDB CE Setup (click to show)

For MongoDB 7.0 Community Edition, the macOS steps required brew:

brew tap mongodb/brew
brew update
brew install mongodb-community

Older versions can be installed by passing the version major and minor numbers:

# Install 6.0
brew install mongodb-community@6.0

If brew was used to install MongoDB, the following command starts a server:

/usr/local/opt/mongodb-community/bin/mongod --config /usr/local/etc/mongod.conf

If Homebrew is configured to use /opt/homebrew, the command is:

/opt/homebrew/opt/mongodb-community/bin/mongod --config /opt/homebrew/etc/mongod.conf

  1. 创建基础项目并安装依赖：

    ¥Create base project and install the dependencies:

mkdir sheetjs-mongo
cd sheetjs-mongo
npm init -y
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz mongodb@6.5.0

  1. 将以下内容保存到 SheetJSMongoCRUD.mjs（关键步骤已高亮）：

    ¥Save the following to SheetJSMongoCRUD.mjs (the key step is highlighted):

SheetJSMongoCRUD.mjs
import { writeFile, set_fs, utils } from 'xlsx';
import * as fs from 'fs'; set_fs(fs);
import { MongoClient } from 'mongodb';

const url = 'mongodb://localhost:27017/sheetjs';
const db_name = 'sheetjs';

/* Connect to mongodb server */
const client = await MongoClient.connect(url, { useUnifiedTopology: true });

/* Sample data table */
const db = client.db(db_name);
try { await db.collection('pres').drop(); } catch(e) {}
const pres = db.collection('pres');
await pres.insertMany([
  { name: "Barack Obama", idx: 44 },
  { name: "Donald Trump", idx: 45 },
  { name: "Joseph Biden", idx: 46 }
], {ordered: true});

/* Create worksheet from collection */
const aoo = await pres.find({}, {projection:{_id:0}}).toArray();
const ws = utils.json_to_sheet(aoo);

/* Export to XLSX */
const wb = utils.book_new();
utils.book_append_sheet(wb, ws, "Presidents");
writeFile(wb, "SheetJSMongoCRUD.xlsx");

/* Close connection */
client.close();

这个脚本：

¥This script:

  • 使用数据库 sheetjs 连接到本地 MongoDB 服务器

    ¥connects to the local MongoDB server using database sheetjs

  • 如果 pres 集合已存在，则删除它

    ¥removes the pres collection if it already exists

  • 使用示例数据创建一个新集合 pres

    ¥creates a new collection pres with sample data

  • 从集合中创建一个 SheetJS 工作表（在代码片段中高亮）

    ¥creates a SheetJS worksheet from the collection (highlighted in the snippet)

  • 创建 SheetJS 工作簿、添加工作表并导出到 XLSX

    ¥creates a SheetJS workbook, adds the worksheet, and exports to XLSX

  1. 运行脚本：

    ¥Run the script:

node SheetJSMongoCRUD.mjs

终端中不应有任何错误。该脚本将生成文件 SheetJSMongoCRUD.xlsx。该文件可以在电子表格编辑器中打开。

¥There should be no errors in the terminal. The script will generate the file SheetJSMongoCRUD.xlsx. That file can be opened in a spreadsheet editor.

[^1]: 请参阅 MongoDB 文档中的 insertMany。

¥See insertMany in the MongoDB documentation.

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

¥See sheet_to_json in "Utilities"

[^3]: 见 "工作簿对象"

¥See "Workbook Object"

[^4]: 见 "读取文件" 中的 read 和 readFile

¥See read and readFile in "Reading Files"

[^5]: 请参阅 MongoDB 文档中的 find。

¥See find in the MongoDB documentation.

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

¥See json_to_sheet in "Utilities"

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

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

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

¥See writeFile in "Writing Files"

[^9]: 请参阅 MongoDB 文档中的 "安装 MongoDB 社区版"。

¥See "Install MongoDB Community Edition" in the MongoDB documentation.

[^10]: 请参阅 FerretDB 文档中的 "使用 Docker Compose 设置 SQLite"。

¥See "SQLite Setup with Docker Compose" in the FerretDB documentation.