Skip to main content

使用 PostgreSQL 的工作表

PostgreSQL（俗称 "Postgres"）是一个开源对象关系数据库系统。

¥PostgreSQL (colloquially referenced as "Postgres") is an open source object-relational database system.

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

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

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

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

强烈建议将 PostgreSQL 与查询构建器或 ORM 一起使用。

¥It is strongly recommended to use PostgreSQL with a query builder or ORM.

虽然可以直接生成 SQL 语句，但存在许多微妙的细节和陷阱。经过实际检验的解决方案通常可以针对 SQL 注入和其他漏洞提供缓解措施。

¥While it is possible to generate SQL statements directly, there are many subtle details and pitfalls. Battle-tested solutions generally provide mitigations against SQL injection and other vulnerabilities.

测试部署

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

¥This demo was tested in the following environments:

Postgres连接器库日期
17.2pg(8.13.1)2025-01-03
16.6pg(8.13.1)2025-01-03
15.10pg(8.13.1)2025-01-03
14.15pg(8.13.1)2025-01-03
13.18pg(8.13.1)2025-01-03
12.22pg(8.13.1)2025-01-03

集成详情​

¥Integration Details

SheetJS NodeJS 模块 可以在连接到 PostgreSQL 数据库的 NodeJS 脚本中加载。

¥The SheetJS NodeJS module can be loaded in NodeJS scripts that connect to PostgreSQL databases.

该演示使用 pg 连接器模块 [^1]，但相同的机制适用于其他 PostgreSQL 库。

¥This demo uses the pg connector module[^1], but the same mechanics apply to other PostgreSQL libraries.

导出数据​

¥Exporting Data

Client#query 返回一个解析为结果集的 Promise。结果的 rows 属性是一个对象数组。

¥Client#query returns a Promise that resolves to a result set. The rows property of the result is an array of objects.

SheetJS json_to_sheet 方法 [^2] 可以从对象数组生成工作表对象 [^3]：

¥The SheetJS json_to_sheet method[^2] can generate a worksheet object[^3] from the array of objects:

const table_name = "Tabeller1"; // name of table

/* fetch all data from specified table */
const res = await client.query(`SELECT * FROM ${table_name}`);

/* generate a SheetJS worksheet object from the data */
const worksheet = XLSX.utils.json_to_sheet(res.rows);

可以使用实用函数 [^4] 从工作表构建工作簿对象。可以使用 SheetJS writeFile 方法 [^5] 导出工作簿：

¥A workbook object can be built from the worksheet using utility functions[^4]. The workbook can be exported using the SheetJS writeFile method[^5]:

/* create a new workbook and add the worksheet */
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, worksheet, "Sheet1");

/* export workbook to XLSX */
XLSX.writeFile(wb, "SheetJSPGExport.xlsx");

导入数据​

¥Importing Data

SheetJS sheet_to_json 函数 [^6] 接受一个工作表对象并生成一个对象数组。

¥The SheetJS sheet_to_json function[^6] takes a worksheet object and generates an array of objects.

必须从对象手动生成查询。假设对象中的字段名称与列标题匹配，则循环可以生成 INSERT 个查询。

¥Queries must be manually generated from the objects. Assuming the field names in the object match the column headers, a loop can generate INSERT queries.

PostgreSQL 不允许使用可变列名的参数化查询

¥PostgreSQL does not allow parameterized queries with variable column names

INSERT INTO table_name (?) VALUES (?);
-- ---------------------^ variable column names are not valid

查询是手动生成的。为了帮助防止 SQL 注入漏洞，pg-format[^7] 模块对标识符和字段进行转义。

¥Queries are generated manually. To help prevent SQL injection vulnerabilities, the pg-format[^7] module escapes identifiers and fields.

/* generate an array of arrays from the worksheet */
const aoo = XLSX.utils.sheet_to_json(ws);

const table_name = "Blatte1"; // name of table

/* loop through the data rows */
for(let row of aoo) {

  /* generate format helper strings */
  const ent = Object.entries(row);
  const Istr = Array.from({length: entries.length}, ()=>"%I").join(", ");
  const Lstr = Array.from({length: entries.length}, ()=>"%L").join(", ");

  /* generate INSERT statement */
  let query = format.withArray(
    `INSERT INTO %I (${Istr}) VALUES(${Lstr})`,
    [ table_name, ...ent.map(x => x[0]), ...ent.map(x => x[1]) ]
  );

  /* execute INSERT statement */
  await client.query(query);
}

创建表​

¥Creating a Table

可以扫描工作表以确定列名和类型。有了名称和类型，就可以编写 CREATE TABLE 查询。

