Thank you @Steve-Mcl and @TotallyInformation for your contributions.
We are still looking for a solution.
In node red, should @antoniosm be able to read the xslx file in, decompress it, feed it through an xml node to convert to a js object, edit the cell, back through xml file again to convert back, compress it, and write it back out again?
They could do that certainly, I am saying that they shouldn't.
What should be done is to use a library that will simply update a specific cell. The behind-the-scenes mechanism shouldn't need to be visible at all.
Of course, if the tabular data needs to be processed in some way in order to do an update, then you will get to a point where it is probably more sensible to read the sheet into a JSON (not XML) object, process it and either update specific cells or the whole sheet depending on how many changes are made and whether your input data includes Excel formula-based data.
Trying to unpack an xlsx file to update data is fraught with potential issues of understanding the structures and XML schema's involved. Others have already done that, don't reinvent the wheel.
OK, understood.
@antoniosm a google search for
nodejs edit excel file
yields a number of hits, or if you can't find anything there then apparently there are Python libraries that will let you do what you want. You can call a python script from node red using an Exec node.
You could also call out to a PowerShell script if Node-RED is running on Windows. PowerShell wraps .NET and lets you work with Windows and Office API's.
Best to get one of the node.js libraries though if you can't find a suitable node in the library.
Of course, you haven't really explained what it is you are trying to achieve - there are many ways to update something in Excel and using Node-RED to directly update the sheet may not be the easiest or best. For example, you could use Node-RED as an API server and have a formula or PowerQuery in Excel that fetches the data instead.
Hi Colin,
I will try to develop a Python solution, I think it the better solution.
Thanks
Hi Julian,
I try on to update the value of a especific cell. I know the line and the column. That is my goal.
Thanks a lot for your contributions.
Hi Dceejay,
It seems greate to use Exceljs. Thanks.
I ran "npm install exceljs" and after that I reboot the Node-red.
I tried this option (it's a test):
const Excel = require('exceljs');
const fileName = 'file.xls';
const wb = new Excel.Workbook();
const ws = wb.addWorksheet('sheet');
ws.getCell('A1').value = 'John Doe';
ws.getCell('B1').value = 'gardener';
ws.getCell('C1').value = new Date().toLocaleString();
const r3 = ws.getRow(3);
r3.values = [1, 2, 3, 4, 5, 6];
wb.xlsx
.writeFile(fileName)
.then(() => {
console.log('file created');
})
.catch(err => {
console.log(err.message);
});
return msg;
but this error appear:
"ReferenceError: require is not defined (line 1, col 15)"
That is not how you import module in node-red.
See this article : Use any npm module in Node-RED ā¢ FlowFuse for instruction
Hi Stephen,
I setuped the fuction to use module execjs:
I modifed the sentence in "On message":
var Excel = exceljs;
const workbook = new Excel.Workbook();
workbook.xlsx.readFile('C:\asm\clientes\Inovadoor\Pasta1.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');
})
return msg;
But when I run, the node-red abort.
Could help me?
Show us the error, we are not telepathic.
But first use forward slashes in file names, otherwise you have to escape them. Forward slashes work in Windows.
If you showed us the error, it would be immediately obvious what (part of) the problem was
The console would show something like:
[NR] 20/08/2023 14:48:06 [info] 20 Aug 14:48:06 - [red] Uncaught Exception:
[NR] 20/08/2023 14:48:06 [error] Error: File not found: C: asmclientesInovadoorPasta1.xlsx
at XLSX.readFile (C:\opt\flowforge-device\project\node_modules\exceljs\lib\xlsx\xlsx.js:51:13)
at which point we would recognise the file name you used was not was attempted to be written.
Try this - it writes a file but I dont have / dont use MS Office so cannot test.
const inputFile = 'C:/temp/template.xlsx'
const outputFile = 'C:/temp/new_workbook.xlsx'
try {
const workbook = new exceljs.Workbook();
await workbook.csv.readFile(inputFile)
const worksheet = workbook.getWorksheet(1);
const cell = worksheet.getCell('A5');
cell.value = new Date(1968, 5, 1);
await workbook.xlsx.writeFile(outputFile)
msg.payload = `Success. Read in '${inputFile}', modified cell A5 and wrote it to '${outputFile}'`
return msg
} catch(e) {
msg.error = e
node.error(`Error writing to file '${outputFile}'`, msg);
}
PS: You were crashing node-red because you were not handling errors (you did not have a catch on the promise. I re-wrote it with async/await & wrapped it in a try catch to avoid crashing Node-RED
Proof:
Hi Colin,
After use the slashes in file names, the flow are working !
thanks
Nice solution Stephen , thank you very much.
I'll try to update the especific cell now.
I would like to thank everyone for contributing to the solution.
This sentence that is working for them.
The problem is that when the Excel file is very large and contains many formulas, when running the flow, the Node-Red window aborts, but in smaller files it works very well.
const inputFile = 'c:/asm/clientes/Inovadoor/teste.xlsx'
const outputFile = 'c:/asm/clientes/Inovadoor/new.xlsx'
try {
const workbook = new exceljs.Workbook();
await workbook.xlsx.readFile(inputFile);
const worksheet = workbook.getWorksheet(1);
const cell = worksheet.getCell('A1833');
cell.value = 123456;
await workbook.xlsx.writeFile(outputFile);
msg.payload = `Success. Read in '${inputFile}', modified cell A1833 and wrote it to '${outputFile}'`
return msg
} catch (e) {
msg.error = e
node.error(`Error writing to file '${outputFile}'`, msg);
}
return msg;
You might also give alasql - npm (npmjs.com) a go. But if the file is really large, it generally needs to be totally read into memory in order to be processed. This is mostly true for all languages though occasionally you might be able to process a sheet using a stream.
Also, obviously, a sheet containing lots of Excel formulae is always going to be difficult for any non-native language to process.
Honestly, as mentioned before, it would be better to get Excel to ask for the updated cell data from Node-RED rather than trying to push it the other way.
That library supports Streaming IO. You might have more success with that.
I have never used this lib so I do not have an example for you (but there is a lot of info around the web and in the issues to work from)
e.g: node.js - Using stream to modify existing Excel file using ExcelJS - Stack Overflow
You initialize your workbooks differently if you want to use streaming. Rather than setting the workbook with a constructor you would do something along the lines of:
const workbook = new Excel.stream.xlsx.WorkbookReader(options);Where options is:
{ filename: 'filename' }The interface for writing is
Excel.stream.xlsx.WorkbookWriter(options);After that everything else can be done the same. The package does list some minor differences:
- Once a worksheet is added to a workbook, it cannot be removed.
- Once a row is committed, it is no longer accessible since it will have been dropped from the worksheet.
- unMergeCells() is not supported.
As long as you can work within those constraints the streaming option will likely be the most memory performant option for you.
