vue导出excel表格（含多级表头）-补充改表头填充色

第一代

vue导出excle（含多级表头）-CSDN博客

改变表头填充色的关键在于如下代码(可以忽略直接往下看，过程不重要，封装好可以用就行)

for (let row = 0; row < 2 ; row++) {//这个row表示行数(前面那几行做表头，就填几)
    for (let col = 0; col < header.length; col++) {
        const cell_ref = XLSX.utils.encode_cell({ r: row, c: col });
        if (!ws[cell_ref]) {
            continue;
        }
        if (!ws[cell_ref].s) {
            ws[cell_ref].s = {};
        }
        ws[cell_ref].s.fill = {
            patternType: "solid",
            fgColor: { rgb: 'ADD8E6' } // 设置对应背景填充色
        };
    }
}

直接一点，参照一代，改一下Export2Excel.js（直接复制过去）

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

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 exportJsonToExcel({
    multiHeader = [],
    header,
    data,
    filename,
    merges = [],
    autoWidth = true,
    bookType = 'xlsx',
    tableHeaderStyle= {row:2,color:'ADD8E6'}//表头样式设置默认值(前两行填充淡蓝色)
} = {}) {
    /* 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
                    }
                }
            }
        }
    }

// 用于设置表头背景色的方法start
for (let row = 0; row < tableHeaderStyle.row ; row++) {
    for (let col = 0; col < header.length; col++) {
        const cell_ref = XLSX.utils.encode_cell({ r: row, c: col });
        if (!ws[cell_ref]) {
            continue;
        }
        if (!ws[cell_ref].s) {
            ws[cell_ref].s = {};
        }
        ws[cell_ref].s.fill = {
            patternType: "solid",
            fgColor: { rgb: tableHeaderStyle.color } // Light blue color
        };
    }
}
//end
    var wbout = XLSX.write(wb, {
        bookType: bookType,
        bookSST: false,
        type: 'binary'
    });
    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), `${filename}.${bookType}`);
}

我只是在exportJsonToExcel方法里加了如下行,以及最前面的代码(改了一下参数)

tableHeaderStyle= {row:2,color:'ADD8E6'}//表头样式设置默认值(前两行填充淡蓝色)

 前面都是废话，直接上使用方法

还是一样的，只是在使用该方法的页面，可以再传入一个参数用于配置表头，可以不传

vue页面方法使用的参考完整代码如下

<template>
  <div>
    <el-button @click="handleExport">全部导出</el-button>
  </div>
</template>
<script>
import {exportJsonToExcel} from '@/utils/excle/Export2Excel'
export default {
  data() {
    return {
      data:[
        {
          id:1,
          name:'小明',
          type:'男',
          age:18,
          adress:'河11111111111111111111111北'
        },
        {
          id:1,
          name:'小卡',
          type:'女',
          age:20,
          adress:'河南'
        },
        {
          id:1,
          name:'小明',
          type:'男',
          age:18,
          adress:'小时'
        },
        {
          id:1,
          name:'小已',
          type:'女',
          age:28,
          adress:'西安'
        },
      ]
    }
  },
  methods: {
    handleExport() {
            let transTable = this.data; // 获取最新表格数据
            let filterVal = [
                // 对应的字段，与下文中header字段相对应
                "id",
                "name",
                "type",
                "age",
                "adress"
            ];
            const data = this.formatJson(filterVal, transTable); // 处理数据
            exportJsonToExcel({
                filename: "学生表格", // 文件名
                bookType: "xlsx", // 导出文件类型(一般不改)
                autoWidth: true, // 是否适应列宽(一般不改)
                multiHeader: [ // 有多级表头时，可再继续增加数组
                    ["ID", "学生", "", "信息",''],
                ],
                header: [
                    "",
                    "姓名",
                    "性别",
                    "年龄",
                    "住址",
                ],
                merges: ["A1:A2", "B1:C1", "D1:E1"], // 所合并的单元格
                data,
                tableHeaderStyle:{row:2,color:'ADD8E6'}//设置表头
            });
        },
        formatJson(filterVal, jsonData) {
            return jsonData.map((v) =>
                filterVal.map((j) => {
                    if (j === "timestamp") {
                        return parseTime(v[j]);
                    } else {
                        return v[j];
                    }
                })
            );
        },


  },
}
</script>

效果如图