exceljs解析和生成excel文件

安装

 npm install exceljs

解析excel

1. 通过 Workbook 的 readFile 方法可以拿到workbook对象, workbook对象包含的概念有 worksheet(工作表) --> row(行) --> cell(单元格).于是可以通过依次遍历 worksheet, row, cell来拿到单元格的数据
2. 直接通过 worksheet.getSheetValues() 拿到数据(包含了列号,还有行号那些数据的位置,以empty代替)

const { Workbook } = require("exceljs");const workbook = new Workbook();(async function () {const res = await workbook.xlsx.readFile("./test.xlsx");// const res = await workbook.xlsx.load(File对象); 如果在浏览器端,可以直接 load 一个 File对象// each* 方法可以遍历// 遍历工作表res.eachSheet(item => {// 第1种 遍历行// item.eachRow((row, rowIndex) => {//   const rowData = [];//   // 遍历 单元格//   row.eachCell((cell, cellIndex) => {//     rowData.push(cell.value);//   });//   console.log(rowData);// });// 第2种直接通过getSheetValues湖获取const res = item.getSheetValues();console.log(res);});
})();

生成excel

1. 创建 workbook, 添加worksheet, 设置columns列,然后添加行数据rowData

const { Workbook } = require("exceljs");const workbook = new Workbook();(async function () {const sheet = workbook.addWorksheet("test-sheet");// 列的定义sheet.columns = [{ header: "id", key: "id", width: 20 },{ header: "姓名", key: "nickname", width: 30 },{ header: "年龄", key: "age", width: 30 },{ header: "手机号", key: "phone", width: 50 }];// 行数据const rowData = [{ id: 1, nickname: "小明1", age: 20, phone: "123456789" },{ id: 2, nickname: "小明2", age: 20, phone: "123456789" },{ id: 3, nickname: "小明3", age: 20, phone: "123456789" },{ id: 4, nickname: "小明4", age: 20, phone: "123456789" }];sheet.addRows(rowData);workbook.xlsx.writeFile("./test1.xlsx");// 如果是浏览器端,那么新建一个 ArrayBuffer,// const arraybuffer = new ArrayBuffer(10 * 1024 * 1024);// const res = await workbook.xlsx.writeBuffer(arraybuffer);// console.log(res.buffer); // 将buffer通过 a 标签就可以进行下载
})();