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

小鱼儿 2023-03-13 06:51 138阅读 0赞 
  1. <html lang="en">
  2. <head>
  3.     <meta charset="UTF-8">
  4.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  5.     <title>Document</title>
  8. </head>
  9. <body>
  11.     <!-- <button id="export">Export Sheet to XLSX</button> -->
  12.     <button onclick="downloadExl(jsono)">导出xlsx带样式</button>
  14.     <button id="export2">导出xlsx</button>
  19.     <!-- 使用js-xlsx将后端返回数据导出为excel(.xlsx格式) -->
  24.     <!--  引入的js只能和下面相连的结合使用  使用时请注释掉138行以下-->
  25.     <script src="js/xlsx.core.min.js"></script>
  26.     <script>
  27.         document.getElementById("export2").onclick = function() {
  30.                 // 导出多张表
  31.                 // let data = { '基本信息': [{name: '李四', sex: 'nan', age: 12}], '成绩': [{class: '计算机', teacher: 'Mrs wang', score: 90}] };
  32.                 // let columnHeaders = { '基本信息': ['name', 'sex', 'age'], '成绩': ['class', 'teacher', 'score']}
  33.                 // outputXlsxFile(
  34.                 //     data,
  35.                 //     [{ wch: 50 }, { wch: 50 }, { wch: 10 }],
  36.                 //     "test-xlsx"
  37.                 // );
  39.                 // function outputXlsxFile(data, wscols, xlsxName) {
  40.                 //     let sheetNames = [];
  41.                 //     let sheetsList = {};
  42.                 //     const wb = XLSX.utils.book_new();
  43.                 //     console.log(wb)
  44.                 //     for (let key in data) {
  45.                 //         sheetNames.push(key);
  46.                 //         let columnHeader = columnHeaders[key] // 此处是每个sheet的表头
  47.                 //         let temp = transferData(data[key], columnHeader);
  48.                 //         console.log(temp)
  49.                 //         sheetsList[key] = XLSX.utils.aoa_to_sheet(temp);
  50.                 //         sheetsList[key]["!cols"] = wscols;
  51.                 //     }
  52.                 //     console.log(sheetsList)
  53.                 //     wb["SheetNames"] = sheetNames;
  54.                 //     wb["Sheets"] = sheetsList;
  56.                 //     XLSX.writeFile(wb, xlsxName + ".xlsx");
  57.                 // }
  59.                 // function transferData(data, columnHeader) {
  60.                 //     let content = [];
  61.                 //     content.push(columnHeader);
  62.                 //     data.forEach((item, index) => {
  63.                 //         let arr = [];
  64.                 //         columnHeader.map(column =>{
  65.                 //         arr.push(item[column]);
  66.                 //         })
  67.                 //         content.push(arr);
  68.                 //     });
  69.                 //     return content;
  70.                 // }
  74.                 // // 导出一张表
  75.                 var sheetN = '我是表名字，不是导出文件名'
  76.                 var data = [{name: '后台返回数据11', sex: 'women', age: 56},{name: '后台返回数据2', sex: 'nan', age: 12}]
  77.                 var columnHeaders = ['name', 'sex', 'age']
  78.                 outputXlsxFile(
  79.                     data,        // 数据
  80.                     [{ wch: 50 }, { wch: 50 }, { wch: 10 }],   // 列宽
  81.                     "test-xlsx"       // 导出文件名
  82.                 );
  84.                 function outputXlsxFile(data, wscols, xlsxName) {
  85.                     let sheetsList = {};
  86.                     console.log(XLSX.utils)
  87.                     const wb = XLSX.utils.book_new();
  88.                     let temp = transferData(data, columnHeaders);
  89.                     temp.unshift(['我是表格上面的标题啊标题,不是表头不是表头'])
  90.                     console.log(temp)
  91.                     sheetsList[sheetN] = XLSX.utils.aoa_to_sheet(temp);
  92.                     console.log(XLSX.utils.aoa_to_sheet(temp))
  93.                     sheetsList[sheetN]["!merges"] = [{      //合并第一行数据，也就是表头[B1,C1,D1,E1]
  94.                         s: {//s为开始
  95.                             c: 0,//开始列
  96.                             r: 0//开始取值范围
  97.                         },
  98.                         e: {//e结束
  99.                             c: 4,//结束列
  100.                             r: 0//结束范围
  101.                         }
  102.                     }];
  103.                     sheetsList[sheetN]["!cols"] = wscols;
  104.                     sheetsList[sheetN]["!rows"] = [{ hpx: 50 }];
  105.                     // console.log(sheetsList)
  106.                     // 官网写入样式api，但是引入xlsx.core.min.js没用
  107.                     // sheetsList[sheetN]['A1'].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } }
  108.                     wb["SheetNames"] = [sheetN];
  109.                     wb["Sheets"] = sheetsList;
  110.                     console.log(wb)
  112.                     XLSX.writeFile(wb, xlsxName + ".xlsx",{defaultCellStyle: { font: {name: 'Arial', sz: '40'}}});
  114.                     console.log(XLSX.utils)
  115.                     function transferData(data, columnHeader) {
  116.                         let content = [];
  117.                         content.push(columnHeader);
  118.                         data.forEach((item, index) => {
  119.                             let arr = [];
  120.                             columnHeader.map(column =>{
  121.                                 arr.push(item[column]);
  122.                             })
  123.                             content.push(arr);
  124.                         });
  125.                         return content;
  126.                     }
  127.                 }
  128.         }
  134.       </script>
  138.         <!-- 导出带样式 引入的js只能和下面相连的结合使用，注释掉135行以上-->
  139.         <!-- <script src="js/xlsx.full.min.js"></script> -->
  140.         <script>
  141.             function saveAs(obj, fileName) {
  142.                 var tmpa = document.createElement("a");
  143.                 tmpa.download = fileName || "下载";
  144.                 tmpa.href = URL.createObjectURL(obj);
  145.                 tmpa.click();
  146.                 setTimeout(function () {
  147.                     URL.revokeObjectURL(obj);
  148.                 }, 100);
  149.             }
  150.             var jsono = [{
  151.                 "id": 1, "合并的列头1": "数据11", "合并的列头2": "数据12", "合并的列头3": "数据13", "合并的列头4": "数据14",
  152.             }, {
  153.                 "id": 2, "合并的列头1": "数据21", "合并的列头2": "数据22", "合并的列头3": "数据23", "合并的列头4": "数据24",
  154.             }];
  155.             const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true };
  156.             function downloadExl(json, type) {
  157.                 var tmpdata = json[0];
  158.                 json.unshift({});
  159.                 var keyMap = []; //获取keys
  160.                 for (var k in tmpdata) {
  161.                     keyMap.push(k);
  162.                     json[0][k] = k;
  163.                 }
  164.                 var tmpdata = [];//用来保存转换好的json 
  165.                 json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
  166.                     v: v[k],
  167.                     position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
  168.                 }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
  169.                     v: v.v
  170.                 });
  174.                 var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
  175.                 console.log(tmpdata)
  176.                 tmpdata["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } };//<====设置xlsx单元格样式
  177.                 tmpdata["!merges"] = [{
  178.                     s: { c: 1, r: 0 },
  179.                     e: { c: 4, r: 0 }
  180.                 }];//<====合并单元格 
  181.                 var tmpWB = {
  182.                     SheetNames: ['mySheet'], //保存的表标题
  183.                     Sheets: {
  184.                         'mySheet': Object.assign({},
  185.                             tmpdata, //内容
  186.                             {
  187.                                 '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
  188.                             })
  189.                     }
  190.                 };
  191.                 console.log(XLSX.utils)
  192.                 tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
  193.                     { bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
  194.                 ))], {
  195.                         type: ""
  196.                     });
  197.                 saveAs(tmpDown, "这里是下载的文件名" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
  198.             }
  199.             function getCharCol(n) {
  200.                 let temCol = '',
  201.                     s = '',
  202.                     m = 0
  203.                 while (n > 0) {
  204.                     m = n % 26 + 1
  205.                     s = String.fromCharCode(m + 64) + s
  206.                     n = (n - m) / 26
  207.                 }
  208.                 return s
  209.             }
  210.             function s2ab(s) {
  211.                 if (typeof ArrayBuffer !== 'undefined') {
  212.                     var buf = new ArrayBuffer(s.length);
  213.                     var view = new Uint8Array(buf);
  214.                     for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  215.                     return buf;
  216.                 } else {
  217.                     var buf = new Array(s.length);
  218.                     for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
  219.                     return buf;
  220.                 }
  221.             }
  222.         </script>
  224.     <!-- <script>
  226.         var ViewModel = function() {
  227.         /* use an array of arrays */
  228.             this.aoa = ko.observableArray([
  229.                 [1,2],
  230.                 [3,4]
  231.             ]);
  233.         };
  236.         // var UserInfos = [
  237.         //         { id: 1, name: "张三", age: "21", aclass: "1801" },
  238.         //         { id: 2, name: "李四", age: "22", aclass: "1802" },
  239.         //         { id: 3, name: "王五", age: "23", aclass: "1803" }
  240.         // ];
  241.         // var ViewModel = {};
  242.         // ViewModel = function ()
  243.         // {
  244.         //     this.aoa = ko.observableArray(UserInfos);               
  245.         // }
  246.             // ko.applyBindings(ViewModel); 
  253.         var model = new ViewModel();
  254.         ko.applyBindings(model);
  255.         /* do an update to confirm KO was loaded properly */
  256.         // model.aoa([[1,2,3],[4,5,6]]);
  257.         // model.aoa.push([7,8,9]);
  258.         // model.aoa([[1,2,3],[4,5,6]]);
  260.         document.getElementById("export").onclick = function() {
  262.             var aoa = [
  263.                 ['姓名', '性别', '年龄', '注册时间'],
  264.                 ['张三', null, null, new Date()],
  265.                 ['李四', '女', 22, new Date()]
  266.             ];
  267.             var sheet = XLSX.utils.aoa_to_sheet(aoa);
  268.             console.log(sheet)
  269.             // openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
  277.             /* get array of arrays */
  278.             var data = model.aoa();
  279.             /* convert to worksheet */
  280.             var ws = XLSX.utils.aoa_to_sheet(aoa);
  281.             /* build new workbook */
  282.             var wb = XLSX.utils.book_new(); 
  283.             XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  284.             /* write file */
  285.             XLSX.writeFile(wb, "knockout.xlsx")
  286.         };
  287.     </script> -->
  288. </body>
  289. </html>

发表评论

表情：
评论列表 （有 0 条评论，138人围观）

还没有评论，来说两句吧...

相关阅读