核心使用 node-xlsx
模块
ExcelCreator.js
模块const xlsx = require('node-xlsx').default; const fs = require('fs'); class ExcelCreator{ constructor(opts) { let {filePath,sheets} = opts; this.filePath_ = filePath; this.sheets_ = sheets; } build(){ let buffer = xlsx.build(this.sheets_); // Returns a buffer fs.writeFile(this.filePath_, buffer, "binary",function(err) { }); } } module.exports = ExcelCreator;
SheetDataCreator.js
模块class SheetDataCreator{ constructor(opts = {}) { let {rowLen,colLen,cell,calcConf} = opts; this.rowLen_ = rowLen; this.colLen_ = colLen; this.cell_ = cell; this.calcConf_ = calcConf; this.cellTextLength_ = this.cell_ && this.cell_.text.length; } getCellTextDesc(){ if(this.cell_.textFixed){ return `文本固定${this.cell_.text.length}个字符`; } return ''; } buildFileName(opts = {}){ const {fileExt = 'xlsx'} = opts; const prefix = `EP-${this.rowLen_}行-${this.colLen_}列-${this.rowLen_*this.colLen_}个单元格`; if(this.calcConf_){ return `${prefix}包含计算公式${this.calcConf_.expressing}.${fileExt}` } return `${prefix}${this.getCellTextDesc()}.${fileExt}` } getRandomCellDataForCalc(rowIndex,colIndex){ if(rowIndex===0){ return colIndex; } return {f:`=${this.calcConf_.expressing}(A1:L1)`}; } getRandomCellData(rowIndex,colIndex){ if(this.calcConf_){ return this.getRandomCellDataForCalc(rowIndex,colIndex); } if(this.cell_ && this.cell_.text){ if(this.cell_.textFixed){ return this.cell_.text; } if(this.cellTextLength_){ let end = parseInt(Math.random(0,1) * this.cellTextLength_); return this.cell_.text.substr(parseInt(end*Math.random(0,1)), end); } } } getCellsData(){ let result = []; for (let i = 0; i < this.rowLen_; i++) { let rowData = []; for (let j = 0; j < this.colLen_; j++) { rowData.push(this.getRandomCellData(i,j)) } result.push(rowData); } return result; } buildSheets(){ let cellsData = this.getCellsData(); let result = [{ name: "mySheetName", data: cellsData }]; // console.log(result) return result; } } module.exports = SheetDataCreator;
xlsx
文件const fs = require('fs'); const path = require('path'); const ExcelCreator = require('./lib/ExcelCreator'); const SheetDataCreator = require('./lib/SheetDataCreator'); const sd = new SheetDataCreator({ "rowLen": 2500, //行数 "colLen": 10, //列数 "calcConf": { "expressing": "SUM" //计算公式配置 }, "cell": { text: 'none' //文本内容 } }); let ec = new ExcelCreator({ filePath: path.join('cal-output', sd.buildFileName({ fileExt: 'xlsx', })), //配置输出目录 sheets: sd.buildSheets() //构建表格 }); ec.build(); //执行任务