¥The worksheet can be scanned to determine column names and types. With the names and types, a CREATE TABLE query can be written.

Implementation Details (click to show)

The sheet_to_pg_table function:

  • scans worksheet cells to determine column names and types
  • drops and creates a new table with the determined column names and types
  • loads the entire dataset into the new table
/* create table and load data given a worksheet and a PostgreSQL client */
async function sheet_to_pg_table(client, worksheet, tableName) {
  if (!worksheet['!ref']) return;

  const range = XLSX.utils.decode_range(worksheet['!ref']);

  /* Extract headers from first row, clean names for PostgreSQL */
  const headers = [];
  for (let col = range.s.c; col <= range.e.c; col++) {
    const cellAddress = XLSX.utils.encode_cell({ r: range.s.r, c: col });
    const cell = worksheet[cellAddress];
    const headerValue = cell ? String(cell.v).replace(/[^a-zA-Z0-9_]/g, '_') : `column_${col + 1}`;
    headers.push(headerValue.toLowerCase());
  }

  /* Group cell values by column for type deduction */
  const columnValues = headers.map(() => []);
  for (let row = range.s.r + 1; row <= range.e.r; row++) {
    for (let col = range.s.c; col <= range.e.c; col++) {
      const cellAddress = XLSX.utils.encode_cell({ r: row, c: col });
      const cell = worksheet[cellAddress];
      columnValues[col].push(cell);
    }
  }

  /* Deduce PostgreSQL type for each column */
  const types = {};
  headers.forEach((header, idx) => {
    types[header] = deduceType(columnValues[idx]);
  });

  /* Delete table if it exists in the DB */
  await client.query(format('DROP TABLE IF EXISTS %I', tableName));

  /* Create table */
  const createTableSQL = format(
    'CREATE TABLE %I (%s)',
    tableName,
    headers.map(header => format('%I %s', header, types[header])).join(', ')
  );
  await client.query(createTableSQL);

  /* Insert data row by row */
  for (let row = range.s.r + 1; row <= range.e.r; row++) {
    const values = headers.map((header, col) => {
      const cellAddress = XLSX.utils.encode_cell({ r: row, c: col });
      const cell = worksheet[cellAddress];
      return parseValue(cell, types[header]);
    });

    const insertSQL = format(
      'INSERT INTO %I (%s) VALUES (%s)',
      tableName,
      headers.map(h => format('%I', h)).join(', '),
      values.map(() => '%L').join(', ')
    );
    await client.query(format(insertSQL, ...values));
  }
}

function deduceType(cells) {
  if (!cells || cells.length === 0) return 'text';

  const nonEmptyCells = cells.filter(cell => cell && cell.v != null);
  if (nonEmptyCells.length === 0) return 'text';

  // Check for dates by looking at both cell type and formatted value
  const isDateCell = cell => cell?.t === 'd' || (cell?.t === 'n' && cell.w && /\d{4}-\d{2}-\d{2}|\d{1,2}\/\d{1,2}\/\d{4}|\d{2}-[A-Za-z]{3}-\d{4}|[A-Za-z]{3}-\d{2}|\d{1,2}-[A-Za-z]{3}/.test(cell.w));

  if (nonEmptyCells.some(isDateCell)) { return 'date'; }

  const allBooleans = nonEmptyCells.every(cell => cell.t === 'b');
  if (allBooleans) { return 'boolean'; }

  const allNumbers = nonEmptyCells.every(cell => cell.t === 'n' || (cell.t === 's' && !isNaN(cell.v.replace(/[,$\s%()]/g, ''))));

  if (allNumbers) {
    const numbers = nonEmptyCells.map(cell => {
      if (cell.t === 'n') return cell.v;
      return parseFloat(cell.v.replace(/[,$\s%()]/g, ''));
    });

    const needsPrecision = numbers.some(num => {
      const str = num.toString();
      return str.includes('e')
          || (str.includes('.') && str.split('.')[1].length > 6)
          || Math.abs(num) > 1e15;
    });

    return needsPrecision ? 'numeric' : 'double precision';
  }
  return 'text';
}

function parseValue(cell, type) {
  if (!cell || cell.v == null) return null;

  switch (type) {
    case 'date':
      if (cell.t === 'd') { return cell.v.toISOString().split('T')[0]; }
      if (cell.t === 'n') {
        const date = new Date((cell.v - 25569) * 86400 * 1000);
        return date.toISOString().split('T')[0];
      }
      return null;

    case 'numeric':
    case 'double precision':
      if (cell.t === 'n') return cell.v;
      if (cell.t === 's') {
        const cleaned = cell.v.replace(/[,$\s%()]/g, '');
        if (!isNaN(cleaned)) return parseFloat(cleaned);
      }
      return null;

    case 'boolean':
      return cell.t === 'b' ? cell.v : null;

    default:
      return String(cell.v);
  }
}

