需要读取的文件格式
1.安装插件
npm install xlsx2.引入
<script>
import * as XLSX from 'xlsx'
</script>3.上传
<input type="file" @change="handleFileUpload" name="" ref="fjset">上传事件读取
handleFileUpload(event) {
const files = event.target.files || event.dataTransfer.files;
this.files = [];
for (let i = 0; i < files.length; i++) {
const imgType = files[i].type === "application/vnd.ms-excel";
if (!imgType) {
this.$message.error("请选择excel文件");
return false;
}
const isLt10M = files[i].size / 1024 / 1024 < 10;
if (!isLt10M) {
this.$message.error("文件大小不能大于10M!");
return false;
}
}
const file = event.target.files[0];
const than=this
than.tableData=[]//读取所有的数据数组
than.tableDatadq=[]//分页后的数组
than.sousuo.page=1;
than.sousuo.pageSize=5
// 用readAsBinaryString或readAsArrayBuffer读取文件内容（根据xlsx版本可能需要不同方式）
const reader = new FileReader();
reader.onload = (e) => {
const data = e.target.result;
console.log(XLSX)
let workbook;
if (typeof data === 'string') { // 对于旧版xlsx，可能直接是二进制字符串
workbook = XLSX.read(data, {type: 'binary'});
} else { // 对于新版xlsx，通常为ArrayBuffer
workbook = XLSX.read(new Uint8Array(data), {type: 'array'});
}
// 获取第一个工作表
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
// 模板应有的列名和顺序校验模板
const requiredColumnsOrder = ['姓名 *','身份证号 *','银行卡账号 *','考勤天数 *', '应发工资 *', '扣发工资 *',"班组名称","备注"];
const headerRow = XLSX.utils.sheet_to_json(worksheet, { header: 1 })[0]; // 获取第一行作为标题行
// 检查列名是否存在并且顺序正确
if (requiredColumnsOrder.some((col, index) => headerRow[index] !== col)) {
this.$message.error('模板文件的结构不正确，请检查列名或列顺序！');
return;
}
// 将工作表转换为JSON对象数组
let excelData = XLSX.utils.sheet_to_json(worksheet);
// 现在excelData是一个包含表格数据的数组，你可以在此处进一步处理数据，并绑定到Vue实例的数据属性上
const fieldMap = {
'姓名 *': 'aac003',
'应发工资 *': 'bac313',
'扣发工资 *': 'bac314',
'班组名称': 'bac502',
'考勤天数 *': 'bac303',
'身份证号 *': 'aac002',
'银行卡账号 *': 'aae010',
'备注':'aae013'
};
excelData = excelData.map(row => {
return Object.keys(row).reduce((newRow, key) => {
if (fieldMap[key]) {
newRow[fieldMap[key]] = row[key];
} else {
newRow[key] = row[key]; // 如果没有对应英文名称，保留原字段名
}
return newRow;
}, {});
});
than.tableData = excelData;
console.log(than.tableData)
//分页方法
than.getTabelData()
};
this.$refs['fjset'].value = '';
reader.readAsArrayBuffer(file); // 读取为ArrayBuffer，适用于大多数情况
},
4.页面显示 可以对导入的数据进行修改和删除 分页
<el-table :data="tableDatadq" border height="100%" style="width: 100%">
<el-table-column
prop="rk"
label="序号"
align="center"
width="50"
>
<template slot-scope="scope">
{{ scope.$index + 1 + (sousuo.page - 1) * sousuo.pageSize }}
</template>
</el-table-column>
<el-table-column
prop="aac003"
align="center"
label="姓名"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="30"
v-model="xgObj.aac003"
/>
<span v-else>{{ scope.row.aac003 }}</span>
</template>
</el-table-column>
<el-table-column
prop="aac002"
align="center"
label="身份证号码"
width="170px;"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="18"
v-model="xgObj.aac002"
/>
<span v-else>{{ scope.row.aac002 }}</span>
</template>
</el-table-column>
<el-table-column
prop="aae010"
align="center"
label="银行卡号"
show-overflow-tooltip
width="210px;"
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="24"
@change="identificationCardNumber(xgObj.aae010)"
v-model="xgObj.aae010"
@input="xgObj.aae010=xgObj.aae010.replace(/[^0-9 ]/g,'').replace(/[\s]/g, '').replace(/(\d{4})(?=\d)/g,'$1 ')"
/>
<span v-else>{{ scope.row.aae010 }}</span>
</template>
</el-table-column>
<el-table-column
prop="bac303"
align="center"
label="考勤天数"
width="100px"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="4"
v-model="xgObj.bac303"
@input="xgObj.bac303=xgObj.bac303.replace(/[^0-9\.]/g,'')"
/>
<span v-else>{{ scope.row.bac303 }}</span>
</template>
</el-table-column>
<el-table-column
prop="bac313"
align="center"
label="应发工资"
width="120px"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="11"
v-model="xgObj.bac313"
@input="xgObj.bac313=xgObj.bac313.replace(/[^0-9\.]/g,'')"
/>
<span v-else>{{ scope.row.bac313 }}</span>
</template>
</el-table-column>
<el-table-column
prop="bac314"
align="center"
width="120px"
label="扣发工资"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="11"
v-model="xgObj.bac314"
@input="xgObj.bac314=xgObj.bac314.replace(/[^0-9\.]/g,'')"
/>
<span v-else>{{ scope.row.bac314 }}</span>
</template>
</el-table-column>
<el-table-column
prop="bac502"
align="center"
label="班组名称"
width="120px;"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="20"
v-model="xgObj.bac502"
/>
<span v-else>{{ scope.row.bac502 }}</span>
</template>
</el-table-column>
<el-table-column
prop="aae013"
align="center"
label="备注"
show-overflow-tooltip
>
<template slot-scope="scope">
<el-input v-despace
class="inputWidth100"
v-if="xgShow && xgIndex == scope.$index"
type="text"
maxlength="150"
v-model="xgObj.aae013"
/>
<span v-else>{{ scope.row.aae013 }}</span>
</template>
</el-table-column>
<el-table-column
fixed="right"
align="center"
label="操作"
width="100"
>
<template slot-scope="scope">
<div v-if="xgShow && xgIndex == scope.$index">
<el-button @click="editBc31(scope.row,scope.$index)" type="text" v-throttle>保存</el-button>
<el-button type="text" @click="xgClick">取消</el-button>
</div>
<el-button v-else-if="scope.row.bac312 != 1" type="text" @click="xgClick(scope.row,scope.$index)" v-throttle
>修改</el-button
>
<el-button type="text" @click="deleBc31(scope.$index)" style="color: red;" v-throttle
>删除</el-button
>
<el-button type="text" style="opacity:0;">1</el-button >
</template>
</el-table-column>
</el-table>
<el-pagination
background
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="sousuo.page"
:page-sizes="[10, 20, 50, 100]"
:page-size="sousuo.pageSize"
layout="total, sizes, prev, pager, next, jumper"
:total="count"
>
</el-pagination><script>
export default {
return{
data() {
xgObj: {
// table中修改时显示的对象
aac002:"",
aac003:"",
aae010: "",
baa101: "",
bac303: "",
bac313: "",
bac314: "",
bac502: "",
aae013: "",
bac311: "",
},
xgShow: false, // 显示隐藏table修改input
xmxxShow: false, // 显示隐藏项目信息
sousuo: {
// 表单搜索
page: 1,
pageSize: 10,
},
count: 0, // 列表总数
tableData: [], // 表单列表
tableTS: "暂无数据", // 列表数据查询提示
tableDatadq:[]// 读取xls表格
}
}
methods: {
xgClick(row,index) {
// 修改显示table input输入框
if(row){
this.xgObj = {
aae010:row.aae010,
aac002: row.aac002,
aac003: row.aac003,
baa101: row.baa101,
bac303: row.bac303,
bac313: row.bac313,
bac314: row.bac314,
aae013: row.aae013,
oae001: row.oae001,
bac502: row.bac502,
bac311: row.bac311,
}
}
if(String(index)){
this.xgIndex = String(index);
}
this.xgShow = !this.xgShow;
},
deleBc31(index){
// 删除列表数据
if(String(index)){
this.tableDatadq.splice(index,1)
const id=index + (this.sousuo.page - 1) * this.sousuo.pageSize
this.tableData.splice(id,1)
this.count=this.tableData.length
this.getTabelData()
}
console.log(this.tableDatadq)
},
editBc31(row,index){
// 修改工资明细列表数据
if(Number(this.xgObj.bac313) <= Number(this.xgObj.bac314)){
this.$message({
type: "info",
message: '应发工资必须大于扣发工资',
});
return;
}
const aae010 = this.xgObj.aae010.replace(/[\s]/g, '');
if(aae010.length < 16){
this.$message({
type: "info",
message: "请输入工资卡号16-20位"
});
return;
}
if(Number(this.xgObj.bac303) != 0.5 && Number(this.xgObj.bac303) < 1){
this.$message({
type: "info",
message: "请输入正确的考勤天数"
});
return;
}
this.xgObj.aae010=this.xgObj.aae010.replace(/[\s]/g, '');
for(const key in this.xgObj){
this.tableDatadq[index][key] = this.xgObj[key];
}
this.xgShow = false;
},
handleSizeChange(val) {
// 切换每页显示数量
this.sousuo.pageSize = val;
this.sousuo.page = 1;
this.getTabelData();
},
handleCurrentChange(val) {
// 切换分页
this.sousuo.page = val;
this.getTabelData();
},
getTabelData(){
//分页
this.count=this.tableData.length
// this.tableDatadq=this.tableData.slice((this.sousuo.page-1)*this.sousuo.pageSize,this.sousuo.page*this.sousuo.pageSize);
this.$set(this,'tableDatadq',this.tableData.slice((this.sousuo.page-1)*this.sousuo.pageSize,this.sousuo.page*this.sousuo.pageSize))
console.log(this.tableDatadq)
},
}
}
</script>