koa2+vue3通过exceljs实现数据导出excel文档

1. 服务端安装exceljs依赖

npm i exceljs
1

2. 服务端实现代码
   1. 实现导出excel文件工具方法

const ExcelJS = require('exceljs');
/**
 * @description: 导出excel文件
 * @param {*} fileHeader  导出的表头
 * @param {*} data 导出的数据
 * @param {*} ctx 上下文对象
 * @return {*}
 */
async function exportExcel(fileHeader, data, ctx){
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet();
  sheet.columns = fileHeader.map((item,index)=>{
    return {
      header: item.header,
      key: item.key,
      width: item.width||25,
    }
  })
  data.forEach((item,index)=>{
    sheet.addRow(item);
  })
  
  // 生成Excel文件
  const buffer = await workbook.xlsx.writeBuffer();

  ctx.status = 200;
  ctx.set('Content-Type', 'application/vnd.openxmlformats');
  ctx.attachment('example.xlsx');
  ctx.body = buffer;

}

module.exports = exportExcel;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

b. 使用案例
1

await exportExcel(
      [
        { header: "Name", key: "name", width: 20 },
        { header: "Age", key: "age", width: 10 },
        { header: "Email", key: "email" },
      ],
      [
        { name: "测试数据1", age: 320, email: "alice@example.com" },
        { name: "测试数据2", age: 330, email: "alice@example.com" },
        { name: "测试数据3", age: 340, email: "alice@example.com" },
      ],
      ctx
    );
1
2
3
4
5
6
7
8
9
10
11
12
13

3. 客户端代码实现
   1. 创建下载excel工具方法

/**
 * @description: 下载excel方法
 * @param {String} filename
 * @param {Blob} blob
 */
async function exportExcel(filename = "导出文件", blob: Blob) {
  const url = window.URL.createObjectURL(blob);
  const link = document.createElement("a");
  link.href = url;
  link.download = filename; // 设置下载的文件名
  document.body.appendChild(link); // 需要添加到DOM中才能生效
  link.click();
  document.body.removeChild(link);
  window.URL.revokeObjectURL(url); // 释放内存
}

export default exportExcel;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

b. 接口请求方法封装
1

export const exportTableApi = (url: string, params: any = {}) => {
  return http.get(url + "/export-to-excel", params, {
    headers: {
      Accept:
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    },
    **responseType: "blob",**
  });
};
1
2
3
4
5
6
7
8
9

c. 使用案例
1

/**
   * @description 导出表格数据
   * @return void
   * */
  const exportData = async (apiUrl, params) => {
    const data = await exportTableApi(apiUrl, params);
    const blob = new Blob([data as any], {
      type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    });
    exportExcel(tableName, blob);
  };
1
2
3
4
5
6
7
8
9
10
11