使用 node.js 修改现有 Excel 文件

Modify existing Excel File using node.js

提问by jengfad

Is there any way to modify existing excel file in node.js? I've looked into exceljs but it does not provide any functionality that will just modify the existing data. It seems like it will write to a new stream.

有没有办法修改node.js中现有的excel文件？我已经研究过 exceljs，但它不提供任何只会修改现有数据的功能。看起来它会写入一个新的流。

Or did I miss something on exceljs?

还是我错过了 exceljs 上的某些东西？

Thanks in advance.

提前致谢。

回答by Clarence Leung

exceljsdoes let you modify Excel spreadsheets.

exceljs确实允许您修改 Excel 电子表格。

Here's an example of reading in an existing spreadsheet and writing it back out to a different file:

以下是读取现有电子表格并将其写回不同文件的示例：

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

workbook.xlsx.readFile('old.xlsx')
    .then(function() {
        var worksheet = workbook.getWorksheet(1);
        var row = worksheet.getRow(5);
        row.getCell(1).value = 5; // A5's value set to 5
        row.commit();
        return workbook.xlsx.writeFile('new.xlsx');
    })

If you're using the Streams API with exceljs, you can also pipe your stream into fs.createWriteStreamto write to a file as well.

如果您将 Streams API 与 一起使用exceljs，您还可以通过管道将流fs.createWriteStream写入文件。

回答by tejp124

You can modify excel, below is the example.

你可以修改excel，下面是例子。

var Excel = require('exceljs');
async function excelOp() {
    let workbook = new Excel.Workbook();
    workbook = await workbook.xlsx.readFile('question_39869739.xlsx'); // replace question_39869739.xls with your file
    let worksheet = workbook.getWorksheet('sheetname'); // replace sheetname with actual sheet name
    worksheet.getRow('rowNumber').getCell('cellNumber').value = 350; // replace rowNumber and cellNumber with the row and cell you want to modify
    workbook.xlsx.writeFile('question_50508131.xlsx');
}

excelOp();

Have a look at https://www.npmjs.com/package/exceljs#interfacefor all the possible operations with exceljs.

查看https://www.npmjs.com/package/exceljs#interface了解所有可能的 exceljs 操作。

回答by Yogesh Sanchihar

    function faaltu(){
workbook.xlsx.readFile(__dirname+"/Question-file.xlsx")
    .then(function() {

        var worksheet = workbook.getWorksheet(1);
        var row = worksheet.getRow(2);
        console.log(worksheet.rowCount);
        console.log(row.getCell(1).value)
        console.log(row.getCell(2).value);

        var row2=worksheet.getRow(3);
        row2.getCell(1).value=2;
        row2.getCell(2).value='sasasas';
        row2.commit();

        console.log(worksheet.rowCount);
        console.log(row2.getCell(1).value)
        console.log(row2.getCell(2).value);
        workbook.xlsx.writeFile(__dirname+"/new.xlsx");


    })
    .then(()=>{
    var filePath = __dirname+"/Question-file.xlsx"; 
    fs.unlinkSync(filePath);
    fs.rename(__dirname+"/new.xlsx", filePath, function(err) {
    if ( err ) console.log('ERROR: ' + err);
     });    

    });





    /*fs.rename(__dirname+"/new.xlsx", "Question-file.xlsx", function(err) {
    if ( err ) console.log('ERROR: ' + err);
});*/

}   

Steps-

脚步-

1. Modify the file.
2. Write the data to another file.
3. Delete the previous file.
4. Rename the newer file.

1. 修改文件。
2. 将数据写入另一个文件。
3. 删除之前的文件。
4. 重命名较新的文件。

回答by Ashish Sharma

Thankyou all answer. same file you can update row value by below code No need to rename or create new file.

谢谢大家解答。同一个文件，您可以通过以下代码更新行值 无需重命名或创建新文件。

    let filename = 'src/write.xlsx';
    let workbook = new Excel.Workbook();
    await workbook.xlsx.readFile(filename);
    let worksheet = workbook.getWorksheet("Sheet1");
    // header id name dob
    let row = worksheet.getRow(3);
    console.log(row);
    row.getCell(1).value = 2;
    row.getCell(2).value = 'test';
    row.getCell(3).value = '12/09/1991';
    row.commit();
    workbook.xlsx.writeFile('src/write.xlsx');