后端很忙没有实现配合,主要由前端来实现,需要点击下载模版生成一个excel并下载的功能,
这是格式
<a href="javascript:void(0)"@click="downloadTemplate">下载模板</a>
import * as XLSX from "xlsx";
const downloadTemplate = async () => {const data = [["编号"],["1650000008818"],["1650000009224"],["1650000009118"],];// excel导出const sheet = XLSX.utils.aoa_to_sheet(data);const book = XLSX.utils.book_new();XLSX.utils.book_append_sheet(book, sheet, "sheet1"); // 生成sheet// 设置行宽const rowNum = data.length - 1;sheet["!cols"] = [];for (let i = 0; i < rowNum; i++) {sheet["!cols"].push({ wpx: 100 });}// 导出样式需要用到xlsx-style,所有没有表格样式XLSX.writeFile(book, `模版.xlsx`); // 导出
};
也可以多列数据,这个暂时用不到
const data = [["编号", "姓名"],["1650000008818", "zs"],["1650000009224", "ls"],["1650000009118", "ww"],
];
另外如果数据是json格式,这里没有用到
const json = [{问题: "1",答案: "2",},
];// 将json数据转换成excel文件
const worksheet = XLSX.utils.json_to_sheet(json);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
<el-upload:http-request="uploadHttpRequest"action="#":limit="1":auto-upload="true"ref="upload":on-success="fileSuccess":on-change="fileChange":data="fileData":on-remove="fileRemove"drag:accept="acceptFile":file-list="fileList"><div class="el-icon-upload"><svgt="1724643479911"class="icon"viewBox="0 0 1024 1024"version="1.1"xmlns="http://www.w3.org/2000/svg"p-id="4294"width="48"height="48"><pathd="M1024 693.248q0 25.6-8.704 48.128t-24.576 40.448-36.864 30.208-45.568 16.384l1.024 1.024-17.408 0-4.096 0-4.096 0-675.84 0q-5.12 1.024-16.384 1.024-39.936 0-74.752-15.36t-60.928-41.472-40.96-60.928-14.848-74.752 14.848-74.752 40.96-60.928 60.928-41.472 74.752-15.36l1.024 0q-1.024-8.192-1.024-15.36l0-16.384q0-72.704 27.648-137.216t75.776-112.128 112.128-75.264 136.704-27.648 137.216 27.648 112.64 75.264 75.776 112.128 27.648 137.216q0 37.888-8.192 74.24t-22.528 69.12q5.12-1.024 10.752-1.536t10.752-0.512q27.648 0 52.736 10.752t43.52 29.696 29.184 44.032 10.752 53.76zM665.6 571.392q20.48 0 26.624-4.608t-8.192-22.016q-14.336-18.432-31.744-48.128t-36.352-60.416-38.4-57.344-37.888-38.912q-18.432-13.312-27.136-14.336t-25.088 12.288q-18.432 15.36-35.84 38.912t-35.328 50.176-35.84 52.224-36.352 45.056q-18.432 18.432-13.312 32.768t25.6 14.336l16.384 0q9.216 0 19.968 0.512t20.992 0.512l17.408 0q14.336 1.024 18.432 9.728t4.096 24.064q0 17.408-0.512 30.72t-0.512 25.6-0.512 25.6-0.512 30.72q0 7.168 1.536 15.36t5.632 15.36 12.288 11.776 21.504 4.608l23.552 0q9.216 0 27.648 1.024 24.576 0 28.16-12.288t3.584-38.912q0-23.552 0.512-42.496t0.512-51.712q0-23.552 4.608-36.352t19.968-12.8q11.264 0 32.256-0.512t32.256-0.512z"p-id="4295"fill="#e6e6e6"></path></svg></div><div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div><div class="el-upload__tip" slot="tip">支持格式为xls、xlsx,文件大小不超过1MB<aclass="ls-upload__tip-link"href="javascript:void(0)"@click="downloadTemplate">下载模板</a></div></el-upload>
getExcelData(val) {const file = val;const reader = new FileReader();reader.onload = (ev) => {const data = ev.target.result;const workbook = XLSX.read(data, { type: "binary" });const sheetName = workbook.SheetNames[0];const sheetData = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName],{ header: 1 });// 过滤掉汉字or nullthis.sheetData = sheetData.map((i) => i[0]).filter((i) => i && !/[\u4e00-\u9fa5]/.test(i));};reader.readAsBinaryString(file);
},
uploadHttpRequest(params) {this.getExcelData(params.file);},
本页全部代码
<template><CommonDialog@open="dialogOpen"ref="dialog":title="title"size="tiny":buttonList="buttonList"@save="save"@cancel="cancel"><el-upload:http-request="uploadHttpRequest"action="#":limit="1":auto-upload="true"ref="upload":on-success="fileSuccess":on-change="fileChange":data="fileData":on-remove="fileRemove"drag:accept="acceptFile":file-list="fileList"><div class="el-icon-upload"><svgt="1724643479911"class="icon"viewBox="0 0 1024 1024"version="1.1"xmlns="http://www.w3.org/2000/svg"p-id="4294"width="48"height="48"><pathd="M1024 693.248q0 25.6-8.704 48.128t-24.576 40.448-36.864 30.208-45.568 16.384l1.024 1.024-17.408 0-4.096 0-4.096 0-675.84 0q-5.12 1.024-16.384 1.024-39.936 0-74.752-15.36t-60.928-41.472-40.96-60.928-14.848-74.752 14.848-74.752 40.96-60.928 60.928-41.472 74.752-15.36l1.024 0q-1.024-8.192-1.024-15.36l0-16.384q0-72.704 27.648-137.216t75.776-112.128 112.128-75.264 136.704-27.648 137.216 27.648 112.64 75.264 75.776 112.128 27.648 137.216q0 37.888-8.192 74.24t-22.528 69.12q5.12-1.024 10.752-1.536t10.752-0.512q27.648 0 52.736 10.752t43.52 29.696 29.184 44.032 10.752 53.76zM665.6 571.392q20.48 0 26.624-4.608t-8.192-22.016q-14.336-18.432-31.744-48.128t-36.352-60.416-38.4-57.344-37.888-38.912q-18.432-13.312-27.136-14.336t-25.088 12.288q-18.432 15.36-35.84 38.912t-35.328 50.176-35.84 52.224-36.352 45.056q-18.432 18.432-13.312 32.768t25.6 14.336l16.384 0q9.216 0 19.968 0.512t20.992 0.512l17.408 0q14.336 1.024 18.432 9.728t4.096 24.064q0 17.408-0.512 30.72t-0.512 25.6-0.512 25.6-0.512 30.72q0 7.168 1.536 15.36t5.632 15.36 12.288 11.776 21.504 4.608l23.552 0q9.216 0 27.648 1.024 24.576 0 28.16-12.288t3.584-38.912q0-23.552 0.512-42.496t0.512-51.712q0-23.552 4.608-36.352t19.968-12.8q11.264 0 32.256-0.512t32.256-0.512z"p-id="4295"fill="#e6e6e6"></path></svg></div><div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div><div class="el-upload__tip" slot="tip">支持格式为xls、xlsx,文件大小不超过1MB<aclass="el-upload__tip-link"href="javascript:void(0)"@click="downloadTemplate">下载模板</a></div></el-upload></CommonDialog>
</template>
<script>
// import { jbneplmsMsDp } from "@/api";
// import axios from "axios";
import * as XLSX from "xlsx";
import { downloadTemplate } from "@/utils/downloadExcel.js";
const params = () => {return {fileList: [],};
};export default {data() {return {title: "",buttonList: [{name: "取消",methods: "cancel",},{name: "上传",methods: "save",},],fileList: [],acceptFile: ".xls,.xlsx",fileData: {},params: params(),sheetData: [],};},methods: {downloadTemplate() {const data = [["发电户编号"],["1650000008818"],["1650000009224"],["1650000009118"],];downloadTemplate(data);},getExcelData(val) {const file = val;const reader = new FileReader();reader.onload = (ev) => {const data = ev.target.result;const workbook = XLSX.read(data, { type: "binary" });const sheetName = workbook.SheetNames[0];const sheetData = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName],{ header: 1 });// 过滤掉汉字or nullthis.sheetData = sheetData.map((i) => i[0]).filter((i) => i && !/[\u4e00-\u9fa5]/.test(i));};reader.readAsBinaryString(file);},uploadHttpRequest(params) {this.getExcelData(params.file);// const that = this;// const formData = new FormData();// formData.append("file", params.file);// for (let key in this.fileData) {// formData.append(key, this.fileData[key]);// }// axios// .post(// "/jbneplms-ms-dp/acqPowerCust/getPowerCustElecListByFile",// formData,// { headers: { "Content-Type": "multipart/form-data" } }// )// .then((res) => {// // console.log(res.data.data, ">>>>>>>>>>");// that.$emit("update", res.data.data);// that.$refs.dialog.handleClose();// });},dialogOpen() {this.fileList = [];},open(data) {this.fileList = [];this.title = data.title || "导入";if (data.obj) {this.fileData = data.obj;}this.$refs.dialog.handleOpen();},cancel() {this.$refs.dialog.handleClose();},save() {if (this.fileList.length == 0) {this.$message.warning("请上传文件");} else {const uniqueArr = this.sheetData.filter((item, index) => this.sheetData.indexOf(item) === index);// this.$refs.upload.submit();const maxL = this.sheetData.length;const minL = uniqueArr.length;console.log(maxL, minL);if (minL < maxL) {this.$message.warning(`有${maxL - minL}条发电户信息重复,已合并信息去除重复发电户`);}if (maxL == 0) {this.$message.warning("导入数据无效,请重新导入");return;}this.$emit("updateFile", uniqueArr);this.cancel();}},fileChange(file, fileList) {this.fileList = fileList;let type = file.name.slice(file.name.lastIndexOf("."));if (this.acceptFile.indexOf(type) == -1) {this.$message.warning(`请上传${this.acceptFile}类型的文件`);this.fileList.pop();} else if (file.size > 1 * 1024 * 1024) {this.$message.warning(`文件大小不超过1MB`);this.fileList.pop();}},fileRemove(file, fileList) {this.fileList = fileList;},fileSuccess(res, file, fileList) {// console.log(res, ">>>>>>>>");this.params.fileList.push(res.data);},},
};
</script>
<style lang="less" scoped>
.top {display: flex;align-items: center;justify-content: space-between;margin-bottom: 13px;.name {white-space: nowrap;}
}
/deep/.ms-upload {width: 100%;
}
/deep/.ms-upload-dragger {width: 100%;
}
.el-icon-upload {margin-top: 60px;
}
.el-upload__text {margin-top: 20px;
}
.el-upload__tip-link {display: block;color: rgb(0, 153, 204);font-size: 14px;
}
</style>