使用 AlaSQL 的工作表

AlaSQL 是一个纯 JavaScript 内存 SQL 数据库。它通过 XLSX 目标运算符内置了对 SheetJS 的支持。

¥AlaSQL is a pure JavaScript in-memory SQL database. It has built-in support for SheetJS through the XLSX target operator.

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

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

该演示涵盖了与数据导入和导出相关的基本概念。官方文档包括高级示例和部署技巧以及 AlaSQL 表达式中的一般数据处理策略。

¥This demo covers basic concepts pertaining to data import and export. The official documentation includes advanced examples and deployment tips as well as strategies for general data processing in AlaSQL expressions.

测试部署

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

¥This demo was tested in the following environments:

环境	AlaSQL	日期
NodeJS	3.1.0	2024-06-03
独立（Chrome）	3.1.0	2024-06-03

在线演示

¥Live Demo

该演示获取 https://xlsx.nodejs.cn/pres.numbers，使用内置 AlaSQL + SheetJS 集成执行 SELECT 查询，然后显示结果。该演示将使用结果作为数据源写入新的电子表格。

¥This demo fetches https://xlsx.nodejs.cn/pres.numbers, performs a SELECT query using the built-in AlaSQL + SheetJS integration, then displays the result. Using the result as a data source, the demo will write to a new spreadsheet.

Demo AlaSQL Queries (click to show)

AlaSQL Query for reading data from a workbook
SELECT `Index`,          -- "Index" field is the "Index" column of the sheet
  UPPER(`Name`) AS `Nom` -- "Nom" field will be uppercase of "Name" column
FROM XLSX(?, {           -- Parse the workbook bytes passed to alasql.promise
  autoExt: false         -- This option is required in the browser
})

AlaSQL Query for writing data to a workbook
SELECT *                 -- use every field from every row in dataset
INTO XLSX(               -- export data to file
  "SheetJSAlaSQL.xlsx"   -- filename for export
) FROM ?

如果在线演示显示一条消息

¥If the live demo shows a message

alasql undefined

请刷新页面。这是文档生成器中的一个已知错误。

¥please refresh the page. This is a known bug in the documentation generator.

Result

Live Editor

function SheetJSAlaSQL() {
  const q1 = "SELECT `Index`, UPPER(`Name`) AS `Nom` FROM XLSX(?,{autoExt:false})";
  const q2 = `SELECT * INTO XLSX("SheetJSAlaSQL.xlsx") FROM ?`;
  const url = "https://xlsx.nodejs.cn/pres.numbers";
  const [rows, setRows] = React.useState([]);
  const loadURL = React.useCallback(async() => {
    if(typeof alasql=="undefined") return setRows([{Nom:"alasql undefined"}]);
    const blob = await (await fetch(url)).blob();
    const data = URL.createObjectURL(blob);
    const res = await alasql.promise(q1,[data]);
    setRows(res);
    await alasql.promise(q2, [res]);
  }, []);
  return ( <>
    <pre><b>URL: </b>{url}<br/><b>Import: </b>{q1}<br/><b>Export: </b>{q2}</pre>
    <table><tr><th>Index</th><th>Nom</th></tr>
      {rows.map(({Nom, Index}) => <tr><td>{Index}</td><td>{Nom}</td></tr>)}
    </table>
    <button onClick={loadURL}>Click to start</button>
  </> );
}

浏览器

¥Browser

独立脚本

¥Standalone Scripts

SheetJS 独立脚本应该在 alasql 脚本之前加载：

¥The SheetJS Standalone scripts should be loaded before the alasql script:

<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/shim.min.js"></script>
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/alasql"></script>

框架和打包器

¥Frameworks and Bundlers

alasql 使用旧版本的库。可以通过 package.json 覆盖来覆盖它。应在安装 alasql 之前添加以下行：

¥alasql uses an older version of the library. It can be overridden through a package.json override. The lines should be added before installing alasql:

