Node处理Excel(xlsx文件)批量导入（支持wps内嵌图片）

QinyangChen
512 阅读7分钟

背景介绍

最近接到一个需求，用户在系统中上传Excel文件批量导入员工信息，Excel如下图

一开始我是用自己写的示例Excel上传到Node端，直接用exceljs读取xlsx文件，获取到workbook的media，media中按照顺序Image1、Image2...获取到了图片，我以为用图片的index和每一行的信息一一对应即可获取到正确完整的员工信息，万万没想到！ 当用户那边给到我真正的员工信息Excel文件时，行信息和图片信息并不能通过index一一对应，我只能另寻他法了……

demo.png

xlsx文件是什么？

.xlsx文件是一种基于XML的文件格式，用于存储电子表格数据。相比旧的.xls格式，.xlsx提供了更高的效率和更好的兼容性。一个.xlsx文件实际上是一个压缩包（ZIP），其中包含多个文件和文件夹，协同管理电子表格的内容、样式、关系等信息。将.xlsx文件的后缀名改为.zip，用解压工具将文件解压缩会得到如下图的文件夹结构：

image.png

.xlsx文件的主要组成部分

1. [Content_Types].xml

这个文件位于ZIP包的根目录下，定义了文件类型和每种内容类型的默认处理方式。例如，数据表、样式、关系等。

2. _rels 文件夹

这个文件夹包含_rels/.rels文件，它描述了文件内部的关系和各部分之间的链接。例如，它可以描述工作簿与工作表之间的关系。

3. docProps 文件夹

该文件夹一般包含三个文件：

  • app.xml：存储有关应用程序的扩展属性，例如工作簿中的页数、标题等。
  • core.xml：包含文件的核心属性，如创建者、创建日期、修改日期等。
  • custom.xml：文件为开发者提供了存储自定义元数据的灵活性，允许将特定信息嵌入到 Excel 文件中，以便在不同的场景中使用。

4. xl 文件夹

这个文件夹是最关键的部分，包含Excel文件的主要数据和结构：

  • workbook.xml：描述整个工作簿的结构和各个工作表的信息。
  • _rels/workbook.xml.rels：定义了工作簿与其他文件（如工作表、样式）的关系。
  • worksheets 文件夹：包含单独的工作表文件，每个文件表示一个工作表。例如，sheet1.xml，sheet2.xml。
  • styles.xml：定义工作簿的样式，如字体、颜色、边框等。
  • sharedStrings.xml：存储工作簿中所有共享的字符串（即那些在单元格中使用的文本）。这有助于减少重复字符串的存储。
  • theme 文件夹：包含theme1.xml文件，用于定义Excel文件的主题，包括颜色、字体、效果等。
  • drawings 文件夹：存储绘图对象的数据，如图表、图像等。
  • charts 文件夹：包含chart1.xml，chart2.xml等文件，每个文件定义一个图表的结构和数据。
  • tables 文件夹：存储表格数据结构，例如table1.xml文件定义一个表格。
  • media 文件夹：包含Excel文件中使用的多媒体文件，如图像。

找寻内嵌图片与行数据的对应关系

通过在WPS中点击图片可以看到这个图片有一个ID一样的字符串ID_AB37DB48D15743BEAA44D25696395B8E，如下图：

image.png

用exceljs读取xlsx文件也可以获取到这个ID，结果如下：

image.png

有了这个ID后，通过查找xlsx文件夹发现xl目录下的cellimages.xml文件包含了图片的ID信息，同时还发现了另一个关键信息rId4，在cellimages.xml文件中ID_AB37DB48D15743BEAA44D25696395B8E和rId4是一一对应的，如下图：

image.png

继续查找xlsx文件夹发现xl目录下_rels目录中的cellimages.xml.rels描述文件包含了图片的rId4信息以及图片在xlsx文件夹中的存储路径 media/image4.jpeg ，如下图，它们也是一一对应的关系

image.png

接下来查看xlsx文件夹中xl目录的media目录，我们发现果然image4.jpeg就是ID_AB37DB48D15743BEAA44D25696395B8E这个ID对应的图片！Surprise！🎉🎉🎉

image.png

经过我们的不懈努力，终于发现了图片资源在xlsx文件夹内的文件之间的对应关系，用xlsx文件中的第一行信息图片ID为ID_AB37DB48D15743BEAA44D25696395B8E举例，总结如下：

在xl/cellimages.xml文件中我们找到了ID_AB37DB48D15743BEAA44D25696395B8E和rId4的对应关系，然后在xl/_rels/cellimages.xml.rels文件中找到了rId4和media/image4.jpeg的对应关系，由此得到了ID_AB37DB48D15743BEAA44D25696395B8E和media/image4.jpeg的对应关系。

Node代码实现

说了这么多，终于到了干货，上代码！

Service主逻辑

import path from 'path';
// 引入 ExcelJS 库，用于解析 Excel 文件
import ExcelJS from 'exceljs';
// 引入 unzipper 库，用于解压缩文件
import unzipper from 'unzipper';
// 引入 xml2js 库，用于解析 XML 文件
import { parseStringPromise } from 'xml2js';
import { BaseService } from '../base';
import { uploadBufferToCos } from '../base/utils/cos';

export interface ImageFile {
  type: string;
  name: string;
  extension: string;
  buffer: Buffer;
  index: number;
}

export interface PersonFace {
  formula: string;
  result: string;
}

export interface Person {
  姓名: string;
  身份证号: string;
  部门: string;
  部门ID: string;
  职务: string;
  人脸照片: PersonFace;
}

/**
 * 员工表服务
 */
