Use node-excel-stream to process Excel data row by row

Original link:


Data analysis is a very common requirement, and in actual landing scenarios, Python is the most used. However, because I have written the front-end for a long time, I am actually rusty with Python. When I start a project, I choose to do npm init to initialize a project. That being the case, try using Node.js for data analysis.

To operate Excel in Node.js, the best is Exceljs . However, ExcelJs encapsulates a large number of functions, which may not be applicable to most data analysis scenarios (not necessarily, but I prefer to use code to describe logic, Excel is more of an import and export).

When it became clear that I just needed a simple import and export, then node-excel-stream came into my sight.

Read Excel content

Unlike Exceljs, the package of node-excel-stream is relatively simple. It is a Reader and Writer. The methods provided are also very simple: read files, define formats, and process content by line;

It should be noted that node-excel-stream only supports xlsx, not xls, so if you are using an older version, you need to re-save it as xlsx for processing.

 let dataStream = fs.createReadStream('data.xlsx');let reader = new ExcelReader(dataStream, { sheets: [{ name: 'Users', rows: { headerRow: 1, allowedHeaders: [{ name: 'User Name', key: 'userName' }, { name: 'Value', key: 'value', type: Number }] } }]})console.log('starting parse');reader.eachRow((rowData, rowNum, sheetSchema) => { console.log(rowData);}).then(() => { console.log('done parsing');});

Write Excel content

When writing and reading, it is slightly more complicated, and all inputs need to be wrapped with Promise.all

 let writer = new ExcelWriter({ sheets: [{ name: 'Test Sheet', key: 'tests', headers: [{ name: 'Test Name', key: 'name' }, { name: 'Test Coverage', key: 'testValue', default: 0 }] }]});let dataPromises = => { // 'tests' is the key of the sheet. That is used // to add data to only the Test Sheet writer.addData('tests', input);});Promise.all(dataPromises).then(() => { return;}).then((stream) => { stream.pipe(fs.createWriteStream('data.xlsx'));});


If you need to import Excel into Js for processing, then node-excel-stream is a good choice.

This article is reprinted from:
This site is for inclusion only, and the copyright belongs to the original author.

Leave a Comment