xlsx-populate handsontable批量導(dǎo)出excel

      //導(dǎo)出按鈕
      multiExport(nodes, single) {
        this.getAllTableData(nodes, single);
      },
      //獲取所有表數(shù)據(jù)
      getAllTableData(nodes, single) {
        //查詢表數(shù)據(jù)條件
        let data = { ... };
        queryData(data).then(result => {
          if (result.data.errorCode == '0') {   
            let exportData = [];
            for (let i = 0, len = result.data.resultList.length; i < len; i++) {
              let ele = result.data.resultList[i];
              //表的格式不一樣， 按個(gè)人需求定
              let type = this.getTableType(ele.defaultSheet.showByDate, 
                                           ele.defaultSheet.showChineseMoneyType);
              //表頭數(shù)據(jù)  只有表頭需要合并單元格
              let head = this.getHeaderData(type, ele.defaultSheet);
              //表體數(shù)據(jù)
              let body = this.getBodyData(type, ele.defaultSheet, ele.sheetData);
              exportData.push({
                tableData: head.headDT.concat(body.bodyData),
                mergeCells: head.mergeCells, 
                rowHeights: body.rowHeights,
                colWidths: body.colWidths,
                reportName: ele.defaultSheet.sheetName,
                tableType: type
              });
            }   
            if(single)//是否所有數(shù)據(jù)導(dǎo)出到一個(gè)sheet
              this.exportSingleSheet(exportData);
            else
              this.exportMultipleSheets(exportData);
          } else {
            Message.error(result.data.msg);
          }
        }).catch(err => {
          Message.error(`${err}`);
        });
      },
  • 1、每張表分別導(dǎo)出到多個(gè)sheet
handsontable展示圖
//批量導(dǎo)出多個(gè)賬簿
      exportMultipleSheets(tableData) {
        let _this = this;
        // Load a new blank workbook
        XlsxPopulate.fromBlankAsync().then(function(workbook) {
          // Modify the workbook.
          for (let i = 0, len = tableData.length; i < len; i++) {
            // let sheet = workbook.addSheet(tableData[i].reportName); //名稱太長(zhǎng)
            let sheet = workbook.addSheet("sheet"+(i+1));
            let data = tableData[i].tableData;
            let tableType = tableData[i].tableType;
            let range = sheet.range(1, 1, data.length, data[0].length);
            // data[0][0] = data[0][0].replace('<br>', '\r\n');//顯示的時(shí)候不換行 編輯時(shí)才換行
            range.value(data);

            // 設(shè)置行高, 列寬
            for (let j = 1, len = tableData[i].rowHeights.length; j <= len; j++) {
              sheet.row(j).height(tableData[i].rowHeights[j - 1]);
            }
            for (let j = 1, len = tableData[i].colWidths.length; j <= len; j++) {
              sheet.column(j).width(tableData[i].colWidths[j - 1] / 8);
            }
            // 合并單元格
            let mergeCells = tableData[i].mergeCells;
            // 判斷mergeCells是否為數(shù)組
            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++) {
                // font-size, font-weight
                // let td = opt[i].hot.getCell(i, j)
                let td = _this.getTd(tableType, {"style": {}}, i, j, row[j]);
                let fontSize = td.style.fontSize
                  ? parseInt(td.style.fontSize, 10)
                  : 14
                // let fontFamily = td.style.fontFamily
                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 fill = td.style.backgroundColor ?
                           rgb2Hex(td.style.backgroundColor ).slice(1) : '';
                let wrapText = true; // 自動(dòng)換行

                let cell = sheet.cell(i + 1, j + 1);
                cell.style({
                  fontSize,
                  bold,
                  italic,
                  fontColor,
                  verticalAlignment,
                  horizontalAlignment,
                  underline,
                  wrapText
                })
                fill &&  cell.style({
                    fill
                  });
                if( i > 3){
                  cell.style({  border: true  });
                }
                if(tableType == 1 && i > 5 && (j == 2 || j == 3 || j == 5)){
                  cell.style({  numberFormat: "#,##0.00"  });
                }
                if(tableType == 2 && i > 5 && (j == 3 || j == 4 || j == 6)){
                  cell.style({  numberFormat: "#,##0.00"  });
                }
              }
            }
          }
          workbook.deleteSheet('Sheet1');

          // 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, '總賬.xlsx')
            } else {
              let url = window.URL.createObjectURL(blob);
              let a = document.createElement('a');
              document.body.appendChild(a);
              a.href = url;
              a.download = '總賬.xlsx';
              a.click();
              window.URL.revokeObjectURL(url);
              document.body.removeChild(a);
            }
          })
        })
      },
