1.安装exceljs
cnpm install exceljs@4.2.1 --save
npm安装很难成功
2.组件代码
<template>
<div style="background-color: skyblue;width: 100px;height: 50px;
line-height: 50px;text-align: center;cursor: pointer;" @click="exportExcel">导出</div>
</template>
<script>
import Excel from 'exceljs'
export default {
props: {
exportExcelParams: {
default: {
data: [],
fileName: 'file',
header: [],
sheetName: 'sheet1',
imageKeys: [],
creator:'me',
lastModifiedBy:'her',
},
required: true,
}
},
mounted () {
},
methods: {
initWorkbook() {
const workbook = new Excel.Workbook();
// 设置属性
const { creator = 'demo', lastModifiedBy = 'demo' } = this.exportExcelParams
workbook.creator = creator;
workbook.lastModifiedBy = lastModifiedBy;
workbook.created = new Date();
workbook.modified = new Date();
workbook.lastPrinted = new Date();
// 设置打开时候的视图
workbook.views = [
{
x: 0,
y: 0,
width: 10000,
height: 20000,
firstSheet: 0,
activeTab: 1,
visibility: 'visible'
}
]
return workbook;
},
setWorksheet(worksheet, header, data) {
// Excel行高所使用单位为磅,列宽使用单位为0.1英寸
// 行高:1毫米=2.7682个单百位,1厘米=27.682个单位(磅);1个单位=0.3612毫米
// 列宽:1毫米=0.4374个单位,1厘米=4.374 个单位(英寸*0.1);1个单位=2.2862毫米
// 单位(磅) 96像素/英寸,则 96 像素 = 1 英寸 = 2.54 厘米; 1像素 = 1/96*2.54*27.682磅
let rowHeight = Math.ceil(108/96*2.54*27.682) + 1;
worksheet.state = 'visible';
// 设置列
worksheet.columns = header;
for (let i = 1; i <= header.length; i++) {
worksheet.getColumn(i).alignment = { vertical: 'middle', horizontal: 'center' };
worksheet.getColumn(i).font = { name: 'Arial Unicode MS' };
}
// 设置行
worksheet.addRows(data);
// 设置表头样式
worksheet.getRow(1).font = { name: 'Arial Unicode MS', family: 4, size: 13 };
worksheet.getRow(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF8DB4E2' } }
data.forEach((item, i) => {
worksheet.getRow(i + 2).height = rowHeight
})
},
async exportExcel() {
const workbook = this.initWorkbook();
const { sheetName = 'sheet1' } = this.exportExcelParams
const worksheet = workbook.addWorksheet(sheetName);
const { imageKeys = [], data = [], header = [] } = this.exportExcelParams
const getIndexByKey = (arr, key) => {
for (let i = 0; i < arr.length; i++) {
const element = arr[i];
if (element.key == key) {
return i
}
}
}
let imageList = imageKeys.map(key => {
return data.map((item, index) => {
return {
key,
url: item[key],
col: getIndexByKey(header, key) + 1,
row: index + 2,
width: 164,
height: 108
}
})
})
this.setWorksheet(worksheet, header, data);
// 图片处理
await Promise.all(imageList.reduce((arr, list) => {
return arr.concat(list.map(item => {
return this.getImageId(workbook, item.url).then(imageId => {
worksheet.getRow(item.row).getCell(item.key).value = '';
worksheet.addImage(imageId, {
tl: { col: item.col - 1, row: item.row - 1},
ext: { width: item.width, height: item.height}
});
})
}))
}, []))
let bufferContent = await workbook.xlsx.writeBuffer();
this.saveFile(bufferContent)
},
getImageId(workbook, url) {
return this.getBase64(url).then(res => {
const imageId = workbook.addImage({
base64: res.data.url,
extension: res.data.type.split('/')[1],
});
return imageId
})
},
fileAsBase64(file) {
return new Promise((resolve, reject) => {
let reader = new FileReader();
reader.readAsDataURL(file);
reader.onload = function (e) {
resolve({
code: 1,
data: {
name: file.name,
type: file.type,
originSize: file.size,
url: e.target.result
}
})
}
reader.onerror = function () {
reject({code: 0, msg : 'can not readFile'})
}
})
},
getStaticFile(url, type = '') {
return new Promise((resolve, reject) => {
let xhr = this.createXMLHttp();
xhr.onload = function () {
if (this.response) {
resolve({code: 1, data: this.response})
} else {
reject({code: 0, data: url, msg: '服务器所返回的类型和你所设置的返回值类型不兼容'})
}
}
xhr.onerror = function (error) {
reject(error)
}
xhr.open('GET', url, true);
xhr.responseType = type;
xhr.send();
})
},
saveFile(file) {
const { fileName = 'file' } = this.exportExcelParams
let ieKit = /(?:ms|\()(ie)\s([\w\.]+)|trident|
(edge|edgios|edga|edg)/i.test(window.navigator.userAgent);
let blobData = new Blob([file], {type:
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
if (ieKit) {
navigator.msSaveBlob && navigator.msSaveBlob(blobData, fileName)
} else {
let objectURL = window.URL.createObjectURL(blobData),
save_link = document.createElementNS('http://www.w3.org/1999/xhtml', 'a'),
event = document.createEvent('MouseEvents');
save_link.href = objectURL;
save_link.download = fileName;
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false,
false, false, false, 0, null);
save_link.dispatchEvent(event)
window.URL.revokeObjectURL(objectURL);
}
},
getBase64(url, type = 'blob') {
return this.getStaticFile(url, type).then(res => this.fileAsBase64(res.data))
},
createXMLHttp() {
let xmlhttp;
try {
xmlhttp = new XMLHttpRequest(); //尝试创建 XMLHttpRequest 对象,
除 IE 外的浏览器都支持这个方法。
} catch (e) {
try {
xmlhttp = ActiveXobject('Msxml12.XMLHTTP'); //使用较新版本的
IE 创建 IE 兼容的对象(Msxml2.XMLHTTP)。
} catch (ex) {
try {
xmlhttp = ActiveXobject('Microsoft.XMLHTTP'); //使用较老版本的
IE 创建 IE 兼容的对象(Microsoft.XMLHTTP)。
} catch (failed) {
xmlhttp = false; //如果失败了还保持false
}
}
}
return xmlhttp;
}
}
}
</script>
3.使用
data() {
return {
exportExcelParams: {
header: [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'avatar', key: 'avatar', type: 'image', width: Math.ceil(164/96*2.54*4.374) + 2},
{ header: 'cover', key: 'cover', type: 'image', width: Math.ceil(164/96*2.54*4.374) + 2},
{ header: 'D.O.B.', key: 'DOB', width: 10 }
],
data: [
{ id: 1, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
DOB: 'dddddd' },
{ id: 2, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
DOB: 'dddddd1' },
{ id: 3, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
DOB: 'dddddd2' },
{ id: 4, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
DOB: 'dddddd3' },
{ id: 5, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
DOB: 'dddddd4' },
{ id: 6, avatar: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
cover: 'http://p0.meituan.net/tuanpic/3df525af5a3f7fe04077567d2a6caf794904.png',
DOB: 'dddddd5' },
],
imageKeys: ['avatar', 'cover'],
sheetName: '小橘子的sheet',
fileName: '小橘子的file',
}
}
},