一、excel读写测试
1.导入依赖
<properties><easyexcel.version>2.2.0-beta2</easyexcel.version></properties><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>${easyexcel.version}</version></dependency>
2.创建实体类
@Datapublic class User {@ExcelProperty(index = 0,value = "用户编号")private int id;@ExcelProperty(index = 1,value = "用户姓名")private String userName;}
3.写入excel文件
public class WriteTest {public static void main(String[] args) {List<User> list=new ArrayList<>();for (int i = 0; i < 10; i++) {User user=new User();user.setId(i);user.setUserName("candy"+i);list.add(user);}String path="F:\\git\\ShangGuiGu\\excel\\01.xlsx";EasyExcel.write(path,User.class).sheet("用户信息").doWrite(list);}}
4.读excel文件
4.1.读之前要添加监听器
public class EventListener extends AnalysisEventListener<User>{/*** 从第二行开始一行一行读取* @param user 实体类* @param analysisContext 内容*/@Overridepublic void invoke(User user, AnalysisContext analysisContext) {System.out.println(user);}/*** 读取第一行* @param headMap* @param context*/@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {System.out.println("头信息"+headMap);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}
4.2读excel文件
public class ReadTest {public static void main(String[] args) {String file="F:\\git\\ShangGuiGu\\excel\\01.xlsx";EasyExcel.read(file,User.class, new EventListener()).sheet().doRead();}}
二、excel整合SpringBoot
1.编写实体类
@Datapublic class DictEeVo {/*** 属性上添加注解，设置表头内容*/@ExcelProperty(value = "id" ,index = 0)private Long id;@ExcelProperty(value = "上级id" ,index = 1)private Long parentId;@ExcelProperty(value = "名称" ,index = 2)private String name;@ExcelProperty(value = "值" ,index = 3)private String value;@ExcelProperty(value = "编码" ,index = 4)private String dictCode;}
2.编写mapper文件
@Repositorypublic interface DictMapper extends BaseMapper<Dict> {}
3.编写监听器
/*** @FileName: DictListener* @Author Steven* @Date: /3/17*/public class DictListener extends AnalysisEventListener<DictEeVo>{@AutowiredDictMapper dictMapper;public DictListener(DictMapper dictMapper) {this.dictMapper=dictMapper;}@Overridepublic void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {Dict dict=new Dict();BeanUtils.copyProperties(dictEeVo,dict);dictMapper.insert(dict);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}
3.编写service接口
public interface DictService extends IService<Dict>{/*** 导出* @param response*/void exportData(HttpServletResponse response);/*** @param multipartFile 导入字典*/void importDictData(MultipartFile multipartFile);}
3.编写service实现类
@Servicepublic class DictServiceImpl extends ServiceImpl<DictMapper, Dict> implements DictService {@Overridepublic void exportData(HttpServletResponse response) {try {response.setContentType("application/json");response.setCharacterEncoding("UTF-8");String fileName = URLEncoder.encode("数据字典", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");List<Dict> dictList = dictMapper.selectList(null);List<DictEeVo> dictVos = new ArrayList<>(dictList.size());for (Dict dict : dictList) {DictEeVo dictVo = new DictEeVo();BeanUtils.copyProperties(dict, dictVo);dictVos.add(dictVo);}EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVos);} catch (Exception e) {throw new YyghException("数据导出失败！！！", 123);}}/*** 调用后清空所有方法** @param multipartFile 导入字典*/@CacheEvict(value = "dict", allEntries = true)@Overridepublic void importDictData(MultipartFile multipartFile) {try {EasyExcel.read(multipartFile.getInputStream(), DictEeVo.class, new DictListener(baseMapper)).sheet().doRead();} catch (IOException e) {e.printStackTrace();}}}
4.编写controller
@Api(tags = "数据字典接口")@RestController@RequestMapping("/admin/cmn/dict")public class DictController {@ApiOperation("数据导出")@GetMapping("/exportData")public Result exportData(HttpServletResponse response){dictService.exportData(response);return Result.ok();}@ApiOperation("导入字典数据")@PostMapping("/importData")public Result importData(MultipartFile file){dictService.importDictData(file);return Result.ok();}}
