js利用sheetjs生成excel并导出

下载sheet.js

github官方地址 : https://github.com/SheetJS/js-xlsx

引入sheet.js

<script src="./sheetjs-master/dist/xlsx.full.min.js"></script>

依赖js

        function openDownloadDialog(url, saveName) {
            if (typeof url == 'object' && url instanceof Blob) {
                url = URL.createObjectURL(url); // 创建blob地址
            }
            var aLink = document.createElement('a');
            aLink.href = url;
            aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
            var event;
            if (window.MouseEvent) event = new MouseEvent('click');
            else {
                event = document.createEvent('MouseEvents');
                event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0,
                    null);
            }
            aLink.dispatchEvent(event);
        }

        // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
        function sheet2blob(sheet, sheetName) {
            sheetName = sheetName || 'sheet1';
            var workbook = {
                SheetNames: [sheetName],
                Sheets: {}
            };
            workbook.Sheets[sheetName] = sheet;
            // 生成excel的配置项
            var wopts = {
                bookType: 'xlsx', // 要生成的文件类型
                bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
                type: 'binary'
            };
            var wbout = XLSX.write(workbook, wopts);
            var blob = new Blob([s2ab(wbout)], {
                type: "application/octet-stream"
            });
            // 字符串转ArrayBuffer
            function s2ab(s) {
                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;
            }
            return blob;
        }

点击下载

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

可以格式化aoa和sheet

        function dataToAoa(list) {
            var hashHeadToChinese = {
                name: '姓名',
                mobile: '手机',
                goods_name: '所报项目',
                admin_realname: '学习顾问',
                success_time: '成交时间'
            }
            let aoa = [],
                header = []
            Object.keys(hashHeadToChinese).forEach((key) => {
                header.push(hashHeadToChinese[key])
                if (key === 'goods_name') {
                    header.push(null, null, null)
                } else if (key === 'admin_realname' || key === 'success_time') {
                    header.push(null, null)
                } else if (key === 'mobile') {
                    header.push(null)
                }
            })
            aoa.push(header)
            list.forEach(item => {
                let line = [];
                Object.keys(hashHeadToChinese).forEach(key => {
                    line.push(item[key])
                    if (key === 'goods_name') {
                        line.push(null, null, null)
                    } else if (key === 'admin_realname' || key === 'success_time') {
                        line.push(null, null)
                    } else if (key === 'mobile') {
                        line.push(null)
                    }
                })
                aoa.push(line)
            })
            return aoa
        }

        $('.getsheet').on('click', () => {
            let excelName = '表格'
            if (userList && userList.length !== 0) {
                let aoa = dataToAoa(userList)
                var tableName = window.prompt('请输入你要导出的表格的名字,例如: 表格')
                var sheet = XLSX.utils.aoa_to_sheet(aoa);
                let merges = [];
                for (let i = 0; i < aoa.length; i++) {
                    merges.push({
                        s: {           // 合并第i行的第1个到第2个单元格为一个格
                            r: i,
                            c: 1
                        },
                        e: {
                            r: i,
                            c: 2
                        }
                    }, {
                        s: {    // 合并第i行的第3个到第6个单元格为一个格
                            r: i,
                            c: 3
                        },
                        e: {
                            r: i,
                            c: 6
                        }
                    }, {
                        s: {
                            r: i,
                            c: 7
                        },
                        e: {
                            r: i,
                            c: 9
                        }
                    }, {
                        s: {
                            r: i,
                            c: 10
                        },
                        e: {
                            r: i,
                            c: 13
                        }
                    })
                }
                sheet['!merges'] = merges;
                console.log(merges);
                openDownloadDialog(sheet2blob(sheet), `${tableName}.xlsx`);
            } else {
                alert('数据异常，生成excel失败')
            }
        })