Vue + ElementPlus 实现权限管理系统:Nest实现Excel的导入与导出

东方小月
823 阅读7分钟

在后台管理系统中，导出和导入数据是常见的需求。我们经常需要将查询到的表格数据导出为 Excel 文件，以便于用户查看和分析。同时，有时我们也需要将 Excel 文件批量导入到数据库中，以便快速更新或添加数据。

导入、导出数据为 Excel 文件可以通过使用库如 xlsx 或 exceljs 来实现。这些库允许我们将数据库中的数据格式化为 Excel 文件或者将 Excel 文件解析为数据库中的数据插入数据库中,从而实现了数据的导入导出功能。本篇文章将以用户管理为例,介绍如何使用 xlsx 库来实现Excel导入导出功能。

在此之前,我们已经实现了用户的增删改查功能,实现方式和上篇文章角色管理差不多,这里就不再单独介绍了,可以自行查看源码。下面我们就开始实现Excel导入导出功能。

xslx 库的使用

这里不会对xlsx库的使用做过多的说明,只介绍我们需要用到的部分,如果你感兴趣的话,可以参考官方文档。

首先,我们需要安装xlsx库:

npm install xlsx

至于这里安装到前端 Vue 项目还是后端的 Nest 项目,根据自己的需求来选择即可。建议在后端处理 Excel 文件,我这里安装到了后台 Nest 项目中。

导入导出功能我们需要用到xlsx库中read, utils, write这三个模块。

这里举个两个简单的例子,看一下xlsx是如何将excel文件解析为json格式的数据的,以及如何将json格式的数据格式化为excel文件的。

  1. JSON 格式的数据格式化为 Excel 文件
import { read, utils, write } from "xlsx";
//要转为为excel的数据
export const exportData = () => {
  const data = [
    { name: "张三", email: "zhangsan@example.com" },
    { name: "李四", email: "lisi@example.com" },
  ];
  const worksheet = utils.json_to_sheet(data);
  // 创建一个新的工作簿
  const workbook = utils.book_new();
  //sheet的名称
  const sheetName = "Sheet1";
  utils.book_append_sheet(workbook, worksheet, sheetName);
  const excelBuffer: any = write(workbook, {
    bookType: "xlsx",
    type: "buffer",
  });
  return excelBuffer;
};

这样我们就可以将json格式的数据格式化为excel文件了。如下图

image.png

  1. Excel 文件解析为 JSON 格式的数据

同样的,如果我们想要将excel文件解析为json格式的数据,也很简单,只需要使用read模块即可。注意因为 excel 会有多个工作表,所以我们需要遍历每个工作表,将每个工作表的数据合并起来再返回。

export const uploadExcel = (file) => {
  // 使用 read 方法读取 Excel 文件的缓冲区，返回工作簿对象
  const workbook = read(file.buffer, { type: "buffer" });

  // 获取工作簿中的所有工作表名称
  const sheetNames = workbook.SheetNames;

  // 初始化一个数组，用于存储所有工作表的数据
  const sheetData = [];

  // 遍历每个工作表名称
  sheetNames.forEach((sheetName) => {
    // 获取当前工作表对象
    const worksheet = workbook.Sheets[sheetName];

    // 将工作表转换为 JSON 格式的数据
    const sheetJson = utils.sheet_to_json(worksheet);

    // 将当前工作表的数据添加到 sheetData 数组中
    sheetData.push(...sheetJson);
  });

  // 返回所有工作表的合并数据
  return sheetData;
};

知道了xlsx如何使用,接下来实现用户的导入导出功能就很简单了。

用户导出

用户导出的流程是这样的:

  1. 前端点击导出按钮
  2. 后台根据前端传来的条件查询数据库中的用户数据
  3. 后台将查询到的数据格式化为所需格式
  4. 后台将格式化后的数据格式化为excel文件并返回给前端

我们需要在user.service.ts中实现导出功能,通过查询用户表中的数据然后将数据格式化为excel文件并返回给前端。但是有一个问题,我们查询到的数据是这样的:

{
    "id": 1,
    "username": "admin",
    "password": "123456",
    "nickname": "管理员"
    ...
}

这样的话导出的excel表头是英文的如username,password,nickname等,所以我们需要将其转为中文格式的表头。

这里我们新建一个文件src/config/excelHeader.ts用于存放各个表头的中文格式。

//用户表
export const mapUserZh = {
  id: "id",
  username: "用户名",
  nickname: "昵称",
  telephone: "手机号",
  email: "邮箱",
  status: "状态",
  create_time: "创建时间",
  update_time: "更新时间",
};

然后我们在utils/common.ts中实现导出的通用方法

//数据转excel
export function exportExcel(
  data: any[],
  mapZh: any = {},
  sheetName: string = "sheet1"
) {
  const worksheet = utils.json_to_sheet([mapZh, ...data], {
    // 字段显示顺序
    header: Object.keys(mapZh),
    //隐藏表头
    skipHeader: true,
  });
  // 创建一个新的工作簿
  const workbook = utils.book_new();
  utils.book_append_sheet(workbook, worksheet, sheetName);
  const excelBuffer: any = write(workbook, {
    bookType: "xlsx",
    type: "buffer",
  });
  return excelBuffer;
}

