excelJS——将接口返回的数据生成excel表格，支持图片

需求/背景

最近接手一个项目，需要导出excel表格，因为数据量太大，涉及到的接口比较多，所以文件下载这个功能移交给前端。需要实现的功能是

导出多张sheet（工作表）
支持一张sheet包含多个独立的表格
支持导出图片
表格数据支持自定义列宽，文字居中
接口是分页，需轮询获取全部数据
表头需合并

效果图

碰到的问题

1:插件选哪个？

目前使用最多的插件就是xlsx和excelJS。根据需求确定插件，xlsx更易上手，api比较少，但是xlsx只有pro版本才支持图片的下载，免费版本不支持，而且数据样式需要额外下载xlsx-style。所以经过技术调研，最终确定使用excelJS。

xlsx

excelJS

file-server

项目实现

const handle_exports_json_excel = async ({
  filename,
  excelData = [],
  bookType = 'xlsx'
}) => {
  let workbook = null
  let worksheet = null
  workbook = new ExcelJS.Workbook()
  workbook.created = new Date()
  workbook.modified = new Date()

  excelData.forEach(async sheet => {
    let worksheet = null
    //添加新的工作表
    worksheet = workbook.addWorksheet(sheet.sheetNames)
    //添加表数据，需区分一张表内是否有多个独立的表格
    for (let e = 0; e < sheet.header.length; e++){
      //将数据写入工作表
      worksheet.addRows([
        ...sheet.header[e],
        ...sheet.data[e]
      ])
      //添加一行空数据
      worksheet.addRows([''])
    }
    //判断当前表是否需要自定义列宽
    if (sheet.customWidth) {
      sheet.customWidth.forEach((width,wIndex) => {
        const column = worksheet.getColumn(wIndex+1); // 获取B列（第二列）
        if (column) {
          column.width = width || 24; // 设置B列的宽度为30
        }
        wIndex && column.eachCell({ minCol: column, maxCol: column }, (cell, rowNum) => {
          // 设置文字居中
          cell.style = {
            alignment: {
              horizontal: 'center',
              vertical: 'center'
            }
          };
        });
      })
    }
    //判断当前表是否需要自适应列宽
    if (sheet.autoWidth) {
      // 设置最大宽度
      const maxWidth = 70; // 你可以根据需要调整这个值
      //获取表格列数
      const columnCount = sheet.data.reduce((prev, cur) => (prev=prev+cur.length),0)
      // 遍历工作表的每一列
      for (let column = 1; column <= columnCount; column++) {
        let maxWidthInColumn = 0;

        // 遍历当前列的所有单元格
        worksheet.getColumn(column).eachCell({ minCol: column, maxCol: column }, (cell, rowNum) => {
          // 计算单元格内容的宽度
          const cellWidth = cell.value ? cell.value.toString().length : 0;
          // 更新当前列的最大宽度
          if (cellWidth > maxWidthInColumn && cellWidth < 70) {
            maxWidthInColumn = cellWidth;
          }
          // 设置文字居中
          cell.style = {
            alignment: {
              horizontal: 'center',
              vertical: 'center'
            }
          };
        });

        // 设置列的宽度，不超过最大宽度
        const finalWidth = Math.min(maxWidthInColumn * 2.5, maxWidth); // 假设每个字符宽度为10，你可以根据需要调整这个值
        worksheet.getColumn(column).width = finalWidth;
      }
    }
    //判断是否需要插入图片
    if (sheet.insertImage) {
      // 将静态资源转换为base64
      // 获取图片URL或者Blob对象
      // 将Canvas内容转换为Data URL
      // let dataUrl = invotice_ex
      // canvas.toDataURL("image/png", 1.0);
      // const imgUrl = require('@/assets/images/putaway.png')
      // let dataUrl = await imgToBase64(imgUrl)
      // console.log(dataUrl)

      const imageId = workbook.addImage({
        base64: sheet.insertImage,
        extension: 'png', // 图片的扩展名
      });
      worksheet.addImage(imageId, {
        tl: {
          col: 1,
          row: 0,
        },
        ext: {
          width: 500,
          height: 300,
        },
      // 超链接
        hyperlinks: {
          hyperlink: 'https://www.baidu.com',
          tooltip: 'https://www.baidu.com',
        },
      })
    }
  })

  const buffer = await workbook.xlsx.writeBuffer()
  saveAs(
    new Blob([buffer], {
      type: 'application/octet-stream',
    }),
    `${filename}.${bookType}`
  )
}

