使用exceljs软件包npm读写xlsm文件

问题描述

我有一个xlsm文件，其中已包含少量数据，需要在自动化期间写入一些数据并创建一个新的xlsm文件.使用以下代码创建文件，但是该文件损坏并且无法打开.文件大小从8kb减小到7kb.不确定写入文件时会丢失什么.

I have a xlsm file with few data already in it and need to write some data and create a new xlsm file during automation. With below code the file gets created , but it becomes corrupt and unable to open. File size reduces, for ex from 8kb to 7kb. Not sure what is getting missed while writing the file.

var Excel = require('exceljs');
var workbook = new Excel.Workbook();

workbook.xlsx.readFile('Book.xlsm')
.then(function () {
var worksheet = workbook.getWorksheet(1);
var row = worksheet.getRow(1);
console.log(row.getCell(1).value + " - First value"); // Get A1 value
row.getCell(3).value = "c"; //Set value to A3
row.commit();
return workbook.xlsx.writeFile('new.xlsm');
})

注意:刚刚创建的Book.xlsm具有一些值列a，b和值1,2.尝试将A3设置为'c'并另存为new.xlsm

Note : Just created Book.xlsm with some value columns a,b and values 1,2. Trying to set A3 with 'c' and save as new.xlsm

如果还有其他不存在此问题的npm软件包，那就太好了.

If there are any other npm package which doesn't have this problem also would be great.

推荐答案

我认为当前exeljs不适合用于xlsm文件的软件包.对于这种情况，更强大的软件包是 xlsx .

I think that currently exeljs is not suitable package for working with xlsm files. More robust package for this case is xlsx.

这是一个小代码段，它将演示如何读取现有的xlsm文件，向其中添加其他数据并写入新的xlsm文件.您可以在NodeJS环境中对其进行测试.

Here is a small code snippet that will demonstrate reading existing xlsm file, adding additional data to it and writing new xlsm file. You can test it in NodeJS environment.

请注意，在处理xlsm文件时，请将bookVBA: true选项传递给readFile方法，默认情况下为false.有关详细信息，请参见解析选项.

Note that when working with xlsm files you pass bookVBA: true option to readFile method, which by default is false. See parsing options for details.

const XLSX = require('xlsx');

try {

    const workbook = XLSX.readFile('./test.xlsm', { bookVBA: true });
    let worksheet = workbook.Sheets['Sheet1'];
    XLSX.utils.sheet_add_aoa(worksheet, [
        ['text in A1', 'text in B1', 5000],
        ['text in A2', 'text in B2', 'text in C2'],
        [null, new Date(), 'text in C3', 'text in D3']
    ]);
    XLSX.writeFile(workbook, './output.xlsm');
    console.log('Completed ...');

} catch (error) {
    console.log(error.message);
    console.log(error.stack);
}

有关详细信息，请参见支持的输出格式.

See the supported output formats for details.