安装xlsx
npm install xlsx
import XLSX from 'xlsx'
上传与预览
页面组件
<el-dialog :title="upload.title" :visible.sync="upload.open" width="400px" append-to-body>
<el-upload ref="upload" accept=".xlsx, .xls" :disabled="upload.isUploading" action="" :http-request="httRequest" :on-progress="handleFileUploadProgress" :auto-upload="false" drag:on-change="handleChange">
<i class="el-icon-upload"></i>
<div class="el-upload__text">
将文件拖到此处,或
<em>点击上传</em>
</div>
<div class="el-upload__tip" style="color:red" slot="tip">提示:仅允许导入“xls”或“xlsx”格式文件!</div>
</el-upload>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitPreview">预 览</el-button>
<el-button @click="upload.open = false">取 消</el-button>
</div>
</el-dialog>
<el-dialog title="数据预览" :visible.sync="isPreview" append-to-body width="80%">
<el-table :data="previewList">
<el-table-column prop="用户ID" label="用户ID" align="center" :show-overflow-tooltip="true"/>
<el-table-column prop="用户名称" label="用户名称" align="center" :show-overflow-tooltip="true"/>
<el-table-column prop="用户性别" label="用户性别" align="center" :show-overflow-tooltip="true"/>
<el-table-column prop="用户账号" label="用户账号" align="center" :show-overflow-tooltip="true"/>
<el-table-column prop="用户地址" label="用户地址" align="center" :show-overflow-tooltip="true"/>
<el-table-column :formatter="dateFormat" prop="出生日期" label="出生日期" align="center"
:show-overflow-tooltip="true"/>
</el-table>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click="submitFileForm">上 传</el-button>
<el-button @click="isPreview = false">取 消</el-button>
</div>
</el-dialog>
js代码
data(){
return {
upload: {
// 是否显示弹出层(用户导入)
open: false,
// 弹出层标题(用户导入)
title: "",
// 是否禁用上传
isUploading: false,
},
// 预览弹窗
isPreview: false,
// 预览数据
previewList: [],
// 上传的文件
formData: '',
}
}
httRequest(param) {
let fileObj = param.file // 相当于input里取得的files
let fd = new FormData()// FormData 对象
fd.append('file', fileObj)// 文件对象
this.importExcel(param.file);
this.formData = fd;
},
// 文件上传替换
handleChange(file, fileList) {
if (fileList.length > 1) {
fileList.splice(0, 1);
}
},
// 文件上传中处理
handleFileUploadProgress(event, file, fileList) {
this.upload.isUploading = true;
},
// 预览文件
submitPreview() {
this.isPreview = true;
this.upload.open = false;
this.upload.isUploading = false;
this.$refs.upload.submit();
},
// 上传文件
submitFileForm() {
manualImport(this.formData).then((response) => { // 调用接口,传入formData
if (response.code === 200) {
this.isPreview = false;
this.$refs.upload.clearFiles();
}
})
},
// 解析文件
importExcel(file) {
const types = file.name.split('.')[1]
const fileType = ['xlsx', 'xlc', 'xlm', 'xls', 'xlt', 'xlw', 'csv'].some(item => item === types)
if (!fileType) {
this.msgError('格式错误!请重新选择');
return
}
this.exportExcel(file).then(tabJson => {
if (tabJson && tabJson.length > 0) {
this.previewList = tabJson[0].sheet
// console.log('数据', this.previewList)
}
})
},
// 生成数据
exportExcel(file) {
return new Promise(function (resolve, reject) {
const reader = new FileReader()
reader.onload = function (e) {
const data = e.target.result
this.wb = XLSX.read(data, {
type: 'binary',
})
const result = []
this.wb.SheetNames.forEach((sheetName) => {
result.push({
sheetName: sheetName,
sheet: XLSX.utils.sheet_to_json(this.wb.Sheets[sheetName])
})
})
resolve(result)
}
reader.readAsBinaryString(file)
})
},
// 日期格式转换
dateFormat(row, column) {
let date = row[column.property];
if (date === undefined) {
return '';
}
return XLSX.SSF.format('YYYY-MM-DD', date); // 解决渲染出的日期少一天问题,需要用SSF
},
下载
页面组件
<el-button type="primary" icon="el-icon-download" size="mini" @click="handleExportExcel">导出</el-button>
js代码
接口配置 需要加上 responseType:'blob'
handleExportExcel() {
exportExcel() // 调用接口
.then((response) => {
let date = new Date()
let blob = new Blob([response], {type: "application/vnd.ms-excel;charset=utf-8"}); // response就是接口返回的文件流了,数据返回格式需和后端商议
let objectUrl = URL.createObjectURL(blob);
const elink = document.createElement("a");
elink.download = '用户信息表'; //下载文件名称,
elink.style.display = "none";
elink.href = objectUrl;
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href); // 释放URL 对象
document.body.removeChild(elink);
})
},