導(dǎo)出到多個(gè)sheet
  • 2、所有表導(dǎo)出到一個(gè)sheet
//批量導(dǎo)出 一個(gè)賬簿
      exportSingleSheet(tableData){
        let _this = this;
        // Load a new blank workbook
        XlsxPopulate.fromBlankAsync().then(function(workbook) {
          // Modify the workbook.
          let sheet = workbook.sheet('Sheet1').name('總賬');
          let tableGap = 2;//每個(gè)表之間空的行數(shù)
          let sumLastLen = 0;
          for (let k = 0, len = tableData.length; k < len; k++) {
            let data = tableData[k].tableData;
            let tableType = tableData[k].tableType;
            let range;
             /***和多個(gè)sheet的區(qū)別****/
            if(k == 0){
              range = sheet.range(1, 1, data.length, data[0].length);
            }else{
              sumLastLen += tableData[k-1].tableData.length ? tableData[k-1].tableData.length : 0;
              sumLastLen += tableGap;
              range = sheet.range(1 + sumLastLen, 1, sumLastLen + data.length, data[0].length);
            }
            range.value(data);

            // 設(shè)置行高, 列寬
            for (let j = 1, len = tableData[k].rowHeights.length; j <= len; j++) {
              sheet.row(j).height(tableData[k].rowHeights[j - 1]);
            }
            for (let j = 1, len = tableData[k].colWidths.length; j <= len; j++) {
              sheet.column(j).width(tableData[k].colWidths[j - 1] / 8);
            }

            // 合并單元格
            let mergeCells = tableData[k].mergeCells;
            // 判斷mergeCells是否為數(shù)組
            if (Object.prototype.toString.call(mergeCells) !== '[object Array]') {
              mergeCells = [];
            }
            /***和多個(gè)sheet的區(qū)別****/
            mergeCells.forEach(o => {
              sheet
                .range(o.row + 1 + sumLastLen , o.col + 1,
                  o.row + o.rowspan + sumLastLen, 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 = _this.getTd(tableType, {"style": {}}, i, j, row[j]);
                let fontSize = td.style.fontSize
                  ? parseInt(td.style.fontSize, 10)
                  : 14;
                // let fontFamily = td.style.fontFamily
                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 fill = td.style.backgroundColor ? 
                             rgb2Hex(td.style.backgroundColor ).slice(1) : '';
                let wrapText = true; // 自動(dòng)換行
                /***和多個(gè)sheet的區(qū)別****/
                let cell = sheet.cell(i + 1 + sumLastLen, j + 1);
                cell.style({
                  fontSize,
                  bold,
                  italic,
                  fontColor,
                  verticalAlignment,
                  horizontalAlignment,
                  underline,
                  wrapText
                });
                fill &&  cell.style({
                  fill
                });
                if( i > 3){
                  cell.style({  border: true  });
                }
                if(tableType == 1 && i > 5 && (j == 2 || j == 3 || j == 5)){
                  cell.style({  numberFormat: "#,##0.00"  });
                }
                if(tableType == 2 && i > 5 && (j == 3 || j == 4 || j == 6)){
                  cell.style({  numberFormat: "#,##0.00"  });
                }

              }
            }
          }
          // 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, '總賬.xlsx')
            } else {
              let url = window.URL.createObjectURL(blob);
              let a = document.createElement('a');
              document.body.appendChild(a);
              a.href = url;
              a.download = '總賬.xlsx';
              a.click();
              window.URL.revokeObjectURL(url);
              document.body.removeChild(a);
            }
          })
        })
      }
多張表導(dǎo)出到一個(gè)sheet

