文件EasyExcel的导入和导出

一、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) {
 
    }
}