项目框架及依赖
此项目是基于vue2 + elementUI下开发的,此次使用到的插件有xlsx插件库
xlsx插件安装命令npm install xlsx
此模块实现的功能
前端导入Excel并预览导入的结果及其导入失败的信息功能组件
封装组件的代码
代码如下
<template>
<el-dialog
title="导入人员补贴"
width="70%"
:visible.sync="visible"
@close="closeHander"
:close-on-click-modal="false"
>
<div class="button-box">
<el-button
type="primary"
icon="el-icon-folder-add"
size="mini"
@click="uploadingtHander"
style="margin-right: 15px"
>
打开文件
</el-button>
<input
type="file"
id="uploadExcel"
ref="uploadExcel"
v-show="false"
accept=".xls, .xlsx, .excel"
@change="readExcel"
/>
<el-button
type="warning"
icon="el-icon-edit"
size="mini"
@click="importHander"
:loading="importLoading"
>
开始导入
</el-button>
<el-button
type="info"
icon="el-icon-download"
size="mini"
@click="exportHander"
>
导出失败信息
</el-button>
</div>
<el-table
:data="showData"
height="380px"
border
:stripe="true"
:header-cell-style="{ background: '#eef1f6', color: '#606266' }"
>
<template v-for="(item, index) in column">
<el-table-column
:prop="item.property"
:label="item.label"
:key="index"
v-if="item.property == 'excelReturnResult'"
>
<template slot-scope="scope">
<span
:style="{
color: scope.row.excelReturnResult != 'success' ? 'red' : '',
}"
>{{ scope.row.excelReturnResult }}</span
>
</template>
</el-table-column>
<el-table-column
:prop="item.property"
:label="item.label"
:key="index"
v-else
>
</el-table-column>
</template>
</el-table>
<div class="botton-box">
<!-- 导入统计结果 -->
<div style="color: red">
共{{ allData.length }}
<span v-if="allData.every((item) => item.excelReturnResult)">
,成功{{
allData.filter((item) => item.excelReturnResult == "success").length
}}, 失败{{
allData.filter((item) => item.excelReturnResult !== "success").length
}}
</span>
</div>
<!-- 分页器 -->
<div class="block">
<!-- :page-sizes="[20]" -->
<el-pagination
background
:current-page.sync="currentPage"
:page-sizes="[20]"
:page-size="currentSize"
layout=" jumper, prev, pager,next, total,sizes"
:total="dataCount"
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
/>
</div>
</div>
<div slot="footer" class="dialog-footer">
<el-button type="default" @click="closeHander">关闭</el-button>
</div>
</el-dialog>
</template>
<script>
import { uploadExcel } from "@/api/attendanceManagement";
//excel文件数据解析
import * as XLSX from "xlsx";
// 按照二进制读取文件 excel文件的处理
const readFile = (file) => {
return new Promise((resolve) => {
const reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = (e) => {
resolve(e.target.result);
};
});
};
export default {
data() {
// currentSize: 20,
return {
visible: false,
currentPage: 1,
currentSize: 20,
dataCount: 0,
// 当前展示数据
showData: [],
// 原始数据
allData: [],
column: [
{
property: "In_FPersonNo",
label: "人员编号",
},
{
property: "In_FPersonName",
label: "姓名",
},
{
property: "In_FSubsidyImportTypeNo",
label: "导入方式",
},
{
property: "In_FSubsidyMonth",
label: "补贴月份",
},
{
property: "In_FSubsidyMoney",
label: "补贴金额",
},
// {
// property: "In_FSubsidyUsable",
// label: "发放方式",
// },
{
property: "excelReturnResult",
label: "导入结果",
},
],
tableConfig: {
In_FPersonNo: "人员编号",
In_FPersonName: "姓名",
In_FSubsidyImportTypeNo: "导入方式",
In_FSubsidyMonth: "补贴月份",
In_FSubsidyMoney: "补贴金额",
// In_FSubsidyUsable: "发放方式"
},
// 开始导入加载
importLoading: false,
// 导入的文件名
excelName: '',
// 上传接收的文件流
excelFile: null,
};
},
methods: {
init() {
this.currentPage = 1;
this.currentSize = 20;
this.dataCount = 0;
this.visible = true;
},
closeHander() {
this.showData = [];
this.allData = [];
// 清空上传文件事件的值
this.$refs.uploadExcel.value = null;
this.excelName = ''
this.excelFile = null
this.visible = false;
},
handleSizeChange(val) {
this.currentSize = val;
this.showData = this.allData
? this.allData.slice(
(this.currentPage - 1) * this.currentSize,
(this.currentPage - 1) * this.currentSize + this.currentSize
)
: [];
},
handleCurrentChange(val) {
// 当前页
this.currentPage = val;
this.showData = this.allData
? this.allData.slice(
(this.currentPage - 1) * this.currentSize,
(this.currentPage - 1) * this.currentSize + this.currentSize
)
: [];
},
// 上传文件
uploadingtHander() {
this.$refs.uploadExcel.click(); // 浏览文件
},
readExcel(event) {
if (event.target.files.length != 0) {
let fileList = event.target.files;
this.excelName = fileList[0].name;
this.excelFile = this.$refs.uploadExcel.files[0];
console.log("this.excelFile =>", this.excelFile);
this.gmDrSure();
}
},
//读取Excel中的数据
// 导入时excel中必须填写的有:人员编号、姓名、导入方式、补贴月份、补贴金额
async gmDrSure() {
//读取file中的数据
//把文件解析成二进制数据,把二级制数据变成excel表格式的数据
let data = await readFile(this.excelFile);
let workbook = XLSX.read(data, { type: "binary" });
//拿到第一个sheet表的数据,把第一个表格的数据转换成JSON数据
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
data = XLSX.utils.sheet_to_json(worksheet);
console.log("data222", data);
// return
const arr = [];
data.forEach((item) => {
let obj = {};
for (let key in this.tableConfig) {
// 此处keyof是为了解决直接使用key索引会报错问题
let v = this.tableConfig[key];
v = item[v] || "";
obj[key] = v;
}
arr.push(obj);
});
console.log("表格数据", arr);
this.allData = arr;
this.dataCount = this.allData ? this.allData.length : 0;
// 分页计算
this.showData = this.allData.length
? this.allData.slice(
(this.currentPage - 1) * this.currentSize,
(this.currentPage - 1) * this.currentSize + this.currentSize
)
: [];
},
// 开始导入
importHander() {
let formData = new FormData();
formData.append("file", this.excelFile);
// console.log("formData", formData);
if (this.excelName === "") {
this.$message({
type: "warning",
message: "请选择excel文件!",
duration: 2 * 1000,
});
return;
}
// 组装请求数据
let jsonStr = `{"apiName":"/ykt/COST_Subsidy_UpdateImport",
"excelColums":"In_FPersonNo,In_FPersonName,In_FSubsidyImportTypeNo,In_FSubsidyMonth,In_FSubsidyMoney",
"excelColumsName":"人员编号,姓名,导入方式,补贴月份,补贴金额"}`;
formData.append("jsonStr", jsonStr);
// 此导入如果工号已经在人员编号中存在,则会进行更新人员的信息
this.importLoading = true;
uploadExcel(formData)
.then((res) => {
if (res.code == 200) {
this.allData = res.data;
this.dataCount = this.allData ? this.allData.length : 0;
this.showData = this.allData
? this.allData.slice(
(this.currentPage - 1) * this.currentSize,
(this.currentPage - 1) * this.currentSize + this.currentSize
)
: [];
this.$message.success("导入成功!");
} else {
this.$message.warning("导入失败:" + res.msg);
}
})
.finally(() => {
this.importLoading = false;
// 清空上传文件事件的值
this.$refs.uploadExcel.value = null;
});
},
// 导出执行开始导入时未成功的信息
exportHander() {
if(!this.allData.every(item => item.excelReturnResult )) {
this.$message.warning('数据未操作开始导入,请先操作开始导入!')
return
}
if(this.allData.every(item => item.excelReturnResult == 'success' )) {
this.$message.warning('导入时无失败的数据!')
return
}
const tableData = this.transData(this.column, this.allData.filter(item => item.excelReturnResult != 'success'));
// 转换成excel时,使用原始的格式,这样导出的时候数字过长不会变成科学计数法
var xlsxParam = { raw: true };
// 将一组JS数据数组转换为工作表
const ws = XLSX.utils.aoa_to_sheet(tableData, xlsxParam); // 使用二维数组创建一个工作表对象
// 创建workbook 工作簿对象
const wb = XLSX.utils.book_new();
// XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
XLSX.utils.book_append_sheet(wb, ws, '补贴导入失败结果数据');
// 将workbook写入文件
XLSX.writeFile(wb, '补贴导入失败结果数据' + ".xlsx");
},
// 转换表头组装数据
transData(columns, tableList) {
const obj = columns.reduce((acc, cur) => {
if (!acc.titles && !acc.keys) {
acc.titles = [];
acc.keys = [];
}
acc.titles.push(cur.label);
acc.keys.push(cur.property);
return acc;
}, {});
const tableBody = tableList.map((item) => {
return obj.keys.map((key) => item[key]);
});
return [obj.titles, ...tableBody];
},
},
};
</script>
<style lang="scss" scoped>
// 分页器
.block {
margin-top: 20px;
// margin-bottom: 20px;
overflow: hidden;
.el-pagination {
float: right;
margin-right: 20px;
// margin-bottom: 20px;
}
}
.dialog-footer {
display: flex;
justify-content: center;
}
.button-box {
margin-bottom: 20px;
}
.botton-box {
display: flex;
justify-content: space-between;
align-items: center;
}
</style>
效果图
刚打开组件时的初始界面
功能使用时的操作指示
导入成功后的预览结果