完整示例​

¥Complete Example

  1. 安装并启动 PostgreSQL 服务器。

    ¥Install and start the PostgreSQL serve r.

Installation Notes (click to show)

On macOS, install the postgresql formula with Homebrew:

brew install postgresql@16

The last few lines of the installer explain how to start the database:

Or, if you don't want/need a background service you can just run:
  LC_ALL="C" /usr/local/opt/postgresql@16/bin/postgres -D /usr/local/var/postgresql@16

On Linux, install postgresql by running the following script:

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt update
sudo apt install postgresql-16
sudo systemctl start postgresql

# Optional: Create user with password
sudo -u postgres createuser -P $USER
sudo -u postgres psql -c "ALTER USER $USER WITH SUPERUSER;"

If running the optional user creation steps above, a PostgreSQL password will be required. [^8]

Run the command to start a local database instance.

  1. 删除任何名为 SheetJSPG 的现有数据库：

    ¥Drop any existing database with the name SheetJSPG:

dropdb SheetJSPG

某些 Linux 安装不会创建 dropdb 命令。该命令可以通过 postgres 命令行工具运行：

¥Some Linux installations do not create the dropdb command. The command can be run through the postgres command-line tool:

dropdb in Ubuntu/Debian
sudo -i -u postgres dropdb SheetJSPG

如果服务器在其他地方运行，或者用户名与当前用户不同，则命令行标志可以覆盖默认值。

¥If the server is running elsewhere, or if the username is different from the current user, command-line flags can override the defaults.

选项解释
-h HOSTNAME服务器名称
-p PORT指定端口号
-U USERNAME指定用户名

如果数据库不存在，命令将失败并出现以下错误：

¥If the database does not exist, the command will fail with the following error:

dropdb: error: database removal failed: ERROR:  database "SheetJSPG" does not exist

可以安全地忽略此错误。

¥This error can be safely ignored.

  1. 使用 createdb 命令创建一个空的 SheetJSPG 数据库：

    ¥Create an empty SheetJSPG database using the createdb command:

createdb SheetJSPG

某些 Linux 安装不会创建 createdb 命令。该命令可以通过 postgres 命令行工具运行：

¥Some Linux installations do not create the createdb command. The command can be run through the postgres command-line tool:

createdb in Ubuntu/Debian
sudo -i -u postgres createdb SheetJSPG

createdb 支持与 dropdb 相同的 -h、-p 和 -U 标志。

¥createdb supports the same -h, -p, and -U flags as dropdb.

连接器测试​

¥Connector Test

  1. 创建项目文件夹：

    ¥Create a project folder:

mkdir sheetjs-pg
cd sheetjs-pg
npm init -y

  1. 安装 pg 连接器模块：

    ¥Install the pg connector module:

npm i --save pg@8.13.1

  1. 将以下示例代码块保存到 PGTest.js：

    ¥Save the following example codeblock to PGTest.js:

PGTest.js
const pg = require("pg");
const client = new pg.Client({
  database:"SheetJSPG",
  host: "127.0.0.1", // localhost
  port: 5432,
  //user: "",
  //password: ""
});
(async() => {

await client.connect();
const res = await client.query('SELECT $1::text as message', ['Hello world!']);
console.log(res.rows[0].message); // Hello world!
await client.end();

})();

  1. 编辑新的 PGTest.js 脚本并修改代码块中高亮的行以反映数据库部署设置。

    ¥Edit the new PGTest.js script and modify the highlighted lines from the codeblock to reflect the database deployment settings.

代码块中的设置与 macOS Homebrew PostgreSQL 服务器的默认配置匹配。对于其他部署：

¥The settings in the codeblock match the default configuration for macOS Homebrew PostgreSQL server. For other deployments:

  • 如果服务器未在你的计算机上运行，请将 host 和 port 设置为正确的主机名和端口号。

    ¥If the server is not running on your computer, set host and port to the correct host name and port number.

  • 如果服务器需要不同的用户名和密码，请取消注释 user 和 password 行并将这些值替换为用户名和密码。

    ¥If the server expects a different username and password, uncomment the user and password lines and replace the values with the username and password.

  • 对于 Ubuntu/Debian PostgreSQL 安装，默认用户为 postgres。必须在安装期间设置密码，或者在 psql 提示符中使用 sudo -u postgres psql 后跟 \password postgres。

    ¥For Ubuntu/Debian PostgreSQL installations, the default user is postgres. The password must be set during installation or using sudo -u postgres psql followed by \password postgres in the psql prompt.

  1. 运行脚本：

    ¥Run the script:

