xlsx-populate handsontable导出exce

handsontable数据分页(element-ui 分页)显示，但是导出时导出所有页面的数据

handsontable数据展示图 导出效果图

<div style="height: 48px; text-align: right; padding: 10px 0px;">
      <!-- 分页 -->
        <el-pagination
          @size-change="handleSizeChange"
          @current-change="handleCurrentChange"
          :current-page="currentPage"
          :page-sizes="[ 10, 15, 20, 50, 100, 200, 500, 1000, 2000]"
          :page-size="pageSize"
          layout="total, sizes, prev, pager, next, jumper"
          :total="totalRow">
        </el-pagination>
    </div>
    <div style="height: calc(100% - 50px);border: 1px solid #ccc; margin-left: 5px;">
      <div id="handsontable" ></div>
    </div>

import XlsxPopulate from '../../common/js/xlsx-populate';

computed: {
      ...mapGetters([ 'queryData', 'tableSrc', 'pageSize', 'currentPage']),
      pagination: function(){
        return this.currentPage*this.pageSize;
      },
      //列数
      colNum: function(){
        return this.colTitles.length ? this.colTitles.length : 0;
      },
      //金额列索引
      moneyColIndex: function(){
        let arr = [];
        this.colTitles.forEach(function(item, index){
          if(item['en'] == 'lastBalande' || item['en'] == 'thisMoney' 
                    || item['en'] == 'sumMoney'){
            arr.push(index);
          }
        });
        return arr;
      },
      //显示的列英文
      showColEns: function(){
        let arr = [];
        this.colTitles.forEach(function(item){
          arr.push(item['en']);
        });
        return arr;
      },
    },

 watch: {
      //this.currentPage 或this.pageSize 变化就更新表数据
      pagination: function(){
        this.setTaleDT();
      },
}

 handleSizeChange(pageSize) {
        this.setPageSize(pageSize);
      },
      handleCurrentChange(currentPage) {
        this.setCurrentPage(currentPage);
      },

let hsDT = [...]; //所有数据
 let setting = {
          data: head.concat(hsDT.slice((this.currentPage-1)*this.pageSize, 
                                                   this.currentPage*this.pageSize)) ,
          colHeaders: true,
          mergeCells: head.mergeCells, 
          cells: this.myCells,
        };
        if(this.hot){
          this.hot.destroy();
          this.hot = null;
        }
        this.hot = creatHandsontable('handsontable', setting);

