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
}