下面可忽略


  //渲染表格樣式
      mycells: function(row, col, props) {
        let _this = this;
        return {
          renderer: function (instance, td, row, col, prop, value, cellProps) {
            _this.getTd(_this.tableType, td, row, col, value);
          }
        }
      },

      getTd(tableType, td, row, col, value){
        if(tableType == 1){
          td.innerText = value ? value: '';
          td.style.verticalAlign =  'middle';
          td.style.fontSize =  '10px';
          td.style.fontFamily =  '宋體';
          td.style.height = '20px';
          td.style.overflow = 'hidden';
          td.style.textOoverflow = 'ellipsis';
          td.style.whiteSpace = 'nowrap';
          if(row == 0 || row == 1){//標(biāo)題
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '14px';
            td.style.textDecoration =  'underline';
            // td.innerHTML = value;
          }
          else if( row == 3 && col == 5){//單位
            td.style.textAlign = 'right';
          }
          else if(row == 4){//列頭
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '12px';
          } else if( (row > 5 && (col == 0 || col == 4) )){//日期 借或貸
            td.style.textAlign = 'center';
          }
          else if( (row > 5 && (col == 2 || col == 3 || col == 5))){//金額
            td.style.textAlign = 'right';
            td.innerText = value ? money2Thousand(value) : "0.00";
            if(!isNaN(value) && Number(value) < 0){
              td.style.color = "#f00";
              td.innerText = value ? money2Thousand(Math.abs(value)) : "0.00";
            }
          }
          else{
            td.style.textAlign = 'left';
          }
        }
        if(tableType == 2){
          td.innerText = value ? value: '';
          td.style.verticalAlign =  'middle';
          td.style.fontSize =  '10px';
          td.style.fontFamily =  '宋體';
          td.style.height = '20px';
          td.style.overflow = 'hidden';
          td.style.textOoverflow = 'ellipsis';
          td.style.whiteSpace = 'nowrap';
          if(row == 0 ||  row == 1){//標(biāo)題
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '14px';
            td.style.textDecoration =  'underline';
            // td.innerHTML = value;
          }
          else if( row == 3 && col == 5){//單位
            td.style.textAlign = 'right';
          }
          else if(row == 4 || row == 4){//列頭
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '12px';
          } else if( row > 5 && (col == 0 || col == 1 || col == 5)){//日期 借或貸
            td.style.textAlign = 'center';
          }
          else if( (row > 5 && (col == 3 || col == 4 || col == 6))){//金額
            td.style.textAlign = 'right';
            td.innerText = value ? money2Thousand(value) : "0.00";
            if(!isNaN(value) && Number(value) < 0){
              td.style.color = "#f00";
              td.innerText = value ? money2Thousand(Math.abs(value)) : "0.00";
            }
          }
          else{
            td.style.textAlign = 'left';
          }
        }
        if(tableType == 3){
          td.innerText = value ? value: '';
          td.style.verticalAlign =  'middle';
          td.style.fontSize =  '10px';
          td.style.fontFamily =  '宋體';
          td.style.height = '20px';
          td.style.overflow = 'hidden';
          td.style.textOoverflow = 'ellipsis';
          td.style.whiteSpace = 'nowrap';
          if(row == 0 ||  row == 1){//標(biāo)題
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '14px';
            td.style.textDecoration =  'underline';
            td.innerHTML = value;
          }
          else if(row == 4 || row == 5){//列頭
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '12px';
          } else if( row > 5 && col != 1 ){//日期 金額
            td.style.textAlign = 'center';
          }
          else{
            td.style.textAlign = 'left';
          }

        }
        if(tableType == 4){
          td.innerText = value ? value: '';
          td.style.verticalAlign =  'middle';
          td.style.fontSize =  '10px';
          td.style.fontFamily =  '宋體';
          td.style.height = '20px';
          td.style.overflow = 'hidden';
          td.style.textOoverflow = 'ellipsis';
          td.style.whiteSpace = 'nowrap';
          if(row == 0 ||  row == 1){//標(biāo)題
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '14px';
            td.style.textDecoration =  'underline';
            td.innerHTML = value;
          }
          else if(row == 4 || row == 5){//列頭
            td.style.textAlign = 'center';
            td.style.fontWeight = 'bold';
            td.style.fontSize =  '12px';
          }  else if( row > 5 && col != 2 ){//日期 金額
            td.style.textAlign = 'center';
          }
          else{
            td.style.textAlign = 'left';
          }
        }
        return td;
      },
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • Spring Cloud為開發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見模式的工具（例如配置管理，服務(wù)發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,641評(píng)論 19贊 139
  • 她站在懸崖的邊緣 一只野鳥撕咬她微睜的雙眼 血液如注跟她的衣裳一般斑斕 指尖暫停著腐朽難聞的谷底風(fēng) 白色棉質(zhì)的蕾絲...
    七月喵喵喵閱讀 415評(píng)論 0贊 5
  • 年初五，梁婆婆來病房找我。她不知道我有無(wú)上班，只是想來碰碰運(yùn)氣。她覺得我接手處理她傷口后，感受到我換藥處理的專業(yè)細(xì)...
    劉楊明兒閱讀 239評(píng)論 0贊 0
  • 2018年6月5日 星期二 這篇日記的題目，是鄧麗君的一句話，名字叫做《我只在乎你》。喜馬拉雅日FM微博里遇到這句...
    徐宸灝閱讀 152評(píng)論 0贊 1
  • 想趁著現(xiàn)在的美好留下點(diǎn)回憶，終于發(fā)現(xiàn)一塊凈土。不過現(xiàn)在要吃飯，每天抽空記得記一下流水賬吧(?????????)
    摘星星給你閱讀 204評(píng)論 1贊 0

友情鏈接更多精彩內(nèi)容