索引数据库 API

IndexedDB 是一个非常底层的 API。

¥IndexedDB is a very low-level API.

浏览器供应商建议在生产应用中使用 WebSQL 或封装器库。

¥Browser vendors recommend using WebSQL or wrapper libraries in production applications.

IndexedDB API 为 JSON 对象提供浏览器内沙盒本地数据存储。与 本地存储 API 一样，IndexedDB 是离线存储的流行选择。

¥The IndexedDB API provides an in-browser sandboxed local data store for JSON objects. Like the Local Storage API, IndexedDB is a popular choice for offline storage.

封装库

¥Wrapper Libraries

许多流行的封装器库都致力于简化 IndexedDB 操作。

¥A number of popular wrapper libraries seek to simplify IndexedDB operations.

本节中的封装器库已被 SheetJS 用户在生产站点中使用。

¥The wrapper libraries in this section have been used by SheetJS users in production sites.

localForage

测试部署

该演示最后在以下环境中进行了测试：

¥This demo was last tested in the following environments:

浏览器	日期	localForage
Chrome 122	2024-03-21	1.10.0
Safari 17.4	2024-03-23	1.10.0

localForage 是一个 IndexedDB 封装器，它提供了一个异步存储接口。

¥localForage is a IndexedDB wrapper that presents an async Storage interface.

可以使用 setItem 使用行索引作为键来存储对象数组：

¥Arrays of objects can be stored using setItem using row index as key:

const aoo = XLSX.utils.sheet_to_json(ws);
for(var i = 0; i < aoo.length; ++i) await localForage.setItem(i, aoo[i]);

恢复对象数组涉及对存储的迭代：

¥Recovering the array of objects involves an iteration over the storage:

const aoo = [];
await localforage.iterate((v, k) => { aoa[+k] = v; });
const ws = XLSX.utils.json_to_sheet(aoo);

演示

¥Demo

该演示准备了一个带有一些示例数据的小型 IndexedDB 数据库。

¥This demo prepares a small IndexedDB database with some sample data.

保存导出的文件后，可以在开发者工具的 "应用" 选项卡的 "索引数据库" 部分检查 IndexedDB 数据库：

¥After saving the exported file, the IndexedDB database can be inspected in the "IndexedDB" section of the "Application" Tab of Developer Tools:

Result

Loading...

Live Editor

function SheetJSLocalForage() {
  const data = [
    { Name: "Barack Obama", Index: 44 },
    { Name: "Donald Trump", Index: 45 },
    { Name: "Joseph Biden", Index: 46 }
  ];
  const xport = React.useCallback(async() => {
    /* force use of IndexedDB and connect to DB */
    localforage.config({
      driver: [ localforage.INDEXEDDB ],
      name: "SheetQL",
      size: 2097152
    });

    /* create sample data */
    await localforage.clear();
    for(var i = 0; i < data.length; ++i) await localforage.setItem(i, data[i]);

    /* pull data and generate aoa */
    const aoo = [];
    await localforage.iterate((v, k) => { aoo[+k] = v; });

    /* export */
    const ws = XLSX.utils.json_to_sheet(aoo);
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Presidents");
    XLSX.writeFile(wb, "SheetJSLocalForage.xlsx");
  });
  return ( <pre><button onClick={xport}><b>Do it!</b></button></pre> );
}

DexieJS

测试部署

该演示最后在以下环境中进行了测试：

¥This demo was last tested in the following environments:

浏览器	日期	DexieJS
Chrome 122	2024-03-21	3.2.4

DexieJS 是 IndexedDB 的简约封装器。它提供了一个方便的界面来创建多个逻辑表，非常适合工作簿。

¥DexieJS is a minimalistic wrapper for IndexedDB. It provides a convenient interface for creating multiple logical tables, well-suited for workbooks.

导入数据

¥Importing Data

配置表时，DexieJS 需要一个 schema。模式定义支持主键和其他属性，但它们不是必需的：

¥When configuring tables, DexieJS needs a schema. The schema definition supports primary keys and other properties, but they are not required:

/* assuming `wb` is a workbook from XLSX.read */
var db = new Dexie("SheetJSDexie");
db.version(1).stores(Object.fromEntries(wb.SheetNames.map(n => ([n, "++"]))));

