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

最新推荐文章于 2024-05-27 23:07:51 发布
慢半拍、
最新推荐文章于 2024-05-27 23:07:51 发布
阅读量1.9k
点赞数
分类专栏： javascript
原文链接：https://www.cnblogs.com/hill-foryou/p/9230182.html
版权
javascript 专栏收录该内容
25 篇文章 1 订阅
订阅专栏
<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 id="export">Export Sheet to XLSX</button> -->
    <button onclick="downloadExl(jsono)">导出xlsx带样式</button>
    
    <button id="export2">导出xlsx</button>




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




    <!--  引入的js只能和下面相连的结合使用  使用时请注释掉138行以下-->
    <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>



        <!-- 导出带样式 引入的js只能和下面相连的结合使用，注释掉135行以上-->
        <!-- <script src="js/xlsx.full.min.js"></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>

    <!-- <script>

        var ViewModel = function() {
        /* use an array of arrays */
            this.aoa = ko.observableArray([
                [1,2],
                [3,4]
            ]);
            
        };


        // var UserInfos = [
        //         { id: 1, name: "张三", age: "21", aclass: "1801" },
        //         { id: 2, name: "李四", age: "22", aclass: "1802" },
        //         { id: 3, name: "王五", age: "23", aclass: "1803" }
        // ];
        // var ViewModel = {};
        // ViewModel = function ()
        // {
        //     this.aoa = ko.observableArray(UserInfos);               
        // }
            // ko.applyBindings(ViewModel); 
            





        var model = new ViewModel();
        ko.applyBindings(model);
        /* do an update to confirm KO was loaded properly */
        // model.aoa([[1,2,3],[4,5,6]]);
        // model.aoa.push([7,8,9]);
        // model.aoa([[1,2,3],[4,5,6]]);

        document.getElementById("export").onclick = function() {

            var aoa = [
                ['姓名', '性别', '年龄', '注册时间'],
                ['张三', null, null, new Date()],
                ['李四', '女', 22, new Date()]
            ];
            var sheet = XLSX.utils.aoa_to_sheet(aoa);
            console.log(sheet)
            // openDownloadDialog(sheet2blob(sheet), '导出.xlsx');







            /* get array of arrays */
            var data = model.aoa();
            /* convert to worksheet */
            var ws = XLSX.utils.aoa_to_sheet(aoa);
            /* build new workbook */
            var wb = XLSX.utils.book_new(); 
            XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
            /* write file */
            XLSX.writeFile(wb, "knockout.xlsx")
        };
    </script> -->
</body>
</html>
慢半拍、
关注
0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
使用js-xlsx将后端返回数据导出为excel(.xlsx格式)

<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 id="export"&g
复制链接

扫一扫