vue页面中,通过接口获取json返回值,并导出到Excel中;
注意事项:
- 1、json中的key翻译成对应标题;
- 2、过滤掉json中不需要的字段;
1、接口返回的json:
{
"errcode": 0,
"data": {
"total": 3,
"items": [
{
"hospital": "医院1",
"department": "心内科",
"num": 10
},
{
"hospital": "医院2",
"department": "心内科",
"num": 4
},
{
"hospital": "医院3",
"department": "体检",
"num": 3
}
]
},
"errmsg": "成功"
}
2、安装 xlsx 库:
npm install xlsx
3、定义导出方法:
export function exportToExcel(jsonData, fileName) {
const worksheet = XLSX.utils.json_to_sheet(jsonData)
const workbook = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
const excelBuffer = XLSX.write(workbook, {
bookType: 'xlsx',
type: 'array',
})
const data = new Blob([excelBuffer], { type: 'application/octet-stream' })
if (window.navigator && window.navigator.msSaveOrOpenBlob) {
// For IE
window.navigator.msSaveOrOpenBlob(data, fileName)
} else {
// For other browsers
const url = window.URL.createObjectURL(data)
const link = document.createElement('a')
link.href = url
link.download = fileName
link.click()
window.URL.revokeObjectURL(url)
}
}
4、处理JSON返回值,映射成对应的标题:
写法一:按照JSON返回的 字段内容 和 字段顺序导出,只是将key映射成对应标题:
<template>
<div>
<button @click="handleExportXls">导出Excel</button>
</div>
</template>
<script>
import * as XLSX from 'xlsx'
export default {
data() {
return {
titleMapping: { // key映射成对应的title
hospital: '医院',
department: '科室',
num: '数量',
},
excludedKeys: ['num'], // 需要过滤的key
};
},
methods: {
handleExportXls() {
console.log('导出到Excel')
try {
const res= await axios.get('YOUR_API_URL');
if (res.errcode == 0) {
console.log(res.data.items)
var chartList = res.data.items
console.log('数据长度:' + chartList.length)
const transformedData = res.data.items.map((item) => {
const transformedItem = {}
for (const key in item) {
this.excludedKeys.forEach((key) => delete item[key]) //过滤掉不需要的key
if (key in this.titleMapping) {
//key映射成对应的标题
transformedItem[this.titleMapping[key]] = item[key]
}
}
return transformedItem
})
exportToExcel(
transformedData,
this.titleStr + '报表(' + this.defaultStart + ' ~ ' + this.defaultEnd + ').xlsx'
)
}
} catch (error) {
console.error(error);
}
},
}
};
</script>
写法二:从JSON中选取需要的字段,导出到Excel,自己控制顺序:
handleExportXls() {
console.log('导出到Excel')
const res= await axios.get('YOUR_API_URL');
if (res.errcode == 0) {
const transformedData = res.data.items.map((item) => {
return {
'姓名': item.province,
'年龄': item.salesman,
'邮箱': item.dealerName
};
})
exportToExcel(
transformedData,
this.titleStr + '报表(' + this.defaultStart + ' ~ ' + this.defaultEnd + ').xlsx'
)
}
}
},