使用 SQLite 的工作表

SQLite 是一个轻量级的嵌入式 SQL 数据库引擎。许多流行的 JavaScript 服务器端平台都有连接器库。

¥SQLite is a lightweight embeddable SQL database engine. There are connector libraries for many popular JavaScript server-side platforms.

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

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

该演示使用 SQLite 和 SheetJS 在电子表格和 SQL 服务器之间交换数据。我们将探讨如何使用将数据库中的表保存到电子表格以及如何将电子表格中的数据添加到数据库中。

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

该演示涵盖 SQLite .db 文件处理。

¥This demo covers SQLite .db file processing.

WebSQL 演示涵盖了 Web SQL 数据库 API，这是 Chromium 和 Google Chrome 中内置的 SQLite 兼容数据库。

¥The WebSQL demo covers the Web SQL Database API, a SQLite-compatible database built into Chromium and Google Chrome.

测试部署

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

¥This demo was tested in the following environments:

平台	连接器库	日期
Chromium 122	`sql.js`(`1.8.0`)	2024-04-09
NodeJS `20.12.1`	`better-sqlite3`(`9.4.5`)	2024-04-09
BunJS `1.1.3`	（内置）	2024-04-09
Deno `1.42.1`	`sqlite`(`3.8`)	2024-04-09

演示

¥Demo

以下示例演示如何查询 SQLite 数据库中的每个表、查询每个表的数据、将每个非空表添加到工作簿以及导出为 XLSX。

¥The following examples show how to query for each table in an SQLite database, query for the data for each table, add each non-empty table to a workbook, and export as XLSX.

示例数据库

¥Sample Database

Chinook 数据库是 MIT 许可的示例数据库。原始源代码存储库 http://chinookdatabase.codeplex.com 不再可用，因此原始 SQL 查询镜像在这里。

¥The Chinook database is a MIT-licensed sample database. The original source code repository http://chinookdatabase.codeplex.com is no longer available, so the raw SQL queries are mirrored here.

导出数据

¥Exporting Data

连接器库通常提供一种根据 SELECT 查询的结果生成对象数组的方法。例如，在 NodeJS 中使用 better-sqlite3：

¥Connector libraries typically provide a way to generate an array of objects from the result of a SELECT query. For example, using better-sqlite3 in NodeJS:

import Database from "better-sqlite3";

/* open database */
var db = Database("chinook.db");

/* get data from the `Invoice` table */
var aoo = db.prepare("SELECT * FROM 'Invoice' LIMIT 100000").all();

SheetJS json_to_sheet 方法 [^1] 可以获取结果并生成工作表对象 [^2]。book_new 和 book_append_sheet 方法 [^3] 帮助构建工作簿对象 [^4]。writeFile 方法 [^5] 生成一个文件：

¥The SheetJS json_to_sheet method[^1] can take the result and generate a worksheet object[^2]. The book_new and book_append_sheet methods[^3] help build a workbook object[^4]. The writeFile method[^5] generates a file:

import * as XLSX from "xlsx";

/* Create Worksheet from the row objects */
var ws = XLSX.utils.json_to_sheet(aoo, {dense: true});

/* Add to Workbook */
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

/* Write File */
XLSX.writeFile(wb, "SheetJSQLiteNode.xlsx");

导入数据

¥Importing Data

"生成表" 部分包含一个代码片段，用于从 SheetJS 工作表对象生成与 SQLite 兼容的 SQL 查询。每个查询都可以顺序运行。

¥The "Generating Tables" section includes a code snippet for generating SQLite-compatible SQL queries from a SheetJS worksheet object. Each query can be run sequentially.

浏览器

¥Browser

sql.js[^6] 是 SQLite 编译到 WebAssembly 中的版本，使其可以在 Web 浏览器中使用。

¥sql.js[^6] is a compiled version of SQLite into WebAssembly, making it usable in web browsers.

SQLite 数据库文件可以是 fetch 并加载：

¥SQLite database files can be fetched and loaded:

