Node.js实现Excel文件打开、保存、关闭及公式自动计算方案

Sheet1通过代码写入数据，Sheet2包含大量引用Sheet1的公式（例如=sum(Sheet1!B2:B3)）。Excel仅在打开文件时才会计算公式结果，若未打开、保存并关闭文件就导入计算结果，值会为空或零。

在Python中可通过xlwings后台打开、保存、关闭文件解决该问题，代码如下：

import xlwings as xl
import os

app = xl.App(visible=False)
book = app.books.open(os.path.join(path, 'test.xlsx'))
book.save()
app.kill()

但在Node.js中，尝试xlsxpopulate、xlsx、ExcelJS等库均无法实现类似效果，公式重计算也未成功。需求是：实现Node.js代码打开、保存、关闭xlsx文件，或在写入数据时触发公式计算，以便后续将结果导入其他文件。当前使用ExcelJS写入数据的代码如下：

const ExcelJS = require('exceljs');

async function insertDFExcel(archive, df) {
    const workbook = new ExcelJS.Workbook();

    try {        
        await workbook.xlsx.readFile(arquivo); // 注意此处变量名错误，应为archive

        const worksheet = workbook.getWorksheet('Sheet2'); // 原问题中Sheet1接收数据，此处需修正

        df.forEach((row) => {
            worksheet.addRow(row);
        });
        
        await workbook.xlsx.writeFile(archive);
        console.log('Ok.');
    } catch (error) {
        console.error('Error:', error);
    }
}

const df = [
    ['Name', 'Age'],
    ['João', 30],
    ['Maria', 25]
];

const archive = 'path/archive.xlsx';

insertDFExcel(archive, df);

方案1：ExcelJS强制配置计算属性 #

ExcelJS支持通过配置calcProperties和calculate选项触发公式计算，适合简单公式场景：

const ExcelJS = require('exceljs');

async function insertDFExcel(archive, df) {
    const workbook = new ExcelJS.Workbook();

    try {        
        // 读取文件时设置打开即全量计算
        await workbook.xlsx.readFile(archive, {
            calcProperties: {
                fullCalcOnLoad: true
            }
        });

        const worksheet = workbook.getWorksheet('Sheet1'); // 修正为接收数据的Sheet1

        // 跳过表头（若原文件已有表头），写入数据行
        df.slice(1).forEach((row) => {
            worksheet.addRow(row);
        });
        
        // 写入时触发全量计算并保存
        await workbook.xlsx.writeFile(archive, {
            calcProperties: {
                fullCalcOnLoad: true
            },
            calculate: true
        });
        console.log('数据写入并完成公式计算');
    } catch (error) {
        console.error('错误:', error);
    }
}

const df = [
    ['Name', 'Age'],
    ['João', 30],
    ['Maria', 25]
];

const archive = 'path/archive.xlsx';

insertDFExcel(archive, df);

方案2：使用node-xlwings调用原生Excel（推荐复杂公式场景） #

该方案和Python的xlwings逻辑完全一致，通过调用本地Excel后台实例完成计算，能保证所有公式和Excel原生计算结果一致。

首先安装依赖：

npm install node-xlwings

代码示例：

const xl = require('node-xlwings');
const path = require('path');

async function updateExcelAndCalculate(archive, df) {
    // 启动不可见的Excel实例
    const app = await xl.App({ visible: false });
    try {
        // 打开目标工作簿
        const book = await app.books.open(path.resolve(archive));
        
        // 获取Sheet1并写入数据（从A1单元格开始）
        const sheet = book.sheets('Sheet1');
        sheet.range('A1').value = df;
        
        // 保存工作簿，此时Excel已自动完成公式计算
        await book.save();
        console.log('数据写入并完成公式计算，已保存');
    } catch (error) {
        console.error('错误:', error);
    } finally {
        // 关闭Excel实例
        await app.kill();
    }
}

const df = [
    ['Name', 'Age'],
    ['João', 30],
    ['Maria', 25]
];

const archive = 'path/archive.xlsx';

updateExcelAndCalculate(archive, df);

方案3：xlsx-populate触发公式计算 #

xlsx-populate自带公式计算方法，对简单公式支持较好：

安装依赖：

npm install xlsx-populate

代码示例：

const XlsxPopulate = require('xlsx-populate');
const path = require('path');

async function updateAndCalculate(archive, df) {
    try {
        // 打开工作簿
        const workbook = await XlsxPopulate.fromFileAsync(path.resolve(archive));
        
        // 获取Sheet1并写入数据
        const sheet = workbook.sheet('Sheet1');
        sheet.cell('A1').value(df);
        
        // 触发全工作簿公式计算
        await workbook.calc();
        
        // 保存文件
        await workbook.toFileAsync(path.resolve(archive));
        console.log('数据写入并完成公式计算');
    } catch (error) {
        console.error('错误:', error);
    }
}

const df = [
    ['Name', 'Age'],
    ['João', 30],
    ['Maria', 25]
];

const archive = 'path/archive.xlsx';

updateAndCalculate(archive, df);

内容的提问来源于stack exchange，提问作者Wanderson Bittencourt