{
  /* add this part before "name" */
  "overrides": {
    "xlsx": "https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz"
  },
  "name": "my-project",
  /* ... more fields ... */

添加覆盖后，可以通过 npm 安装 AlaSQL：

¥After adding the override, AlaSQL can be installed through npm:

npm i --save alasql

在导入中，SheetJS 库必须传递到 AlaSQL，如下所示：

¥In imports, the SheetJS library must be passed to AlaSQL as shown below:

import * as alasql from 'alasql';
import * as XLSX from 'xlsx';
alasql.utils.isBrowserify = false;
alasql.utils.global.XLSX = XLSX;

读取文件

¥Reading Files

XLSX "from" 目标需要一个文件名。在浏览器中，AlaSQL 使用可以从 Blob 或 File 对象创建的对象 URL。

¥The XLSX "from" target expects a filename. In the browser, AlaSQL uses object URLs which can be created from Blob or File objects.

以下代码片段获取数据并传递给 AlaSQL：

¥The following snippet fetches data and passes to AlaSQL:

const blob = await (await fetch("https://xlsx.nodejs.cn/pres.numbers")).blob();
const data = URL.createObjectURL(blob);
const res = await alasql.promise("SELECT * FROM XLSX(?, {autoExt: false}", [data]);

默认情况下，XLSX "from" 目标会自动添加 .xlsx 扩展名。要读取 URL，应将 autoExt: false 选项作为第二个参数传递：

¥By default, the XLSX "from" target automatically adds a .xlsx extension. To read URLs, the autoExt: false option should be passed as the second argument:

SELECT `Name`, `Index` FROM XLSX(
  ? --<< this will be the URL passed into `alasql.promise`
// highlight-start
  , { --<< options are supplied as the second argument to XLSX operator
    autoExt: false --<< do not automatically add ".xlsx" extension!
  }
// highlight-end
) WHERE `Index` < 45

默认情况下，解析工作簿并使用 sheet_to_json 提取数据：

¥By default the workbook is parsed and sheet_to_json is used to pull data:

(async() => {
  const blob = await (await fetch("https://xlsx.nodejs.cn/pres.numbers")).blob();
  const data = URL.createObjectURL(blob);
  const aoo = await alasql.promise("SELECT * FROM XLSX(?, {autoExt: false}", [data]);
  console.log(aoo); // [ { Name: "Bill Clinton", Index: 42 }, ...]
})();

写入文件

¥Writing Files

XLSX "into" 目标在底层调用 XLSX.writeFile：

¥The XLSX "into" target calls XLSX.writeFile under the hood:

(async() => {
  const data = [
    { Name: "Bill Clinton", Index: 42 },
    { Name: "SheetJS Dev", Index: 47 }
  ];
  await alasql.promise(`SELECT * INTO XLSX("PresMod5.xlsx") FROM ?`, [data]);
  /* PresMod5.xlsx will be created */
})();

NodeJS

alasql 使用旧版本的库。它可以通过最新版本的 NodeJS 中的 package.json 覆盖来覆盖：

¥alasql uses an older version of the library. It can be overridden through a package.json override in the latest versions of NodeJS:

{
  "overrides": {
    "xlsx": "https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz"
  }
}

读取文件

¥Reading Files

默认情况下，XLSX "from" 目标会自动添加 .xlsx 扩展名。要读取具有任意文件名的文件，应将 autoExt: false 选项作为第二个参数传递：

¥By default, the XLSX "from" target automatically adds a .xlsx extension. To read files with an arbitrary filename, the autoExt: false option should be passed as the second argument:

SELECT `Name`, `Index` FROM XLSX(
  "pres.numbers" --<< filename is "pres.numbers"
// highlight-start
  , { --<< options are supplied as the second argument to XLSX operator
    autoExt: false --<< do not automatically add ".xlsx" extension!
  }
// highlight-end
) WHERE `Index` < 45

默认情况下，解析工作簿并使用 sheet_to_json 提取数据：

¥By default the workbook is parsed and sheet_to_json is used to pull data:

const { promise: alasql } = require("alasql");

(async() => {
  const aoo = await alasql(`SELECT * from XLSX("pres.xlsx", {autoExt: false})`);
  console.log(aoo); // [ { Name: "Bill Clinton", Index: 42 }, ...]
})();

写入文件

¥Writing Files

XLSX "into" 目标在底层调用 XLSX.writeFile：

¥The XLSX "into" target calls XLSX.writeFile under the hood:

const { promise: alasql } = require("alasql");

(async() => {
  const data = [
    { Name: "Bill Clinton", Index: 42 },
    { Name: "SheetJS Dev", Index: 47 }
  ];
  await alasql(`SELECT * INTO XLSX("PresMod5.xlsx") FROM ?`, [data]);
  /* PresMod5.xlsx will be created */
})();

NodeJS 示例

¥NodeJS Example

为项目创建一个空文件夹：

¥Create an empty folder for the project:

mkdir alasql
cd alasql

在该文件夹中，使用 xlsx 覆盖创建一个存根 package.json：

¥In the folder, create a stub package.json with the xlsx override:

package.json
{
  "overrides": {
    "xlsx": "https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz"
  }
}

安装 SheetJS 和 AlaSQL：

¥Install SheetJS and AlaSQL:

npm i --save alasql@3.1.0 https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

下载测试文件 https://xlsx.nodejs.cn/pres.numbers：

¥Download the test file https://xlsx.nodejs.cn/pres.numbers :

curl -LO https://xlsx.nodejs.cn/pres.numbers

将以下测试脚本保存到 SheetJSAlaSQL.js：

¥Save the following test script to SheetJSAlaSQL.js:

SheetJSAlaSQL.js
const { promise: alasql } = require("alasql");

(async() => {
  /* read data from spreadsheet to JS */
  const data = await alasql(`
    SELECT \`Name\`, \`Index\`
      FROM XLSX("pres.numbers", {autoExt:false})
      WHERE \`Index\` < 45
  `);
  console.log(data);

  /* write data from JS to spreadsheet */
  data.push({ Name: "SheetJS Dev", Index: 47 });
  await alasql(`SELECT * INTO XLSX("SheetJSAlaSQL1.xlsx") FROM ?`, [data]);
})();

运行测试脚本

¥Run the test script

node SheetJSAlaSQL.js

输出应显示：

¥The output should display:

[
  { Name: 'Bill Clinton', Index: 42 },
  { Name: 'GeorgeW Bush', Index: 43 },
  { Name: 'Barack Obama', Index: 44 }
]

该脚本应生成 SheetJSAlaSQL1.xlsx 以及附加行：

¥The script should generate SheetJSAlaSQL1.xlsx with the additional row:

Name,Index
Bill Clinton,42
GeorgeW Bush,43
Barack Obama,44
SheetJS Dev,47

在线演示​

浏览器​

独立脚本​

框架和打包器​

读取文件​

写入文件​

NodeJS​

读取文件​

写入文件​

NodeJS 示例​

在线演示

浏览器

独立脚本

框架和打包器

读取文件

写入文件

NodeJS

读取文件

写入文件

NodeJS 示例