简单前端页面搭建效果：
前端一个vue页面代码，注意用的是elementui组件
<template>
<div id="app">
<el-container>
<el-header>
<el-menu :default-active="activeIndex" class="el-menu-demo el-header" mode="horizontal" @select="handleSelect">
<el-menu-item index="1">处理中心</el-menu-item>
<el-submenu index="2">
<template slot="title">我的工作台</template>
<el-menu-item index="2-1">选项1</el-menu-item>
<el-menu-item index="2-2">选项2</el-menu-item>
<el-menu-item index="2-3">选项3</el-menu-item>
<el-submenu index="2-4">
<template slot="title">选项4</template>
<el-menu-item index="2-4-1">选项1</el-menu-item>
<el-menu-item index="2-4-2">选项2</el-menu-item>
<el-menu-item index="2-4-3">选项3</el-menu-item>
</el-submenu>
</el-submenu>
<el-menu-item index="3" disabled>消息中心</el-menu-item>
<el-menu-item index="4"><a href="https://www.ele.me" target="_blank">订单管理</a></el-menu-item>
</el-menu>
</el-header>
<el-container>
<el-aside width="200px">
<el-row class="tac">
<el-col>
<el-menu
default-active="2"
class="el-menu-vertical-demo"
@open="handleOpen"
@close="handleClose">
<el-submenu index="1">
<template slot="title">
<i class="el-icon-location"></i>
<span>信息管理</span>
</template>
<el-menu-item-group>
<template slot="title">分组一</template>
<el-menu-item index="1-1">选项1</el-menu-item>
<el-menu-item index="1-2">选项2</el-menu-item>
</el-menu-item-group>
<el-menu-item-group title="分组2">
<el-menu-item index="1-3">选项3</el-menu-item>
</el-menu-item-group>
<el-submenu index="1-4">
<template slot="title">选项4</template>
<el-menu-item index="1-4-1">选项1</el-menu-item>
</el-submenu>
</el-submenu>
<el-menu-item index="2">
<i class="el-icon-menu"></i>
<span slot="title">人才引进</span>
</el-menu-item>
<el-menu-item index="3" disabled>
<i class="el-icon-document"></i>
<span slot="title">年度评选</span>
</el-menu-item>
<el-menu-item index="4">
<i class="el-icon-setting"></i>
<span slot="title">升职加薪</span>
</el-menu-item>
</el-menu>
</el-col>
</el-row>
</el-aside>
<el-main>
<div>
<el-card class="box-card">
<div slot="header" class="clearfix">
<span>易立德公司学生个人信息的管理</span>
<el-button style="float: right; padding: 3px 0" type="text">操作按钮</el-button>
</div>
<el-upload
class="upload-file"
drag
action="http://localhost:8002/user/upload">
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
</el-upload>
<!-- <el-upload
class="upload-demo"
ref="upload"
accept="xls"
action="localhost:8002/user/upload"
:http-request="httpRequest"
:file-list="fileList"
:on-change="handleFileChange"
:auto-upload="false">
<el-button slot="trigger" size="small" type="success">选取文件</el-button>
<el-button style="margin-left: 10px;" size="small" type="success" @click="handleSubmit">上传到服务器</el-button>
<div slot="tip" class="el-upload__tip" style="color: #ee4234">
{{ tips }}
</div>
</el-upload> -->
<el-row>
<a href="http://localhost:8002/user/export"><el-button plain>导出</el-button></a>
<el-button type="primary" plain>导出</el-button>
<el-button type="success" plain>导出</el-button>
<el-button type="info" plain>模板下载</el-button>
<el-button type="warning" plain>模板下载</el-button>
<el-button type="danger" plain>模板下载</el-button>
</el-row>
</el-card>
</div>
</el-main>
</el-container>
</el-container>
</div>
</template>
<script>
export default {
name:"upload",
data() {
return {
tips: "只能上传xls格式的文件",
fileList: []
};
},
methods: {
init() {
this.fileList = [];
this.tips = "只能上传dat格式的文件";
},
httpRequest(param) {
console.log("有来到这里吗？")
console.log(param)
let fileObj = param.file; // 相当于input里取得的files
let fileName = fileObj.name;
let formData = new FormData(); // FormData 对象
console.log(fileObj)
console.log(fileName+"1111111111111")
formData.append("MultipartFile", fileObj); // 文件对象
formData.append("fileCreateName", fileName);
console.log(formData)
this.$emit("upload", formData);
},
handleFileChange(file, fileList) {
this.beforeUpload(file);
if (fileList.length > 0) {
this.fileList = [fileList[fileList.length - 1]]; // 这一步，是 展示最后一次选择的dat文件
}
},
beforeUpload(file) {
let fileName = file.name;
this.$emit("update:fileName", fileName);
let isDat = this.isFormatValid(fileName);
if (!isDat) {
this.tips = "当前选择的文件格式不正确，请重新选择！";
} else {
this.tips = "";
}
return isDat;
},
isFormatValid(type) {
let pStrDAt = /\.xls?$/i;
return pStrDAt.test(type);
},
handleSubmit() {
if (!this.tips) {
console.log("这里到了吗?")
this.$refs.upload.submit();
console.log("哈哈")
}
}
}
}
</script>
<style>
font-family: Helvetica, sans-serif;
text-align: center;
}
.el-header, .el-footer {
background-color:
text-align: center;
}
.el-aside {
background-color:
color:
text-align: center;
line-height: 200px;
}
.el-main {
background-color:
color:
text-align: center;
line-height: 160px;
}
body > .el-container {
margin-bottom: 40px;
}
.el-container:nth-child(5) .el-aside,
.el-container:nth-child(6) .el-aside {
line-height: 260px;
}
.el-container:nth-child(7) .el-aside {
line-height: 320px;
}
</style>
pom.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>excel</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.5.0</version>
</dependency>
</dependencies>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
application.properties配置
server.port=8002
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/excel?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.thymeleaf.cache=false
mybatis.mapper-locations=classpath:mapper/*.xml
实体类
package com.edu.entity;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.edu.util.GenderConverter;
import lombok.Data;
@Data
public class User {
@ExcelIgnore
private String id;
@ExcelProperty(value = {"姓名"},index = 0)
private String name;
@ExcelProperty(value = {"年龄"},index = 1)
private Integer age;
@ExcelProperty(value = {"性别"},index = 2,converter = GenderConverter.class)
private Integer gender;
}
service
package com.edu.service;
import com.edu.entity.User;
import java.util.List;
public interface IUser {
//批量添加
public boolean saveData(List<User> list);
/*查询所有的用户*/
public List<User> ListUser();
}
serviceimpl
package com.edu.service.serviceimpl;
import com.edu.dao.Usermapper;
import com.edu.entity.User;
import com.edu.service.IUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class IUserservice implements IUser {
@Autowired
Usermapper usermapper;
@Override
public boolean saveData(List<User> list) {
return usermapper.saveAlldata(list);
}
@Override
public List<User> ListUser() {
return usermapper.listAll();
}
}
数字转性别，性别转数字的工具类
package com.edu.util;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
public class GenderConverter implements Converter<Integer> {
public static final String MALE = "男";
public static final String FEMALE = "女";
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
//导入
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String stringValue = cellData.getStringValue();
if (MALE.equals(stringValue)){
return 1;
}else {
return 2;
}
}
//导出
@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new CellData(integer.equals(1)?"男":"女");
}
}
导入监听器工具类，防止数据量过大，造成内存溢出
package com.edu.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.edu.dao.Usermapper;
import com.edu.service.IUser;
import com.edu.entity.User;
import com.edu.service.serviceimpl.IUserservice;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
@Slf4j
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class UserExcelListener extends AnalysisEventListener<User> {
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<User> list = new ArrayList<User>();
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private IUser iUser;
public UserExcelListener() {
// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
iUser = new IUserservice();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param iUserservice
*/
public UserExcelListener(IUser iUserservice) {
this.iUser = iUserservice;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext
* @param context
*/
@Override
public void invoke(User data, AnalysisContext context) {
System.out.println(data+"1111111111111111111");
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("???????");
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
}
/**
* 加上存储数据库
*/
private void saveData() {
System.out.println(list);
iUser.saveData(list);
}
}
mapper接口
package com.edu.dao;
import com.edu.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface Usermapper {
public boolean saveAlldata(List<User> list);
List<User> listAll();
}
Usermapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.Usermapper">
<resultMap type="com.edu.entity.User" id="ClassesResultMap">
<!-- 实体类的字段名和数据表的字段名映射 -->
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="gender" column="gender"/>
</resultMap>
<!--批量添加-->
<insert id="saveAlldata" parameterType="java.util.List">
insert into user(id,name,age,gender)
values
<foreach collection="list" item="item" index="index" separator=",">
((SELECT REPLACE(UUID(), '-', '') AS id),
</foreach>
</insert>
<select id="listAll" resultType="com.edu.entity.User">
select name,age,gender from user
</select>
</mapper>
控制器
package com.edu.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.edu.entity.User;
import com.edu.service.IUser;
import com.edu.service.serviceimpl.IUserservice;
import com.edu.util.UserExcelListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
@RestController
@Slf4j
@RequestMapping("user")
@CrossOrigin
public class UserController {
@Autowired
private IUser iUser;
/*下载一个模板*/
/*导入一个excel*/
@RequestMapping("upload")
public String uploadit(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
// Sheet sheet=new Sheet(1,0);
// List<Object> read = EasyExcelFactory.read(inputStream, sheet);
// System.out.println(read);
EasyExcel.read(inputStream, User.class,new UserExcelListener(iUser)).sheet().doRead();
inputStream.close();
return "success";
}
/*导出excel*/
@RequestMapping("export")
public String exportit(HttpServletResponse response) throws Exception {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8");
List<User> users=iUser.ListUser();
System.out.println(users);
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
EasyExcel.write(response.getOutputStream(), User.class).sheet("模板").doWrite(users);
// 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
// EasyExcel.write(fileName, User.class).sheet("模板").doWrite(users);
return "success";
}
}
导出效果图：
扫一扫
189
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
