Vue项目根据表格数据一键导出多表头的Excel表格

Vue项目根据表格数据一键导出多表头的Excel表格

第一步

下载依赖包file-saverxlsx-style

npm install file-saver --save
npm install xlsx-style --save

第二步

新建一个Export2Excel.js文件夹并且引入安装后的依赖包

/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx-style'

function generateArray(table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        for (var C = 0; C < columns.length; ++C) {
            var cell = columns[C];
            var colspan = cell.getAttribute('colspan');
            var rowspan = cell.getAttribute('rowspan');
            var cellValue = cell.innerText;
            if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
            //Skip ranges
            ranges.forEach(function (range) {
                if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
                    for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
                }
            });
            //Handle Row Span
            if (rowspan || colspan) {
                rowspan = rowspan || 1;
                colspan = colspan || 1;
                ranges.push({
                    s: {
                        r: R,
                        c: outRow.length
                    },
                    e: {
                        r: R + rowspan - 1,
                        c: outRow.length + colspan - 1
                    }
                });
            };
            //Handle Value
            outRow.push(cellValue !== "" ? cellValue : null);
            //Handle Colspan
            if (colspan)
                for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
        }
        out.push(outRow);
    }
    return [out, ranges];
};

function datenum(v, date1904) {
    if (date1904) v += 1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {
        s: {
            c: 10000000,
            r: 10000000
        },
        e: {
            c: 0,
            r: 0
        }
    };
    for (var R = 0; R != data.length; ++R) {
        for (var C = 0; C != data[R].length; ++C) {
            if (range.s.r > R) range.s.r = R;
            if (range.s.c > C) range.s.c = C;
            if (range.e.r < R) range.e.r = R;
            if (range.e.c < C) range.e.c = C;
            var cell = {
                v: data[R][C]
            };
            if (cell.v == null) continue;
            var cell_ref = XLSX.utils.encode_cell({
                c: C,
                r: R
            });

            if (typeof cell.v === 'number') cell.t = 'n';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else if (cell.v instanceof Date) {
                cell.t = 'n';
                cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            } else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

function Workbook() {
    if (!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

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;
}

export function export_table_to_excel(id) {
    var theTable = document.getElementById(id);
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";

    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);

    /* add ranges to worksheet */
    // ws['!cols'] = ['apple', 'banan'];
    ws['!merges'] = ranges;

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, {
        bookType: 'xlsx',
        bookSST: false,
        type: 'binary'
    });

    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), "test.xlsx")
}

export function export_json_to_excel({
    multiHeader = [],
    header,
    data,
    filename,
    merges = [],
    autoWidth = true,
    bookType = 'xlsx'
} = {}) {
    /* original data */
    filename = filename || 'excel-list'
    data = [...data]
    data.unshift(header);

    for (let i = multiHeader.length - 1; i > -1; i--) {
        data.unshift(multiHeader[i])
    }
    var ws_name = "SheetJS";
    var wb = new Workbook(),
        ws = sheet_from_array_of_arrays(data);
    if (merges.length > 0) {
        if (!ws['!merges']) ws['!merges'] = [];
        merges.forEach(item => {
            ws['!merges'].push(XLSX.utils.decode_range(item))
        })
    }
    if (autoWidth) {
        /*设置worksheet每列的最大宽度*/
        const colWidth = data.map(row => row.map(val => {
            /*先判断是否为null/undefined*/
            if (val == null) {
                return {
                    'wch': 20
                };
            }
            /*再判断是否为中文*/
            else if (val.toString().charCodeAt(0) > 255) {
                return {
                    'wch': val.toString().length * 2
                };
            } else {
                return {
                    'wch': val.toString().length + 5
                };
            }
        }))
        /*以第一行为初始值*/
        let result = colWidth[0];
        for (let i = 1; i < colWidth.length; i++) {
            for (let j = 0; j < colWidth[i].length; j++) {
                if (result[j]['wch'] < colWidth[i][j]['wch']) {
                    result[j]['wch'] = colWidth[i][j]['wch'];
                }
            }
        }
        ws['!cols'] = result;
    }
    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;
    // 设置单元格框线
    const borderAll = {
        top: {
            style: "thin"
        },
        bottom: {
            style: "thin"
        },
        left: {
            style: "thin"
        },
        right: {
            style: "thin"
        }
    }
    // 给所有单元格加上边框,内容居中,字体,字号,标题表头特殊格式部分后面替代
    for (var k = 0; k < header.length; k++) {
        var dataInfo = wb.Sheets[wb.SheetNames[k]]
        // 给所有单元格加上边框,内容居中,字体,字号
        for (var i in dataInfo) {
            if (
                i === '!ref' ||
                i === '!merges' ||
                i === '!cols' ||
                i === '!rows'
            ) {

            } else {
                dataInfo[i + ''].s = {
                    border: borderAll,
                    alignment: {
                        horizontal: 'center',
                        vertical: 'center'
                    },
                    font: {
                        name: '宋体',
                        sz: 11
                    }
                }
            }
        }
    }
    var wbout = XLSX.write(wb, {
        bookType: bookType,
        bookSST: false,
        type: 'binary'
    });
    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), `${filename}.${bookType}`);
}