/* Load sql.js library */
const SQL = await initSqlJs(config);
/* fetch sqlite database */
const ab = await (await fetch("/sqlite/chinook.db")).arrayBuffer();
/* connect to DB */
const db = new SQL.Database(new Uint8Array(ab));

sql.js 连接器库使用类似迭代器的接口。准备好语句后，Statement#step 循环结果，Statement#getAsObject 将每一行作为行对象拉取：

¥The sql.js connector library uses an iterator-like interface. After preparing a statement, Statement#step loops over the result and Statement#getAsObject pulls each row as a row object:

/* perform query and get iterator */
const sql = db.prepare("SELECT * FROM 'Invoice' LIMIT 100000").all();

/* create worksheet from the row objects */
let ws;

while(sql.step()) {
  const row = sql.getAsObject();

  if(!ws) ws = XLSX.utils.json_to_sheet([row], {dense: true, header});
  else XLSX.utils.sheet_add_json(ws, [row], { header, origin: -1, skipHeader: true});
}

演示

¥Demo

该演示获取 chinook.db，加载到 SQLite 引擎中并执行一系列查询来提取数据。工作表是根据数据创建的。从工作表创建工作簿并导出到 XLSX 文件。

¥This demo fetches chinook.db, loads into the SQLite engine and performs a series of queries to extract the data. Worksheets are created from the data. A workbook is created from the worksheets and exported to a XLSX file.

Result

Live Editor

