将table数据导出到excel
方式1:需要发送请求获取blob文件流数据导出
前端代码:
导出函数
/**
* 导出文件工具
* @param blob 文件
* @param fileName 导出文件名
*/
export function exportFile(blob: Blob, fileName: string) {
if (window.navigator && (window.navigator as any).msSaveOrOpenBlob) {
(navigator as any).msSaveBlob(blob, fileName)
}
else {
const downloadElement = document.createElement('a')
const href = window.URL.createObjectURL(blob) // 创建下载的链接
downloadElement.href = href
downloadElement.download = fileName
document.body.appendChild(downloadElement)
downloadElement.click() // 点击下载
document.body.removeChild(downloadElement) // 下载完成移除元素
window.URL.revokeObjectURL(href) // 释放blob对象
}
}
// 使用
const exportExcelBtn = () => {
const loading = ElLoading.service({
lock: true,
text: 'Loading',
background: 'rgba(255, 255, 255, 0.8)',
})
exportFileApi(参数).then((res) => {
//导出函数
exportFile(res.data, props.name)
loading.close()
}).catch((_) => {
loading.close()
})
}
请求函数
// 导出
export function exportFileApi(data: object) {
return request({
url: 'file/listExport',
method: 'post',
data,
responseType: 'blob',
})
}
方式2:前端依靠插件导出到excel
需要用到 xlsx
首先需要 下载插件
npm install xlsx
导出函数
import * as XLSX from 'xlsx'
/*
* @description:
* @param {Object} json 服务端发过来的数据
* @param {String} name 导出Excel文件名字
* @param {String} titleArr 导出Excel表头
* @param {String} sheetName 导出sheetName名字
* @return:
*/
export interface excelType {
json: object
name: string
titleArr: string[]
sheetName: string
}
export function exportExcel(params: excelType) {
/* convert state to workbook */
const data = []
const keyArray = []
const getLength = function (obj: object) {
let count = 0
for (const i in obj) {
if (Object.prototype.hasOwnProperty.call(obj, i)) {
// if (obj.hasOwnProperty(i)) {
count++
}
}
return count
}
for (const key1 in params.json) {
if (Object.prototype.hasOwnProperty.call(params.json, key1)) {
const element = (params.json as { [key: string]: object })[key1]
const rowDataArray = []
for (const key2 in element) {
if (Object.prototype.hasOwnProperty.call(element, key2)) {
const element2 = (element as { [key: string]: object })[key2]
rowDataArray.push(element2)
if (keyArray.length < getLength(element)) {
keyArray.push(key2)
}
console.log(keyArray, 'keyArray')
}
}
data.push(rowDataArray)
}
}
// keyArray为英文字段表头
data.splice(0, 0, keyArray as any, params.titleArr as any)
console.log('data', data)
const ws = XLSX.utils.aoa_to_sheet(data)
const wb = XLSX.utils.book_new()
// 此处隐藏英文字段表头
const wsrows = [{ hidden: true }]
/* 设置worksheet每列的最大宽度 */
const colWidth = data.map(row => row.map((val) => {
/* 先判断是否为null/undefined */
if (val == null) {
return {
wch: 10,
}
}
/* 再判断是否为中文 */
else if (val.toString().charCodeAt(0) > 255) {
return {
wch: val.toString().length * 2,
}
}
else {
return {
wch: val.toString().length,
}
}
}))
/* 以第一行为初始值 */
const result = colWidth[0]
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j].wch < colWidth[i][j].wch) {
result[j].wch = colWidth[i][j].wch
}
}
}
ws['!cols'] = result
ws['!rows'] = wsrows // ws - worksheet
XLSX.utils.book_append_sheet(wb, ws, params.sheetName)
/* generate file and send to client */
XLSX.writeFile(wb, `${params.name}.xlsx`)
}
使用函数导出
exportExcel({
json: [], // 数组数据
name: 表格, // 导出名字
titleArr: ['序号', '名称', '编号', '文件号'],//中文表头
sheetName: 'sheet1',
})