第三步

点击页面导出Excel

// 导出按钮
<el-button
   @click="exportExcel()"
   :loading="btnLoading"
   >导出excel</el-button
 >
 
 //
methods: {
    exportExcel() {
      this.btnLoading = true; // 设置之后,在下载完成之后按钮不可点,防止重复点击
      // @/utils/Export2Excel为文件所放置的位置,直接引入使用
      import("@/utils/Export2Excel").then((excel) => {
        let transTable = this.$refs.multipleTable.tableData; // 获取最新表格数据
        let filterVal = [
          // 对应的字段,与下文中header字段相对应
          "title1",
          "title2",
          "title3",
          "title4",
          "title5",
          "title6",
          "title7",
          "title8",
          "title9",
          "title10",
        ];
        const data = formatJson(filterVal, transTable); // 处理数据
        excel.export_json_to_excel({
          filename: "导出Excel", // 文件名
          bookType: "xlsx", // 导出文件类型
          autoWidth: true, // 是否适应列宽
          multiHeader: [ // 有多级表头时,可再继续增加数组
            ["一级表头", "", "", "", "", "", "", "", "", ""],
            [
              "二级表头1",
              "二级表头2",
              "",
              "",
              "二级表头3",
              "",
              "",
              "二级表头4",
              "",
              "",
            ],
          ],
          header: [
            "title1",
            "title2",
            "title3",
            "title4",
            "title5",
            "title6",
            "title7",
            "title8",
            "title9",
            "title10",
          ],
          merges: ["A1:J1", "B2:D2", "E2:G2", "H2:J2"], // 所合并的单元格
          data,
        });
        this.btnLoading = false;
      });
    },
    formatJson(filterVal, jsonData) {
      return jsonData.map((v) =>
        filterVal.map((j) => {
          if (j === "timestamp") {
            return parseTime(v[j]);
          } else {
            return v[j];
          }
        })
      );
    }
   }

第四步

导出
导出的Excel表格

  • 0
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
要在Vue项目中前端导出具有多级表头表格Excel文件,你可以使用`xlsx`库结合一些操作来实现。下面是一个示例代码: 首先,确保已经安装了`xlsx`库。在项目的根目录下运行以下命令: ``` npm install xlsx ``` 然后,在需要导出Excel文件的组件中,可以按照以下方式编写代码: ```vue <template> <div> <button @click="exportExcel">导出Excel</button> </div> </template> <script> import XLSX from 'xlsx'; export default { methods: { exportExcel() { const headers = [ { text: '姓名', rowspan: 2 }, { text: '信息', colspan: 2 }, { text: '科目', rowspan: 2 }, { text: '成绩', colspan: 3 } ]; const data = [ ['张三', '学生', '数学', '语文', '英语'], [null, null, 90, 80, 85] ]; const mergeCells = [ { s: { r: 0, c: 1 }, e: { r: 0, c: 2 } }, { s: { r: 0, c: 3 }, e: { r: 0, c: 5 } } ]; const ws = XLSX.utils.aoa_to_sheet([headers, ...data]); ws['!merges'] = mergeCells; const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, 'Sheet1'); XLSX.writeFile(wb, 'data.xlsx'); } } } </script> ``` 在上述代码中,我们定义了要导出表格表头`headers`和数据`data`,以及合并单元格的信息`mergeCells`。在`exportExcel`方法中,我们首先将表头数据合并为一个二维数组,然后使用`XLSX.utils.aoa_to_sheet`方法将其转换为工作表对象`ws`。接着,我们设置合并单元格的信息,并将其赋值给工作表对象的`!merges`属性。最后,创建工作簿对象`wb`,将工作表对象添加到工作簿中,并使用`XLSX.writeFile`方法将工作簿保存为Excel文件。 在用户点击"导出Excel"按钮时,将触发`exportExcel`方法,从而导出具有多级表头表格Excel文件。 请注意,这种方式只能在现代浏览器中使用,并且导出Excel文件将保存在用户的本地文件系统中。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值