Sheets with MongoDB

MongoDB is a document-oriented database engine.

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

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.

Tested Deployments

This demo was tested in the following environments:

Server	Connector Library	Date
FerretDB 1.21.0	mongodb (5.9.2)	2024-03-30
MongoDB CE 6.0.10	mongodb (5.7.0)	2023-12-04
MongoDB CE 7.0.2	mongodb (5.7.0)	2023-12-04

Integration Details

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

Data stored in an array of objects can be added to MongoDB Collections using Collection#insertMany¹. The SheetJS sheet_to_json method² 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});

Typically worksheet objects are extracted from workbook objects³ generated from the SheetJS read or readFile methods⁴.

Exporting Data

Collection#find⁵ can pull an array of objects from a Mongo Collection.

The SheetJS json_to_sheet method⁶ can take the result and generate a worksheet object.

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);

Using book_new and book_append_sheet⁷, a workbook object can be created. This workbook is typically exported to the filesystem with writeFile⁸.

Complete Example

0. Install a MongoDB-compatible server. Options include MongoDB CE⁹ and FerretDB¹⁰

1. 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

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

2. 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.2/xlsx-0.20.2.tgz [email protected]

3. 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:

• connects to the local MongoDB server using database sheetjs
• removes the pres collection if it already exists
• creates a new collection pres with sample data
• creates a SheetJS worksheet from the collection (highlighted in the snippet)
• creates a SheetJS workbook, adds the worksheet, and exports to XLSX

4. Run the script:

node SheetJSMongoCRUD.mjs

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.

Footnotes

1. See insertMany in the MongoDB documentation. ↩

2. See sheet_to_json in "Utilities" ↩

3. See "Workbook Object" ↩

4. See read and readFile in "Reading Files" ↩

5. See find in the MongoDB documentation. ↩

6. See json_to_sheet in "Utilities" ↩

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

8. See writeFile in "Writing Files" ↩

9. See "Install MongoDB Community Edition" in the MongoDB documentation. ↩

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