VUE 解析EXCEL 或将数据导出成EXCEL

code_std

已于 2023-02-09 13:58:30 修改

阅读量1.4k

点赞数 1

文章标签： vue.js javascript 前端

于 2022-08-12 11:08:19 首次发布

本文链接：https://blog.csdn.net/qq_26330543/article/details/126300051

版权

解析excel

首先展示excel数据

在这里插入图片描述

js FileReader 代码

//这里使用 Promise 是为了后续使用时，等待文件读取完成
export function readFile(file){
	return new Promise(resolve=>{
		//创建声明一个 FileReader
		let reader = new FileReader();
		//将文件 file 以二进制的方式读取出来
		reader.readAsBinaryString(file);
		//文件读取完成后将结果返回
		reader.onload = (ev)=>{
			resolve(ev.target.result);
		};
	});
}

前端代码（vue3）

<template>
  <div class="common-layout">
    <el-container>
      <el-header>访客数据整理</el-header>
      <el-main>
				 <el-form :model="data_form" label-width="120px">
				    <el-form-item label="类型" >
				      <el-input v-model="data_form.data_type" />
							<el-button type="primary" @click="onSearch()">搜索</el-button>
							
							//文件上传
							<el-upload v-model:file-list="fileList" class="upload-demo" action accept = ".xlsx,.xls" :auto-upload = "false" :show-file-list = "false" :on-change="handleExceed">
							  <el-button type="primary">选择文件</el-button>
							</el-upload>
							
				    </el-form-item>
				  </el-form>
					
					<el-table :data="tableData.data" style="width: 100%" border>
					  <el-table-column prop="ID" label="ID" width="50"/>
						<el-table-column prop="creator_name" label="创建人姓名" />
						<el-table-column prop="create_time" label="创建时间" />
						<el-table-column prop="dept" label="车主部门" />
						<el-table-column label="是否内部员工">
							<template #default="scope">
								<div style="display: flex; align-items: center">
									<span style="margin-left: 10px" v-if="scope.row.is_employee == 1">是</span>
									<span style="margin-left: 10px" v-if="scope.row.is_employee == 2">否</span>
								</div>   
							</template>
						</el-table-column>	
						<el-table-column prop="org_code" label="部门ID" />
					</el-table>
			</el-main>
    </el-container>
  </div>
</template>

<script>
import {readFile} from '../utils/xlsx.js'
import * as XLSX from 'xlsx'
import rq from '../utils/axios.js'
import {ref,reactive} from 'vue' //引入响应式api
export default {
  name: 'HomeView',
	setup(){
		const data_form = reactive({
			data_type:'运三车牌',
		})
		
		const tableData = reactive({
			data:[]
		});
		
		const onSearch = ()=>{
			let sql_data = {
				type:data_form.data_type
			}
			
			rq.requests.post('/search',sql_data).then(res=>{
				tableData.data = res.data;
				console.log(tableData)
			}).catch((err)=>{
			  console.log(err)
			})
		};
		//文件解析
		const handleExceed = async (e)=>{
			let file = e.raw;
			
			if(!file){ return; }
			//读取文件 file，并在读取完后将值赋值给 data
			let data = await readFile(file);
			//利用XLSX的方法，将数据转换成可读数据
			let workbook = XLSX.read(data,{type:'binary'});
			//获取excel表格第一个Sheet页签的数据
			let worksheet = workbook.Sheets[workbook.SheetNames[0]];
			//将数据转换成接送对象
			data = XLSX.utils.sheet_to_json(worksheet);
			console.log(data)//打印出来的数据如下图
			
			//将数据利用post传递至后台，插入进库
			rq.requests.post('/insert',data).then(res=>{
				console.log(res)
			}).catch((err)=>{
			  console.log(err)
			})
		}
		
		return {
			data_form,
			tableData,
			handleExceed,
			onSearch
		};
	}
}
</script>

<style scoped>
@import '../assets/css/base.css';
	.el-header{
		background-color: antiquewhite;
		height: 5vh;
		margin-bottom: 4px;
		line-height: 5vh;
	}
	.el-main{
		background-color: aliceblue;
		height: 92vh;
	}
	.el-input{
		width: 10vw;
	}
	.el-button{
		margin-left: 10px;
	}
	.input-file{
		margin-left: 10px;
		background-color: aqua;
	}
	.upload-demo{
		padding: 0;
		height: 32px;
	}
