【日常记录】【插件】excel.js 的使用

文章目录

• • 1. 引言
  • 2. excel.js
  • • 2.1 创建工作簿和工作表
    • 2.2 excel文件的导出
    • 2.3 excel文件的导入
    • 2.4 列
    • 2.5 行
    • 2.6 添加行
    • 2.7 单元格
    • 2.8 给总价列设置自动计算(除表头行)
    • 3. 总结
    • 参考链接

      1. 引言

      前端导出excel文件常用库一般是 excel.js 和 xlsx.js

      xlsx.js 导出数据确实方便，但是处理样式不大好处理，需要配合 xlsx-style 来处理样式问题，比较麻烦了

      2. excel.js

      npm 安装方式

      npm install exceljs
      

      浏览器端 安装方式

      2.1 创建工作簿和工作表

      要先创建一个工作簿，然后才能创建工作表

      const workbook = new ExcelJS.Workbook(); // 创建工作簿
      const worksheet = workbook.addWorksheet("导入数据明细", { properties: { tabColor: { argb: 'FFC0000' } } }); // 创建一个工作表
      

      

      用 wps打开就是这个样子， 一般不会设置这个 工作表的其他属性，最常用的还是直接创建一个工作表和设置 冻结行/列，这俩个操作更为常见

      冻结第一行，列不做处理，代码如下

          worksheet.views = [{
            state: 'frozen',
            ySplit: 1,
          }];
      

      

      2.2 excel文件的导出

      官方提供了三种方式，最常用的是 写入 buffer

      第一种：写入文件

      // 写入文件
      const workbook = createAndFillWorkbook();
      await workbook.xlsx.writeFile(filename);
      

      

      这种方式，其实是调用了 node的fs 模块 ，如若在浏览器环境直接调用的话，会报错

      第二种方式：写入流

      await workbook.xlsx.write(stream);
      

      这种方式目前用的比较少，也是依赖于node 环境

      第三种方式：写入buffer

      // 写入 buffer
      const buffer = await workbook.xlsx.writeBuffer();
      

      这种一般比较多见

      1. 写入 buffer ，配合 FileSaver 库，进行导出文件
      2. 用原生 js 方式把 blob转化成一个链接，进行导出

          workbook.xlsx
            .writeBuffer()
            .then((buffer) => {
              const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
              saveAs(blob, "导出excek.xlsx")
            })
      

          workbook.xlsx
            .writeBuffer()
            .then((buffer) => {
              const blob = new Blob([buffer], {
                type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
              });
              let aEl = document.createElement("a");
              aEl.style = "display: none";
              aEl.download = `测试excel导出${new Date().getTime()}.xlsx`;
              aEl.href = window.URL.createObjectURL(blob);// 创建blob 文件链接
              document.body.appendChild(aEl);
              aEl.click();
              document.body.removeChild(aEl);
              window.URL.revokeObjectURL(aEl.href); // 销毁链接
            })
            .catch((err) => {
              console.error(err)
            });
      

      2.3 excel文件的导入

      比如说，导入内容为这样的一个excel文件

      

      
      
        
        
        Document
      
      
        
        
        
          document.querySelector('#file').addEventListener("change", function (e) {
            const file = e.target.files[0];
            if (file) {
              const reader = new FileReader();
              reader.onload = function (e) {
                const workbook = new ExcelJS.Workbook();
                let importData = [];
                workbook.xlsx
                  .load(e.target.result)
                  .then(async (res) => {
                    const worksheet = workbook.getWorksheet(1);
                    let importData = [];
                    worksheet.eachRow(
                      {
                        includeEmpty: true, // 包含空行
                      },
                      function (row, rowNumber) {
                        console.log(row, rowNumber, row.values);
                        importData.push(row.values)
                      }
                    );
                    console.log("所有行", importData);
                  })
                  .catch((err) => {
                    console.log("err", err);
                  });
              };
              reader.readAsArrayBuffer(file);
            }
          });
        
      
      
      

      这里的 row,rowNumber,row.values 打印内容如下

      

      这里要特别注意的是，在 excel 文件里面，三行四列 但这每一行的值，数组长度是5，因为便于取数，这个数组的第一项是null，因为数组第一项索引是0，所以，索引1 才是 这一行第一列的值

      2.4 列

          worksheet.columns = [{
            header: "序号",
            key: "Index",
            width: 7,
          },
          {
            header: "单价",
            key: "Price",
          }, {
            header: "数量",
            key: "Quantity"
          }, {
            header: "总价",
            key: "TotalPrice"
          }]
      

      • header：列的标题
      • key：列表ID
      • width：列的宽度，这个宽度和像素不一样，需要自己调试

        导出的效果如下

        

        官网还列举了其他的案例

        最常用的还是 设置列

        // 添加列标题并定义列键和宽度
        // 注意：这些列结构仅是构建工作簿的方便之处，除了列宽之外，它们不会完全保留。
        worksheet.columns = [
          { header: 'Id', key: 'id', width: 10 },
          { header: 'Name', key: 'name', width: 32 },
          { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
        ];
        // 通过键，字母和基于1的列号访问单个列
        const idCol = worksheet.getColumn('id');
        const nameCol = worksheet.getColumn('B');
        const dobCol = worksheet.getColumn(3);
        // 设置列属性
        // 注意：将覆盖 C1 单元格值
        dobCol.header = 'Date of Birth';
        // 注意：这将覆盖 C1:C2 单元格值
        dobCol.header = ['Date of Birth', 'A.K.A. D.O.B.'];
        // 从现在开始，此列将以 “dob” 而不是 “DOB” 建立索引
        dobCol.key = 'dob';
        dobCol.width = 15;
        // 如果需要，隐藏列
        dobCol.hidden = true;
        // 为列设置大纲级别
        worksheet.getColumn(4).outlineLevel = 0;
        worksheet.getColumn(5).outlineLevel = 1;
        // 列支持一个只读字段，以指示基于 `OutlineLevel` 的折叠状态
        expect(worksheet.getColumn(4).collapsed).to.equal(false);
        expect(worksheet.getColumn(5).collapsed).to.equal(true);
        // 遍历此列中的所有当前单元格
        dobCol.eachCell(function(cell, rowNumber) {
          // ...
        });
        // 遍历此列中的所有当前单元格，包括空单元格
        dobCol.eachCell({ includeEmpty: true }, function(cell, rowNumber) {
          // ...
        });
        // 添加一列新值
        worksheet.getColumn(6).values = [1,2,3,4,5];
        // 添加稀疏列值
        worksheet.getColumn(7).values = [,,2,3,,5,,7,,,,11];
        // 剪切一列或多列（右边的列向左移动）
        // 如果定义了列属性，则会相应地对其进行切割或移动
        // 已知问题：如果拼接导致任何合并的单元格移动，结果可能是不可预测的
        worksheet.spliceColumns(3,2);
        // 删除一列，再插入两列。
        // 注意：第4列及以上的列将右移1列。
        // 另外：如果工作表中的行数多于列插入项中的值，则行将仍然被插入，就好像值存在一样。
        const newCol3Values = [1,2,3,4,5];
        const newCol4Values = ['one', 'two', 'three', 'four', 'five'];
        worksheet.spliceColumns(3, 1, newCol3Values, newCol4Values);
        

        2.5 行

        这个地方，一般常用的是，读取行，读取整个工作表的行/读取指定行的值

        官方的例子如下

        // 获取一个行对象。如果尚不存在，则将返回一个新的空对象
        const row = worksheet.getRow(5);
        // Get multiple row objects. If it doesn't already exist, new empty ones will be returned
        const rows = worksheet.getRows(5, 2); // start, length (>0, else undefined is returned)
        // 获取工作表中的最后一个可编辑行（如果没有，则为 `undefined`）
        const row = worksheet.lastRow;
        // 设置特定的行高
        row.height = 42.5;
        // 隐藏行
        row.hidden = true;
        // 为行设置大纲级别
        worksheet.getRow(4).outlineLevel = 0;
        worksheet.getRow(5).outlineLevel = 1;
        // 行支持一个只读字段，以指示基于 `OutlineLevel` 的折叠状态
        expect(worksheet.getRow(4).collapsed).to.equal(false);
        expect(worksheet.getRow(5).collapsed).to.equal(true);
        row.getCell(1).value = 5; // A5 的值设置为5
        row.getCell('name').value = 'Zeb'; // B5 的值设置为 “Zeb” - 假设第2列仍按名称键入
        row.getCell('C').value = new Date(); // C5 的值设置为当前时间
        // 获取行并作为稀疏数组返回
        // 注意：接口更改：worksheet.getRow(4) ==> worksheet.getRow(4).values
        row = worksheet.getRow(4).values;
        expect(row[5]).toEqual('Kyle');
        // 通过连续数组分配行值（其中数组元素 0 具有值）
        row.values = [1,2,3];
        expect(row.getCell(1).value).toEqual(1);
        expect(row.getCell(2).value).toEqual(2);
        expect(row.getCell(3).value).toEqual(3);
        // 通过稀疏数组分配行值（其中数组元素 0 为 `undefined`）
        const values = []
        values[5] = 7;
        values[10] = 'Hello, World!';
        row.values = values;
        expect(row.getCell(1).value).toBeNull();
        expect(row.getCell(5).value).toEqual(7);
        expect(row.getCell(10).value).toEqual('Hello, World!');
        // 使用列键按对象分配行值
        row.values = {
          id: 13,
          name: 'Thing 1',
          dob: new Date()
        };
        // 在该行下方插入一个分页符
        row.addPageBreak();
        // 遍历工作表中具有值的所有行
        worksheet.eachRow(function(row, rowNumber) {
          console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
        });
        // 遍历工作表中的所有行（包括空行）
        worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
          console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
        });
        // 连续遍历所有非空单元格
        row.eachCell(function(cell, colNumber) {
          console.log('Cell ' + colNumber + ' = ' + cell.value);
        });
        // 遍历一行中的所有单元格（包括空单元格）
        row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
          console.log('Cell ' + colNumber + ' = ' + cell.value);
        });
        // 提交给流一个完成的行
        row.commit();
        // 行尺寸
        const rowSize = row.cellCount;
        const numValues = row.actualCellCount;
        

        2.6 添加行

        这个操作，在导出数据的最为常见

            worksheet.addRow({
              Index: 1,
              Price: 9.9,
              Quantity: 1,
              TotalPrice: 1
            })
            worksheet.insertRow(2, {
              Index: 2,
              Price: 3.8,
              Quantity: 1,
              TotalPrice: 1
            })
        

        首先是添加了一行，这一行在 Excel 中，行数应该是2 ，但是后面又插入了一行，并且指定了行数为2， 那么第一行，就得往下移，效果如下

        

        官方代码

        // Add a couple of Rows by key-value, after the last current row, using the column keys
        worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
        worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
        // Add a row by contiguous Array (assign to columns A, B & C)
        worksheet.addRow([3, 'Sam', new Date()]);
        // Add a row by sparse Array (assign to columns A, E & I)
        const rowValues = [];
        rowValues[1] = 4;
        rowValues[5] = 'Kyle';
        rowValues[9] = new Date();
        worksheet.addRow(rowValues);
        // Add a row with inherited style
        // This new row will have same style as last row
        // And return as row object
        const newRow = worksheet.addRow(rowValues, 'i');
        // Add an array of rows
        const rows = [
          [5,'Bob',new Date()], // row by array
          {id:6, name: 'Barbara', dob: new Date()}
        ];
        // add new rows and return them as array of row objects
        const newRows = worksheet.addRows(rows);
        // Add an array of rows with inherited style
        // These new rows will have same styles as last row
        // and return them as array of row objects
        const newRowsStyled = worksheet.addRows(rows, 'i');
        

        2.7 单元格

        如若要是对第一行的列，进行美化怎么处理呢？

        1. 先获取到第一行
        2. 使用 Excel.js 的 行遍历单元格方法

             const headerRow = worksheet.getRow(1);
            // 遍历一行中的所有单元格（包括空单元格）
            headerRow.eachCell({
              includeEmpty: true
            }, function (cell, colNumber) {
              console.log(cell, colNumber);
        	  // getCell 传入一个单元格地址，就可以获取到这个单元格，然后对这个单元格进行操作
              worksheet.getCell(cell._address).font = {
                size: 16,// 字体大小
                bold: true // 加粗
              }
              worksheet.getCell(cell._address).alignment = {
                vertical: 'middle', // 垂直方向剧中
                horizontal: 'center' // 水平方向剧中
              }
            });
        

        下面这个图，就是每次遍历单元格的时候，第一个参数值，里面有一个 _address 存放的就是当前遍历到的单元格的地址

        

        

        

        2.8 给总价列设置自动计算(除表头行)

        在excel文档中，是可以使用 函数的，同样在excel.js库中也可以使用

            console.log('当前sheet总行数', worksheet.rowCount);
            for (let i = 2; i 
              worksheet.getCell(`D${i}`).value = {
                formula: `PRODUCT(B${i},C${i})`
              };
              console.log(worksheet.getCell(`D${i}`));
            }