node PGTest.js

它应该打印 Hello world!

¥It should print Hello world!

如果输出不是 Hello world! 或者存在错误，请将问题报告给 pg 连接器项目以进行进一步诊断。

¥If the output is not Hello world! or if there is an error, please report the issue to the pg connector project for further diagnosis.

添加 SheetJS​

¥Add SheetJS

  1. 安装依赖：

    ¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz pg-format@1.0.4

  1. 下载 SheetJSPG.js：

    ¥Download SheetJSPG.js:

curl -LO https://xlsx.nodejs.cn/postgresql/SheetJSPG.js

该脚本将：

¥This script will:

  • 读取并解析测试文件 pres.numbers

    ¥read and parse the test file pres.numbers

  • 创建与本地 PostgreSQL 服务器上的 SheetJSPG 数据库的连接

    ¥create a connection to the SheetJSPG database on a local PostgreSQL server

  • 将第一个工作表中的数据加载到名为 Presidents 的表中

    ¥load data from the first worksheet into a table with name Presidents

  • 断开并重新连接到数据库

    ¥disconnect and reconnect to the database

  • 从表 Presidents 转储数据

    ¥dump data from the table Presidents

  • 将数据集导出到 SheetJSPG.xlsx

    ¥export the dataset to SheetJSPG.xlsx

  1. 编辑 SheetJSPG.js 脚本。

    ¥Edit the SheetJSPG.js script.

该脚本定义了一个 opts 对象：

¥The script defines an opts object:

SheetJSPG.js (configuration lines)
const XLSX = require("xlsx");
const opts = {
  database:"SheetJSPG",
  host: "127.0.0.1", // localhost
  port: 5432,
  //user: "",
  //password: ""
};

代码块中的设置与 macOS Homebrew PostgreSQL 服务器的默认配置匹配。对于其他部署：

¥The settings in the codeblock match the default configuration for macOS Homebrew PostgreSQL server. For other deployments:

  • 如果服务器未在你的计算机上运行，请将 host 和 port 设置为正确的主机名和端口号。

    ¥If the server is not running on your computer, set host and port to the correct host name and port number.

  • 如果服务器需要不同的用户名和密码，请取消注释 user 和 password 行并将这些值替换为用户名和密码。

    ¥If the server expects a different username and password, uncomment the user and password lines and replace the values with the username and password.

  • 对于 Ubuntu/Debian PostgreSQL 安装，默认用户为 postgres。必须在安装期间设置密码，或者在 psql 提示符中使用 sudo -u postgres psql 后跟 \password postgres。

    ¥For Ubuntu/Debian PostgreSQL installations, the default user is postgres. The password must be set during installation or using sudo -u postgres psql followed by \password postgres in the psql prompt.

  1. 获取示例文件 pres.numbers：

    ¥Fetch the example file pres.numbers:

curl -L -O https://xlsx.nodejs.cn/pres.numbers

  1. 运行脚本：

    ¥Run the script:

node SheetJSPG.js

  1. 验证结果：

    ¥Verify the result:

  • SheetJSPGExport.xlsx 可以在电子表格应用中打开或在终端中测试

    ¥SheetJSPGExport.xlsx can be opened in a spreadsheet app or tested in the terminal

npx xlsx-cli SheetJSPGExport.xlsx

  • 可以使用 psql 命令行工具查询数据库服务器。

    ¥The database server can be queried using the psql command line tool.

如果服务器在本地运行，则命令将为：

¥If the server is running locally, the command will be:

psql SheetJSPG -c 'SELECT * FROM "Presidents";'

psql 支持与 dropdb 和 createdb 相同的 -h、-p 和 -U 标志。

¥psql supports the same -h, -p, and -U flags as dropdb and createdb.

[^1]: 请参阅 pg 官方网站 了解更多信息。

¥See the official pg website for more info.

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

¥See json_to_sheet in "Utilities"

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

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

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

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

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

¥See writeFile in "Writing Files"

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

¥See sheet_to_json in "Utilities"

[^7]: pg-format 包可在公共 NPM 注册表中获取。尽管该项目被标记为已弃用，但官方 pg 网站仍然推荐 pg-format

¥The pg-format package is available on the public NPM registry. Even though the project is marked as deprecated, the official pg website still recommends pg-format

[^8]: Linux 上的 PostgreSQL 使用 默认情况下，SCRAM 身份验证需要密码

¥PostgreSQL on Linux uses SCRAM authentication by default, which requires a password