1.后端文件流导出
//导出
Eexcel(val){
let url = val.filePath;
this.$confirm({
title: '提示',
content: '确定导出吗?',
okText: '确定',
cancelText: '取消',
onOk() {
request('/api/jmis-riskassess/alarmrealtime/fileDownload', METHOD.GET, {filePath:url}, {
headers: {
'Content-Type': 'application/x-www-form-urlencoded', //请求的数据类型为form data格式
},
responseType: 'blob',
}).then((res) => {
console.log('res',res)
var blob = new Blob([res.data], { type: 'application/vnd.ms-excel;charset=utf-8;' })
var contentDisposition = res.headers['content-disposition']
var patt = new RegExp('filename=([^;]+\\.[^\\.;]+);*')
var result = patt.exec(contentDisposition)
var filename = result[1]?result[1]:'----'
var downloadElement = document.createElement('a')
var href = window.URL.createObjectURL(blob) // 创建下载的链接
var reg = /^["](.*)["]$/g
downloadElement.style.display = 'none'
downloadElement.href = href
downloadElement.download = decodeURI(filename.replace(reg, '$1')) // 下载后文件名
document.body.appendChild(downloadElement)
downloadElement.click() // 点击下载
document.body.removeChild(downloadElement) // 下载完成移除元素
window.URL.revokeObjectURL(href)
})
},
})
},
2.前端导出
下载插件:
npm install --save xlsx@0.17.0
npm install --save file-saver@2.0.5
npm i xlsx-style-hzx
使用方法
1.import excelMixin from '@/mixins/excelMixin' // 导出Excel
2. mixins: [excelMixin], 混入
3. 点击导出事件
downLoadExcel(){
this.visibleChildrenloading = true
this.exportExcel(this.titleHead.type,'alarmTable') //导出文件名,导出html的ref
},
revertPage() {
this.visibleChildrenloading = false
},
4.mixins/excelMixin文件内容
import FileSaver from 'file-saver'
import XLSX from 'xlsx'
import XLSXStyle from "xlsx-style-hzx";
export default {
data() {
return {
showbutton: true,
showExcel: false,
allData: false,
oldPageNo: 1,
oldPageSize: 10, // 每页的数据
name: '',
exportId: null
}
},
filters:{
// keepTwoNum:function(value){
// value = Number(value)
// return value.toFixed(2)
// }
},
methods: {
// 保留两位小数
keepTwoNum(value){
value = Number(value)
return value.toFixed(2)
},
getAllData(name, exportId) {
if (exportId === 3 && this.total > 1000) {
this.$message.error('导出数据过大,请更换导出方式')
return
}
this.name = name
this.exportId = exportId
this.loading = true
this.allData = true
this.show = false
this.oldPageNo = this.pageNo
this.oldPageSize = this.pageSize
this.showExcel = true
this.pageNo = 1
this.pageSize = 9999999
this.getData()
},
getSummaries(param) {
const { columns, data } = param
const sums = []
columns.forEach((column, index) => {
if (index === 0) {
sums[index] = '当页合计'
return
}
const values = data.map(item => Number(item[column.property]))
if (!values.every(value => isNaN(value))) {
sums[index] = values.reduce((prev, curr) => {
const value = Number(curr)
if (!isNaN(value)) {
return prev + curr
} else {
return prev
}
}, 0)
sums[index] += ''
} else {
sums[index] = '--'
}
})
return sums
},
exportExcel(excelName,refName) {
this.loading = true
this.showbutton = false
if (this.showbutton === false) {
this.threeGo(excelName,refName)
}
},
threeGo(excelName,refName) {
var that = this
const TIME_COUNT = 3
if (!this.timer) {
this.count = TIME_COUNT
this.show = false
this.timer = setInterval(() => {
if (this.count > 0 && this.count <= TIME_COUNT) {
this.count--
} else {
this.show = true
clearInterval(this.timer)
this.timer = null
// 导出excel的方法
this.revertPage()
try {
const $e = this.$refs[refName].$el
let $table = $e.querySelector('.ant-table-content')
if (!$table) {
$table = $e
}
const wb = XLSX.utils.table_to_book($table, { raw: true })
console.log(wb)
this.setExlStyle(wb['Sheets']['Sheet1']); // 设置列宽 字号等 如果无需多余的样式则省略
if(wb['Sheets']['Sheet1']['!merges']){
this.addRangeBorder(wb['Sheets']['Sheet1']['!merges'],wb['Sheets']['Sheet1']) //设置合并行的border
}
let wbout = XLSXStyle.write(wb, { type: 'buffer'})
// const wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'array' })
FileSaver.saveAs(
new Blob([wbout], { type: 'application/octet-stream' }),
`${excelName}.xlsx`
)
} catch (e) {
if (typeof console !== 'undefined') console.error(e)
}
}
}, 1000)
}
},
setExlStyle(data) {
let borderAll = { //单元格外侧框线
top: {
style: 'thin',
},
bottom: {
style: 'thin'
},
left: {
style: 'thin'
},
right: {
style: 'thin'
}
};
data['!cols'] = [];
for (let key in data) {
// console.log(key)
if (data[key] instanceof Object) {
data[key].s = {
border: borderAll,
alignment: {
horizontal: 'center', //水平居中对齐
vertical:'center'
},
font:{
sz:11
},
bold:true,
numFmt: 0
}
data['!cols'].push({wpx: 115});
}
}
return data;
},
addRangeBorder (range, ws) {
let cols = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
range.forEach(item => {
console.log(item)
let style = {
s: {
border: {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
}
}
}
// 处理合并行
for (let i = item.s.c; i <= item.e.c; i++) {
ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style
// 处理合并列
for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
}
}
})
return ws;
},
}
}