3

I'm working on a NodeJS project and I need to modify an existing Excel spreadsheet but without changing the original format or links, just adding values to a couple of cells. Currently I've been using exceljs which works very well when it comes to modify the spreadsheet but the output removes all the original styles, formats and links.

Is there another javascript npm or library that can help me with this issue or is it possible with exceljs?

Here's what I have so far:

Fetch('url of the file')
    .then(res => res.buffer())
    .then(buffer => {
        var workbook = new Excel.Workbook();
        workbook.xlsx.load(buffer).then(function() {
            var worksheet = workbook.getWorksheet(1);
            var row = worksheet.getRow(4);
            row.getCell(2).value = 'It works!';
            row.commit();

            workbook.xlsx.write(stream)
                .then(function() {
                    // done
                    // here I take the stream and upload it to an AWS S3 bucket
                });
        });
    })

Thanks!

Ramon Zuniga
  • 305
  • 2
  • 14

2 Answers2

4

I found another npm that does it great! It's xlsx-populate.

Here's the same code that used with exceljs to do the same job: 

Fetch('file url')
    .then(res => res.buffer())
    .then(buffer => {
        XlsxPopulate.fromDataAsync(buffer)
            .then(workbook => {
                // Make edits.
                workbook.sheet(0).cell("A1").value("foo");

                // Get the output
                return workbook.outputAsync();
            })
            .then(data => {
                // upload data to AWS S3
            })
            .catch(err => console.error(err));
    });

Hope this helps to someone going through the same case :)

Ramon Zuniga
  • 305
  • 2
  • 14
  • 1
    Thanks @monchoz! It was exacly what I've been looking for, other `npm` packages just removed all formatting, but this one keeps everything! – justd Oct 01 '19 at 13:06
  • Thanks @monchoz. It really worked. I have faced weird problems with exceljs library. – RT Bathula Oct 02 '20 at 19:52
1

For those looking for a TypeScript version of this ...

  1. install XlsxPopulate as usual
  2. Bring in the TypeScript type for this component from JanLoebel's excellent work (https://github.com/JanLoebel/types-xlsx-populate)
  3. Write code to modify existing Excel files as follows:
    import { fromFileAsync } from 'xlsx-populate';
    ...
    fromFileAsync('input.xlsx').then(workbook => {
        let ws=workbook.sheet("Sheet1");
        ws.cell("C1").value("value in C1");
        ws.cell("C2").value("value in C2");
        return workbook.toFileAsync('input.xlsx');
    });

Hope it's useful to someone.

taiff
  • 89
  • 8