</style>

打印的数据

在这里插入图片描述

将数据导出成excel

首先展示对象数据和导出的excel

对象数据
在这里插入图片描述
excel

前端代码

<template>
  <div class="c_home">
    <el-container>
        <el-main>
            <el-form :model="data_form" label-width="120px">
                <el-button id="id_bu" type="primary" @click="onSelect()">查询数据</el-button>
				<el-button id="id_da" type="primary" @click="exportDate()">导出数据</el-button>
            </el-form>
        
        	<el-table :data="tableData.data" style="width: 100%" border v-loading="loading">
        		<el-table-column prop="PM_NO" label="PM号" width="60" />
        		<el-table-column prop="line_no" label="行号" width="100" />
        		<el-table-column prop="REPAIR_MCH" label="标准工作标识" min-width="100" />
        		<el-table-column prop="REPAIR_DESC" label="标准工作描述" min-width="200" />
        		<el-table-column prop="PM_VERSION" label="PM版本" width="150" />
        		<el-table-column prop="repair_version" label="标准工作版本" width="150" />
        		<el-table-column prop="OBJ_MT" label="标准工作域" width="150" />
        	</el-table>
        </el-main>
    </el-container>
  </div>
</template>

<script>
// @ is an alias to /src
import {readFile} from '../utils/xlsx.js'
import * as XLSX from 'xlsx'
import {ref,reactive} from 'vue';
import rq from '../utils/axios.js';
export default {
  name: "c_home",
  setup() {
	const data_form = reactive({
		data_type:'PM数据匹配',
	});
	
  	const tableData = reactive({
  		data:[]
  	});
	
	let onSelect = ()=>{
		rq.requests.post('/select',tableData.data).then(res=>{
			console.log(res.data)
			tableData.data = res.data;
		}).catch((err)=>{
		  console.log(err)
		}) 
	}
	
	 let exportDate = async ()=>{
		let dataList = [['姓名','性别','年龄']];//创建表头
		await tableData.data.forEach(t=>{//tableData.data 数据格式 [{PM_NO:'1',line_no:'1',OBJ_MT:'MT'},{PM_NO:'2',line_no:'1',OBJ_MT:'MT'}]
			dataList.push([t.PM_NO,t.line_no,t.OBJ_MT]);//将数据放入到 dataList 中
		});//dataList 数据格式[['姓名', '性别', '年龄'],['1', 1, 'MT'],['2', 1, 'MT']]
		let workSheet = '';//创建一个excel的页签
		await (workSheet = XLSX.utils.aoa_to_sheet(dataList));//将 dataList 数据添加到excel页签中
		let workBook = XLSX.utils.book_new();//创建一个xlsx工作表
		await XLSX.utils.book_append_sheet(workBook,workSheet,'子表');//将excel页签中 workSheet 添加到xlsx工作表 workBook 中，并将页签取名为 ‘子表’
		await XLSX.writeFile(workBook,'导出数据.xlsx');	//写入文件，并且下载excel
	};
	
	return {
		data_form,
		tableData,
		onSelect,
		exportDate
	};
  }
};
</script>
<style scoped>
	.el-header{
		background-color: antiquewhite;
		height: 5vh;
		margin-bottom: 4px;
		line-height: 5vh;
	}
	.el-form{
		position: relative;
		height: 3vh;
	}
	.upload-demo{
		display: inline !important;
		position: absolute;
		left: 1px;
		bottom: 5px;
	}
	#id_bu{
		position: absolute;
		left: 1px;
		bottom: 5px;
	}
	.el-form-item{
		height: 1vh+1px;
	}
	.el-main{
		background-color: aliceblue;
		height: 93vh;
	}
	.el-input{
		width: 10vw;
	}
	.el-button{
		margin-left: 10px;
	}
	.input-file{
		margin-left: 10px;
		background-color: aqua;
	}
	.upload-demo{
		padding: 0;
		height: 32px;
	}
</style>