文件EasyExcel的导入和导出
程序员文章站
2024-03-20 14:25:10
...
一、EasyExcel是什么以及常用场景
easyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称。
easyExcel常用场景:(1)数据的导入:减少录入的工作量
(2)数据的导出:统计信息归档
二、easyExcel后端代码
首先引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
//批量导出+文件下载+文件删除
@RequestMapping("/downLoad")
public String dasa(HttpServletResponse response) throws IOException {
String path = ResourceUtils.getURL("classpath:").getPath()+"/static/files";
String dataDirPath = path+"/"+new SimpleDateFormat("yyy-MM-dd").format(new Date());
File dateFile = new File(dataDirPath);
if(!dateFile.exists()){
dateFile.mkdirs();
}
String dateFile1 = dateFile+"/"+"haha.xlsx";
ExcelWriterBuilder workBook = EasyExcel.write(dateFile1,Notice.class);
List<Notice> s = new ArrayList<>();
//这里设置需要导出的数据,可以从数据库导入
ExcelWriterSheetBuilder sheet = workBook.sheet();
sheet.doWrite(s);
File date = new File(dateFile1);
FileInputStream is = new FileInputStream(date);
response.setHeader("Content-Disposition",
"attachment; filename=" + java.net.URLEncoder.encode("haha.xlsx", "UTF-8"));
ServletOutputStream os = response.getOutputStream();
IOUtils.copy(is,os);
IOUtils.closeQuietly(is);
IOUtils.closeQuietly(os);
if(date.exists()){
date.delete();
}
return path;
}
//上传加批量导入加文件删除
@RequestMapping("/upLoad")
public String dab(MultipartFile multipartFile) throws IOException {
String oldName = multipartFile.getOriginalFilename();
String path = ResourceUtils.getURL("classpath:").getPath()+"/static/files";
String dataDirPath = path+"/"+new SimpleDateFormat("yyy-MM-dd").format(new Date());
File dateFile = new File(dataDirPath);
if(!dateFile.exists()){
dateFile.mkdirs();
}
multipartFile.transferTo(new File(dataDirPath,oldName));
String patha = dataDirPath+"/"+oldName;
ExcelReaderBuilder workBook = EasyExcel.read(patha, Notice.class, new NoticeListener());
ExcelReaderSheetBuilder sheet = workBook.sheet();
sheet.doRead();
File date = new File(dataDirPath,oldName);
if(date.exists()){
date.delete();
}
return path;
}
public class NoticeListener extends AnalysisEventListener<Notice> {
@Override
public void invoke(Notice notice, AnalysisContext analysisContext) {
System.out.println("notice:"+notice);
//这里得到数据批量导入数据,进行相应的操作,可以存入数据库等
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}