<template>
<div class="content">
<div class="content-box">
<el-form
ref="scaechDom"
:rules="rules"
:model="scaechDom"
label-suffix=":"
label-width="100px"
class="demo-ruleForm"
>
<el-row>
<el-col :span="5">
<el-form-item prop="querytype" label="选择类型">
<el-select v-model="scaechDom.querytype" clearable placeholder="请选择类型..." @change="queryTypeChange">
<el-option
v-for="item in queryTypeList"
:key="item.value"
:label="item.label"
:value="item.value"
>
</el-option>
</el-select>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="5">
<el-form-item prop="yearData" label="选择年">
<el-date-picker
v-model="scaechDom.yearData"
format="yyyy"
value-format="yyyy"
type="year"
placeholder="选择年..."
:picker-options="startYear"
>
</el-date-picker>
</el-form-item>
</el-col>
</el-row>
<el-row>
<el-col :span="5">
<el-form-item prop="monthData" label="选择月">
<el-date-picker
v-model="scaechDom.monthData"
format="yyyy-MM"
value-format="MM"
type="month"
placeholder="选择月..."
:picker-options="startmonth"
>
</el-date-picker>
</el-form-item>
</el-col>
</el-row>
</el-form>
<div class="btn">
<el-button
type="primary"
:disabled="querytypeDisabled"
:loading="querytypeLoading"
@click="excelToFile"
>测试excel文件流转换为file
</el-button>
</div>
</div>
</div>
</template>
<script>
const Excel=require('exceljs')
export default {
data(){
return {
queryTypeList:[
{label:'科目余额',value:'subjectRemain'},
{label:'债权债务',value:'claimOwing'}
],
rules:{
querytype:[
{required:true,message:'请选择类型',trigger:'blur'}
],
yearData:[
{required:true,message:'请选择年时间',trigger:'blur'}
],
monthData:[
{required:true,message:'请选择单月时间',trigger:'blur'}
]
},
scaechDom:{
querytype:'',
monthData:'',
yearData:''
},
startmonth:{
disabledDate:time=>{
let year=new Date().getFullYear()
let month= new Date().getMonth()
let days=new Date(year,month,1).getDate()
const timeDifference=days*86400776
return time.getTime()>Date.now()-timeDifference
}
},
startYear:{
disabledDate:time=>{
let year=new Date().getFullYear()
return(
time.getTime()>new Date(parseInt(year)-parseInt(0)+'-12-31').getTime()
)
}
},
exportExcelList:[],
excelName:'',
querytypeDisabled:false,
querytypeLoading:false
}
},
methods:{
queryTypeChange(val){
console.log(val)
this.exportExcelList=[]
if(val=='subjectRemain'){
this.exportExcelList=[
{'name1':1,'name2':'现金','name3':'借款','name4':100,'name5':'借款','name6':50},
{'name1':2,'name2':'现金','name3':'借款','name4':1000,'name5':'借款','name6':1150},
{'name1':3,'name2':'现金','name3':'借款','name4':1500,'name5':'借款','name6':150},
{'name1':4,'name2':'现金','name3':'借款','name4':1900,'name5':'借款','name6':750},
]
}else if(val=='claimOwing'){
this.exportExcelList=[
{'name1':'个贷金额','name2':100,'name3':'欠款金额','name4':6600},
{'name1':'个贷金额','name2':200,'name3':'欠款金额','name4':8900},
{'name1':'个贷金额','name2':800,'name3':'欠款金额','name4':9000},
{'name1':'个贷金额','name2':1200,'name3':'欠款金额','name4':2300},
]
}
this.queryTypeList.forEach((item)=>{
if(item.value==val){
this.excelName=item.label+'.xlsx'
}
})
},
// 生成excel配置方法
exportExcel(){
const workbook=new Excel.Workbook()
workbook.creator='Admin'
workbook.lastModifiedBy='Admin'
workbook.created=new Date()
workbook.modified=new Date()
workbook.lastPrinted=new Date()
const worksheet=workbook.addWorksheet('My Sheet')
let data=[]
if(this.scaechDom.querytype=='subjectRemain'){
worksheet.columns=[
{header:'序号',key:'name1',width:10},
{header:'资产及支出',key:'name2',width:20},
{header:'期初余额',key:'name3',width:20},
{header:'',key:'name4',width:20},
{header:'期末余额',key:'name5',width:20},
{header:'',key:'name6',width:20}
]
data=this.exportExcelList.map((item,index)=>{
let arr=[
item.name1,
item.name2,
item.name3,
item.name4,
item.name5,
item.name6
];
return arr
})
worksheet.mergeCells('C1:D1') // 合并单元格
worksheet.mergeCells('E1:F1')
worksheet.getRow(1).font={ // 表格第一行,也就是表头设置样式为字体加粗
bold:true
}
worksheet.getColumn(1).alignment={ // 表格第一列数据文字居中,可以修改属性vertical:top middle bottom ;horizontal:left center right 来修改样式
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(2).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(3).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(4).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(5).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(6).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getRow(1).alignment={
vertical:'middle',
horizontal:'center'
}
}else if(this.scaechDom.querytype=='claimOwing'){
worksheet.columns=[
{header:'',key:'name1',width:20},
{header:'',key:'name2',width:20},
{header:'',key:'name3',width:20},
{header:'',key:'name4',width:20}
]
data=this.exportExcelList.map((item,index)=>{
let arr=[
item.name1,
item.name2,
item.name3,
item.name4
]
return arr
})
worksheet.mergeCells('A1:B1')
worksheet.mergeCells('C1:D1')
worksheet.getCell('B1').value='债权'
worksheet.getCell('D1').value='债务'
worksheet.getCell('A2').value='科目名称'
worksheet.getCell('B2').value='本期余额'
worksheet.getCell('C2').value='科目名称'
worksheet.getCell('D2').value='本期余额'
// 上面的操作是合并单元格,并设置多级表头,详情可以下载表格查看具体样式
worksheet.getRow(1).font={
bold:true
}
worksheet.getRow(2).font={
bold:true
}
worksheet.getColumn(1).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(2).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(3).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getColumn(4).alignment={
vertical:'middle',
horizontal:'center'
}
worksheet.getRow(1).alignment={
vertical:'middle',
horizontal:'center'
}
}
worksheet.addRows(data)
workbook.xlsx.writeBuffer().then((buffer)=>{
this.writeFile(`${this.excelName}`,buffer)
})
this.querytypeLoading=false
},
// 表格下载
writeFile(fileName,content){
let a=document.createElement('a');
let blob=new Blob([content],{type:'text/plain'});
console.log(blob)
a.download=fileName;
a.href=URL.createObjectURL(blob);
a.click();
a.remove();
this.blobTofile(blob,fileName).then((res)=>{ // 通过调用转换方法将文件流blob格式转换为file格式
let file=res
let param=new FormData()
param.append('file',file)
param.append('isPermission','N')
console.log(param) // 获取到excel转换为file格式的数据,然后调用upload附件上传接口将文件上传就OK了
this.$axios.uploadFile(param).then((res)=>{ //这里的接口根据实际的上传附件接口来写,这里面返回的res就是附件的Id,fileName这些参数
console.log(res)
})
})
},
// 文件流转换为file形式方法
blobTofile(newBlob,fileName){
return new Promise((resolve)=>{
resolve(new File([newBlob],fileName,{type:'xls/xlsx'}))
})
},
// excel转换为file点击事件
excelToFile(){
this.$refs['scaechDom'].validate(valid=>{
if(valid){
this.querytypeLoading=true
this.querytypeDisabled=true
setTimeout(()=>{
this.querytypeDisabled=false
},3000)
this.exportExcel()
}
})
}
}
}
</script>