使用easyexcel实现excel导入导出功能
基于springboot,前端使用vue,使用阿里的easyexcel实现了单sheet表导入导出,多sheet表导入导出,单数据库表导入导出,多数据库表导入导出。
首先需要搭建好spingboot和前端开发项目环境
然后就可以开始开发excel导入导出功能
首先需要引入easyexcel的maven依赖
1 2 3 4 5 6 7
| <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> </dependency>
|
根据自己需要的excel表作为对象,建立一个实体类
有几个注解是重点
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
|
public class User { @ExcelIgnore private Integer id; @ExcelProperty(value = "姓名", index = 0) private String name; @ExcelProperty(value = "性别", index = 1) private String sex; @ExcelProperty(value = "年龄", index = 2) private Integer age; @DateTimeFormat(value = "yyyy-MM-dd") @ExcelProperty(value = "出生日期", index = 3) private String birthday; }
|
结合前后端
导出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| exportWeb: function () { this.$axios.get('/exportExcel', { responseType: 'blob', }) .then(response => { const blob = new Blob([response.data], { type: response.headers['content-type'] }); const link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); link.download = '导出'; link.click(); }) .catch(error => { console.error('文件下载失败:', error); }); },
|
1 2 3 4 5 6 7 8 9 10 11 12
| @RequestMapping("/api/exportExcel") public void export(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
List<Student> student = studentService.queryStudent(); EasyExcel.write(response.getOutputStream(), Student.class).sheet("导出文件名").doWrite(student); }
|
导入
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
| //导出页面 <template>
<el-upload class="upload-demo" ref="upload" action="http://localhost:8082/api/upload" :on-preview="handlePreview" :on-remove="handleRemove" :on-success="handleSuccess" :file-list="fileList" :multiple="false" :limit="1" :auto-upload="false"> <el-button slot="trigger" size="small" type="primary">选取文件</el-button> <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload($event)">上传到服务器</el-button> <div slot="tip" class="el-upload__tip">只能上传execl文件,且不超过500kb</div> </el-upload>
</template>
<script> export default { data() { return { limitNum: 1, fileList: [], } }, methods:{ handleSuccess(e) { console.log('上传成功', e) this.$message(e.message); }, submitUpload(e) {
console.log('我要上传了', e) this.$refs.upload.submit(); },
handleRemove(file, fileList) { console.log(file, fileList); }, handlePreview(file) { console.log(file); }, } } </script>
<style scoped>
</style>
|
1 2 3 4 5 6 7 8 9
|
@PostMapping("/api/upload") @ResponseBody public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), Student.class, new StudentDataListener(studentService)).sheet().doRead(); return "上传成功"; }
|
可以用配置类配置文件上传的大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| @Configuration public class MulterFile {
@Bean public MultipartConfigElement multipartConfigElement() { MultipartConfigFactory factory = new MultipartConfigFactory(); factory.setMaxFileSize(DataSize.parse("30960KB")); factory.setMaxRequestSize(DataSize.parse("309600KB")); return factory.createMultipartConfig(); } }
|