使用js-xlsx将后端返回数据导出为excel(.xlsx格式)

小鱼儿 2023-03-13 06:51 21阅读 0赞

<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Document</title>
    
    
    </head>
    <body>
    
        
        <button onclick="downloadExl(jsono)">导出xlsx带样式</button>
        
        <button id="export2">导出xlsx</button>
    
    
    
    
        
    
    
    
    
        
        <script src="js/xlsx.core.min.js"></script>
        <script>
            document.getElementById("export2").onclick = function() {
    
    
                    // 导出多张表
                    // let data = { '基本信息': [{name: '李四', sex: 'nan', age: 12}], '成绩': [{class: '计算机', teacher: 'Mrs wang', score: 90}] };
                    // let columnHeaders = { '基本信息': ['name', 'sex', 'age'], '成绩': ['class', 'teacher', 'score']}
                    // outputXlsxFile(
                    //     data,
                    //     [{ wch: 50 }, { wch: 50 }, { wch: 10 }],
                    //     "test-xlsx"
                    // );
            
                    // function outputXlsxFile(data, wscols, xlsxName) {
                    //     let sheetNames = [];
                    //     let sheetsList = {};
                    //     const wb = XLSX.utils.book_new();
                    //     console.log(wb)
                    //     for (let key in data) {
                    //         sheetNames.push(key);
                    //         let columnHeader = columnHeaders[key] // 此处是每个sheet的表头
                    //         let temp = transferData(data[key], columnHeader);
                    //         console.log(temp)
                    //         sheetsList[key] = XLSX.utils.aoa_to_sheet(temp);
                    //         sheetsList[key]["!cols"] = wscols;
                    //     }
                    //     console.log(sheetsList)
                    //     wb["SheetNames"] = sheetNames;
                    //     wb["Sheets"] = sheetsList;
                
                    //     XLSX.writeFile(wb, xlsxName + ".xlsx");
                    // }
            
                    // function transferData(data, columnHeader) {
                    //     let content = [];
                    //     content.push(columnHeader);
                    //     data.forEach((item, index) => {
                    //         let arr = [];
                    //         columnHeader.map(column =>{
                    //         arr.push(item[column]);
                    //         })
                    //         content.push(arr);
                    //     });
                    //     return content;
                    // }
    
    
    
                    // // 导出一张表
                    var sheetN = '我是表名字，不是导出文件名'
                    var data = [{name: '后台返回数据11', sex: 'women', age: 56},{name: '后台返回数据2', sex: 'nan', age: 12}]
                    var columnHeaders = ['name', 'sex', 'age']
                    outputXlsxFile(
                        data,        // 数据
                        [{ wch: 50 }, { wch: 50 }, { wch: 10 }],   // 列宽
                        "test-xlsx"       // 导出文件名
                    );
            
                    function outputXlsxFile(data, wscols, xlsxName) {
                        let sheetsList = {};
                        console.log(XLSX.utils)
                        const wb = XLSX.utils.book_new();
                        let temp = transferData(data, columnHeaders);
                        temp.unshift(['我是表格上面的标题啊标题,不是表头不是表头'])
                        console.log(temp)
                        sheetsList[sheetN] = XLSX.utils.aoa_to_sheet(temp);
                        console.log(XLSX.utils.aoa_to_sheet(temp))
                        sheetsList[sheetN]["!merges"] = [{      //合并第一行数据，也就是表头[B1,C1,D1,E1]
                            s: {//s为开始
                                c: 0,//开始列
                                r: 0//开始取值范围
                            },
                            e: {//e结束
                                c: 4,//结束列
                                r: 0//结束范围
                            }
                        }];
                        sheetsList[sheetN]["!cols"] = wscols;
                        sheetsList[sheetN]["!rows"] = [{ hpx: 50 }];
                        // console.log(sheetsList)
                        // 官网写入样式api，但是引入xlsx.core.min.js没用
                        // sheetsList[sheetN]['A1'].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } }
                        wb["SheetNames"] = [sheetN];
                        wb["Sheets"] = sheetsList;
                        console.log(wb)
                
                        XLSX.writeFile(wb, xlsxName + ".xlsx",{defaultCellStyle: { font: {name: 'Arial', sz: '40'}}});
    
                        console.log(XLSX.utils)
                        function transferData(data, columnHeader) {
                            let content = [];
                            content.push(columnHeader);
                            data.forEach((item, index) => {
                                let arr = [];
                                columnHeader.map(column =>{
                                    arr.push(item[column]);
                                })
                                content.push(arr);
                            });
                            return content;
                        }
                    }
            }
    
    
    
    
            
          </script>
    
    
    
            
            
            <script>
                function saveAs(obj, fileName) {
                    var tmpa = document.createElement("a");
                    tmpa.download = fileName || "下载";
                    tmpa.href = URL.createObjectURL(obj);
                    tmpa.click();
                    setTimeout(function () {
                        URL.revokeObjectURL(obj);
                    }, 100);
                }
                var jsono = [{
                    "id": 1, "合并的列头1": "数据11", "合并的列头2": "数据12", "合并的列头3": "数据13", "合并的列头4": "数据14",
                }, {
                    "id": 2, "合并的列头1": "数据21", "合并的列头2": "数据22", "合并的列头3": "数据23", "合并的列头4": "数据24",
                }];
                const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true };
                function downloadExl(json, type) {
                    var tmpdata = json[0];
                    json.unshift({});
                    var keyMap = []; //获取keys
                    for (var k in tmpdata) {
                        keyMap.push(k);
                        json[0][k] = k;
                    }
                    var tmpdata = [];//用来保存转换好的json 
                    json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
                        v: v[k],
                        position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
                    }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
                        v: v.v
                    });
                    
    
    
                    var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
                    console.log(tmpdata)
                    tmpdata["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } };//<====设置xlsx单元格样式
                    tmpdata["!merges"] = [{
                        s: { c: 1, r: 0 },
                        e: { c: 4, r: 0 }
                    }];//<====合并单元格 
                    var tmpWB = {
                        SheetNames: ['mySheet'], //保存的表标题
                        Sheets: {
                            'mySheet': Object.assign({},
                                tmpdata, //内容
                                {
                                    '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
                                })
                        }
                    };
                    console.log(XLSX.utils)
                    tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
                        { bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
                    ))], {
                            type: ""
                        });
                    saveAs(tmpDown, "这里是下载的文件名" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
                }
                function getCharCol(n) {
                    let temCol = '',
                        s = '',
                        m = 0
                    while (n > 0) {
                        m = n % 26 + 1
                        s = String.fromCharCode(m + 64) + s
                        n = (n - m) / 26
                    }
                    return s
                }
                function s2ab(s) {
                    if (typeof ArrayBuffer !== 'undefined') {
                        var buf = new ArrayBuffer(s.length);
                        var view = new Uint8Array(buf);
                        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
                        return buf;
                    } else {
                        var buf = new Array(s.length);
                        for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
                        return buf;
                    }
                }
            </script>
    
        
    </body>
    </html>