export const creatHandsontable = (id,setting)=>{
  let hot = new Handsontable(document.getElementById(id), {
    data: setting.data ? setting.data : [],
    colHeaders:  setting.colHeaders ? setting.colHeaders: true ,
    rowHeaders: true,
    mergeCells: setting.mergeCells ? setting.mergeCells : true ,
    readOnly: setting.readOnly ? setting.readOnly : true,
    cells: setting.cells ? setting.cells : function(){},
    colWidths: setting.colWidths ,
    rowHeights: setting.rowHeights  ? setting.rowHeights : 32,
    autoColumnSize: true,
    // autoRowSize: true,
    className: 'htCenter htMiddle',//水平  垂直居中
    outsideClickDeselects: false,
    maxRows: setting.data.length ? 
                        setting.data.length : 0,//防止鼠标拖动最后一个元素的时候会自动增加几行
    manualColumnResize: setting.manualColumnResize ? setting.manualColumnResize : true,
    manualRowResize: setting.manualRowResize ? setting.manualRowResize : true,
  });
  hot.selectCell(0, 0);
  return hot;
};

   setTaleDT(){
        let head = this.getHeaderData();//表头数据
        this.hsDT = [];
        let self = this;
        this.tableDT.forEach(function(obj){
          let arr = [];
          //表头数据
          self.colTitles.forEach(function(item, index){
            arr[index] = obj[item['en']];
          });
          self.hsDT.push(arr);
        });
        let setting = {
          data: head.headDT.concat(this.hsDT.slice((this.currentPage-1)*this.pageSize, 
                                                           this.currentPage*this.pageSize)) ,
          colHeaders: true,
          mergeCells: head.mergeCells, 
          cells: this.myCells,
        };
        if(this.hot){
          this.hot.destroy();
          this.hot = null;
        }
        this.hot = creatHandsontable('handsontable', setting);
      },

 getHeaderData(){
        let headDT = new Array(5);//表头数据 二维数组 5行
        headDT[0] = new Array(this.colNum);
        headDT[0][0] = this.headerMap.title ? this.headerMap.title
                           +  '<br>'+ this.headerMap.resultStr: '';
        headDT[1] = new Array(this.colNum);
        headDT[1][0] = this.headerMap.subTitle ? this.headerMap.subTitle : '';
        headDT[2] = new Array(this.colNum);
        headDT[2][this.colNum-1] = this.headerMap.unit ? this.headerMap.unit : '';
        headDT[3] = [];
        headDT[4] = [];
        let colMerges = [];
        this.colTitles.forEach(function(item, index){
          headDT[3].push(item['cn']);
          colMerges.push({
            row: 3,
            col: index,
            rowspan: 2,
            colspan: 1,
          });
        });
        let mergeCells = [
          { row: 0, col: 0, rowspan: 1, colspan: this.colNum},
          { row: 1, col: 0, rowspan: 1, colspan: this.colNum-1},
          { row: 2, col: 0, rowspan: 1, colspan: this.colNum-1},
            ...colMerges,
        ];
        return {
          headDT:  headDT,
          mergeCells: mergeCells
        };
      } 

   myCells: function(row, col, props) {
        let self = this;
        return {
          renderer: function (instance, td, row, col, prop, value, cellProps) {
            self.getTd( td, row, col, value);
          },
        }
      },

 getTd(td, row, col, value){
        td.innerHTML = value ? value: '';
        td.style.verticalAlign =  'middle';
        td.style.fontSize =  '10px';
        td.style.fontFamily =  '宋体';
        td.style.overflow = 'hidden';
        td.style.textOoverflow = 'ellipsis';
        td.style.whiteSpace = 'nowrap';
        if(row == 0){//标题
          td.style.textAlign = 'center';
          td.style.fontWeight = 'bold';
          td.style.fontSize =  '14px';
          td.style.textDecoration =  'underline';
          td.innerHTML = value;
        }
        // else if( row == 2 && col == self.colNum-1){//单位
        else if( row == 2 && col == this.colNum-1){//单位
          td.style.textAlign = 'right';
        }
        else if(row == 3 ){//列头
          td.style.textAlign = 'center';
          td.style.fontWeight = 'bold';
          td.style.fontSize =  '12px';
        }
        else if( row >4 && this.moneyColIndex.indexOf(col) > -1 ){//金额
          td.style.textAlign = 'right';
          td.innerText = value ? money2Thousand(value) : "0.00";
        }
        else{
          td.style.textAlign = 'left';
        }
        return td;
      },

 //导出
      exportFile(){
        if(this.hot && this.hot.getData().length != 0) {
          // writeFile(this.hot, '旬报', this.hsDT);
          let hsDT = this.getHeaderData().headDT.concat(this.hsDT);
          this.writeFile(this.hot, '旬报', hsDT);
        }else{
          Message.error('没有数据可导出!');
        }
      },

