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
}