vue +element 导出多级表头（标题）

矫情吗；* 2022-12-26 09:13 315阅读 0赞

## 借鉴地址  ##

[https://blog.csdn.net/xingchen678/article/details/102686621][https_blog.csdn.net_xingchen678_article_details_102686621]

[https://blog.csdn.net/beidaol/article/details/107004538][https_blog.csdn.net_beidaol_article_details_107004538]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEyMDY2OQ_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEyMDY2OQ_size_16_color_FFFFFF_t_70 1][]

#  安装依赖 #

npm install --save xlsx file-saver

### 按钮  ###

<el-button @click="handleExportMarketingList" type="success">导出</el-button>

### 表格  ###

<el-table :data="tableData" border>
          <el-table-column prop="name" label="热源名称"> </el-table-column>
          <el-table-column prop="date" label="起始日期"> </el-table-column>
          <el-table-column prop="address" label="供热面积"> </el-table-column>
          <el-table-column prop="IndoorTemp" label="室内计算温度">
          </el-table-column>
          <el-table-column prop="outdoorTemp" label="预测室外温度">
          </el-table-column>
          <el-table-column prop="actualTemp" label="实际室外温度">
          </el-table-column>
          <el-table-column header-align="center" label="调度情况">
            <el-table-column prop="Calories" label="热量GJ/h"> </el-table-column>
            <el-table-column prop="flow" label="流量T/h"> </el-table-column>
            <el-table-column prop="Temperature" label="一次供温℃"></el-table-column>
            <el-table-column prop="Warmup" label="一次回温℃"> </el-table-column>
          </el-table-column>
          <el-table-column header-align="center" label="负荷预测">
            <el-table-column prop="Calories2" label="热量"> </el-table-column>
            <el-table-column prop="actualTemp2" label="热负荷"> </el-table-column>
            <el-table-column prop="actualTemp22" label="热指标"> </el-table-column>
    
            
          </el-table-column>
          <el-table-column header-align="center" label="实际供热">
            <el-table-column prop="Calories3" label="热量"> </el-table-column>
            <el-table-column prop="actualTemp31" label="热负荷"> </el-table-column>
            <el-table-column prop="actualTemp32" label="热指标"> </el-table-column>
            <el-table-column prop="actualTemp33" label="流量"> </el-table-column>
            <el-table-column prop="actualTemp34" label="一次供温">
            </el-table-column>
            <el-table-column prop="actualTemp35" label="一次回温">
            </el-table-column>
          </el-table-column>
        </el-table>

### 数据  export default \{ data（）\{\} return\{  tableData：\[ \] \} \} ###

tableData: [
            {
              date: '国投1#',
              name: '王小虎',
              address: '上海',
              IndoorTemp: 15,
              outdoorTemp: 3,
              actualTemp: 12,
              Calories: 1,
              flow: 14,
              Temperature: 23,
              Warmup: 15,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
            {
              date: '国投2#',
              name: '王小虎',
              address: '上海市',
              IndoorTemp: 15,
              outdoorTemp: 13,
              actualTemp: 22,
              Calories: 12,
              flow: 21,
              Temperature: 11,
              Warmup: 12,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
            {
              date: '城西预测',
              name: '王虎',
              address: '上海市普',
              IndoorTemp: 5,
              outdoorTemp: 3,
              actualTemp: 43,
              Calories: 16,
              flow: 31,
              Temperature: 22,
              Warmup: 15,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
            {
              date: '城西预',
              name: '王小',
              address: '上海市普',
              IndoorTemp: 15,
              outdoorTemp: 23,
              actualTemp: 16,
              Calories: 15,
              flow: 31,
              Temperature: 22,
              Warmup: 19,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
            {
              date: '城西预测',
              name: '王虎',
              address: '上海市',
              IndoorTemp: 15,
              outdoorTemp: 13,
              actualTemp: 19,
              Calories: 17,
              flow: 16,
              Temperature: 22,
              Warmup: 21,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
            {
              date: '城西预测',
              name: '小虎',
              address: '上市普',
              IndoorTemp: 15,
              outdoorTemp: 13,
              actualTemp: 13,
              Calories: 16,
              flow: 31,
              Temperature: 22,
              Warmup: 12,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
            {
              date: '城西预测',
              name: '王虎',
              address: '上海普',
              IndoorTemp: 15,
              outdoorTemp: 13,
              actualTemp: 13,
              Calories: 16,
              flow: 31,
              Temperature: 22,
              Warmup: 12,
              Calories2: 1,
              actualTemp2: 2,
              actualTemp22: 22,
              Calories3: 3,
              actualTemp31: 31,
              actualTemp32: 32,
              actualTemp33: 33,
              actualTemp34: 34,
              actualTemp35: 35,
            },
          ],

### 方法名 【@/api/Export2Excel 本地路径 自己个把Export2Excel.js 放到项目里面更改路径即可】 ###

methods: {
    
        handleExportMarketingList() {
          // 懒加载 此路径为本地路径
          import('@/api/Export2Excel').then((excel) => {
            const multiHeader = [
              [
                '热源名称',
                '起始日期',
                '供热面积',
                '室内计算温度',
                '预测室外温度',
                '实际室外温度',
                '调度情况',
                '',
                '',
                '',
                '负荷预测',
                '',
                '',
                '实际供热',
                '',
                '',
                '',
                '',
                '',
              ],
            ]
            const multiHeader2 = [
              [
                '',
                '',
                '',
                '',
                '',
                '',
                '热量GJ/h',
                '流量T/h',
                '一次供温℃',
                '一次回温℃',
                '热量',
                '热负荷',
                '热指标',
                '热量',
                '热负荷',
                '热指标',
                '流量',
                '一次供温',
                '一次回温',
              ],
            ]
            // const tHeader = []
            const filterVal = [
              'date',
              'name',
              'address',
              'IndoorTemp',
              'outdoorTemp',
              'actualTemp',
              'Calories',
              'flow',
              'Temperature',
              'Warmup',
              'Calories2',
              'actualTemp2',
              'actualTemp22',
              'Calories3',
              'actualTemp31',
              'actualTemp32',
              'actualTemp33',
              'actualTemp34',
              'actualTemp35',
            ] // 表头所对应的字段，这里未填写
            // console.log(this.tableData)
            // console.log(filterVal)
            const data = this.tableData.map((v) => filterVal.map((j) => v[j]))
            // console.log(data)
            // const data = this.tableData.map(v => filterVal.map(j => v[j]));
    
            // 进行所有表头的单元格合并
            const merges = [
              'A1:A2',
              'B1:B2',
              'C1:C2',
              'D1:D2',
              'E1:E2',
              'F1:F2',
              'G1:J1',
              'K1:M1',
              'N1:S1',
            ]
    
            excel.export_json_to_excel({
              multiHeader, // 这里是第一行的表头
              multiHeader2, // 这里是第二行的表头
              // header: tHeader, // 这里是第三行的表头
              data,
              filename: '热源预测历史数据',
              merges,
            })
          })
        },
      },

# Export2Excel.js  #

/* eslint-disable */
    import { saveAs } from 'file-saver'
    // import saveAs from 'vue-json-excel'
    import XLSX from 'xlsx'
    
    
    
    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 = "热源预测历史数据";
    
      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 = [],
      multiHeader2 = [],
      // header,
      data,
      filename,
      merges = [],
      autoWidth = true,
      bookType = 'xlsx'
    } = {}) {
      /* original data */
      filename = filename || 'excel-list'
      data = [...data]
      // data.unshift(header);
    
      for (let i = multiHeader2.length - 1; i > -1; i--) {
        data.unshift(multiHeader2[i])
      }
      for (let i = multiHeader.length - 1; i > -1; i--) {
        data.unshift(multiHeader[i])
      }
    
      var ws_name = "热源预测历史数据";
      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': 10
            };
          }
          /*再判断是否为中文*/
          else if (val.toString().charCodeAt(0) > 255) {
            return {
              'wch': val.toString().length * 2
            };
          } else {
            return {
              'wch': val.toString().length
            };
          }
        }))
        /*以第一行为初始值*/
        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;
    
      var wbout = XLSX.write(wb, {
        bookType: bookType,
        bookSST: false,
        type: 'binary'
      });
      saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
      }), `${filename}.${bookType}`);
    }

## ##

[https_blog.csdn.net_xingchen678_article_details_102686621]: https://blog.csdn.net/xingchen678/article/details/102686621
[https_blog.csdn.net_beidaol_article_details_107004538]: https://blog.csdn.net/beidaol/article/details/107004538
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEyMDY2OQ_size_16_color_FFFFFF_t_70]: /images/20221120/25cf445bf7254e419fb80ce5e9ca5386.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjEyMDY2OQ_size_16_color_FFFFFF_t_70 1]: /images/20221120/33aef41d621d44b694576d5487e03b65.png