数据库和存储

"数据库" 是一个包罗万象的术语，指的是传统 RDBMS 以及 K/V 存储、文档数据库和其他 "NoSQL" 存储。有许多外部数据库系统以及浏览器 API，例如 WebSQL 和 localStorage

¥"Database" is a catch-all term referring to traditional RDBMS as well as K/V stores, document databases, and other "NoSQL" storages. There are many external database systems as well as browser APIs like WebSQL and localStorage

数据存储

¥Data Storage

结构化表

¥Structured Tables

数据库表是电子表格的常见导入和导出目标。数据库表的一种常见表示形式是 JS 对象数组，其键是列标题，其值是基础数据值。例如，

¥Database tables are a common import and export target for spreadsheets. One common representation of a database table is an array of JS objects whose keys are column headers and whose values are the underlying data values. For example,

名称	索引
巴拉克奥巴马	44
唐纳德·特朗普	45
约瑟夫·拜登	46

自然地表示为对象数组

¥is naturally represented as an array of objects

[
  { Name: "Barack Obama", Index: 44 },
  { Name: "Donald Trump", Index: 45 },
  { Name: "Joseph Biden", Index: 46 }
]

sheet_to_json 和 json_to_sheet 辅助函数适用于形状相似的对象，在工作表对象之间进行转换。相应的工作表将包含标签的标题行：

¥The sheet_to_json and json_to_sheet helper functions work with objects of similar shape, converting to and from worksheet objects. The corresponding worksheet would include a header row for the labels:

XXX|      A       |   B   |
---+--------------+-------+
 1 | Name         | Index |
 2 | Barack Obama |    44 |
 3 | Donald Trump |    45 |
 3 | Joseph Biden |    46 |

非结构化数据

¥Unstructured Data

"无模式" / "NoSQL" 数据库允许数据库条目中存在任意键和值。K/V 存储和对象添加了额外的限制。

¥"Schema-less" / "NoSQL" databases allow for arbitrary keys and values within the entries in the database. K/V stores and Objects add additional restrictions.

没有自然的方法可以将任意形状的模式转换为工作簿中的工作表。一种常见的技巧是专门使用一张工作表来保存命名键。例如，考虑 JS 对象：

¥There is no natural way to translate arbitrarily shaped schemas to worksheets in a workbook. One common trick is to dedicate one worksheet to holding named keys. For example, considering the JS object:

{
  "title": "SheetDB",
  "metadata": {
    "author": "SheetJS",
    "code": 7262
  },
  "data": [
    { "Name": "Barack Obama", "Index": 44 },
    { "Name": "Donald Trump", "Index": 45 },
  ]
}

专用工作表应存储一次性命名值：

¥A dedicated worksheet should store the one-off named values:

XXX|        A        |    B    |
---+-----------------+---------+
 1 | Path            | Value   |
 2 | title           | SheetDB |
 3 | metadata.author | SheetJS |
 4 | metadata.code   |    7262 |

数据交换

¥Data Interchange

导出数据

¥Exporting Data

许多流行的 RDBMS 系统都有 NodeJS 连接器库。库具有连接数据库、执行查询以及获取可传递给 json_to_sheet 的 JS 对象数组形式的结果的工具。主要差异围绕 API 形状和支持的数据类型。

¥There are NodeJS connector libraries for many popular RDBMS systems. Libraries have facilities for connecting to a database, executing queries, and obtaining results as arrays of JS objects that can be passed to json_to_sheet. The main differences surround API shape and supported data types.

例如，better-sqlite3 是 SQLite 的连接器库。SELECT 查询的结果是适合 json_to_sheet 的对象数组：

¥For example, better-sqlite3 is a connector library for SQLite. The result of a SELECT query is an array of objects suitable for json_to_sheet:

var aoo = db.prepare("SELECT * FROM 'Presidents' LIMIT 100000").all();
var worksheet = XLSX.utils.json_to_sheet(aoo);

