背景介绍
最近接到一个需求,用户在系统中上传Excel文件批量导入员工信息,Excel如下图
一开始我是用自己写的示例Excel上传到Node端,直接用exceljs
读取xlsx文件,获取到workbook的media,media中按照顺序Image1、Image2...获取到了图片,我以为用图片的index和每一行的信息一一对应即可获取到正确完整的员工信息,万万没想到! 当用户那边给到我真正的员工信息Excel文件时,行信息和图片信息并不能通过index一一对应,我只能另寻他法了……
xlsx文件是什么?
.xlsx
文件是一种基于XML的文件格式,用于存储电子表格数据。相比旧的.xls
格式,.xlsx
提供了更高的效率和更好的兼容性。一个.xlsx
文件实际上是一个压缩包(ZIP),其中包含多个文件和文件夹,协同管理电子表格的内容、样式、关系等信息。将.xlsx
文件的后缀名改为.zip
,用解压工具将文件解压缩会得到如下图的文件夹结构:
.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
,如下图:
用exceljs
读取xlsx文件也可以获取到这个ID,结果如下:
有了这个ID后,通过查找xlsx文件夹发现xl
目录下的cellimages.xml
文件包含了图片的ID信息,同时还发现了另一个关键信息rId4
,在cellimages.xml
文件中ID_AB37DB48D15743BEAA44D25696395B8E
和rId4
是一一对应的,如下图:
继续查找xlsx文件夹发现xl
目录下_rels
目录中的cellimages.xml.rels
描述文件包含了图片的rId4
信息以及图片在xlsx文件夹中的存储路径 media/image4.jpeg
,如下图,它们也是一一对应的关系
接下来查看xlsx文件夹中xl
目录的media
目录,我们发现果然image4.jpeg
就是ID_AB37DB48D15743BEAA44D25696395B8E
这个ID对应的图片!Surprise!🎉🎉🎉
经过我们的不懈努力,终于发现了图片资源在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文件有了一些深入的理解,把这其中遇到的处理内嵌图片的心得分享出来,希望能帮助到有类似需求的同学。文章中如有错误请评论区指正🫡