美文网首页
xlsx-populate handsontable导出exce

xlsx-populate handsontable导出exce

作者: 啊啊啊阿南 | 来源:发表于2018-09-06 19:24 被阅读0次

    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
    }
    

    相关文章

      网友评论

          本文标题:xlsx-populate handsontable导出exce

          本文链接:https://www.haomeiwen.com/subject/hyffgftx.html