使用 exceljs, jszip,x2js 解析Excel模板
npm install exceljs jszip x2js --save
- 使用 exceljs 读取文件
function readFileToBuffer (file: File) => {
return new Promise((resolve, reject) => {
const reader = new FileReader();
reader.onloadend = function () {
const arrayBufferView = this.result as any;
const blob = new Blob([arrayBufferView], { type: 'application/octet-stream' });
resolve(blob)
};
reader.onError = function () {
reject(this)
}
reader.readAsArrayBuffer(file);
})
}
async function readFile(){
// 读取Buffer类型文件
const stream = await readFileToBuffer(file) as any;
const workbook = new Excel.Workbook();
await workbook.xlsx.load(stream);
return workbook
}
2. 读取嵌入图片处理逻辑
Excel 表格嵌入图片的表达式
原理:把 Excel,改成 zip 文件,你会发现图片是存放一定的路径
实现:使用 jszip, x2js 解析Excel文件内的图片, x2js 用来解析xml文件,
async function readImages(buffer) {
const zip = new JSZip();
const x2js = new X2JS();
const result = await zip.loadAsync(buffer)
const imagesTarget = {};
const imagesTarResult = {};
const template1 = result.files['xl/_rels/cellimages.xml.rels'] // 把 xxx.xlsx 改成 xxxx.zip 可知文件结构
, template2 = result.files['xl/cellimages.xml']; // 把 xxx.xlsx 改成 xxxx.zip 可知文件结构
if (template1 && template2) {
const xml = await template1.async("string");
const xml2 = await template2.async('string')
let temp = x2js.xml2js(xml);//转为json格式
let temp2 = x2js.xml2js(xml2);//转为json格式
let Relationship = temp.Relationships.Relationship;
if (!Array.isArray(Relationship)) {
Relationship = [Relationship];
}
Relationship.forEach(cell => {
imagesTarget[cell._Id] = cell._Target;
})
let cellImage = temp2.cellImages.cellImage;
if (!Array.isArray(cellImage)) {
cellImage = [cellImage];
}
cellImage.forEach(cell => {
imagesTarResult[cell.pic.nvPicPr.cNvPr._name] = imagesTarget[cell.pic.blipFill.blip['_r:embed']];
})
}
return imagesTarResult
}
3. 处理浮动图片
// 查找浮动图片
function getFloatImage(){
const rangeImage = new Map();
for (const image of worksheet.getImages()) {
const img = workbook.model.media.find(m => m.index === image.imageId);
rangeImage.set(image.range.tl.nativeRow + ':' + image.range.tl.nativeCol, img);
}
return rangeImage
}
// 判断是否为嵌入式图片
const extractIDFromFormula = (formula) => {
if (!formula) {
return null;
}
const match = formula.match(/"([^"]+)"/); // 正则表达式匹配双引号之间的内容
return match ? match[1] : null;
};
// ...业务逻辑
let image;
const url = extractIDFromFormula(str); // str 根据单元格数据
if (url) {
const imgName = imagesTarResult[url].split('/')[1].split('.')[0];
image = images.find(o => o.name == imgName);
} else {
image = rangeImage.get(((startIndex - 1) + pIndex) + ':' + cIndex);
}
// ...业务逻辑