我们通过将原始表头隐藏,然后将我们的映射对象作为第一行数据插入到excel文件中,就实现了中文表头的功能。 然后我们在user.service.ts中实现导出功能。

  //导出
  async export(findUserListDto: FindUserListDto) {

    try {
      const { list } = await this.findUserList(findUserListDto)

      const excelBuffer = await exportExcel(list, mapUserZh);
      return excelBuffer;

    } catch (error) {
      throw new ApiException('导出失败', ApiErrorCode.FAIL);
    }
  }

这里我们返回的是一个excel文件的buffer,user.controller.ts中可以这样写。

 //导出
  @Get('/export')
  @Permissions('system:user:export')
  @ApiOperation({ summary: '用户管理-导出' })
  async export(@Query() findUserListDto: FindUserListDto, @Res() res: Response) {
    const data = await this.userService.export(findUserListDto);
    res.send(data)
  }

这里不再使用常规的响应方式,而是直接使用express中的res.send方法直接将文件返回给前端,这样前端就能接收到一个bolb类型的文件,然后通过相关处理就能下载下来了。

用户导入

用户导入的流程是这样的:

  1. 前端选择需要导入的excel文件,并上传到后台
  2. 后台接收到这里file文件判断是否符合要求
  3. 后台将file文件解析为json格式的数据并转换为数据库所需格式的数据
  4. 后台将转换后的数据插入到数据库中

同样的我们在common.ts中实现导入的通用方法。

/**
 * 导入Excel文件并将其转换为JSON格式
 * @param file - 要导入的Excel文件
 * @returns 包含所有工作表名称和数据的对象
 */
export const importExcel = (file: File & { buffer: Buffer }) => {
  try {
    const workbook = read(file.buffer, { type: "buffer" });
    const sheetNames = workbook.SheetNames;
    const sheetData = [];

    sheetNames.forEach((sheetName) => {
      const worksheet = workbook.Sheets[sheetName];
      const sheetJson = utils.sheet_to_json(worksheet);
      sheetData.push(...sheetJson);
    });

    return sheetData;
  } catch (error) {
    throw new ApiException("文件解析失败,请检查格式是否正确", 20000);
  }
};

因为前端上传的excel表头是中文的,所以我们需要将其转换为数据库所需的格式

export const transformZnToEn = (data, mapZn) => {
  const mapEn = {};
  try {
    //将mapZh中的key和value交换位置
    Object.keys(mapZn).forEach((key) => {
      mapEn[mapZn[key]] = key;
    });
    //将data中的中文key转换为mapEn中的英文key
    const result = data.map((item) => {
      const newItem = {};
      for (const key in item) {
        if (mapEn.hasOwnProperty(key)) {
          newItem[mapEn[key]] = item[key];
        }
      }
      return newItem;
    });

    return result;
  } catch (error) {
    return [];
  }
};

然后我们在user.service.ts中实现导入功能。

 //导入
  async upload(file) {
    //判断文件类型是否为xlsx或xls
    const fileSplitArr = file.originalname.split('.');
    const fileType = fileSplitArr[fileSplitArr.length - 1];
    if (fileType !== 'xlsx' && fileType !== 'xls')
      throw new ApiException('请上传xlsx或xls格式的文件', ApiErrorCode.COMMON_CODE)

    //解析后的数据
    const excelData = importExcel(file.buffer)
    //转换为数据库需要的格式
    const importData = transformZnToEn(excelData, mapUserZh)


    const hasUseName = importData.map(item => item.username)
    //判断是否有重复的用户名
    const isExist = await this.userRepository.findOne({
      where: {
        username: In(hasUseName)
      }
    })
    if (isExist) {
      throw new ApiException('导入用户包含已存在的用户,请核对后再进行导入', ApiErrorCode.COMMON_CODE);
    }
    try {
      await this.userRepository.save(importData);
    } catch (error) {
      throw new ApiException('导入数据库失败', ApiErrorCode.FAIL)
    }
    return '导入成功';
  }

最后在user.controller.ts中可以通过@UploadedFile接收前端上传的文件,同时使用@UseInterceptors(FileInterceptor('file'))拦截获取前端上传的文件字段名。

//导入

  @Post('/upload')
  @Permissions('system:user:import')
  @ApiOperation({ summary: '用户管理-导入' })
  @ApiParam({ name: 'file', type: 'file' })
  @UseInterceptors(FileInterceptor('file'))
  async upload(@UploadedFile() file: Multer.File) {
    return await this.userService.upload(file);
  }

最后需要注意的是导入用户时,excel 中是没有password和salt的,所以默认导入的用户密码和盐值都是固定的,后续由用户自己修改密码即可。所以我们需要在用户实体中(user.entity.ts)给password和salt添加默认值。

image.png

到这里我们就实现了用户的导入导出功能。而其它模块的导入导出功能基本类似,后续将会实现一个代码生成器,可以自动生成Controller、Service、Entity、Dto等文件实现一些通用的功能,从而大大提高开发效率。

如果本篇文章对你有所帮助,欢迎点赞、收藏、关注,笔芯~。

源码地址

avatar
文章
阅读
粉丝