1：获取数据

因为接口是分页的，所以需要轮询接口，一直到获取最后一页。

2：组装数据

excelJS基本实现一个excel表格方法有很多种，在设计组件时，想的是尽量不需要额外操作excel插件，将数据直接封装好插入。所以基本思路就是通过addRows来实现，不区分表头表身两个数据。另外需考虑一次性要生成多个sheet，具体数据格式如下：

// 第一部分数据  
const data = [  
    ['信息', ''],  
    ['姓名', 'xx'],  
    ['年龄', 18],  
    ['居住地', '杭州'],  
    ['头像', 'http://xxxx'] 
]; 
const data1 = [
    ['姓名', '年龄','居住地','头像'],  
    ['xx', '18','杭州','http://xxxx'], 
]

//方法调用
handle_exports_json_excel({
  filename: row.shopId + row.shopName + row.settlementPeriod,
  excelData: [
    {
      sheetNames: 'sheet1',
      header: [[]],
      data: [data],
      merges: [], //是否需要合并单元格
      autoWidth: false,//是否自适应列宽
      customWidth: [100, 70], //是否单独设置列宽
      insertImage:imgDataUrl，//是否需要单独设置图片，图片需提前转为base64
    },
    {
      sheetNames: 'sheet1',
      header: [[]],
      data: [data1],
      merges: [], //是否需要合并单元格
      autoWidth: true,//是否自适应列宽
    }
  ]
})

上述代码需要提前将图片转成base64，如果在解析excel是转base64会出现延迟的问题，导出的表格里图片不显示，所以在组装数据时就需要把图片转了，转base64的方法很多。利用canvas或者fetch都可以，我这个项目是使用的canvas，不管是url还是本地静态资源都支持。

//转本地资源、url为base64
export async function imgToBase64(url) {
    return new Promise((resolve, reject) => {
        const image = new Image()
        image.src = url
        image.crossOrigin = true;
        image.onload = () => {
          const canvas = document.createElement('canvas')
          canvas.crossOrigin = 'anonymous';
          canvas.width = image.naturalWidth // 使用 naturalWidth 为了保证图片的清晰度
          canvas.height = image.naturalHeight
          canvas.style.width = `${canvas.width / window.devicePixelRatio}px`
          canvas.style.height = `${canvas.height / window.devicePixelRatio}px`
          const ctx = canvas.getContext('2d')
          if (!ctx) {
             return null
          }
          ctx.drawImage(image, 0, 0)
          const base64 = canvas.toDataURL('image/png')
          return resolve(base64)
        }
        image.onerror = (err) => {
            return reject(err);
        }
    })
}

//获取图片资源信息
const imgUrl = require('@/assets/images/invoice_ex.png')
let imgDataUrl = await imgToBase64(imgUrl)

3:创建工作簿

至此，数据组装完成，接下来就只需要根据数据将表格渲染出来

1:初始化工作薄

let workbook = null
workbook = new ExcelJS.Workbook()
workbook.created = new Date()
workbook.modified = new Date()

2:循环数组添加新的sheet，并将数据添加进表格；如果当前表格中数据有图片，需提前将图片信息转为base64并利用addImage添加

