JS前端导出Excel

作者: 山哥的哥 | 来源:发表于2023-12-29 11:29 被阅读0次

Vue - table导出excel文件
vue.js、javascript前端导出excel文件
好用的工具库
js-xlsx工具类库 xlsxUtils 使用示例
纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示
VUE-如何实现纯前端导出Excel文件
js-xlsx excel导出学习记录1 基础
文章链接
xlsx.js excel文件下载
使用FileSaver、js-xlsx实现excel导出

项目背景

一个10年+的老项目，还是古老的JS写法。之前的前端导出Excel功能，最近在新版本的浏览器不兼容了。动手改造一下！

JS组件

exceljs 使用教程
file-saver 使用示例

既然是古老的js写法，肯定不能import或require了。
直接浏览器加载：

<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js" integrity="sha512-Qlv6VSKh1gDKGoJbnyA5RMXYcvnpIqhO++MhIM2fStMcGT9i2T//tSwYFlcyoRRDcDZ+TYHpH8azBBCyhpSeqw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js" integrity="sha512-dlPw+ytv/6JyepmelABrgeYgHI0O+frEwgfnPdXDTOIZz+eDgfW07QXG02/O8COfivBdGNINy+Vex+lYmJ5rxw==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>

自定义Excel导出函数

作用是将生成excel文件的一些公共设置做一个简单的封装，方便在需要的地方调用。

// 主要方法
function exportExcel(title, options = {}) {
    if (!options.columns) throw new Error('columns is required');
    if (!options.data) throw new Error('data is required');
    // 创建工作簿
    var _workbook = new ExcelJS.Workbook();

    _workbook.creator = options.creator || 'ziyo.ren';
    _workbook.lastModifiedBy = options.creator || 'ziyo.ren';
    _workbook.created = new Date();
    _workbook.modified = new Date();
    _workbook.lastPrinted = new Date();

    var sheetName = options.sheetName || 'Sheet1';
    // 添加工作表
    var _sheet1 = _workbook.addWorksheet(sheetName);

    _sheet1.columns = options.columns;
    /* 示例
    _sheet1.columns = [
        { header: "名次", key: "sort", width: 10 },
        { header: "班级", key: "class", width: 20 },
        { header: "姓名", key: "name", width: 20 },
        { header: "得分", key: "score", width: 10 },
    ]; 
    _sheet1.addRow({ sort: 1, class: "前端三班", name: "Buer", score: 99 });
    */
    _sheet1.getRow(1).font = { family: 4, size: 14, bold: true }

    options.data.forEach((row, index) => {
        if (index == 0) {
            _sheet1.addRow(row);
            _sheet1.getRow(2).font = { size: 12 }
        } else {
            _sheet1.addRow(row, 'i');
        }
    });
    insertTotalRow(options, _sheet1);

    // 导出表格
    _workbook.xlsx.writeBuffer().then((buffer) => {
        var _file = new Blob([buffer], {
            type: "application/octet-stream",
        });
        saveAs(_file, `${title}.xlsx`);
    });
}


function insertTotalRow(options, worksheet) {
    var data = options.data, 
               columns = options.columns, 
               showTotalRow = options.showTotalRow;
    if (showTotalRow !== true) return;
    var totalLabel = columns.find(c => c.totalLabel);
    var label = totalLabel?.totalLabel || '合计';
    worksheet.addRow([label], 'i');
    var rows = worksheet.rowCount;
    columns.map((c, i) => {
        var cn = columnName(i + 1); // cell name
        var cell = worksheet.getCell(`${cn}${rows}`);
        switch (c.totalFunction) {
            case 'sum':
                var formula = `=SUM(${cn}2:${cn}${data.length + 1})`
                cell.value = { formula, result: 7 }
                break;
        }
    })
}

function columnName(n) {
    var name = '';
    while (n > 0) {
        name = String.fromCharCode(--n % 26 + 65) + name;
        n = (n - n % 26) / 26 | 0;
    }
    return name;
}

可以将上面的自定义函数保存为文件exportExcel.js。在需要导出功能的页面引入加载：

<script src="path/to/exportExcel.js"></script>

完整html

