vue+element -UI前端Excel 文件导入数据

完成的效果图如下，会进行数据筛选，根据你上传的物业名称跟数据库的名称对应

在这里我用到了一个插件，需要在在main.js中全局引入：

import { export_excel_to_json, export_json_to_excel } from '@/api/operExcel'

下载地址：https://download.csdn.net/download/qq_36597079/10634033

不多说，上源码

1.页面样式

<div class="filter-container">
					<el-form :inline="true">
						<el-form-item>
							<el-button @click="handleExport" size="medium">导出上传模板</el-button>
						</el-form-item>
						<el-form-item>
							<a class="el-button el-button--medium">选择要上传的文件
								<input id="fileSelect" type="file" @change="handleUpload" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" class="filecss" />
							</a>
						</el-form-item>
						<el-form-item>
							<el-button @click="handleExportResult">导出校验结果</el-button>
						</el-form-item>
						<el-form-item>
							<el-button type="primary" @click="handleData" size="medium">确认上传</el-button>
						</el-form-item>
					</el-form>
				</div>
				<div>
					<el-table :data="uploadData" stripe show-summary :summary-method="getSummaries" height="475" style="width: 100%" v-loading="loading" :element-loading-text="loadingtxt">
						<el-table-column type="index" label="序号" width="70">
						</el-table-column>
						<el-table-column prop="basic_name" label="物业名称" width="220">
						</el-table-column>
						<!--<el-table-column prop="basic_position" label="位置" width="180">
						</el-table-column>-->
						<el-table-column prop="fees_month" label="月份" width="180">
						</el-table-column>
						<el-table-column prop="pretax" label="税前" width="180">
						</el-table-column>
						<el-table-column prop="Aftertax" label="税后" width="180">
						</el-table-column>
						<el-table-column prop="result" label="检验结果" sortable>
							<template slot-scope="scope">
								<span style="color:red;" v-show="!scope.row.state">{{ scope.row.result }}</span>
								<span v-show="scope.row.state">{{ scope.row.result }}</span>
							</template>
						</el-table-column>
						<el-table-column prop="uploadResult" label="上传结果" width="120" sortable>
							<template slot-scope="scope">
								<span style="color:red;" v-show="!scope.row.uploadState">{{ scope.row.uploadResult }}</span>
								<span v-show="scope.row.uploadState">{{ scope.row.uploadResult }}</span>
							</template>
						</el-table-column>
					</el-table>
				</div>

2，记得加一下方法（核心方法）

import { export_excel_to_json, export_json_to_excel } from '@/api/operExcel'

3.js方法

handleExport() { //导出上传模板
				const tHeader = ['物业名称', '月份', '税前', '税后'];
				const data = [];
				export_json_to_excel(tHeader, data, '用电信息上传模板');
			},

handleExportResult() { //导出校验上传结果文件
				const tHeader = ['物业名称', '月份', '税前', '税后', '校验结果', '上传结果'];
				var Enumerable = require('linq');
				const data = Enumerable.from(this.uploadData)
					.select(function(x) {
						return new Array(x.basic_name, x.fees_month, x.pretax, x.Aftertax, x.result, x.uploadResult)
					})
					.toArray();
				export_json_to_excel(tHeader, data, '用电信息上传数据校验结果');
			},

handleData() { //数据上传（上传导数据库了）
				if(this.result.rightCount > 0) {
					var Enumerable = require('linq');
					var uploadData = Enumerable.from(this.uploadData)
						.where(function(x) {
							return x.state == true
						})
						.select(function(x) {
							return JSON.parse('{"basic_Id":"' + x.basic_Id + '","fees_month":"' +  x.fees_month  + '","pretax":"' + x.pretax + '","Aftertax":' + x.Aftertax + '}')
						})
						.toArray();
					var uploadsize = 500; //每次上传最多记录数
					var times = Math.ceil(uploadData.length / uploadsize); //上传次数
					var execcount = 0;
					for(var i = 0; i < times; i++) {
						var patchData = Enumerable.from(uploadData)
							.skip(i * uploadsize).take(uploadsize)
							.toArray();
						let params = patchData;
						console.log(JSON.stringify(uploadData));
						var dbres = [];
						operData('property_electr_fees', params, 'add').then((res) => {
							dbres = dbres.concat(res.data);
							execcount += 1;
							if(execcount == times) {
								this.uploadResult(dbres);
							}
						}).catch(error => {
							console.log(error);
							execcount += 1;
							if(execcount == times) {
								this.uploadResult(dbres);
							}
						});
					}
				} else {
					this.$notify({
						title: '提示',
						message: '没有符合上传条件的数据！',
						type: 'warning'
					});
				}
			},

