AlgAgt头像
关注

04- 电子表格数据持久化与SQLite数据库交互实战

04- 电子表格数据持久化与SQLite数据库交互实战

本文基于「工会预决算报表填报查询系统」项目经验，详解电子表格数据如何与 SQLite 数据库进行交互，包括数据模型设计、CRUD 操作、数据备份恢复、视图查询等核心技术。

目录

一、为什么选择 SQLite

1.1 桌面应用的数据库选择

在 Electron 桌面应用中，数据持久化有多种方案：

方案优点缺点适用场景
SQLite轻量、零配置、单文件、ACID并发写入有限单机桌面应用
IndexedDB浏览器原生、异步查询能力弱、API 复杂纯 Web 应用
LocalStorage简单易用容量小（5MB）、同步简单配置存储
JSON 文件直观、易调试大数据量性能差、不安全小型配置
服务端数据库功能强大、支持并发需要部署服务端多人协作系统

1.2 SQLite 的核心优势

对于本项目（工会预决算报表填报查询系统），SQLite 是最佳选择：

  1. 零配置部署：不需要安装数据库服务，随应用一起打包
  2. 单文件存储：整个数据库就是一个 .db 文件，方便备份和迁移
  3. 事务支持：ACID 特性，保证数据一致性
  4. 功能丰富：支持视图、触发器、索引、复杂查询
  5. 性能优秀：对于单用户桌面应用，性能完全够用
  6. 生态成熟：Node.js 有成熟的 sqlite3 包

1.3 SQLite 在 Electron 中的位置

┌──────────────────────────────────┐
│        Electron 主进程            │
│  ┌──────────────────────────┐    │
│  │       Node.js 运行时      │    │
│  │  ┌───────────────────┐   │    │
│  │  │     sqlite3 模块   │   │    │
│  │  └─────────┬─────────┘   │    │
│  │            ↓             │    │
│  │  ┌───────────────────┐   │    │
│  │  │   SQLite 引擎     │   │    │
│  │  └─────────┬─────────┘   │    │
│  └────────────┼──────────────┘    │
│               ↓                   │
│  userData/sqlite3.db (单文件)     │
└──────────────────────────────────┘

数据库文件存放在 Electron 的 userData 目录下：

  • Windows: C:\Users\<用户名>\AppData\Roaming\<appName>\sqlite3.db
  • macOS: ~/Library/Application Support/<appName>/sqlite3.db
  • Linux: ~/.config/<appName>/sqlite3.db

二、数据模型设计

2.1 业务数据模型概览

本项目涉及的核心数据表：

┌──────────────────┐      ┌──────────────────┐
│   organization   │      │   fill_report    │
│  (组织结构表)    │      │  (填报数据表)    │
├──────────────────┤      ├──────────────────┤
│ id (PK)          │      │ report_order     │
│ code             │      │ row_num          │
│ name             │      │ column_num       │
│ pid              │      │ cell_value       │
│ type             │      └──────────────────┘
│ ...              │           │
└──────────────────┘           │
                               │
                               ↓
┌──────────────────┐      ┌──────────────────┐
│   query_report   │      │    query_view    │
│  (查询报表表)    │      │   (查询视图)     │
├──────────────────┤      ├──────────────────┤
│ organization_code│      │ (虚拟表，基于    │
│ report_year      │      │  fill_report 和  │
│ report_order     │      │  query_report    │
│ row_num          │      │  联合计算得出)   │
│ column_num       │      └──────────────────┘
│ cell_value       │
└──────────────────┘

2.2 填报数据表设计（fill_report）

CREATE TABLE IF NOT EXISTS fill_report (
  report_order INTEGER NOT NULL,   -- 报表/工作表序号
  row_num INTEGER NOT NULL,        -- 行号
  column_num INTEGER NOT NULL,     -- 列号
  cell_value NUMERIC NULL,         -- 单元格值
  PRIMARY KEY (report_order, row_num, column_num)
);

设计思路：

  1. 联合主键：(report_order, row_num, column_num) 三元组唯一标识一个单元格
  2. 稀疏存储：只保存有值的单元格，没有值的不存记录
  3. NUMERIC 类型：报表数据主要是数字，使用 NUMERIC 兼顾整数和小数

