赞
踩
欢迎关注微信公众号:序辑
数据导入 减轻录入工作量
数据导出 统计信息归档
数据传输 异构系统之间数据传输
常见excel分解框架:POL、EasyExcel
https://github.com/alibaba/easyexcel
快速开始:
https://www.yuque.com/easyexcel/doc/easyexcel
1.Java领域解析、生成Excel 比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行,但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
2.EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析。
3.EasyExcel采用一行一行的解析模式,并将一行的解析结果以观察者的模式通知处理(AnalysisEventListener)。
项目名:alibaba-easyexcel
<dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.7</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies>
package com.aojiaoge.easyexcel.dto;
@Data
public class ExcelStudentDTO {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("生日")
private Date birthday;
@ExcelProperty("薪资")
private Double salary;
}
package com.aojiaoge.easyexcel; public class ExcelWriteTest { @Test public void simpleWriteXlsx() { String fileName = "d:/excel/simpleWrite.xlsx"; //需要提前新建目录 // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭 EasyExcel.write(fileName, ExcelStudentDTO.class).sheet("模板").doWrite(data()); } //辅助方法 private List<ExcelStudentDTO> data(){ List<ExcelStudentDTO> list = new ArrayList<>(); //算上标题,做多可写65536行 //超出:java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535) for (int i = 0; i < 65535; i++) { ExcelStudentDTO data = new ExcelStudentDTO(); data.setName("Helen" + i); data.setBirthday(new Date()); data.setSalary(123456.1234); list.add(data); } return list; } }
@Test
public void simpleWriteXls() {
String fileName = "d:/excel/simpleWrite.xls";
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, ExcelStudentDTO.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());
}
xls版本的Excel最多一次可写0-65535行
xlsx版本的Excel最多一次可写0-104875行
package com.aojiaoge.easyexcel.listener; @Slf4j public class ExcelStudentDTOListener extends AnalysisEventListener<ExcelStudentDTO> { /** * 这个每一条数据解析都会来调用 */ @Override public void invoke(ExcelStudentDTO data, AnalysisContext context) { log.info("解析到一条数据:{}", data); } /** * 所有数据解析完成了 都会来调用 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { log.info("所有数据解析完成!"); } }
package com.aojiaoge.easyexcel; public class ExcelReadTest { /** * 最简单的读 */ @Test public void simpleReadXlsx() { String fileName = "d:/excel/simpleWrite.xlsx"; // 这里默认读取第一个sheet EasyExcel.read(fileName, ExcelStudentDTO.class, new ExcelStudentDTOListener()).sheet().doRead(); } @Test public void simpleReadXls() { String fileName = "d:/excel/simpleWrite.xls"; EasyExcel.read(fileName, ExcelStudentDTO.class, new ExcelStudentDTOListener()).excelType(ExcelTypeEnum.XLS).sheet().doRead(); } }
何为数据字典?数据字典负责管理系统常用的分类数据或者一些固定数据,例如:省市区三级联动数据、民族数据、行业数据、学历数据等,数据字典帮助我们方便的获取和适用这些通用数据。
1.parent_id:上级id,通过id与parent_id构建上下级关系,例如:我们要获取所有行业数据,那么只需要查询parent_id=20000的数据
2.name: 名称,例如:填写用户信息,我们要select标签选择民族,“汉族”就是数据字典的名称
3.value: 值,例如:填写用户信息,我们要select标签选择民族,“1”(汉族的标识)就是数据字典的值
4.dict_code: 编码,编码是我们自定义的,全局唯一,例如:我们要获取行业数据,我们可以通过parent_id获取,但是parent_id是不确定的,所以我们可以根据编码来获取行业数据
core中添加以下依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
</dependency>
package com.aojiaoge.srb.core.pojo.dto; @Data public class ExcelDictDTO { @ExcelProperty("id") private Long id; @ExcelProperty("上级id") private Long parentId; @ExcelProperty("名称") private String name; @ExcelProperty("值") private Integer value; @ExcelProperty("编码") private String dictCode; }
package com.aojaioge.srb.core.listener; @Slf4j //@AllArgsConstructor //全参 @NoArgsConstructor //无参 public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> { /** * 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收 */ private static final int BATCH_COUNT = 5; List<ExcelDictDTO> list = new ArrayList(); private DictMapper dictMapper; //传入mapper对象 public ExcelDictDTOListener(DictMapper dictMapper) { this.dictMapper = dictMapper; } /** *遍历每一行的记录 * @param data * @param context */ @Override public void invoke(ExcelDictDTO data, AnalysisContext context) { log.info("解析到一条记录: {}", data); list.add(data); // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据,确保最后遗留的数据也存储到数据库 saveData(); log.info("所有数据解析完成!"); } /** * 加上存储数据库 */ private void saveData() { log.info("{}条数据,开始存储数据库!", list.size()); dictMapper.insertBatch(list); //批量插入 log.info("存储数据库成功!"); } }
接口:DictMapper
void insertBatch(List<ExcelDictDTO> list);
xml:DictMapper.xml
<insert id="insertBatch"> insert into dict ( id , parent_id , name , value , dict_code ) values <foreach collection="list" item="item" index="index" separator=","> ( #{item.id} , #{item.parentId} , #{item.name} , #{item.value} , #{item.dictCode} ) </foreach> </insert>
接口:DictService
void importData(InputStream inputStream);
实现:DictServiceImpl
注意:此处添加了事务处理,默认情况下 rollbackFor = RuntimeException.class
@Transactional(rollbackFor = {Exception.class})
@Override
public void importData(InputStream inputStream) {
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(baseMapper)).sheet().doRead();
log.info("importData finished");
}
AdminDictController
package com.aojiaoge.srb.core.controller.admin; @Api(tags = "数据字典管理") @RestController @RequestMapping("/admin/core/dict") @Slf4j @CrossOrigin public class AdminDictController { @Resource private DictService dictService; @ApiOperation("Excel批量导入数据字典") @PostMapping("/import") public R batchImport( @ApiParam(value = "Excel文件", required = true) @RequestParam("file") MultipartFile file) { try { InputStream inputStream = file.getInputStream(); dictService.importData(inputStream); return R.ok().message("批量导入成功"); } catch (Exception e) { //UPLOAD_ERROR(-103, "文件上传错误"), throw new BusinessException(ResponseEnum.UPLOAD_ERROR, e); } } }
注意:因为maven工程在默认情况下src/main/java目录下的所有资源文件是不发布到target目录下的,因此我们需要在pom.xml中添加xml配置文件发布配置
<build>
<!-- 项目打包时会将java目录中的*.xml文件也进行打包 -->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
测试
创建 src/views/core/dict/list.vue
<template>
<div class="app-container">
</div>
</template>
<script>
export default {
}
</script>
{ path: '/core', component: Layout, redirect: '/core/dict/list', name: 'coreDict', meta: { title: '系统设置', icon: 'el-icon-setting' }, alwaysShow: true, children: [ { path: 'dict/list', name: '数据字典', component: () => import('@/views/core/dict/list'), meta: { title: '数据字典' } } ] }
<template> <div class="app-container"> <div style="margin-bottom: 10px;"> <el-button @click="dialogVisible = true" type="primary" size="mini" icon="el-icon-download" > 导入Excel </el-button> </div> <el-dialog title="数据字典导入" :visible.sync="dialogVisible" width="30%"> <el-form> <el-form-item label="请选择Excel文件"> <el-upload :auto-upload="true" :multiple="false" :limit="1" :on-exceed="fileUploadExceed" :on-success="fileUploadSuccess" :on-error="fileUploadError" :action="BASE_API + '/admin/core/dict/import'" name="file" accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" > <el-button size="small" type="primary">点击上传</el-button> </el-upload> </el-form-item> </el-form> <div slot="footer" class="dialog-footer"> <el-button @click="dialogVisible = false"> 取消 </el-button> </div> </el-dialog> </div> </template> <script> export default { // 定义数据 data() { return { dialogVisible: false, //文件上传对话框是否显示 BASE_API: process.env.VUE_APP_BASE_API //获取后端接口地址 } }, methods: { // 上传多于一个文件时 fileUploadExceed() { this.$message.warning('只能选取一个文件') }, //上传成功回调 fileUploadSuccess(response) { if (response.code === 0) { this.$message.success('数据导入成功') this.dialogVisible = false } else { this.$message.error(response.message) } }, //上传失败回调 fileUploadError(error) { this.$message.error('数据导入失败') } } } </script>
接口:DictService
List<ExcelDictDTO> listDictData();
实现:DictServiceImpl
@Override
public List<ExcelDictDTO> listDictData() {
List<Dict> dictList = baseMapper.selectList(null);
//创建ExcelDictDTO列表,将Dict列表转换成ExcelDictDTO列表
ArrayList<ExcelDictDTO> excelDictDTOList = new ArrayList<>(dictList.size());
dictList.forEach(dict -> {
ExcelDictDTO excelDictDTO = new ExcelDictDTO();
BeanUtils.copyProperties(dict, excelDictDTO);
excelDictDTOList.add(excelDictDTO);
});
return excelDictDTOList;
}
@ApiOperation("Excel数据的导出") @GetMapping("/export") public void export(HttpServletResponse response){ try { // 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), ExcelDictDTO.class).sheet("数据字典").doWrite(dictService.listDictData()); } catch (IOException e) { //EXPORT_DATA_ERROR(104, "数据导出失败"), throw new BusinessException(ResponseEnum.EXPORT_DATA_ERROR, e); } }
<el-button
@click="exportData"
type="primary"
size="mini"
icon="el-icon-upload2" >导出Excel</el-button>
//Excel数据导出
exportData() {
window.location.href = this.BASE_API + '/admin/core/dict/export'
}
Dict中添加属性:
@ApiModelProperty(value = "是否包含子节点")
@TableField(exist = false)//在数据库表中忽略此列
private boolean hasChildren;
接口:DictService
List<Dict> listByParentId(Long parentId);
实现:DcitServiceImpl
@Override public List<Dict> listByParentId(Long parentId) { List<Dict> dictList = baseMapper.selectList(new QueryWrapper<Dict>().eq("parent_id", parentId)); dictList.forEach(dict -> { //如果有子节点,则是非叶子节点 boolean hasChildren = this.hasChildren(dict.getId()); dict.setHasChildren(hasChildren); }); return dictList; } /** * 判断该节点是否有子节点 */ private boolean hasChildren(Long id) { QueryWrapper<Dict> queryWrapper = new QueryWrapper<Dict>().eq("parent_id", id); Integer count = baseMapper.selectCount(queryWrapper); if(count.intValue() > 0) { return true; } return false; }
@ApiOperation("根据上级id获取子节点数据列表")
@GetMapping("/listByParentId/{parentId}")
public R listByParentId(
@ApiParam(value = "上级节点id", required = true)
@PathVariable Long parentId) {
List<Dict> dictList = dictService.listByParentId(parentId);
return R.ok().data("list", dictList);
}
创建src/api/core/dict.js
import request from '@/utils/request'
export default {
listByParentId(parentId) {
return request({
url: `/admin/core/dict/listByParentId/${parentId}`,
method: 'get'
})
}
}
在views/core/dict/list.vue
定义data:
list: [], //数据字典列表
生命周期函数:
created() {
this.fetchData()
},
获取数据的方法:
import dictApi from '@/api/core/dict'
// 调用api层获取数据库中的数据
fetchData() {
dictApi.listByParentId(1).then(response => {
this.list = response.data.list
})
},
//延迟加载子节点
load(row, treeNode, resolve) {
dictApi.listByParentId(row.id).then(response => {
//负责将子节点数据展示在展开的列表中
resolve(response.data.list)
})
},
<el-table :data="list" border row-key="id" lazy :load="load">
<el-table-column label="名称" align="left" prop="name" />
<el-table-column label="编码" prop="dictCode" />
<el-table-column label="值" align="left" prop="value" />
</el-table>
数据导入后刷新页面的数据列表
//上传成功回调
fileUploadSuccess(response) {
if (response.code === 0) {
this.$message.success('数据导入成功')
this.dialogVisible = false
this.fetchData()
} else {
this.$message.error(response.message)
}
},
})
},
//延迟加载子节点
load(row, treeNode, resolve) {
dictApi.listByParentId(row.id).then(response => {
//负责将子节点数据展示在展开的列表中
resolve(response.data.list)
})
},
##### 4.2.3组件模板
```vue
<el-table :data="list" border row-key="id" lazy :load="load">
<el-table-column label="名称" align="left" prop="name" />
<el-table-column label="编码" prop="dictCode" />
<el-table-column label="值" align="left" prop="value" />
</el-table>
数据导入后刷新页面的数据列表
//上传成功回调
fileUploadSuccess(response) {
if (response.code === 0) {
this.$message.success('数据导入成功')
this.dialogVisible = false
this.fetchData()
} else {
this.$message.error(response.message)
}
},
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。