function SheetJSQLJS() { return (<button onClick={async() => {
  /* Load sql.js library */
  const config = {
    locateFile: filename => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${filename}`
  }
  const SQL = await initSqlJs(config);

  /* Initialize database */
  const ab = await (await fetch("/sqlite/chinook.db")).arrayBuffer();
  const db = new SQL.Database(new Uint8Array(ab));

  /* Create new workbook */
  const wb = XLSX.utils.book_new();

  /* Get all table names */
  const sql = db.prepare("SELECT name FROM sqlite_master WHERE type='table'");
  while(sql.step()) {
    const row = sql.getAsObject();

    /* Get first 100K rows */
    const stmt = db.prepare("SELECT * FROM '" + row.name + "' LIMIT 100000");
    let header = [];
    let ws;
    while(stmt.step()) {
      /* create worksheet from headers */
      if(!ws) ws = XLSX.utils.aoa_to_sheet([header = stmt.getColumnNames()])

      const rowobj = stmt.getAsObject();
      /* add to sheet */
      XLSX.utils.sheet_add_json(ws, [rowobj], { header, origin: -1, skipHeader: true });
    }
    if(ws) XLSX.utils.book_append_sheet(wb, ws, row.name);
  }
  XLSX.writeFile(wb, "SheetJSQLJS.xlsx");
}}><b>Click here to start</b></button>) }

服务器端平台

¥Server-Side Platforms

NodeJS

better-sqlite3[^7] 原生模块嵌入了 SQLite C 库。Statement#all 运行一个准备好的语句并返回一个对象数组：

¥The better-sqlite3[^7] native module embeds the SQLite C library. Statement#all runs a prepared statement and returns an array of objects:

import Database from "better-sqlite3";
import * as XLSX from "xlsx";

/* open database */
var db = Database("chinook.db");

/* get data from the `Invoice` table */
var aoo = db.prepare("SELECT * FROM 'Invoice' LIMIT 100000").all();

/* create worksheet from the row objects */
var ws = XLSX.utils.json_to_sheet(aoo, {dense: true});

NodeJS 演示

¥NodeJS Demo

从 SQL 语句构建 chinook.db：

¥Build chinook.db from the SQL statements:

curl -LO https://xlsx.nodejs.cn/sqlite/chinook.sql
sqlite3 chinook.db ".read chinook.sql"

安装依赖：

¥Install the dependencies:

npm init -y
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz better-sqlite3@9.2.0

下载 SheetJSQLiteNode.mjs：

¥Download SheetJSQLiteNode.mjs:

curl -LO https://xlsx.nodejs.cn/sqlite/SheetJSQLiteNode.mjs

运行脚本：

¥Run the script:

node SheetJSQLiteNode.mjs

使用电子表格编辑器打开 SheetJSQLiteNode.xlsx。

¥Open SheetJSQLiteNode.xlsx with a spreadsheet editor.

Bun

Bun 附带内置高性能模块 bun:sqlite[^8]：

¥Bun ships with a built-in high-performance module bun:sqlite[^8]:

import { Database } from "bun:sqlite";
import * as XLSX from "xlsx";

/* open database */
var db = Database.open("chinook.db");

/* get data from the `Invoice` table */
var aoo = db.prepare("SELECT * FROM 'Invoice' LIMIT 100000").all();

/* create worksheet from the row objects */
var ws = XLSX.utils.json_to_sheet(aoo, {dense: true});

BunJS 演示

¥BunJS Demo

从 SQL 语句构建 chinook.db：

¥Build chinook.db from the SQL statements:

curl -LO https://xlsx.nodejs.cn/sqlite/chinook.sql
sqlite3 chinook.db ".read chinook.sql"

安装依赖：

¥Install the dependencies:

bun install https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

下载 SheetJSQLiteBun.mjs：

¥Download SheetJSQLiteBun.mjs:

curl -LO https://xlsx.nodejs.cn/sqlite/SheetJSQLiteBun.mjs

运行脚本：

¥Run the script:

bun run SheetJSQLiteBun.mjs

使用电子表格编辑器打开 SheetJSQLiteBun.xlsx。

¥Open SheetJSQLiteBun.xlsx with a spreadsheet editor.

Deno

Deno sqlite 库 [^9] 返回数组的原始数组：

¥Deno sqlite library[^9] returns raw arrays of arrays:

import { DB } from "https://deno.land/x/sqlite/mod.ts";
// @deno-types="https://cdn.sheetjs.com/xlsx-0.20.3/package/types/index.d.ts"
import * as XLSX from "https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs";

/* open database */
var db = new DB("chinook.db");

/* get data from the `Invoice` table */
var aoa = db.prepareQuery("SELECT * FROM 'Invoice' LIMIT 100000").all();

/* create worksheet from the row objects */
var data = [query.columns().map(x => x.name)].concat(aoa);
var ws = XLSX.utils.aoa_to_sheet(data, {dense: true});

Deno 演示

¥Deno Demo

从 SQL 语句构建 chinook.db：

¥Build chinook.db from the SQL statements:

curl -LO https://xlsx.nodejs.cn/sqlite/chinook.sql
sqlite3 chinook.db ".read chinook.sql"

下载 SheetJSQLiteDeno.ts：

¥Download SheetJSQLiteDeno.ts:

curl -LO https://xlsx.nodejs.cn/sqlite/SheetJSQLiteDeno.ts

运行脚本：

¥Run the script:

deno run --allow-read --allow-write SheetJSQLiteDeno.ts

使用电子表格编辑器打开 SheetJSQLiteDeno.xlsx。

¥Open SheetJSQLiteDeno.xlsx with a spreadsheet editor.

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

¥See json_to_sheet in "Utilities"

[^2]: 详细信息请参见 "SheetJS 数据模型" 中的 "Sheet 对象"。

¥See "Sheet Objects" in "SheetJS Data Model" for more details.

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

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

[^4]: 详细信息请参见 "SheetJS 数据模型" 中的 "工作簿对象"。

¥See "Workbook Objects" in "SheetJS Data Model" for more details.

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

¥See writeFile in "Writing Files"

[^6]: 见 sql.js 文档

¥See the sql.js documentation

[^7]: documentation 可以在项目存储库中找到。

¥The documentation can be found in the project repository.

[^8]: 请参阅 BunJS 文档中的 "SQLite"。

¥See "SQLite" in the BunJS documentation.

[^9]: 请参阅 Deno 模块注册表中的 sqlite 模块。

¥See the sqlite module on the Deno module registry.

演示​

示例数据库​

导出数据​

导入数据​

浏览器​

演示​

服务器端平台​

NodeJS​

NodeJS 演示​

Bun​

BunJS 演示​

Deno​

Deno 演示​

演示