js table转excel文件可导出图片和跨行.xlsx
将table导出为.xlsx文件,可导出图片,支持跨行和跨列。
更多用法参考exceljs文档
需要用到exceljs、file-saver、jquery
import ExcelJS from "exceljs";
import { saveAs } from 'file-saver';
import $ from "jquery";
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
function image2Base64(img) {
let canvas = document.createElement("canvas");
let size=40;
canvas.width = size;
canvas.height = size;
let ctx = canvas.getContext("2d");
ctx.drawImage(img, 0, 0, size, size);
let dataURL = canvas.toDataURL("image/png");
return dataURL;
}
async function tableToExcel(table,title){
table = $(table);
//创建工作薄
const workbook = new ExcelJS.Workbook();
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
workbook.lastPrinted = new Date(2016, 9, 27);
// 将工作簿日期设置为 1904 年日期系统
workbook.properties.date1904 = true;
// 在加载时强制工作簿计算属性
workbook.calcProperties.fullCalcOnLoad = true;
workbook.views = [
{
x: 0, y: 0, width: 10000, height: 20000,
firstSheet: 0, activeTab: 1, visibility: 'visible'
}
]
//sheet
const worksheet = workbook.addWorksheet('Sheet01');
//先获取所有的image
let imageDownloadArr=[];
let images={}
table.find("tbody").find("img").each(function(){
let src=$(this).attr("src");
let key=$(this).attr("src");
let pro=src.substr(0,location.protocol.length);
let pros=["http:","https:"];
if(pro!=location.protocol){
if(location.protocol=="https:"){
//转https:
src=location.protocol+src.substr(5);
}else if(location.protocol=="http:"){
//转https:
src=location.protocol+src.substr(6);
}
}
if(images[key]){
return ;
}
imageDownloadArr.push(new Promise((reject,resvle)=>{
let img = new Image();
img.src=src;
img.setAttribute("crossOrigin",'Anonymous');
img.onload=function(){
const imageId = workbook.addImage({
base64: image2Base64(img),
extension: 'png',
});
images[key]=imageId;
reject();
}
}))
})
function addRow(tds,rowNum=1,isHead=false){
let colNum=1;
let row=worksheet.getRow(rowNum);
$(tds).each(function(val){
let th=$(this);
let src=th.find("img").attr("src");
//跨列
let valColspan=(Number(th.attr("colspan"))||1);
//跨行
let valRowspan=(Number(th.attr("rowspan"))||1);
let cell=row.getCell(colNum);
if(valColspan>1){
let cols=[];
worksheet.mergeCells(rowNum,colNum,(rowNum+valRowspan-1),(colNum+valColspan-1))
}
cell.value=th.text();
console.log(src)
if(src && images[src]!=null){
//添加图片
console.log(images)
worksheet.addImage(images[src],{
tl: { col:colNum-1+0.1 , row: rowNum-1+0.1},
ext: { width: 30, height: 30 }
})
}
if(images.length>0){
row.height=24;
}
cell.alignment = { horizontal:'center'};
if(isHead){
cell.font={
bold:true,
};
}
colNum+=valColspan;
})
row.commit();
}
//等待所有图片下载完成
Promise.all(imageDownloadArr).then(()=>{
//标题
addRow(table.find("th"),1,true);
//创建body
table.find("tbody").find("tr").each(function(i){
addRow($(this).find("td"),2+i);
})
// worksheet.addImage(imageId,{
// tl: { col: 1, row: 1 },
// ext: { width: 40, height: 40 }
// })
workbook.xlsx.writeBuffer().then(buffer=>{
const blob = new Blob([buffer], {type: EXCEL_TYPE});
saveAs(blob, 'download.xlsx');
})
});
}
export default {
tableExport:tableToExcel
}