配置数据库后，bulkPut 可以插入对象数组：

¥After the database is configured, bulkPut can insert arrays of objects:

/* loop over worksheet names */
for(let i = 0; i <= wb.SheetNames.length; ++i) {
  /* get the worksheet for the specified index */
  const wsname = wb.SheetNames[i];
  const ws = wb.Sheets[wsname];
  if(!ws) continue;
  /* generate an array of objects */
  const aoo = XLSX.utils.sheet_to_json(ws);
  /* push to idb */
  await db[wsname].bulkPut(aoo);
}

该演示将选定工作表中的所有数据插入数据库，然后反向从第一个工作表中获取数据。

¥This demo inserts all data from a selected worksheet into a database, then fetches the data from the first worksheet in reverse.

保存导出的文件后，开发者工具的 "应用" 选项卡的 "索引数据库" 部分将包含一个名为 "SheetJSDexie" 的数据库。

¥After saving the exported file, the "IndexedDB" section of the "Application" Tab of Developer Tools will include a database named "SheetJSDexie".

Result

Loading...

Live Editor

/* The live editor requires this function wrapper */
function SheetJSDexieImport(props) {
  const [__html, setHTML] = React.useState("Select a spreadsheet");

  return ( <>
    <input type="file" onChange={async(e) => { try {
      /* get data as an ArrayBuffer */
      const file = e.target.files[0];
      const data = await file.arrayBuffer();

      /* parse worksheet */
      const wb = XLSX.read(data);

      /* load into indexeddb */
      await Dexie.delete("SheetJSDexie");
      const db = new Dexie("SheetJSDexie");
      const wsnames = wb.SheetNames.map(n => ([n, "++"]));
      db.version(1).stores(Object.fromEntries(wsnames));

      /* loop over worksheet names */
      for(let i = 0; i <= wb.SheetNames.length; ++i) {
        /* get the worksheet for the specified index */
        const wsname = wb.SheetNames[i];
        const ws = wb.Sheets[wsname];
        if(!ws) continue;
        /* generate an array of objects */
        const aoo = XLSX.utils.sheet_to_json(ws);
        /* push to idb */
        await db[wsname].bulkPut(aoo);
      }

      /* fetch the first table in reverse order */
      const rev = await db[wb.SheetNames[0]].reverse().toArray();

      setHTML(rev.map(r => JSON.stringify(r)).join("\n"));
    } catch(e) { setHTML(e && e.message || e); }}}/>
    <pre dangerouslySetInnerHTML={{ __html }}/>
  </> );
}

导出数据

¥Exporting Data

db.tables 是表对象的普通数组。toArray 取数据：

¥db.tables is a plain array of table objects. toArray fetches data:

/* create blank workbook */
const wb = XLSX.utils.book_new();
/* loop tables */
for(const table of db.tables) {
  /* get data */
  const aoo = await table.toArray();
  /* create worksheet */
  const ws = XLSX.utils.json_to_sheet(aoo);
  /* add to workbook */
  XLSX.utils.book_append_sheet(wb, ws, table.name);
}

该演示准备了一个包含一些示例数据的小型数据库。

¥This demo prepares a small database with some sample data.

Result

Loading...

Live Editor

function SheetJSDexieExport() {
  const data = [
    { Name: "Barack Obama", Index: 44 },
    { Name: "Donald Trump", Index: 45 },
    { Name: "Joseph Biden", Index: 46 }
  ];
  const xport = React.useCallback(async() => {
    /* prepare db */
    await Dexie.delete("SheetJSDexie");
    var db = new Dexie("SheetJSDexie");
    db.version(1).stores({ Presidents: "++" });
    db.Presidents.bulkPut(data);

    /* pull data and generate workbook */
    const wb = XLSX.utils.book_new();
    for(const table of db.tables) {
      const aoo = await table.toArray();
      const ws = XLSX.utils.json_to_sheet(aoo);
      XLSX.utils.book_append_sheet(wb, ws, table.name);
    }
    XLSX.writeFile(wb, "SheetJSDexie.xlsx");
  });
  return ( <pre><button onClick={xport}><b>Do it!</b></button></pre> );
}