writeFile(hot, reportName, hsDT) {
        let _this = this;
        // Load a new blank workbook
        XlsxPopulate.fromBlankAsync().then(function(workbook) {
          console.log(workbook);
          // Modify the workbook.
          let sheet = workbook.sheet('Sheet1').name(reportName);
          let data = hsDT ?  hsDT : hot.getData();
          let range = sheet.range(1, 1, data.length, data[0].length);
          data[0][0] = data[0][0].replace('<br>', '\r\n');//显示的时候不换行 编辑时才换行
          range.value(data);

          // 设置行高, 列宽
          for (let i = 1; i <= data.length; i++) {
            console.log(hot.getRowHeight(i - 1));
            sheet.row(i).height(hot.getRowHeight(i - 1));
          }
          for (let i = 1; i <= data[0].length; i++) {
            console.log(hot.getColWidth(i - 1));
            sheet.column(i).width(hot.getColWidth(i - 1) / 8);
          }

          // 合并单元格
          let mergeCells = hot.getSettings().mergeCells;
          // 判断mergeCells是否为数组
          if (Object.prototype.toString.call(mergeCells) !== '[object Array]') {
            mergeCells = [];
          }

          mergeCells.forEach(o => {
            //合并单元格
            sheet
              .range(o.row + 1, o.col + 1, o.row + o.rowspan, o.col + o.colspan)
              .merged(true);
          });
    
          for (let i = 0; i < data.length; i++) {
            for (let j = 0, row = data[i], l = row.length; j < l; j++) {
              // let td = hot.getCell(i, j);
              let td = _this.getTd({"style": {}}, i, j);
              let fontSize = td.style.fontSize ? parseInt(td.style.fontSize, 10) : 14;
              let bold = td.style.fontWeight
                ? td.style.fontWeight !== 'normal'
                : false;
              let italic = td.style.fontStyle
                ? td.style.fontStyle !== 'normal'
                : false;
              let fontColor = td.style.color ? rgb2Hex(td.style.color).slice(1) : '';
              let verticalAlignment = td.style.verticalAlign  ? 
                         td.style.verticalAlign === 'middle'  ?                                   
                          'center'  :  td.style.verticalAlign : 'center';
              let horizontalAlignment = td.style.textAlign  ? td.style.textAlign : 'left';

              let underline = td.style.textDecoration ?  td.style.textDecoration : '';
              let bgColor = td.style.backgroundColor ?  td.style.backgroundColor : '';
              let fill = rgb2Hex(bgColor).slice(1);
              let wrapText = _this.moneyColIndex.indexOf(j) > -1 ? true : false; // 自动换行

              let cell = sheet.cell(i + 1, j + 1);           
              if(i == 0 && j == 0){
                // cell.h = '月报22'+'<br/>'+'2018年09月';
                cell.cellHTML = true;
              }
              cell.style({
                fontSize,
                bold,
                italic,
                fontColor,
                verticalAlignment,
                horizontalAlignment,
                underline,
                wrapText
              });
              fill && cell.style({  fill  });
              if( i > 4 && _this.moneyColIndex.indexOf(j) > -1 ){
                cell.style({  numberFormat: "#,##0.00"  });
              }
              if( i > 4){
                cell.style({  border: true  });
              }
            }
          }

          // Write to file.
          workbook.outputAsync().then(function(blob) {
            if (window.navigator && window.navigator.msSaveOrOpenBlob) {
              // If IE, you must uses a different method.
              window.navigator.msSaveOrOpenBlob(blob, reportName + '.xlsx');
            } else {
              let url = window.URL.createObjectURL(blob);
              let a = document.createElement('a');
              document.body.appendChild(a);
              a.href = url;
              a.download = reportName + '.xlsx';
              a.click();
              window.URL.revokeObjectURL(url);
              document.body.removeChild(a);
            }
          })
        })
      }

export function rgb2Hex(colorStr) {
  // 十六进制颜色值的正则表达式
  let reg = /^#([0-9a-fA-f]{3}|[0-9a-fA-f]{6})$/
  // 如果是rgb颜色表示
  if (/^(rgb|RGB)/.test(colorStr)) {
    let aColor = colorStr.replace(/(?:\(|\)|rgb|RGB)*/g, '').split(',')
    let strHex = '#'
    for (let i = 0; i < aColor.length; i++) {
      let hex = Number(aColor[i]).toString(16)
      if (hex === '0') {
        hex += hex
      }
      strHex += hex
    }
    if (strHex.length !== 7) {
      strHex = colorStr
    }
    return strHex
  } else if (reg.test(colorStr)) {
    let aNum = colorStr.replace(/#/, '').split('')
    if (aNum.length === 6) {
      return colorStr
    } else if (aNum.length === 3) {
      let numHex = '#'
      for (let i = 0; i < aNum.length; i += 1) {
        numHex += aNum[i] + aNum[i]
      }
      return numHex
    }
  }
  return colorStr
}