当格式如下图的时候,设置格式的时候是正常导出
当如下图的时候
格式就没有填充完毕
<template>
<div>
<el-button size="small" type="primary" @click="daoChu">导出</el-button>
<el-table
:data="tableData"
id="testOne"
border
style="width: 100%">
<el-table-column label="232">
<el-table-column
width="180">
</el-table-column>
<el-table-column
label="SF2"
width="180">
</el-table-column>
<el-table-column
prop="username"
label="姓名">
</el-table-column>
<el-table-column
prop="password"
label="姓名">
</el-table-column>
</el-table-column>
</el-table>
<el-table
:data="tableDataOne"
id="testTwo"
border
style="width: 100%">
<el-table-column
prop="SF"
label="SF"
width="180">
</el-table-column>
<el-table-column
prop="username"
label="姓名">
</el-table-column>
<el-table-column
prop="password"
label="姓名">
</el-table-column>
</el-table>
</div>
</template>
<script>
import FileSaver from 'file-saver'
import XLSX from 'xlsx'
import XLSX2 from 'xlsx-style'
export default {
data() {
return {
tableData: [],
tableDataOne:[],
one1:'',
sdate:'',
};
},
methods: {
daoChu() {
let xlsxParam={
raw:true
};//转换成excel时,使用原始的格式,这样导出的时候数字过程不会变成科学计数法
let workbook=XLSX.utils.book_new();
let ws1=XLSX.utils.table_to_sheet(document.querySelector('#testOne'),xlsxParam);
//这里可以添加多个sheet页面
XLSX.utils.book_append_sheet(workbook,ws1,'table1');
let ws2=XLSX.utils.table_to_sheet(document.querySelector('#testTwo'),xlsxParam);
XLSX.utils.book_append_sheet(workbook,ws2,'table2');
//合并单元格
var merges=['A1:B1']
//将要合并的的那远哥放进工作表中
if(merges.length>0){
if(!ws1['!merges']) ws1['!merges']=[]
merges.forEach(item=>{
ws1['!merges'].push(XLSX.utils.decode_range(item))
})
}
//合并结束
//表格宽度
if (!ws1['!cols']) ws1['!cols'] = []
for (let i = 0; i <= 3; i++) {
if (i === 1) {
ws1['!cols'][i] = { wpx: 120 }
} else if (i === 2) {
ws1['!cols'][i] = { wpx: 240 }
} else {
ws1['!cols'][i] = { wpx: 20 }
}
}
//表格宽度结束
let borderAll = { // 单元格外侧框线
top: {
style: 'thick',
},
bottom: {
style: 'thick',
},
left: {
style: 'thick',
},
right: {
style: 'thick',
}
}
let borderAll2 = { // 单元格外侧框线
top: {
style: 'thick',
},
bottom: {
style: 'thick',
},
left: {
style: 'thick',
},
right: {
style: 'thick',
},
diagonalDown: true, // 斜线方向
diagonal: { // 斜线样式
color: { rgb: '000000' },
style: 'thin'
}
}
let borderAll1 = { // 单元格外侧框线
top: {
style: 'thin',
},
bottom: {
style: 'thin',
},
left: {
style: 'thin',
},
right: {
style: 'thin',
}
}
//设置单元格格式
for(const key in ws1){
if (['A1'].includes(key)) {
ws1[key].s = {
border: borderAll2,
};
}
if (['B1', 'C1', 'D1'].includes(key)) {
ws1[key].s = {
border: borderAll,
font: {
sz: 20,
bold: true,
color: {
rgb: '1C1C1C', //白色
},
},
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true,
},
};
}
if(['C2','C3','C4','C5','D2','D3','D4','D5'].includes(key)){
ws1[key].s = {
border: borderAll1,
font: {
sz: 12,
bold: true,
color: {
rgb: '1C1C1C', //白色
},
},
alignment: {
horizontal: 'right',
vertical: 'right',
wrapText: true,
},
};
}
}
const s2ab =((s) => {
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;
})
// 表格宽度
let wbout=XLSX2.write(workbook,{
bookType:'xlsx',
bookSST:false,
type:'binary'
});
try {
FileSaver.saveAs(
new Blob([s2ab(wbout)],{
type:'application/octet-steam'
}),'多sheet演示.xlsx');
}catch (e){
if(typeof console !== 'undefined') console.log(e,wbout)
}
return wbout;
},
}
};
</script>