一,文件导出
1. 下包
npm install / yarn add xlsx@0.14.1 file-saver script-loader --save //安装三个包
2. 导出文件
<el-button type="danger" @click="handleDownload">导出文件</el-button>
<el-button type="success"
@click="$router.push('/loadFile')" //跳转到上传文件页面
>导入文件</el-button>
// 导出员工excel表格
async handleDownload() {
//发请求获取数据
const res = await getEmployeeList({ page: 1, size: this.total })
// this.downloadLoading = true
//vendor/Export2Excel 导入的 js文件
import('@/vendor/Export2Excel').then(excel => {
// const tHeader = ['Id', 'Title', 'Author', 'Readings', 'Date']
// const filterVal = ['id', 'title', 'author', 'pageviews', 'display_time']
const list = res.data.rows
const data = this.formatJson(list)
excel.export_json_to_excel({
header: this.tHeader, // 表头 报错 wch (表头字段不够)
data, // 显示表格数据
filename: this.filename, // 设置导出的文件名
autoWidth: true, // 自动匹配表表格宽度
bookType: 'xlsx' // 设置文件导出的类型,文件后缀
})
// this.downloadLoading = false
})
},
formatJson(jsonData) {
// console.log(jsonData, 4444)
// 处理数据,把英文转换为中文 ---- 返回一个二维数组
const map = {
'姓名': 'username',
'手机号': 'mobile',
'身份证':'xxxxxx'
}
this.tHeader = Object.values(map) // 返回对象键值的一个数组
// jsonData === 表格数据
return jsonData.map(item => {
var newObj = {}
var enkey = Object.keys(map) // 返回对象里面键名的一个数组
enkey.forEach(key => {
newObj[map[key]] = item[key]
})
return Object.values(newObj)
})
},
二, 导入文件
utils/index.js
封装工具函数
// 把excel文件中的日期格式的内容转回成标准时间
// https://blog.csdn.net/qq_15054679/article/details/107712966
export function formatExcelDate(numb, format = '/') {
const time = new Date((numb - 25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000)
time.setYear(time.getFullYear())
const year = time.getFullYear() + ''
const month = time.getMonth() + 1 + ''
const date = time.getDate() + ''
if (format && format.length === 1) {
return year + format + month + format + date
}
return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
}
-
注册公共组件上传文件功能
components/uploadExcel/index.vue
<template> <div> <input ref="excel-upload-input" class="excel-upload-input" type="file" accept=".xlsx, .xls" @change="handleClick" > <div class="drop" @drop="handleDrop" @dragover="handleDragover" @dragenter="handleDragover" > 将文件拖入此处或 <el-button :loading="loading" style="margin-left: 16px" size="mini" type="primary" @click="handleUpload" > 点击上传 </el-button> </div> </div> </template> <script> import XLSX from 'xlsx' export default { name: 'UploadExcel', props: { beforeUpload: Function, // eslint-disable-line onSuccess: Function // eslint-disable-line }, data() { return { loading: false, excelData: { // header 表头 results 表格内容 ==== 都是数组 header: null, results: null } } }, methods: { generateData({ header, results }) { // header 表头 results 表格内容 console.log(header, results) this.excelData.header = header this.excelData.results = results this.onSuccess && this.onSuccess(this.excelData) // &&找假 左边为 true 执行右边代码 // if(this.onSuccess){ // this.onSuccess(this.excelData) // } }, handleDrop(e) { e.stopPropagation() e.preventDefault() if (this.loading) return const files = e.dataTransfer.files if (files.length !== 1) { this.$message.error('Only support uploading one file!') return } const rawFile = files[0] // only use files[0] if (!this.isExcel(rawFile)) { this.$message.error( 'Only supports upload .xlsx, .xls, .csv suffix files' ) return false } this.upload(rawFile) e.stopPropagation() e.preventDefault() }, handleDragover(e) { e.stopPropagation() e.preventDefault() e.dataTransfer.dropEffect = 'copy' }, handleUpload() { this.$refs['excel-upload-input'].click() }, handleClick(e) { const files = e.target.files const rawFile = files[0] // only use files[0] if (!rawFile) return this.upload(rawFile) }, upload(rawFile) { this.$refs['excel-upload-input'].value = null // fix can't select the same excel if (!this.beforeUpload) { this.readerData(rawFile) return } const before = this.beforeUpload(rawFile) if (before) { this.readerData(rawFile) } }, readerData(rawFile) { this.loading = true return new Promise((resolve, reject) => { const reader = new FileReader() reader.onload = (e) => { const data = e.target.result const workbook = XLSX.read(data, { type: 'array' }) const firstSheetName = workbook.SheetNames[0] const worksheet = workbook.Sheets[firstSheetName] const header = this.getHeaderRow(worksheet) const results = XLSX.utils.sheet_to_json(worksheet) this.generateData({ header, results }) this.loading = false resolve() } reader.readAsArrayBuffer(rawFile) }) }, getHeaderRow(sheet) { const headers = [] const range = XLSX.utils.decode_range(sheet['!ref']) let C const R = range.s.r /* start in the first row */ for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */ const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */ let hdr = 'UNKNOWN ' + C // <-- replace with your desired default if (cell && cell.t) hdr = XLSX.utils.format_cell(cell) headers.push(hdr) } return headers }, isExcel(file) { return /.(xlsx|xls|csv)$/.test(file.name) } } } </script> <style scoped> .excel-upload-input { display: none; z-index: -9999; } .drop { border: 2px dashed #bbb; width: 600px; height: 160px; line-height: 160px; margin: 0 auto; font-size: 24px; border-radius: 5px; text-align: center; color: #bbb; position: relative; } </style>
3. 引入组件,处理数据
<template>
<div>
//引入公共组件
<uploadExcel :on-success="handleSuccess" />
</div>
</template>
<script>
import { importEmployee } from '@/api/employees'
import { formatExcelDate } from '@/utils'
// import dayjs from 'dayjs'
export default {
name: '',
data() {
return {
}
},
methods: {
// 封装处理数据函数
transData(results) {
const mapInfo = {
'姓名': 'username',
'手机号': 'mobile',
'身份证':'xxxxxx'
}
var newArr = results.map(item => {
var newObj = {}
// 使用Object.keys()方法单独拿出对象中的键装进一个数组中去,在遍历数组拿到每个中文的键名
var zhKeys = Object.keys(item)
zhKeys.forEach(Key => {
if (mapInfo[Key] === 'timeOfEntry' || mapInfo[Key] === 'correctionTime') {
// 日期格式转换标准时间
newObj[mapInfo[Key]] = new Date(formatExcelDate(item[Key]))
} else {
newObj[mapInfo[Key]] = item[Key]
}
})
return newObj
})
return newArr
},
// 把数据从excel文件读入到浏览器内存
async handleSuccess({ header, results }) {
var newResults = this.transData(results)
const res = await importEmployee(newResults)
console.log(res)
}
}
}
</script>