vue+handsontable合并单元格获取数据以及导出表格功能

首先我的handsontable数据格式是json数组

1.合并单元格获取合并后的数据：

 <HotTable :root="root" :settings="hotSettings" v-if="showTable" ref="hottable"

style="width:100%;height:300px;overflow:hidden;"></HotTable>

注意：这是以前版本：现在不能用了

this.$refs.hottable.table.getPlugin('MergeCells').mergedCellsCollection.mergedCells

现在这样用：handsontable6.0.0版本的这样获取合并单元格数据：（目前7.0.0版本不能用）

先安装

npm install handsontable@6.0.0 @handsontable/vue

<HotTable
      :root="root"
      :settings="hotSettings"
      v-if="showTable"
      ref="hottable"
      style="width:100%;height:min-content;overflow:hidden;"
    ></HotTable>

import { HotTable } from '@handsontable/vue';
import 'handsontable/dist/handsontable.full.css';
import "handsontable/languages/zh-CN"; //引入中文语言包

components: {
    HotTable
  },
data () {
    return {
      dragTo: [],
      showTable: false, //控制表格显示隐藏
      root: "test-hot",
      hotSettings: {
        data: [],
        columns: [],
        className: "htCenter htMiddle",
        mergeCells: [], //右键菜单如果有合并单元格，这项必须写
        //表格线customBorders:true必须写，contextMenu里面的borders才能生效
        customBorders: true,
        language: "zh-CN", //声明用中文的语言包
        contextMenu: [
          "row_above",
          "row_below",
          "remove_row",
          "mergeCells"
          // hsep1,hsep2,undo，redo,col_left,col_right,remove_col,alignment,borders,copy,cut,row_below,row_above
        ],
        mergeCells: true, //别忘了还有他！！
        stretchH: 'all',
        trimWhitespace: true,  //过滤掉空格
        afterChange: this.afterChangeMe,
        afterRender: this.afterRenderMe
      }
    }
  },

methods: {
    showTableMe (dataObj) {
      this.showTable = true
      this.dragTo = dataObj.dragTo
      this.hotSettings.columns = []
      this.hotSettings.data = []
      let obj = {} //拼凑表头
      for (let item of dataObj.columnList) {
        obj[item] = item
        this.hotSettings.columns.push({
          data: item
        })
      }
      this.hotSettings.data.unshift(obj)
      
      // console.log(this.hotSettings.columns)  //[{data: "YEAR"},{data: "DW"}]
      // this.hotSettings.data  //[{YEAR:"YEAR",DW:"DW"}]
    },
    editShowTableMe (dataObj) {
      this.showTable = true
      this.dragTo = dataObj.dragTo
      this.hotSettings.columns = []
      this.hotSettings.data = []
      this.hotSettings.mergeCells = []
      this.hotSettings.data = dataObj.hotSettings.hotSettingsData
      this.hotSettings.mergeCells = dataObj.hotSettings.mergeCellsArr
      for (let item of this.dragTo) {
        if (item.column != 'ID') {
          this.hotSettings.columns.push({
            data: item.column
          })
        }
      }
      // console.log(this.hotSettings.columns)  //[{data: "YEAR"},{data: "DW"}]
    },
    //更新
    afterChangeMe (changes, source) {
      this.mergeArr = this.mergeMethod()
    },
    //获取合并后的数据
    mergeMethod () {
      if (this.$refs.hottable.hotInstance != undefined) {
        return this.$refs.hottable.hotInstance.getPlugin('MergeCells').mergedCellsCollection.mergedCells
      }
    },
    // isForced:Boolean
    afterRenderMe (isForced) {
      let obj = {
        dragTo: this.dragTo,
        hotSettingsData: this.hotSettings.data,
        mergeCellsArr: this.mergeArr ? this.mergeArr : this.hotSettings.mergeCells
      }
      this.$emit('saveHeader', obj)
    }
  }

 

2.导出表格功能：

this.$refs.hottable.hotInstance.getData()此方法获取的结果是二维数组

导出表格的时候我需要这样的二维数组去导出表格

上代码：导出表格

exportExcel() {
      let sheetMerges = this.changeMergeCellsToSheet(this.hotSettings.mergeCells)
      aoaToExcel(this.hotSettings.getData,sheetMerges, this.selectTree.name)
},
changeMergeCellsToSheet(mergeCells){
      let merges = []
      mergeCells.forEach(item =>{
        if (item.rowspan !==1 || item.colspan !== 1){
          let tmp = {
            s: {//s为开始
              c: item.col,//开始列
              r: item.row//可以看成开始行,实际是取值范围
            },
            e: {//e结束
              r: item.row + item.rowspan-1,//结束列
              c: item.col + item.colspan-1//结束行
            }
          }
          merges.push(tmp)
        }
      })
      return merges;
}
export function aoaToExcel(aoa,mergeCells, fileName) {
  const wopts = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  }
  const workBook = {
    SheetNames: ['Sheet1'],
    Sheets: {},
    Props: {}
  }
  workBook.Sheets['Sheet1'] = XLSX.utils.aoa_to_sheet(aoa)
  workBook.Sheets['Sheet1']['!merges'] = mergeCells
  FileSaver.saveAs(new Blob([changeData(XLSX.write(workBook, wopts))],
    { type: 'application/octet-stream' }), fileName + '.xlsx')
}

function changeData(s) {
  if (typeof ArrayBuffer !== 'undefined') {
    let buf = new ArrayBuffer(s.length)
    let view = new Uint8Array(buf)
    for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
    return buf
  } else {
    let buf = new Array(s.length)
    for (let i = 0; i !== s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff
    return buf
  }
}