<!DOCTYPE html>
<html lang="zh-cn">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>ExcelJS</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js"
        referrerpolicy="no-referrer"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"
        integrity="sha512-Qlv6VSKh1gDKGoJbnyA5RMXYcvnpIqhO++MhIM2fStMcGT9i2T//tSwYFlcyoRRDcDZ+TYHpH8azBBCyhpSeqw=="
        crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"
        integrity="sha512-dlPw+ytv/6JyepmelABrgeYgHI0O+frEwgfnPdXDTOIZz+eDgfW07QXG02/O8COfivBdGNINy+Vex+lYmJ5rxw=="
        crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script type="text/javascript">
        // 主要方法
        function exportExcel(title, options = {}) {
            if (!options.columns) throw new Error('columns is required');
            if (!options.data) throw new Error('data is required');
            // 创建工作簿
            var _workbook = new ExcelJS.Workbook();

            _workbook.creator = options.creator || 'ziyo.ren';
            _workbook.lastModifiedBy = options.creator || 'ziyo.ren';
            _workbook.created = new Date();
            _workbook.modified = new Date();
            _workbook.lastPrinted = new Date();

            var sheetName = options.sheetName || 'Sheet1';
            // 添加工作表
            var _sheet1 = _workbook.addWorksheet(sheetName);

            _sheet1.columns = options.columns;
            /* 示例
            _sheet1.columns = [
                { header: "名次", key: "sort", width: 10 },
                { header: "班级", key: "class", width: 20 },
                { header: "姓名", key: "name", width: 20 },
                { header: "得分", key: "score", width: 10 },
            ]; 
            _sheet1.addRow({ sort: 1, class: "前端三班", name: "Buer", score: 99 });
            */
            _sheet1.getRow(1).font = { family: 4, size: 14, bold: true }

            options.data.forEach((row, index) => {
                if (index == 0) {
                    _sheet1.addRow(row);
                    _sheet1.getRow(2).font = { size: 12 }
                } else {
                    _sheet1.addRow(row, 'i');
                }
            });
            insertTotalRow(options, _sheet1);

            // 导出表格
            _workbook.xlsx.writeBuffer().then((buffer) => {
                var _file = new Blob([buffer], {
                    type: "application/octet-stream",
                });
                saveAs(_file, `${title}.xlsx`);
            });
        }


        function insertTotalRow(options, worksheet) {
            var data = options.data,
                columns = options.columns,
                showTotalRow = options.showTotalRow;
            if (showTotalRow !== true) return;
            var totalLabel = columns.find(c => c.totalLabel);
            var label = totalLabel?.totalLabel || '合计';
            worksheet.addRow([label], 'i');
            var rows = worksheet.rowCount;
            columns.map((c, i) => {
                var cn = columnName(i + 1); // cell name
                var cell = worksheet.getCell(`${cn}${rows}`);
                switch (c.totalFunction) {
                    case 'sum':
                        var formula = `=SUM(${cn}2:${cn}${data.length + 1})`
                        cell.value = { formula, result: 7 }
                        break;
                }
            })
        }

        function columnName(n) {
            var name = '';
            while (n > 0) {
                name = String.fromCharCode(--n % 26 + 65) + name;
                n = (n - n % 26) / 26 | 0;
            }
            return name;
        }

        function download() {
            var title = ' 数据报表';
            var options = {
                sheetName: title,
                creator: 'ziyo.ren',
                // showTotalRow: true,
                columns: [ // 这里是表头
                    { header: "姓名", key: "name", width: 15, totalLabel: '合计' },
                    { header: "年龄", key: "age", width: 12 },
                    { header: "存款", key: "money", width: 12, totalFunction: 'sum' },
                ],
                data: [  //这里是数据
                    { "name": "asun", "age": 1, "money": 1 },
                    { "name": "ziyoren", "age": 2, "money": 3 },
                    { "name": "shange", "age": 3, "money": 5 }
                ],
            };
            exportExcel(title, options);
        }

    </script>
</head>

<body>
    <button onclick="download()">下载</button>
</body>

</html>

Vue - table导出excel文件
前端导出excel（三----基于Blob.js和 Export2Excel.js做前端导出）[https://w...
vue.js、javascript前端导出excel文件
(vue.js、javascript导出excel文件，前端导出excel文件) 项目需要导出excel表格，本来...
好用的工具库
前端操作Excel JS-XLSX支持Excel文件的导入，json数据导出xlsx，支持各大主流前端框架，详见官...
js-xlsx工具类库 xlsxUtils 使用示例
js-xlsx相关教程:纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示例纯前端利用 js-xls...
纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示
相关教程:纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示例(2)纯前端利用 js-xlsx(3)...
VUE-如何实现纯前端导出Excel文件
excel导出一般后台可以实现，但是如果前台已经有了符合要求的数据，也可以在前端纯js导出。在操作excel方面，...
js-xlsx excel导出学习记录1 基础
项目要用到前端excel导出功能，找到js-xlsx希望啃下安装 npm install xlsx --save...
文章链接
Js 前端导出excel:https://www.jianshu.com/p/619f523d18ad[https...
xlsx.js excel文件下载
excel 文件的下载前端后端导出 excel
使用FileSaver、js-xlsx实现excel导出
需要将页面上的表格内容导出成excel格式。使用了FileSaver、js-xlsx的前端插件。 FileSave...