Original link: https://www.ixiqin.com/2022/08/03/use-the-node-excel-to-press-line-processing-excel-data-stream/
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 = inputs.map((input) => { // '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 writer.save();}).then((stream) => { stream.pipe(fs.createWriteStream('data.xlsx'));});
Summarize
If you need to import Excel into Js for processing, then node-excel-stream is a good choice.
This article is reprinted from: https://www.ixiqin.com/2022/08/03/use-the-node-excel-to-press-line-processing-excel-data-stream/
This site is for inclusion only, and the copyright belongs to the original author.