Nest 实现 excel 导入导出

安装依赖

npm install --save exceljs

控制器

import {
  Controller,
  Get,
  Post,
  Body,
  Patch,
  Param,
  Delete,
  Res,
} from "@nestjs/common";
import { ExcelService } from "./excel.service";

@Controller("excel")
export class ExcelController {
  constructor(private readonly excelService: ExcelService) {}

  @Get()
  async findAll() {
    return {
      data: await this.excelService.findAll(),
    };
  }

  @Get("sheet2")
  async findAll2() {
    return {
      data: await this.excelService.findAll2(),
    };
  }
  // 第一种导出
  @Get("exportExcel1")
  async exportExcel1() {
    return {
      data: await this.excelService.exportExcel1(),
    };
  }
  // 第二种导出(推荐)
  @Get("exportExcel2")
  async exportExcel2(@Res() res: any) {
    const buffer = await this.excelService.exportExcel2();
    return res.send(buffer);
  }
}

服务

import { Injectable } from "@nestjs/common";
import { Workbook, Cell } from "exceljs";
// 调用

@Injectable()
export class ExcelService {
  // 第一个导入
  async findAll() {
    console.log(process.cwd() + "/public/data.xlsx");
    const workbook = new Workbook();
    const workbook2 = await workbook.xlsx.readFile(
      process.cwd() + "/public/data.xlsx"
    );
    workbook2.eachSheet((worksheet, index) => {
      console.log("工作表" + index);
      worksheet.eachRow((row, rowNumber) => {
        const rowData: any = [];
        row.eachCell((cell, colNumber) => {
          rowData.push(cell.value);
        });
        console.log("行" + rowNumber, rowData);
      });
    });
  }
  // 第二种导入
  async findAll2() {
    console.log(process.cwd() + "/public/data.xlsx");
    const workbook = new Workbook();
    const workbook2 = await workbook.xlsx.readFile(
      process.cwd() + "/public/data.xlsx"
    );
    let result: any = [];
    workbook2.eachSheet((worksheet, index) => {
      // console.log('工作表' + index);
      const value = worksheet.getSheetValues();
      // 获取到每一个sheet的值
      if (value) {
        result.push(value);
      }
    });
    console.log(result);
  }

  // 第一种导出
  async exportExcel1() {
    const workbook = new Workbook();

    const worksheet = workbook.addWorksheet("第一个sheet");

    worksheet.columns = [
      { header: "ID", key: "id", width: 20 },
      { header: "姓名", key: "name", width: 30 },
      { header: "出生日期", key: "birthday", width: 30 },
      { header: "手机号", key: "phone", width: 50 },
    ];

    const data = [
      {
        id: 1,
        name: "光光",
        birthday: new Date("1994-07-07"),
        phone: "13255555555",
      },
      {
        id: 2,
        name: "东东",
        birthday: new Date("1994-04-14"),
        phone: "13222222222",
      },
      {
        id: 3,
        name: "小刚",
        birthday: new Date("1995-08-08"),
        phone: "13211111111",
      },
    ];
    worksheet.addRows(data);

    // 第一种写入,然后再让前端调用下载接口下载 这种情况服务器会存储文件
    workbook.xlsx.writeFile(process.cwd() + "/public/data2.xlsx");

    // 返回路径
    return "http://localhost:5000/static/data2.xlsx";
  }

  // 工具类
  async exportExcelTools(colunms: any, data: unknown[], sheetName: string) {
    const workBook = new Workbook();
    const sheet = workBook.addWorksheet(sheetName);
    sheet.columns = colunms;
    data.forEach((row) => {
      sheet.addRow(row);
    });
    return await workBook.xlsx.writeBuffer();
  }

  // 第2种导出 直接下载
  async exportExcel2() {
    const sheetName = "第一个sheet";
    const columns = [
      { header: "ID", key: "id", width: 20 },
      { header: "姓名", key: "name", width: 30 },
      { header: "出生日期", key: "birthday", width: 30 },
      { header: "手机号", key: "phone", width: 50 },
    ];
    const data = [
      {
        id: 1,
        name: "光光44",
        birthday: new Date("1994-07-07"),
        phone: "13255555555",
      },
      {
        id: 2,
        name: "东东5454",
        birthday: new Date("1994-04-14"),
        phone: "13222222222",
      },
      {
        id: 3,
        name: "小刚6565",
        birthday: new Date("1995-08-08"),
        phone: "13211111111",
      },
    ];
    const buffer = await this.exportExcelTools(columns, data, sheetName);

    return buffer;
  }
}

对应的前端页面

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Document</title>
    <script src="https://unpkg.com/axios@1.5.0/dist/axios.min.js"></script>
  </head>
  <body>
    <button
      onClick="downloadBlob('http://localhost:5000/excel/exportExcel2','测试.xlsx')"
    >
      下载Blob
    </button>
    <button
      onClick="downlandlink('http://localhost:5000/static/data2.xlsx','测试.xlsx')"
    >
      下载连接
    </button>
  </body>
  <script>
    async function downloadBlob(url, filename) {
      try {
        // 发送 GET 请求并设置 responseType 为 'blob'
        const response = await axios.get(url, {
          responseType: "blob",
        });

        // 创建一个临时 URL 用于下载
        const blobUrl = window.URL.createObjectURL(new Blob([response.data]));

        // 创建一个隐藏的 a 标签并触发点击
        const link = document.createElement("a");
        link.href = blobUrl;
        link.setAttribute("download", filename);
        document.body.appendChild(link);
        link.click();

        // 清理临时 URL 和 a 标签
        document.body.removeChild(link);
        window.URL.revokeObjectURL(blobUrl);
      } catch (error) {
        console.error("下载失败:", error);
      }
    }
    // 下载连接
    async function downlandlink(url, filename) {
      try {
        // 发送 GET 请求并设置 responseType 为 'blob'
        const response = await axios.get(url, {
          responseType: "blob",
        });

        // 创建一个临时 URL 用于下载
        const blobUrl = window.URL.createObjectURL(new Blob([response.data]));

        // 创建一个隐藏的 a 标签并触发点击
        const link = document.createElement("a");
        link.href = blobUrl;
        link.setAttribute("download", filename);
        document.body.appendChild(link);
        link.click();

        // 清理临时 URL 和 a 标签
        document.body.removeChild(link);
        window.URL.revokeObjectURL(blobUrl);
      } catch (error) {
        console.error("下载失败:", error);
      }
    }
  </script>
</html>