EasyExcel的几种用法
https://xuefanzz.ltd/download/iFLYBUDS.exe
主要作用：
提取上传excel文件内容，使得批量入库，或者快速填写表单。
获取库中某些信息并以excel形式导出。
导入pom.xml
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
读取excel文件
根据excel字段创建实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
//excel头id，名字一定要一样
@ExcelProperty("id")
private Integer id;
@ExcelProperty("name")
private String username;
@ExcelProperty("password")
private String password;
}
读取excel文件，转成集合对象
@Test
public void testReadExcel(){
// 将每一行数据按照对象存入集合
List<User> list = new LinkedList<>();
EasyExcel.read("G:\\file\\wwy.xls")
//以什么为对象
.head(User.class)
//哪一个表
.sheet("user")
//注册读取监听
.registerReadListener(new AnalysisEventListener<User>() {
//每读取一行执行一次
@Override
public void invoke(User user, AnalysisContext analysisContext) {
list.add(user);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("读取完毕！");
}
}).doRead();
for (User user : list) {
System.out.println(user);
}
}
通过前台传的文件
有这么几种情况。
1.若是下载，只需要请求文件就行了。
http://localhost:10010/wwy.xls
2.若是仅仅希望返回前台传来的excel里面的信息，这样很简单，只需要将读取到的list返回，这中间可以进行一些操作，都是逻辑上的操作这里就不讨论。
3.若是采用调用其它模块里面的excel文件，并解析。可以采用Resttemplet里的exchange()调用，估计也可以使用Feign调用。
原生前端
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="http://cdn.bootcss.com/jquery/1.12.2/jquery.js"></script>
<script src="https://cdn.bootcdn.net/ajax/libs/jquery.serializeJSON/2.9.0/jquery.serializejson.js"></script>
</head>
<body>
<form id="uploadForm" enctype="multipart/form-data">
<input type="file" name="file"/>
</form>
<button id="upload">提交文件</button>
<script type="text/javascript">
$(function () {
$("#upload").click(function () {
alert(123456)
var formData = new FormData($('#uploadForm')[0]);
$.ajax({
type: 'post',
url: "http://localhost:8080/jiexiexcel", //上传文件的请求路径必须是绝对路劲
data: formData,
cache: false,
processData: false,
contentType: false,
}).success(function (data) {
console.log(data)
alert(data);
}).error(function () {
alert("上传失败");
});
});
});
</script>
</body>
</html>
Vue+Element (如果要使用此方法后台接口要使用@CrossOrigin解决跨域)
<template>
<div id="app">
<el-row>
<el-button type="primary" plain @click="importExcel()">上传文件</el-button>
</el-row>
<el-dialog title="导入文件" :visible.sync="dialogTableVisible" width="30%">
<el-upload
class="upload-demo"
drag
action="http://localhost:8080/jiexiexcel"
multiple
:on-success="importSuccess"
:on-error="importError"
accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
<div class="el-upload__tip" slot="tip">只能上传xls文件，且不超过500kb</div>
</el-upload>
</el-dialog>
<div id="formd">
<el-table
:data="user"
style="width: 100%">
<el-table-column
prop="id"
label="用户ID"
width="180">
</el-table-column>
<el-table-column
prop="username"
label="姓名"
width="180">
</el-table-column>
<el-table-column
prop="password"
label="密码">
</el-table-column>
</el-table>
</div>
</div>
</template>
<script>
export default {
name: 'app',
data() {
return {
dialogTableVisible: false,
user: []
}
},
methods:{
importSuccess(data){
var _this = this
this.$alert('数据导入成功！', '', {
confirmButtonText: '确定',
callback: action => {
this.dialogTableVisible=false
_this.user=data
}
});
},
importError(){
this.$alert('数据导入失败', '', {
confirmButtonText: '确定',
callback: action => {
this.dialogTableVisible=false
location.reload()
}
});
},
importExcel(){
this.dialogTableVisible=true
}
}
}
</script>
统一后台
@CrossOrigin
@RequestMapping("/jiexiexcel")
public List<User> jiexiexcel(@RequestParam("file") MultipartFile file) throws IOException {
List<User> list = new LinkedList<>();
EasyExcel.read(file.getInputStream())
.head(User.class)
.sheet("user")
.registerReadListener(new AnalysisEventListener<User>() {
@Override
public void invoke(User user, AnalysisContext analysisContext) {
list.add(user);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据解析完毕");
}
}).doRead();
return list;
}
调用其它模块(采用转文件形式转化)
@RequestMapping("/jiexiOtherService")
public List<User> download(String fileurl){
HttpHeaders headers = new HttpHeaders();
HttpEntity<Resource> httpEntity = new HttpEntity<Resource>(headers);
ResponseEntity<byte[]> bytes = restTemplate.exchange(fileurl, HttpMethod.GET, httpEntity, byte[].class);
//从其他模块获取到的文件字节流转成文件，保存在本项目中。记得用完后删除。
File f = new File("G:\\copy.xls");
try {
boolean newFile = f.createNewFile();
System.out.println(newFile);
} catch (IOException e) {
e.printStackTrace();
}
FileOutputStream out = null;
try {
out = new FileOutputStream(f);
out.write(bytes.getBody(),0,bytes.getBody().length);
out.flush();
} catch (Exception e) {
e.printStackTrace();
}finally {
if (out!=null){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//解析excel文件
List<User> list = new LinkedList<>();
EasyExcel.read("G:\\copy.xls")
.head(User.class)
.sheet("user")
.registerReadListener(new AnalysisEventListener<User>() {
@Override
public void invoke(User user, AnalysisContext analysisContext) {
list.add(user);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕！");
}
}).doRead();
for (User user : list) {
System.out.println(user);
}
return list;
}
调用其它模块（直接根据字节流转化）
@RequestMapping("/down")
public List<User> downloadOther(String fileurl){
System.out.println(fileurl);
HttpHeaders headers = new HttpHeaders();
HttpEntity<Resource> httpEntity = new HttpEntity<Resource>(headers);
//关键
ResponseEntity<byte[]> bytes = restTemplate.exchange(fileurl, HttpMethod.GET, httpEntity, byte[].class);
byte[] body = bytes.getBody();
InputStream inputStream = new ByteArrayInputStream(body);
List<User> list = new LinkedList<>();
//解析excel
EasyExcel.read(inputStream)
.head(User.class)
.sheet("user")
.registerReadListener(new AnalysisEventListener<User>() {
@Override
public void invoke(User user, AnalysisContext analysisContext) {
list.add(user);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据读取完毕！");
}
}).doRead();
for (User user : list) {
System.out.println(user);
}
return list;
}
写excel文件
作用：一般是将库里面满足的写成excel文件，返回到前端
普通写excel
// 最后把文件所在目录返回回去，然后根据文件所在服务器位置进行请求，可以直接下载
@SpringBootTest
public class WriteTest {
@Test
public void writetest() {
List<User> list = new LinkedList<>();
User user1 = new User(4, "zs", "wwy123456");
User user2 = new User(5, "ls", "wwy123456");
User user3 = new User(6, "wangwu", "wwy123456");
list.add(user1);
list.add(user2);
list.add(user3);
//list写入Excel文件
EasyExcel.write("G:\\file\\wwy-copy.xls")
.head(User.class)
.excelType(ExcelTypeEnum.XLS)
.sheet("user")
.doWrite(list);
}
}
若是服务接口
// 这边可以直接下载excel
@GetMapping("/export")
public void exportData(HttpServletResponse response) throws IOException {
// 对头的一些设置
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
String fileName = URLEncoder.encode("文件名字", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
// 模拟从库里获取用户的集合
List<User> userList = new LinkedList<>();
User user1 = new User(1, "root", "root123456");
User user2 = new User(2, "admin", "admin123456");
User user3 = new User(3, "wwy", "wwy123456");
userList.add(user1);
userList.add(user2);
userList.add(user3);
EasyExcel.write(response.getOutputStream())
.head(User.class)
.excelType(ExcelTypeEnum.XLS)
.sheet("user")
.doWrite(userList);
}
240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
