技术文档

Appearance

Nest 实现 excel 导入导出 ​

安装依赖 ​

bash
npm install --save exceljs

创建 excel.service 和 excel.module ​

excel.module.ts ​

ts
import { DynamicModule, Module } from "@nestjs/common";
import { ExcelService } from "./excel.service";
export interface DataOptions {
  name: string;
}
@Module({})
export class ExcelModule {
  static forRoot(options: DataOptions): DynamicModule {
    return {
      module: ExcelModule,
      controllers: [],
      providers: [
        ExcelService,
        {
          provide: "EXCEL_OPTIONS",
          useValue: options,
        },
      ],
      exports: [ExcelService],
    };
  }
}

excel.service.ts ​

ts
import { Inject, Injectable } from "@nestjs/common";
import { ExcelModule, type DataOptions } from "./excel.module";
import { Workbook, Cell } from "exceljs";
import { GlobalCheckException } from "../../common/globalcheck.exception";
@Injectable()
export class ExcelService {
  @Inject("EXCEL_OPTIONS")
  private readonly options: DataOptions;

  // 导入 获取到每个sheet页的数据
  async importexcel(buffer: any) {
    const workbook = new Workbook();
    const workbook2 = await workbook.xlsx.load(buffer);
    const resultAll: any = [];
    workbook2.eachSheet((worksheet, index) => {
      const resultsheet: any[] = [];
      worksheet.eachRow((row, rowNumber) => {
        const rowData: any = [];
        row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
          // 检查是否为 null 或空字符串
          if (
            cell.value == null ||
            (typeof cell.value === "string" && cell.value.trim() === "")
          ) {
            throw new GlobalCheckException(
              `第${rowNumber}行第${colNumber}列数据为空`
            );
          }
          rowData.push(cell.value);
        });
        resultsheet.push(rowData);
      });
      resultAll.push(resultsheet);
    });
    return resultAll;
  }

  // 导出
  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: null,
        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);
    });
    console.log(data);
    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;
  }
}

其他模块 使用 ​

其他模块模型 ​

ts
import { Module } from "@nestjs/common";
import { TestdemoService } from "./testdemo.service";
import { TestdemoController } from "./testdemo.controller";
// 增加Excel 动态模块
import { ExcelModule } from "../../commonModules/excel/excel.module";
@Module({
  imports: [ExcelModule.forRoot({ name: "testdemoexcel" })],
  controllers: [
    TestdemoController, // Excel 动态模块
  ],
  providers: [TestdemoService],
})
export class TestdemoModule {}

控制器 ​

ts
import {
  Controller,
  Get,
  Post,
  Body,
  Patch,
  Param,
  Delete,
  UseGuards,
  Req,
  Res,
} from "@nestjs/common";
import { TestdemoService } from "./testdemo.service";

import { AuthGuard } from "@nestjs/passport";
// 通过这个装饰器让全局拦截器放行
import { Bypass } from "../../common/decorators/bypass.decorator";
import { memoryStorage } from "multer";
@Controller({
  path: "testdemo",
  version: ["1"],
})
export class TestdemoController {
  constructor(private readonly testdemoService: TestdemoService) {}

  // 导入Excel 测试
  @Post("importexcel1")
  @ByGuardpass()
  @UseInterceptors(
    // 独立自己的
    FileInterceptor("file", {
      storage: memoryStorage(),
      limits: {
        fileSize: 5 * 1024 * 1024, // 限制文件大小为 5MB，防止过大
      },
    })
  )
  async importexcel1(
    @UploadedFile()
    file: Express.Multer.File
  ) {
    let result = await this.testdemoService.importexcel(file.buffer);
    return {
      data: result,
    };
  }

  // 第一种导出
  @Get("exportexcel1")
  async exportexcel1() {
    // 这里应该上传成功后，返回一个url，然后通过url获取到excel文件
    const result = await this.testdemoService.exportexcel1();
    console.log(result);
    return {
      data: result,
    };
  }

  // 第2种导出
  @Get("exportexcel2")
  @Bypass()
  async exportexcel2(@Res() res: any) {
    // 这里应该上传成功后，返回一个url，然后通过url获取到excel文件
    const result = await this.testdemoService.exportexcel2();
    console.log(result);
    return res.send(result);
  }
}

服务 ​

  • testdemo.service.ts
ts
import { Inject, Injectable } from "@nestjs/common";
import { ExcelService } from "../../commonModules/excel/excel.service";
@Injectable()
export class TestdemoService {
  // Excel
  @Inject()
  private readonly ExcelService: ExcelService;

  // Excel导入
  async importexcel(data) {
    const result = await this.ExcelService.importexcel(data);
    return result;
  }

  // 第一种Excel导出
  async exportexcel1() {
    const result = await this.ExcelService.exportExcel1();
    return {
      data: result,
    };
  }

  // 第2种Excel导出
  async exportexcel2() {
    const result = await this.ExcelService.exportExcel2();
    return result;
  }
}

对应的前端页面 ​

导入 ​

html
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>图片上传</title>
  </head>
  <body>
    <form id="uploadForm" enctype="multipart/form-data">
      <label for="excelInput">excel:</label>
      <input type="file" id="excelInput" name="image" accept="file/*" />
      <button type="button" onclick="uploadImage()">导入</button>
    </form>

    <script>
      function uploadImage() {
        const fileInput = document.getElementById("excelInput");
        const file = fileInput.files[0]; // 获取选中的文件
        if (!file) {
          alert("请选择一个文件");
          return;
        }
        const formData = new FormData();

        formData.append("file", file); // 'image' 是后端期待的字段名，根据需要调整

        fetch("http://localhost:5000/v1/testdemo/importexcel1", {
          // '/upload' 是你的后端处理上传的URL，需要替换为实际URL
          method: "POST",
          body: formData,
        })
          .then((response) => response.json()) // 假设服务器返回JSON格式的响应
          .then((data) => {
            console.log("Success:", data); // 处理响应数据，例如显示上传成功信息等
            if (data.code == 200) {
              alert("上传成功");
            } else {
              alert("上传失败");
            }
          })
          .catch((error) => {
            console.error("Error:", error); // 处理错误情况，例如显示错误信息等
            alert("上传失败2222"); // 或者在页面上显示错误消息等操作
          });
      }
    </script>
  </body>
</html>

导出 ​

html
<!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>

网站备案号:津ICP备15001525号-2