1.导入依赖
< dependency>
< groupId> com.alibaba</ groupId>
< artifactId> easyexcel</ artifactId>
</ dependency>
一、简单的Excel读写操作
2.写好对应读写信息的类
@Data
public 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;
}
3.读操作需要的监听类
public class ExcelListener extends AnalysisEventListener < DictEeVo > {
private DictMapper dictMapper;
public DictEasyExcelListener ( DictMapper dictMapper) {
this . dictMapper = dictMapper;
}
@Override
public void invoke ( DictEeVo dictEeVo, AnalysisContext analysisContext) {
Dict dict = new Dict ( ) ;
BeanUtils . copyProperties ( dictEeVo, dict) ;
dictMapper. insert ( dict) ;
}
@Override
public void invokeHeadMap ( Map headMap, AnalysisContext context) {
super . invokeHeadMap ( headMap, context) ;
}
@Override
public void doAfterAllAnalysed ( AnalysisContext analysisContext) {
}
}
4.导出操作(写)
@GetMapping ( "download" )
public void download ( 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 + ".xlsx" ) ;
List < Dict > dictList = baseMapper. selectList ( null ) ;
ArrayList < DictEeVo > dictEeVoList = new ArrayList < > ( ) ;
dictList. forEach ( dict-> {
DictEeVo dictEeVo = new DictEeVo ( ) ;
BeanUtils . copyProperties ( dict, dictEeVo) ;
dictEeVoList. add ( dictEeVo) ;
} ) ;
EasyExcel . write ( response. getOutputStream ( ) , DictEeVo . class ) . sheet ( "模板" ) . doWrite ( dictEeVoList) ;
}
private List < DemoData > data ( ) {
List < DemoData > list = ListUtils . newArrayList ( ) ;
for ( int i = 0 ; i < 10 ; i++ ) {
DemoData data = new DemoData ( ) ;
data. setString ( "字符串" + i) ;
data. setDate ( new Date ( ) ) ;
data. setDoubleData ( 0.56 ) ;
list. add ( data) ;
}
return list;
}
5.操作读
@PostMapping ( "upload" )
@ResponseBody
public String upload ( MultipartFile file) throws IOException {
EasyExcel . read ( file. getInputStream ( ) , UploadData . class , new UploadDataListener ( uploadMapper) ) . sheet ( ) . doRead ( ) ;
return "success" ;
}