class DtEmployeeService extends BaseService() {
  /**
   * 批量上传员工数据
   */
  async batchUpload(files: any) {
    const file = files.file_excel;
    const workbook = new ExcelJS.Workbook();
    const workbookFile = await workbook.xlsx.readFile(file.filepath);
    // 获取第一个工作表
    const worksheet = workbookFile.worksheets[0];

    if (!worksheet) {
      throw new Error('工作表未找到');
    }

    // 提取Excel中的图片
    const mediaArray: ImageFile[] = await this.extractImagesFromExcel(
      file.filepath,
    );
    // 解析工作表为JSON数据
    const jsonData: Person[] = this.parseWorksheet(worksheet);

    // 处理每个人的数据
    for (const [index, item] of jsonData.entries()) {
      await this.processPersonData(item, index, mediaArray);
    }

    return '批量上传成功';
  }

  /**
   * 解析工作表为JSON数据
   */
  private parseWorksheet(worksheet: ExcelJS.Worksheet): Person[] {
    const headers: string[] = [];
    // 获取表头
    worksheet.getRow(1).eachCell((cell, colNumber) => {
      headers[colNumber] = cell.value as string;
    });

    const jsonData: Person[] = [];
    // 获取数据
    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      if (rowNumber === 1) return;

      const rowData: any = {};
      row.eachCell((cell, colNumber) => {
        rowData[headers[colNumber]] = cell.value;
      });

      jsonData.push(rowData);
    });

    return jsonData;
  }

  /**
   * 提取Excel中的图片
   */
  private async extractImagesFromExcel(filePath: string): Promise<ImageFile[]> {
    const zip = await unzipper.Open.file(filePath);
    // 获取 cellimages.xml 和 cellimages.xml.rels 文件
    const cellimagesFile = zip.files.find(
      (file) => file.path === 'xl/cellimages.xml',
    );
    const cellimagesRelsFile = zip.files.find(
      (file) => file.path === 'xl/_rels/cellimages.xml.rels',
    );

    if (!cellimagesFile || !cellimagesRelsFile) {
      return [];
    }

    // 解析 cellimages.xml 和 cellimages.xml.rels 文件
    const cellimagesXml = await cellimagesFile.buffer();
    const cellimagesRelsXml = await cellimagesRelsFile.buffer();
    const cellimagesData = await parseStringPromise(cellimagesXml.toString());
    const cellimagesRelsData = await parseStringPromise(
      cellimagesRelsXml.toString(),
    );

    // 从 cellimages.xml 文件中提取图片名称和 rid 的映射关系
    const cellimagesNameRidMap: Record<string, string> = {};
    cellimagesData['etc:cellImages']['etc:cellImage'].forEach((pic: any) => {
      const rid =
        pic['xdr:pic'][0]['xdr:blipFill'][0]['a:blip'][0].$['r:embed'];
      const name = pic['xdr:pic'][0]['xdr:nvPicPr'][0]['xdr:cNvPr'][0].$.name;
      cellimagesNameRidMap[rid] = name;
    });

    // 从 cellimages.xml.rels 文件中提取图片数据
    const mediaArray: ImageFile[] = [];
    for (const relationship of cellimagesRelsData['Relationships'][
      'Relationship'
    ]) {
      const rid = relationship.$.Id;
      const target = relationship.$.Target;
      const extension = path.extname(target);
      const index = parseInt(target.match(/image(\d+)/)[1], 10);
      const imgFile = zip.files.find((file) => file.path === `xl/${target}`);

      if (imgFile) {
        const buffer = await imgFile.buffer();
        mediaArray.push({
          type: 'image',
          name: `${cellimagesNameRidMap[rid]}`,
          extension,
          buffer,
          index,
        });
      }
    }

    return mediaArray;
  }

  /**
   * 图片上传到腾讯云COS获取图片URL
   */
  private async getImageCosUrl(
    imgName: string,
    mediaArray: ImageFile[],
  ): Promise<string> {
    const media = mediaArray.find((item) => item.name === imgName);
    if (!media) return '';

    const { data } = await uploadBufferToCos({
      buffer: media.buffer,
      target: 'db',
      ext: media.extension,
    });

    if (data.statusCode !== 200) {
      throw new Error('上传人脸照片失败');
    }

    return `https://${data.Location}`;
  }

  /**
   * 处理每个人的数据
   */
  private async processPersonData(
    item: Person,
    index: number,
    mediaArray: ImageFile[],
  ) {
    if (!item.姓名) {
      throw new Error(`第${index + 2}行 姓名不存在`);
    }

    if (!item.部门ID) {
      throw new Error(`第${index + 2}行 部门ID不存在`);
    }

    // 正则匹配人脸照片中的图片名称
    const regex = /ID_[0-9A-F]{32}/i;
    // 从人脸照片中提取图片名称
    const imgName = item?.人脸照片?.result
      ? item.人脸照片.result.match(regex)[0]
      : '';
    // 上传人脸照片到cos获取到url
    const faceFilename = await this.getImageCosUrl(imgName, mediaArray);

    // 调用创建员工方法
    await this.create({
      companyId: 1,
      departmentId: item.部门ID,
      name: item.姓名,
      cardId: item.身份证号,
      jobTitle: item.职务,
      faceFilename,
    });
  }

  /**
   * 创建员工
   */
  async create(params: { [x: string]: any }) {
    // ……
  }
}

export default new DtEmployeeService();

PS：解析工作表数据和提取图片的逻辑分别在parseWorksheet和extractImagesFromExcel方法中，可着重查看这两个function ~

结语

通过Excel批量上传这个需求也算是对xlsx文件有了一些深入的理解，把这其中遇到的处理内嵌图片的心得分享出来，希望能帮助到有类似需求的同学。文章中如有错误请评论区指正🫡

avatar
文章
阅读
粉丝