为什么不用 JSON 存储整个表格？

  • 单元格级别的查询和更新更高效
  • 支持按行/列/报表进行统计
  • 数据一致性更好（事务保证）
  • 便于后续扩展查询功能

2.3 查询报表表设计（query_report）

CREATE TABLE IF NOT EXISTS query_report (
  organization_code VARCHAR(50) NOT NULL,  -- 组织编码
  report_year VARCHAR(4) NOT NULL,         -- 报表年度
  report_order INTEGER NOT NULL,           -- 报表序号
  row_num INTEGER NOT NULL,                -- 行号
  column_num INTEGER NOT NULL,             -- 列号
  cell_value NUMERIC NULL,                 -- 单元格值
  PRIMARY KEY (organization_code, report_year, report_order, row_num, column_num)
);

与 fill_report 类似，但多了 organization_code 和 report_year 维度，用于存储不同单位、不同年度的历史报表数据。

2.4 组织结构表设计（organization）

CREATE TABLE IF NOT EXISTS organization (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code VARCHAR(50) UNIQUE NOT NULL,        -- 组织编码
  name VARCHAR(200) NOT NULL,              -- 组织名称
  pid INTEGER,                              -- 父级ID
  organization_type VARCHAR(20),            -- 组织类型
  sort_order INTEGER DEFAULT 0,             -- 排序号
  status INTEGER DEFAULT 1,                 -- 状态：1启用 0禁用
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

支持树形结构的组织管理，通过 pid 实现层级关系。

三、数据库层架构

3.1 分层设计

┌─────────────────────────────────┐
│      IPC 接口层 (main.ts)       │
│  ipcMain.handle(...)            │
└───────────────┬─────────────────┘
                ↓
┌─────────────────────────────────┐
│      模型层 (models/)           │
│  FillReport / QueryReport / ... │
│  (封装具体业务的 CRUD)          │
└───────────────┬─────────────────┘
                ↓
┌─────────────────────────────────┐
│      数据库核心层 (db.ts)       │
│  getDB / runSQL / querySQL      │
│  backup / restore               │
└───────────────┬─────────────────┘
                ↓
┌─────────────────────────────────┐
│      sqlite3 驱动层             │
│  node-sqlite3 模块              │
└─────────────────────────────────┘

3.2 数据库核心层实现

// electron/db/db.ts
import { app } from "electron";
import path from "path";
import sqlite3 from "sqlite3";
import fs from "fs";
import { promisify } from "util";

let db: sqlite3.Database | null = null;

// 获取数据库实例（单例模式）
export function getDB() {
  if (db) return db;

  const dbPath = path.join(app.getPath("userData"), "sqlite3.db");
  db = new sqlite3.Database(dbPath, (err) => {
    err && console.error("Database connection failed:", err);
  });

  console.log("Database connected at:", dbPath);
  return db;
}

// 关闭数据库连接
export function closeDB() {
  return new Promise<void>((resolve) => {
    if (!db) return resolve();
    db.close((err) => {
      err ? console.error("Close failed:", err) : console.log("Database closed");
      db = null;
      resolve();
    });
  });
}

// 执行 SQL（INSERT/UPDATE/DELETE）
export async function runSQL(sql: string, params: any[] = []) {
  return new Promise<void>((resolve, reject) => {
    getDB().run(sql, params, (err) => {
      err ? reject(err) : resolve();
    });
  });
}

// 查询数据（返回数组）
export async function querySQL<T = any>(sql: string, params: any[] = []) {
  return new Promise<T[]>((resolve, reject) => {
    getDB().all(sql, params, (err, rows) => {
      err ? reject(err) : resolve(rows as T[]);
    });
  });
}

设计要点：

  1. 单例模式：全局只有一个数据库连接
  2. Promise 封装：将 sqlite3 的回调 API 封装为 Promise，便于 async/await
  3. 统一错误处理：所有数据库操作都通过统一的方法执行

3.3 模型层设计

每个数据表对应一个模型文件，封装该表的 CRUD 操作：

electron/db/models/
├── FillReport.ts       -- 填报数据模型
├── QueryReport.ts      -- 查询报表模型
├── QueryView.ts        -- 查询视图模型
└── Organization.ts     -- 组织结构模型

3.4 数据库初始化

// electron/db/index.ts
import { getDB } from "./db";
import { createTable as createFillReportTable } from "./models/FillReport";
import { createTable as createQueryReportTable } from "./models/QueryReport";
import { createTable as createOrganizationTable } from "./models/Organization";
import { createTable as createQueryViewTable } from "./models/QueryView";
import { FillReport } from "./models/FillReport";
import { QueryReport } from "./models/QueryReport";
import { QueryView } from "./models/QueryView";
import { Organization } from "./models/Organization";

// 初始化数据库
export async function initDatabase() {
  const db = getDB();
  
  // 1. 创建所有表
  await createFillReportTable();
  await createQueryReportTable();
  await createOrganizationTable();
  await createQueryViewTable();
  
  console.log("Database tables initialized");
}

// 导出数据库对象（包含各模型的方法）
export const db = {
  fillReport: FillReport,
  queryReport: QueryReport,
  queryView: QueryView,
  organization: Organization,
  
  backup: backupDB,
  restore: restoreDB,
  close: closeDB,
};

四、填报数据 CRUD 实现

4.1 FillReport 模型完整实现

// electron/db/models/FillReport.ts
import { runSQL, querySQL } from "../db";

// 类型定义
export interface FillReport {
  report_order: number;
  row_num: number;
  column_num: number;
  cell_value: string | number | null;
}

// 创建表
export async function createTable() {
  const sql = `
  CREATE TABLE IF NOT EXISTS fill_report (
    report_order INTEGER NOT NULL,
    row_num INTEGER NOT NULL,
    column_num INTEGER NOT NULL,
    cell_value NUMERIC NULL,
    PRIMARY KEY (report_order, row_num, column_num)
  )`;
  await runSQL(sql);
}

// 插入数据
export async function insert(data: FillReport) {
  const sql = `INSERT INTO fill_report 
    (report_order, row_num, column_num, cell_value) 
    VALUES (?, ?, ?, ?)`;
  
  await runSQL(sql, [
    data.report_order, 
    data.row_num, 
    data.column_num, 
    data.cell_value, 
  ]);
}

// 修改数据
export async function update(data: FillReport) {
  // 参数校验
  if (data.report_order === undefined || data.report_order === null ||
      data.row_num === undefined || data.row_num === null ||
      data.column_num === undefined || data.column_num === null) {
    throw new Error('更新操作需要提供 sheet 索引、row 和 column');
  }
  
  const sql = `UPDATE fill_report 
    SET cell_value = ?
    WHERE report_order = ? AND row_num = ? AND column_num = ?`;
  
  await runSQL(sql, [
    data.cell_value, 
    data.report_order, 
    data.row_num, 
    data.column_num, 
  ]);
}

// 重置所有数据（清空单元格值）
export async function updateAll() {
  const sql = `UPDATE fill_report SET cell_value = null`;  
  await runSQL(sql);
}

// 查询单个单元格
export async function get(report_order: number, row_num: number, column_num: number) {
  const sql = `SELECT * FROM fill_report 
    WHERE report_order = ? AND row_num = ? AND column_num = ?`;
  const result = await querySQL<FillReport>(sql, [report_order, row_num, column_num]);
  return result[0] || null;
}

// 获取所有填报数据
export async function getAll() {
  return querySQL<FillReport>(
    "SELECT * FROM fill_report ORDER BY report_order ASC, row_num ASC, column_num ASC"
  );
}

// 导出模型对象
export const FillReport = {
  createTable,
  insert,
  update,
  updateAll,
  get,
  getAll,
};

4.2 Upsert 模式（插入或更新）

在保存报表数据时，我们使用 Upsert 模式：先查询是否存在，存在则更新，不存在则插入。

// 渲染进程中的保存逻辑
const handleSave = async () => {
  await Promise.all(
    editCell.flatMap((reportGroup) => 
      reportGroup.cell.map(async (cellItem) => {
        // 1. 获取单元格当前值
        const value = luckysheet.getCellValue(cellItem.row_num, cellItem.column_num, {
          order: reportGroup.report_order,
        });

        const fillReport = {
          report_order: reportGroup.report_order,
          row_num: cellItem.row_num,
          column_num: cellItem.column_num,
          cell_value: value,
        };

        // 2. 查询是否已存在
        const existing = await ReportAPI.getFillReport(
          reportGroup.report_order,
          cellItem.row_num,
          cellItem.column_num
        );

        // 3. 存在则更新，不存在则插入
        if (existing?.cell_value !== undefined) {
          await ReportAPI.updateFillReport(fillReport);
        } else {
          await ReportAPI.insertFillReport(fillReport);
        }
      })
    )
  );
};

优化思路：也可以使用 SQLite 的 INSERT ... ON CONFLICT ... UPDATE 语法，将两次数据库操作合并为一次：

INSERT INTO fill_report (report_order, row_num, column_num, cell_value)
VALUES (?, ?, ?, ?)
ON CONFLICT(report_order, row_num, column_num) 
DO UPDATE SET cell_value = excluded.cell_value;

4.3 IPC 注册

// electron/main.ts
ipcMain.handle(
  "insertFillReport",
  async (event, params) => await db.fillReport.insert(params as FillReport)
);
ipcMain.handle(
  "updateFillReport",
  async (event, params) => await db.fillReport.update(params as FillReport)
);
ipcMain.handle("resetFillReport", async () => await db.fillReport.updateAll());
ipcMain.handle(
  "getFillReport",
  async (event, report_order, row_num, column_num) =>
    await db.fillReport.get(report_order, row_num, column_num)
);
ipcMain.handle("getAllFillReport", async () => await db.fillReport.getAll());

五、查询报表与视图优化

5.1 为什么需要视图

在报表查询场景中，用户需要查看不同组织、不同年度的数据。如果每次都从原始表查询并实时计算，效率较低。

使用数据库视图的好处：

  1. 简化查询：将复杂的多表关联封装成视图
  2. 逻辑复用：多个地方使用相同的查询逻辑
  3. 性能优化：可以在视图上建立索引（物化视图）
  4. 数据安全：只暴露需要的字段

5.2 查询视图设计

CREATE VIEW IF NOT EXISTS query_view AS
SELECT 
  qr.organization_code,
  qr.report_year,
  qr.report_order,
  qr.row_num,
  qr.column_num,
  qr.cell_value,
  o.name as organization_name,
  o.pid as organization_pid
FROM query_report qr
LEFT JOIN organization o ON qr.organization_code = o.code;

5.3 分页查询实现

// electron/db/models/QueryView.ts
export interface QueryViewFilters {
  organization_code?: string;
  report_year?: string;
  report_order?: number;
  page?: number;
  pageSize?: number;
}

export async function getPage(params: QueryViewFilters) {
  const { 
    organization_code, 
    report_year, 
    report_order,
    page = 1,
    pageSize = 20,
  } = params;

  // 构建 WHERE 条件
  const where: string[] = [];
  const values: any[] = [];

  if (organization_code) {
    where.push("organization_code = ?");
    values.push(organization_code);
  }
  if (report_year) {
    where.push("report_year = ?");
    values.push(report_year);
  }
  if (report_order !== undefined) {
    where.push("report_order = ?");
    values.push(report_order);
  }

  const whereSQL = where.length > 0 ? "WHERE " + where.join(" AND ") : "";
  
  // 分页查询
  const offset = (page - 1) * pageSize;
  const sql = `
    SELECT * FROM query_view 
    ${whereSQL}
    ORDER BY report_order ASC, row_num ASC, column_num ASC
    LIMIT ? OFFSET ?
  `;
  values.push(pageSize, offset);
  
  return querySQL(sql, values);
}

// 刷新视图（数据变更后调用）
export async function refreshView() {
  // SQLite 的视图是虚拟表，会自动查询最新数据
  // 这里可以做一些缓存清理工作
  return true;
}

5.4 年度数据查询

// 获取所有报表年度
export async function getYears() {
  const sql = `
    SELECT DISTINCT report_year 
    FROM query_report 
    ORDER BY report_year DESC
  `;
  const result = await querySQL<{ report_year: string }>(sql);
  return result.map(row => row.report_year);
}

六、数据备份与恢复机制

6.1 备份策略设计

对于报表系统来说，数据备份至关重要。我们设计了完整的备份恢复机制：

备份方式触发时机用途
手动备份用户点击备份按钮重要操作前备份
定时备份按 cron 表达式自动执行日常数据保护
自动备份数据导入/批量操作前操作保护

6.2 备份实现原理

SQLite 数据库备份有几种方式：

方式原理优点缺点
文件复制直接复制 .db 文件简单直接需要确保没有写入
.backup APISQLite 内置备份 API安全、支持热备部分 Node 驱动不支持
SQL 导出导出为 SQL 语句可读性好大数据量慢

本项目采用 WAL 检查点 + 文件复制 的方案：

// electron/db/db.ts
export async function backupDB(
  backupName: string,
  backupDir: string
): Promise<string> {
  // 确保备份目录存在
  if (!fs.existsSync(backupDir)) {
    fs.mkdirSync(backupDir, { recursive: true });
  }
  
  const backupFilePath = path.join(backupDir, backupName);
  const mainDbPath = path.join(app.getPath("userData"), "sqlite3.db");
  const mainDb = getDB();

  try {
    // 第一步：执行 WAL 检查点，确保所有数据写入主文件
    await new Promise<void>((resolve, reject) => {
      mainDb.run("PRAGMA wal_checkpoint(TRUNCATE);", function (err) {
        err ? reject(new Error(`检查点失败: ${err.message}`)) : resolve();
      });
    });

    // 第二步：使用文件流复制数据库文件
    await new Promise<void>((resolve, reject) => {
      const readStream = fs.createReadStream(mainDbPath);
      const writeStream = fs.createWriteStream(backupFilePath);

      readStream.on("error", reject);
      writeStream.on("error", reject);
      writeStream.on("finish", resolve);

      readStream.pipe(writeStream);
    });

    return backupFilePath;
  } catch (error: unknown) {
    // 删除不完整的备份文件
    if (fs.existsSync(backupFilePath)) {
      fs.unlinkSync(backupFilePath);
    }
    throw error;
  }
}

为什么需要 WAL 检查点？

SQLite 默认使用 WAL（Write-Ahead Logging）模式，写入的数据先记录在 WAL 文件中，不是立即写入主数据库文件。直接复制可能导致数据不完整。

正常状态：
  sqlite3.db (主文件) + sqlite3-wal (WAL日志) + sqlite3-shm (共享内存)
  
执行 wal_checkpoint(TRUNCATE) 后：
  sqlite3.db (所有数据都写入主文件)
  WAL 文件被清空

6.3 恢复实现

export async function restoreDB(backupFilePath: string): Promise<boolean> {
  // 检查备份文件是否存在
  if (!fs.existsSync(backupFilePath)) {
    throw new Error(`备份文件不存在: ${backupFilePath}`);
  }

  const mainDbPath = path.join(app.getPath("userData"), "sqlite3.db");
  const tempDbPath = `${mainDbPath}.tmp`;

  try {
    // 1. 关闭当前数据库连接
    await closeDB();

    // 2. 先备份当前数据库作为临时文件，以防恢复失败
    if (fs.existsSync(mainDbPath)) {
      await copyFileAsync(mainDbPath, tempDbPath);
    }

    // 3. 复制备份文件到主数据库位置
    await copyFileAsync(backupFilePath, mainDbPath);

    // 4. 重新连接数据库
    getDB();

    // 5. 验证数据库完整性
    await new Promise<void>((resolve, reject) => {
      const db = getDB();
      db.run("PRAGMA integrity_check;", function (err) {
        if (err) {
          reject(new Error(`数据库完整性检查失败: ${err.message}`));
        } else {
          resolve();
        }
      });
    });

    // 6. 恢复成功，删除临时备份
    if (fs.existsSync(tempDbPath)) {
      await unlinkAsync(tempDbPath);
    }

    console.log(`数据库已从 ${backupFilePath} 恢复成功`);
    return true;
  } catch (error: unknown) {
    console.error("数据库恢复失败:", error);
    
    // 恢复失败，尝试还原原始数据库
    if (fs.existsSync(tempDbPath)) {
      if (fs.existsSync(mainDbPath)) {
        await unlinkAsync(mainDbPath);
      }
      await copyFileAsync(tempDbPath, mainDbPath);
      await unlinkAsync(tempDbPath);
    }

    // 重新连接数据库
    getDB();
    throw error;
  }
}

恢复流程设计：

开始恢复
   ↓
关闭数据库连接
   ↓
备份当前数据库为 .tmp 文件  ←─── 失败保护
   ↓
复制备份文件覆盖主数据库
   ↓
重新连接数据库
   ↓
完整性检查 ──失败──→ 从 .tmp 还原 ──→ 抛出错误
   │
   成功
   ↓
删除 .tmp 临时文件
   ↓
恢复完成

6.4 备份历史管理

// 获取备份历史
ipcMain.handle("getBackupHistory", async (event, savePath: string) => {
  try {
    // 读取目录中的备份文件
    const files = await fs.promises.readdir(savePath);
    const backupFiles = files.filter((file) => file.endsWith("_backup.db"));

    // 获取文件信息
    const history = [];
    for (const file of backupFiles) {
      const stats = await fs.promises.stat(path.join(savePath, file));
      history.push({
        fileName: file,
        timestamp: stats.ctime.toLocaleString(),
      });
    }

    // 按时间倒序排列
    history.sort(
      (a, b) => new Date(b.timestamp).getTime() - new Date(a.timestamp).getTime()
    );

    return { success: true, data: history };
  } catch (error) {
    return { success: false, message: error.message };
  }
});

七、电子表格与数据库的数据流转

7.1 完整数据流转图

┌───────────────────────────────────────────────────────────┐
│                        用户操作                            │
│                                                           │
│   打开填报页 → 表格初始化 → 加载数据 → 用户编辑 → 保存      │
└───────────────────────────────────────────────────────────┘
                              │
                              ↓
┌───────────────────────────────────────────────────────────┐
│                  Luckysheet (前端表格)                     │
│                                                           │
│  ┌─────────┐    渲染     ┌──────────┐   用户编辑   ┌──────┐ │
│  │ 模板数据 │ ────────→  │ 单元格   │ ─────────→  │ 值   │ │
│  │ (data)  │             │ (Canvas) │             │ 变更 │ │
│  └─────────┘             └──────────┘             └──┬───┘ │
│                                                      │     │
│  getCellValue() / setCellValue()                    │     │
│  getluckysheetfile() / toJson()                     ↓     │
└───────────────────────────────────────────────────────────┘
                              │
                              │ IPC 通信
                              ↓
┌───────────────────────────────────────────────────────────┐
│                    SQLite (主进程数据库)                   │
│                                                           │
│   ┌─────────────────────────────────────────────────┐     │
│   │  fill_report 表                                  │     │
│   │  (report_order, row_num, column_num, cell_value)│     │
│   └─────────────────────────────────────────────────┘     │
│                                                           │
│   稀疏存储：只保存有值的单元格                              │
│   联合主键：(报表序号, 行号, 列号) 唯一标识                 │
└───────────────────────────────────────────────────────────┘

7.2 加载数据流程（数据库 → 表格）

用户打开填报页
    ↓
Luckysheet 初始化（使用模板数据）
    ↓
调用 getAllFillReport() 获取所有填报数据
    ↓
遍历数据，对每个单元格：
  luckysheet.setCellValue(row, col, value, { order: report_order })
    ↓
调用 luckysheet.refresh() 刷新视图
    ↓
数据加载完成

代码示例：

const setCellValues = async () => {
  // 从数据库获取所有填报数据
  const fillReportData = await ReportAPI.getAllFillReport();
  
  if (Array.isArray(fillReportData) && fillReportData.length > 0) {
    // 遍历每个数据项，设置到表格中
    fillReportData.forEach((item) => {
      luckysheet.setCellValue(
        item.row_num,
        item.column_num,
        item.cell_value,
        { order: item.report_order }
      );
    });
  }
  
  // 刷新视图
  luckysheet.refresh();
};

7.3 保存数据流程（表格 → 数据库）

用户点击保存按钮
    ↓
调用 luckysheet.exitEditMode() 退出编辑模式
    ↓
遍历配置的可编辑单元格（editCell）：
  1. luckysheet.getCellValue(row, col) 获取值
  2. 查询数据库是否已有记录
  3. 有 → UPDATE，无 → INSERT
    ↓
全部保存完成
    ↓
提示保存成功

7.4 为什么用配置驱动而不是全表保存

方式数据量性能灵活性
配置驱动（推荐）几十~几百个单元格快需要维护配置
全表遍历几千~上万个单元格慢不需要配置

在报表系统中，可编辑的单元格通常只占很小一部分（比如一张表 500 个单元格，只有 50 个可编辑）。使用配置驱动的方式：

  1. 保存速度提升 10 倍以上
  2. 数据库记录减少 90%
  3. 数据更可控，不会保存意外修改的单元格

八、性能优化实践

8.1 批量操作优化

保存时的并发优化：

// ❌ 串行保存，速度慢
for (const cell of cells) {
  await saveCell(cell);  // 每次都要等待
}

// ✅ 并发保存，速度快
await Promise.all(
  cells.map(cell => saveCell(cell))  // 同时执行
);

注意：SQLite 的并发写入能力有限，过多的并发写入可能导致 SQLITE_BUSY 错误。对于几百条以内的数据，Promise.all 是安全的；如果数据量更大，建议使用分批并发。

8.2 索引优化

为常用的查询字段创建索引：

-- 按组织和年度查询
CREATE INDEX IF NOT EXISTS idx_query_report_org_year 
ON query_report (organization_code, report_year);

-- 按报表序号查询
CREATE INDEX IF NOT EXISTS idx_fill_report_order 
ON fill_report (report_order);

8.3 事务优化

批量操作时使用事务，可以大幅提升性能：

// ❌ 逐条插入，每条一个事务
for (const item of items) {
  await runSQL("INSERT INTO ...", item);
}

// ✅ 一个事务批量插入
async function batchInsert(items: FillReport[]) {
  const db = getDB();
  return new Promise<void>((resolve, reject) => {
    db.serialize(() => {
      db.run("BEGIN TRANSACTION");
      
      const stmt = db.prepare(
        "INSERT INTO fill_report VALUES (?, ?, ?, ?)"
      );
      items.forEach(item => {
        stmt.run(item.report_order, item.row_num, item.column_num, item.cell_value);
      });
      stmt.finalize();
      
      db.run("COMMIT", (err) => {
        err ? reject(err) : resolve();
      });
    });
  });
}

性能对比（1000 条数据）：

方式耗时
逐条插入~2000ms
事务批量插入~50ms

事务可以带来 40 倍以上的性能提升！

8.4 连接复用

使用单例模式复用数据库连接，避免频繁创建关闭：

let db: sqlite3.Database | null = null;

export function getDB() {
  if (db) return db;  // 已存在则直接返回
  
  // 不存在则创建
  db = new sqlite3.Database(dbPath);
  return db;
}

九、定时自动备份实现

9.1 整体设计

┌─────────────────────────────────┐
│         配置文件                 │
│  { cronTime, savePath }         │
└───────────────┬─────────────────┘
                ↓
┌─────────────────────────────────┐
│      taskScheduler.ts           │
│  - 解析 cron 表达式             │
│  - 计算下次执行时间             │
│  - setTimeout 定时触发          │
└───────────────┬─────────────────┘
                ↓
        执行备份操作
                ↓
┌─────────────────────────────────┐
│       备份数据库文件             │
│  backupDB(name, path)           │
└─────────────────────────────────┘

9.2 定时任务调度器

// electron/taskScheduler.ts
import cronParser from "cron-parser";

let backupTimer: NodeJS.Timeout | null = null;

/**
 * 调度备份任务
 * @param cronTime cron 表达式，如 "0 2 * * *"（每天凌晨2点）
 * @param savePath 备份保存路径
 */
export async function scheduleBackupTask(cronTime: string, savePath: string) {
  // 清除旧的定时器
  if (backupTimer) {
    clearTimeout(backupTimer);
    backupTimer = null;
  }

  if (!cronTime || !savePath) {
    console.log("备份配置不完整，跳过调度");
    return;
  }

  const scheduleNext = async () => {
    try {
      // 解析 cron 表达式，计算下次执行时间
      const interval = cronParser.parseExpression(cronTime);
      const nextRun = interval.next().toDate();
      const delay = nextRun.getTime() - Date.now();

      console.log(`下次备份时间: ${nextRun.toLocaleString()}`);

      backupTimer = setTimeout(async () => {
        try {
          // 执行备份
          const now = new Date();
          const fileName = `${formatTime(now)}_backup.db`;
          await backupDB(fileName, savePath);
          console.log(`自动备份成功: ${fileName}`);
        } catch (error) {
          console.error("自动备份失败:", error);
        } finally {
          // 调度下一次
          scheduleNext();
        }
      }, delay);
    } catch (error) {
      console.error("调度备份任务失败:", error);
    }
  };

  scheduleNext();
}

// 格式化时间为文件名
function formatTime(date: Date): string {
  const pad = (n: number) => String(n).padStart(2, "0");
  return `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())}_${pad(date.getHours())}-${pad(date.getMinutes())}-${pad(date.getSeconds())}`;
}

9.3 Cron 表达式说明

*    *    *    *    *
┬    ┬    ┬    ┬    ┬
│    │    │    │    │
│    │    │    │    └── 星期 (0 - 7) (0 或 7 是周日)
│    │    │    └─────── 月份 (1 - 12)
│    │    └──────────── 日期 (1 - 31)
│    └───────────────── 小时 (0 - 23)
└────────────────────── 分钟 (0 - 59)

常用示例：

表达式含义
0 2 * * *每天凌晨 2 点
0 0 * * 0每周日凌晨 0 点
0 0 1 * *每月 1 号凌晨 0 点
*/30 * * * *每 30 分钟

9.4 配置管理

备份配置保存在 JSON 文件中：

// public/config/config.json
{
  "backup": {
    "cronTime": "0 2 * * *",
    "savePath": "D:/backup"
  }
}

保存配置时同时更新定时任务：

ipcMain.handle("saveBackupConfig", async (event, params) => {
  // 1. 写入配置文件
  await fs.promises.writeFile(configPath, JSON.stringify(configData, null, 2));
  
  // 2. 更新定时任务
  await scheduleBackupTask(params.cronTime, params.savePath);
  
  return { success: true, message: "备份配置保存成功，定时任务已更新" };
});

总结

本文从数据库选型、数据模型设计、CRUD 实现、备份恢复机制、数据流转、性能优化等多个维度，详细介绍了电子表格数据与 SQLite 数据库的交互方案。

核心要点回顾：

  1. SQLite 是桌面应用的理想选择：零配置、单文件、性能足够
  2. 稀疏存储设计：只保存有值的单元格，大幅减少数据量
  3. 配置驱动：只操作可编辑单元格，性能提升显著
  4. 完整的备份恢复机制：手动 + 定时 + 自动，数据安全有保障
  5. 性能优化手段：事务、索引、连接复用、并发控制

掌握这些技术，你就能为电子表格系统构建一个稳定、高效的数据持久层。

参考资料

关于作者：本文基于工会预决算报表填报查询系统的实际开发经验撰写，涵盖了电子表格数据持久化的完整技术方案。如果觉得有帮助，欢迎点赞、收藏、关注！

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/u010972645/article/details/167224118

文章来源转载

评论

赞0

评论列表

微信小程序
QQ小程序

关于作者

点赞数：0
关注数：0
粉丝：0
文章：0
关注标签：0
加入于：--