//记录上传返回结果
			uploadResult(dbres) {
				var mydata = this.uploadData;
				dbres.forEach(function(dbitem) {
					mydata.forEach(function(item) {
						if(dbitem.basic_name && item.basic_name == dbitem.basic_name) {
							item.uploadResult = '上传成功！';
							item.uploadState = true;
						}
					});
				});
				this.$notify({
					title: '信息',
					message: '上传数据完成，结果请参考数据校验表格的上传结果栏！',
					type: 'success'
				});
			},

getSummaries(param) { //底部验证显示
				const sums = [];
				if(this.uploadData) {
					sums[1] = '校验结果：';
					var Enumerable = require('linq');
					//记录数
					this.result.rightCount = Enumerable.from(this.uploadData).where(function($) {
						return $.state == true
					}).count();
					sums[2] = '符合要求记录数：' + this.result.rightCount;
					this.result.errCount = Enumerable.from(this.uploadData).where(function($) {
						return $.state == false
					}).count();
					sums[3] = '不符合记录数：' + this.result.errCount;
				}

				return sums;
			},

handleUpload(evt) { //选择上传文件并校验
				export_excel_to_json(evt).then((res) => {
					//var jsondata=res;
					const filterName = ['物业名称', '月份', '税前', '税后'];
					const filterValue = ['basic_name', 'fees_month', 'pretax', 'Aftertax'];
					const filterElse = ['"result":""', '"state":true', '"basic_Id":""', '"uploadResult":"未上传"', '"uploadState":false']

					var mydata = getFormatData(res, filterName, filterValue, filterElse);
					let orgpar = {
//						filter: {
//							where: 'id='+basic_Id,
//						}
					};
					operData('property_basics', orgpar, 'getdata').then((res) => {
						var basicsdata = res.data;
					
						var Enumerable = require('linq');
						mydata.forEach(function(item) {
							
							if(item.basic_name == null) {
							
								item.result += '物业名称不允许为空！';
								item.state = false;
							}
							if(item.fees_month == null) {
								
								item.result += '月份不允许为空！';
								item.state = false;
							}
							if(item.basic_name) {
								item.basic_Id = Enumerable.from(basicsdata)
									.where(function(x) {
										return x.basic_name == item.basic_name
									})
									.select(function(x) {
										return x.id
									})
									.toJoinedString();
									
								if(!item.basic_Id) {
									item.result += '物业名称不存在！';
									item.state = false;
								}
							} else {
								item.basic_Id = item.basic_Id
							}
						});
						
//						
						var arrEmail = Enumerable.from(mydata).where(function(x) {
							return x.state == true && x.basic_name != null
						}).select(function(x) {
							return x.basic_name
						}).toArray();
						
						var arrPhone = Enumerable.from(mydata).where(function(x) {
							return x.state == true && x.fees_month != null
						}).select(function(x) {
							return x.fees_month
						}).toArray();
						
						var chksize = 500; //每次最多记录数
						var times = Math.max(Math.ceil((arrEmail.length) / chksize), Math.ceil((arrPhone.length) / chksize)); //次数
						var execcount = 0;
						for(var i = 0; i < times; i++) {
							var patchEmail = Enumerable.from(arrEmail)
								.skip(i * chksize).take(chksize)
								.toArray();
							var patchPhone = Enumerable.from(arrPhone)
								.skip(i * chksize).take(chksize)
								.toArray();
							let params = {};
							if(patchEmail.length > 0 && patchPhone.length > 0) {
								params = {
									filter: {
										"where": {
											"or": [{
												"basic_name": {
													"inq": patchEmail
												}
											}, {
												"fees_month": {
													"inq": patchPhone
												}
											}]
										},
										"fields": ["basic_name", "fees_month"]
									}
								};
							} else if(patchEmail.length > 0) {
								params = {
									filter: {
										"where": {
											"basic_name": {
												"inq": patchEmail
											}
										},
										"fields": ["basic_name"]
									}
								};
							} else if(patchPhone.length > 0) {
								params = {
									filter: {
										"where": {
											"fees_month": {
												"inq": personPhone
											}
										},
										"fields": ["fees_month"]
									}
								};
							}
							console.log("这回"+JSON.stringify(params));

							var dbres = [];
							operData('v_property_elect_fees', params,"getdata").then((res) => {
								dbres = dbres.concat(res.data);
								console.log("这回是+"+JSON.stringify(dbres));
								execcount += 1;
								if(execcount == times) {
									//检查手机和email是否已存在于数据库中
									dbres.forEach(function(dbitem) {
										mydata.forEach(function(item) {
											if(dbitem.basic_name && item.basic_name == dbitem.basic_name&&dbitem.fees_month && item.fees_month == dbitem.fees_month) {
												item.result += '系统中已存在该物业的月份！';
												item.state = false;
											}
											
										});
									});
								}
							});
						}
						
						this.uploadData = mydata;
						console.log(JSON.stringify(this.uploadData));
						
					});
				});
			}

以上基本上就是全代码了

几个重要点：由于是JS，所以我的数据传递基本上是JSON格式