excelData.forEach(async sheet => {
    let worksheet = null
    //添加新的工作表
    worksheet = workbook.addWorksheet(sheet.sheetNames)
    //添加表数据，需区分一张表内是否有多个独立的表格
    for (let e = 0; e < sheet.header.length; e++){
      //将数据写入工作表
      worksheet.addRows([
        ...sheet.header[e],
        ...sheet.data[e]
      ])
      //如果表格数据每条都有图片，并且是第二个数据
      if(e == 1){
        const image = workbook.addImage({  
          base64: '', // 这里是你的图片的Base64编码  
          extension: 'png' // 图片格式  
        });
        // 添加图片到单元格  
        worksheet.addImage(rowData.imageId, {  
          tl: { col: 1, row: e + 1 }, // 图片的左上角位置（列和行）  
          ext: { width: 50, height: 50 } // 图片的尺寸  
        });
      }
      //添加一行空数据
      worksheet.addRows([''])
    }
    ...
  }
)

3:判断当前表是否需要自定义or自适应列宽，并将每个单元格数据居中；自适应列宽需要根据当前单元格内数据的长度来判断，同时还需要设置一个最大列宽

worksheet.getColumn(wIndex+1):获取当前sheet第wIndex+1列
column.eachCell:获取当前列的每个单元格
cell.style:设置当前列的样式

//判断当前表是否需要自定义列宽
if (sheet.customWidth) {
  sheet.customWidth.forEach((width,wIndex) => {
    const column = worksheet.getColumn(wIndex+1); // 获取B列（第二列）
    if (column) {
      column.width = width || 24; // 设置B列的宽度为30
    }
    wIndex && column.eachCell({ minCol: column, maxCol: column }, (cell, rowNum) => {
      // 设置文字居中
      cell.style = {
        alignment: {
          horizontal: 'center',
          vertical: 'center'
        }
      };
    });
  })
}
//判断当前表是否需要自适应列宽
if (sheet.autoWidth) {
  // 设置最大宽度
  const maxWidth = 70; // 你可以根据需要调整这个值
  //获取表格列数
  const columnCount = sheet.data.reduce((prev, cur) => (prev=prev+cur.length),0)
  // 遍历工作表的每一列
  for (let column = 1; column <= columnCount; column++) {
    let maxWidthInColumn = 0;

    // 遍历当前列的所有单元格
    worksheet.getColumn(column).eachCell({ minCol: column, maxCol: column }, (cell, rowNum) => {
      // 计算单元格内容的宽度
      const cellWidth = cell.value ? cell.value.toString().length : 0;
      // 更新当前列的最大宽度
      if (cellWidth > maxWidthInColumn && cellWidth < 70) {
        maxWidthInColumn = cellWidth;
      }
      // 设置文字居中
      cell.style = {
        alignment: {
          horizontal: 'center',
          vertical: 'center'
        }
      };
    });

    // 设置列的宽度，不超过最大宽度
    const finalWidth = Math.min(maxWidthInColumn * 2.5, maxWidth); // 假设每个字符宽度为10，你可以根据需要调整这个值
    worksheet.getColumn(column).width = finalWidth;
  }
}

4:添加特定地址图片；根据insertImage判断是否需要单独添加数据，当前业务是固定在第三张sheet B1单元格添加图片，所以直接使用addImage添加就可

const imageId = workbook.addImage({
    base64: sheet.insertImage,
    extension: 'png', // 图片的扩展名
});
worksheet.addImage(imageId, {
tl: {
  col: 1,
  row: 0,
},
ext: {
  width: 500,
  height: 300,
},
// 超链接
hyperlinks: {
  hyperlink: 'https://www.baidu.com',
  tooltip: 'https://www.baidu.com',
},
})

5:保存工作薄，使用file-server下载文件

const buffer = await workbook.xlsx.writeBuffer()

saveAs(
    new Blob([buffer], {
      type: 'application/octet-stream',
    }),
    `${filename}.${bookType}`
)

结束

目前excel导出的数据最多不会超过100条，但是超过100条一条会出现数据卡顿的情况。目前还不知道如何处理，要是各位大佬有较好的方法希望可以提供下