SpringBoot整合EasyExcel上传下载前后端
需求,在项目启动时加载表格里的数据初始化,前端可以上传全部部门的表格数据,后台根据部门名字解析归类数据和根据表格的部门下载部门数据
EasyExcel初识请点击
1.后端
1.1创建一个SpringBoot项目,引入依赖
<?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>AutoCreateCode</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.31</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.15</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<dependency>
<groupId>com.github.yulichang</groupId>
<artifactId>mybatis-plus-join</artifactId>
<version>1.3.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.6</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-commons</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-integration</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.integration</groupId>
<artifactId>spring-integration-stream</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.6</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.7.4</version>
</plugin>
</plugins>
<resources>
<resource>
<directory>${basedir}/src/main/java</directory>
<includes>
<include>**
1.2 在resource目录下建立一个static目录放入要解析的excel
![在这里插入图片描述](https://img-blog.csdnimg.cn/5deb5ee4440f42d9bc8a93d0bb86bc98.png)
1.3写一个与表格表头相对应的实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class EasyExcelData {
@ExcelProperty(value = "类别",index = 0)
String category;
@ExcelProperty(value = "责任处室",index = 1)
String responsibilityOffic;
@ExcelProperty(value ="指标名称",index = 2)
String nameOfIndex;
@ExcelProperty(value ="结构化类型",index = 3)
String structuredType;
@ExcelProperty(value ="结构化名称",index = 4)
String structuredName;
@ExcelProperty(value ="单位",index = 5)
String unit;
@ExcelProperty(value ="当月值",index = 6)
String monthlyValue;
@ExcelProperty(value ="单月同比",index = 7)
String monthOnMont;
@ExcelProperty(value ="月度累计",index = 8)
String monthlyAccumulation;
@ExcelProperty(value ="月度累计同比",index = 9)
String monthlyCumulativeYearOnYear;
}
1.4创建一个存储表格数据的静态类
import com.huilian.demo.entity.EasyExcelData;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class GlobalImportTemplateData {
public static Map<Integer, String> modelHead=new HashMap<>();
public static Map<String, List<EasyExcelData>>modelData=new HashMap<>();
public static Map<String,String>modelName=new HashMap<>();
}
1.5创建一个表格监听类得到上传表格数据时实现自己的业务逻辑,在项目启动时初始化数据
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.huilian.demo.config.GlobalImportTemplateData;
import com.huilian.demo.entity.EasyExcelData;
import lombok.SneakyThrows;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class ScanGlobalImportTemplateListener extends AnalysisEventListener<EasyExcelData> {
@Override
public void invoke(EasyExcelData easyExcelData, AnalysisContext analysisContext) {
boolean isData = GlobalImportTemplateData.modelData.containsKey(easyExcelData.getResponsibilityOffic());
if(isData){
List<EasyExcelData> easyExcelData1 = GlobalImportTemplateData.modelData.get(easyExcelData.getResponsibilityOffic());
easyExcelData1.add(easyExcelData);
List<EasyExcelData> easyExcelData2 = GlobalImportTemplateData.modelData.get("全局导入模版");
easyExcelData2.add(easyExcelData);
}else {
List<EasyExcelData> easyExcelData1=new ArrayList<>();
easyExcelData1.add(easyExcelData);
GlobalImportTemplateData.modelData.put(easyExcelData.getResponsibilityOffic(),easyExcelData1);
GlobalImportTemplateData.modelName.put(easyExcelData.getResponsibilityOffic(),easyExcelData.getResponsibilityOffic());
List<EasyExcelData> easyExcelData2 = GlobalImportTemplateData.modelData.get("全局导入模版");
easyExcelData2.add(easyExcelData);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
GlobalImportTemplateData.modelName.put("全局导入模版","全局导入模版");
System.out.println("全局表格数据加载完成");
}
@SneakyThrows
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
GlobalImportTemplateData.modelHead=headMap;
List<EasyExcelData> easyExcelData1=new ArrayList<>();
GlobalImportTemplateData.modelData.put("全局导入模版",easyExcelData1);
}
}
1.6创建一个类,在项目启动时初始化数据
import com.alibaba.excel.EasyExcel;
import com.huilian.demo.entity.EasyExcelData;
import com.huilian.demo.listener.EasyExcelDataListener;
import com.huilian.demo.listener.ScanGlobalImportTemplateListener;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Component
public class ScanGlobalImportTemplate {
@PostConstruct
public void scanExcelConfig() throws Exception {
System.out.println("项目启动===开始加载全局表格数据!!");
try {
ClassPathResource classPathResource = new ClassPathResource("static/GlobalImportTemplate.xls");
if (classPathResource.exists()) {
InputStream inputStream = classPathResource.getInputStream();
EasyExcel.read(inputStream,EasyExcelData.class, new ScanGlobalImportTemplateListener()).sheet().doRead();
} else {
throw new Exception("==全局表格数据加载为空==!请检查“resources/static/GlobalImportTemplate.xls” 全局模版表格是否存在,或者联系xxx处理");
}
} catch (Exception e) {
throw new Exception("==全局表格数据加载失败==!请检查“resources/static/GlobalImportTemplate.xls” 全局模版表格是否存在或正确,或者联系xxx处理");
}
}
}
1.7创建一个表格监听实现自己的业务,在表格上传得到数据时
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.huilian.demo.config.GlobalImportTemplateData;
import com.huilian.demo.entity.EasyExcelData;
import lombok.SneakyThrows;
import java.util.*;
public class EasyExcelDataListener extends AnalysisEventListener<EasyExcelData> {
private List<EasyExcelData> datas;
private String deptName;
public EasyExcelDataListener(List<EasyExcelData> datas,String deptName) {
this.datas = datas;
this.deptName=deptName;
}
@Override
public void invoke(EasyExcelData easyExcelData, AnalysisContext analysisContext) {
datas.add(easyExcelData);
}
@SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
List<EasyExcelData> modelData = getDatas(deptName);
if(modelData.size()==0){
throw new Exception("模版库中,初始化数据失败!!请联系黄达滔");
}
if(modelData.size()!=datas.size()){
throw new Exception(deptName+"模版行数不对,请严格按照模版上传");
}
for (int i=0;i<datas.size();i++){
if(!datas.get(i).getCategory().equals(modelData.get(i).getCategory())){
throw new Exception(deptName+":第"+(i+2)+"行“"+datas.get(i).getCategory()+"”与模版:第"+(i+2)+"行”"+modelData.get(i).getCategory()+"”对不上,请按模版修改");
}
if(!datas.get(i).getResponsibilityOffic().equals(modelData.get(i).getResponsibilityOffic())){
throw new Exception(deptName+":第"+(i+2)+"行”"+datas.get(i).getResponsibilityOffic()+"”与模版:第"+(i+2)+"行”"+modelData.get(i).getResponsibilityOffic()+"”对不上,请按模版修改");
}
if(!datas.get(i).getNameOfIndex().equals(modelData.get(i).getNameOfIndex())){
throw new Exception(deptName+":第"+(i+2)+"行”"+datas.get(i).getNameOfIndex()+"”与模版:第"+(i+2)+"行”"+modelData.get(i).getNameOfIndex()+"”对不上,请按模版修改");
}
if(!datas.get(i).getStructuredType().equals(modelData.get(i).getStructuredType())){
throw new Exception(deptName+":第"+(i+2)+"行”"+datas.get(i).getStructuredType()+"”与模版:第"+(i+2)+"行”"+modelData.get(i).getStructuredType()+"”对不上,请按模版修改");
}
if(!datas.get(i).getStructuredName().equals(modelData.get(i).getStructuredName())){
throw new Exception(deptName+":第"+(i+2)+"行”"+datas.get(i).getStructuredName()+"”与模版:第"+(i+2)+"行”"+modelData.get(i).getStructuredName()+"”对不上,请按模版修改");
}
if(!datas.get(i).getUnit().equals(modelData.get(i).getUnit())){
throw new Exception(deptName+":第"+(i+2)+"行”"+datas.get(i).getUnit()+"”与模版:第"+(i+2)+"行”"+modelData.get(i).getUnit()+"”对不上,请按模版修改");
}
}
System.out.println("数据解析完了!!!");
}
@SneakyThrows
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
if (headMap.size() == 10) {
Map<Integer, String> modelHead = GlobalImportTemplateData.modelHead;
Set<Integer> set = headMap.keySet();
for (Integer i : set) {
if (!modelHead.containsValue(headMap.get(i))) {
throw new Exception("上传表格的表头“" + headMap.get(i) + "”与模版不一致,请检查!!");
}
}
} else {
throw new Exception("表头列数与模版不一致,请不要改动模版表头!!");
}
}
public List<EasyExcelData>getDatas(String deptName){
return GlobalImportTemplateData.modelData.get(deptName);
}
}
1.8 表格上传的controller方法
@ApiOperation(value = "导入全局Exccel表数据")
@PostMapping("/uploadGlobalImportTemplate")
public Result uploadGlobalImportTemplate(@RequestParam("file") MultipartFile file) {
GlobalImportTemplateData.modelData.clear();
GlobalImportTemplateData.modelHead.clear();
GlobalImportTemplateData.modelName.clear();
try {
EasyExcel.read(file.getInputStream(), EasyExcelData.class, new ScanGlobalImportTemplateListener()).sheet().doRead();
} catch (IOException e) {
return ResultFactory.buildFailResult("导入全局Exccel表数据异常,请修复");
}
return ResultFactory.buildSuccessResult("导入成功!!");
}
1.9 表格下载的controller方法
@ApiOperation(value = "下载全局Exccel表数据")
@GetMapping("/downloadGlobalImportTemplate")
public void downloadGlobalImportTemplate(HttpServletResponse response,@RequestParam("deptName")String deptName) throws IOException {
boolean isExist = GlobalImportTemplateData.modelData.containsKey(deptName);
if(isExist) {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName1 = URLEncoder.encode(deptName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName1 + ".xls");
EasyExcel.write(response.getOutputStream(), EasyExcelData.class).sheet(deptName).doWrite(GlobalImportTemplateData.modelData.get(deptName));
}
}
@ApiOperation(value = "获取已上传部门模版Exccel表格")
@GetMapping("/getGlobalImportTemplateDept")
public Result getGlobalImportTemplateDept() {
Map<String, String> map = GlobalImportTemplateData.modelName;
List<String> deptName = new ArrayList<>();
Set<String> keys = map.keySet();
for (String key : keys) {
deptName.add(map.get(key));
}
return ResultFactory.buildSuccessResult(deptName);
}
2.前端vue实现
<template>
<div class="app-container">
<el-form label-width="200px">
<el-form-item label="信息描述">
<el-tag type="info">选择要下载的部门模版</el-tag>
<el-select v-model="value" placeholder="点击选择部门模版">
<el-option
v-for="item in options"
:key="item"
:label="item"
:value="item">
</el-option>
</el-select>
<el-tag>
<i class="el-icon-download"/>
<a :href="href" @click="dwonExcel()">点击下载部门模版</a>
</el-tag>
</el-form-item>
<el-form-item label="选择Excel">
<el-upload
ref="upload"
:auto-upload="false"
:on-success="fileUploadSuccess"
:on-error="fileUploadError"
:disabled="importBtnDisabled"
:action="BASE_API+'/reportImportData/uploadGlobalImportTemplate'"
:limit="1"
name="file"
accept="application/vnd.ms-excel">
<el-button slot="trigger" size="small" type="primary">选取全局模版文件</el-button>
<el-button
:loading="loading"
style="margin-left: 10px;"
size="small"
type="success"
@click="submitUpload">上传提交
</el-button>
</el-upload>
</el-form-item>
</el-form>
</div>
</template>
<script>
export default {
data() {
return {
BASE_API: process.env.VUE_APP_BASE_API,
importBtnDisabled: false,
loading: false,
options:[],
value: '',
href:'',
}
},
created() {
this.getGlobalImportTemplateDept();
},
methods: {
getGlobalImportTemplateDept(){
this.$http.get('/reportImportData/getGlobalImportTemplateDept',{},{
}).then((res) => {
if (res.code == 200) {
this.options = res.data;
} else {
this.$alert(res.message, {
type: 'error',
});
}
});
},
dwonExcel(){
if (this.value!=''){
this.href=this.BASE_API+'/reportImportData/downloadGlobalImportTemplate'+'?deptName='+this.value;
}else {
this.href='javascript:;'
this.$message({
type:"warning",
message:"请在左边选择要下载的部门"
});
}
},
submitUpload() {
this.$confirm('检查好要上传的全局模版表格了吗?', '提示', {
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}).then(() => {
this.importBtnDisabled = false;
this.loading = false;
this.$refs.upload.submit();
}).catch(() => {
this.$message({
type: 'info',
message: '已取消上传覆盖'
});
});
},
fileUploadSuccess(res,file){
this.$message({
type :"success",
message:"导入成功"
});
this.importBtnDisabled = false;
this.loading = false;
this.$refs.upload.clearFiles()
this.value='';
this.getGlobalImportTemplateDept();
},
fileUploadError(res){
this.$message({
type:"失败",
message
});
this.importBtnDisabled = false;
this.loading = false;
},
}
}
</script>