其他数据库将需要后处理。例如，MongoDB 结果包含对象 ID（通常存储在 _id 键中）。可以在生成工作表之前将其删除：

¥Other databases will require post-processing. For example, MongoDB results include the Object ID (usually stored in the _id key). This can be removed before generating a worksheet:

const aoo = await db.collection('coll').find({}).toArray();
aoo.forEach((x) => delete x._id);
const ws = XLSX.utils.json_to_sheet(aoo);

导入数据

¥Importing Data

当需要严格模式时，sheet_to_json 辅助函数会生成 JS 对象数组，可以扫描这些数组来确定列 "types"。

¥When a strict schema is needed, the sheet_to_json helper function generates arrays of JS objects that can be scanned to determine the column "types".

像 MongoDB 这样的文档数据库往往不需要模式。可以直接使用对象数组，无需设置模式：

¥Document databases like MongoDB tend not to require schemas. Arrays of objects can be used directly without setting up a schema:

const aoo = XLSX.utils.sheet_to_json(ws);
await db.collection('coll').insertMany(aoo, { ordered: true });

"SQL 连接器" 演示包括用于生成 SQL CREATE TABLE 和 INSERT 查询的示例函数。

¥The "SQL Connectors" demo includes sample functions for generating SQL CREATE TABLE and INSERT queries.

DSV 交汇处

¥DSV Interchange

许多数据库提供用于读写 CSV、管道分隔文档和其他简单数据文件的实用程序。它们支持库生成 CSV 数据供数据库处理或库解析数据库创建的 CSV 文件的工作流程。

¥Many databases offer utilities for reading and writing CSV, pipe-separated documents, and other simple data files. They enable workflows where the library generates CSV data for the database to process or where the library parses CSV files created by the database.

工作表转 CSV

¥Worksheet to CSV

可以使用 XLSX.utils.sheet_to_csv 从工作表生成 CSV 数据。

¥CSV data can be generated from worksheets using XLSX.utils.sheet_to_csv.

// starting from a worksheet object
const csv = XLSX.utils.sheet_to_json(ws);

// whole workbook conversion
const csv_arr = wb.SheetNames.map(n => XLSX.utils.sheet_to_json(wb.Sheets[n]));

CSV 到工作表

¥CSV to Worksheet

XLSX.read 可以读取带有 CSV 数据的字符串。它将生成工作表名称为 Sheet1 的单表工作簿。

¥XLSX.read can read strings with CSV data. It will generate single-sheet workbooks with worksheet name Sheet1.

在支持的情况下，XLSX.readFile 可以读取文件。

¥Where supported, XLSX.readFile can read files.

// starting from a CSV string
const ws_str = XLSX.read(csv_str, {type: "string"}).Sheets.Sheet1;

// starting from a CSV binary string (e.g. `FileReader#readAsBinaryString`)
const ws_bstr = XLSX.read(csv_bstr, {type: "binary"}).Sheets.Sheet1;

// starting from a CSV file in NodeJS or Bun or Deno
const ws_file = XLSX.readFile("test.csv").Sheets.Sheet1;

演示

¥Demos

Web API

以下 Web API 在单独的演示中具有特性：

¥The following Web APIs are featured in separate demos:

• Web SQL Database
• Local Storage API
• IndexedDB API

SQL 数据库

¥SQL Databases

单独的演示涵盖了以下与 SQL 相关的主题：

¥The following SQL-related topics are covered in separate demos:

• SQL 连接器
• SQLite
• Knex SQL 生成器
• PostgreSQL
• MariaDB / MySQL
• AlaSQL

NoSQL 数据存储

¥NoSQL Data Stores

以下 "NoSQL" 数据存储的演示应用结构化访问模式：

¥Demos for the following "NoSQL" data stores apply structured access patterns:

• MongoDB / FerretDB

以下 "NoSQL" 数据存储的演示应用非结构化访问模式：

¥Demos for the following "NoSQL" data stores apply unstructured access patterns:

• Redis / KeyDB
• PouchDB