EasyExcel的导入导出总结

1、先在maven中引用EasyExcel的jar包

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.5</version>
        </dependency>

2、如何向单个sheet导出数据

1、定义导出数据对象，可以定义列名和列宽

public class Export {
	//忽略该字段
    @ExcelIgnore
    private Integer column1;
	//定义导出属性
    @ExcelProperty(index = 0, value = "列名")
    @ColumnWidth(10)
    private Integer column2;
}

2、执行导出操作

   @GetMapping(value = "/download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
        List<Export> list= new ArrayList<>();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //对中文文件名进行编码
        String fileName = URLEncoder.encode("文件名", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), Export.class).sheet("sheet名称").doWrite(list);
    }

3、如何向多个sheet导出数据

    @GetMapping(value = "/download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
        ExcelWriter excelWriter = null;

        List<Export1> list1= new ArrayList<>();

        List<Export2> list2= new ArrayList<>();        
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("文件名", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        excelWriter = EasyExcel.write(response.getOutputStream()).build();
        //向第一个sheet写入数据
        WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "sheet1名称").head(Export1.class).build();
        excelWriter.write(list1, writeSheet1);
        //向第二个sheet写入数据
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2名称").head(Export2.class).build();
        excelWriter.write(list2, writeSheet2);
        excelWriter.finish();
    }

4、如何导入数据

1、首先要定义AnalysisEventListener的实现，如何将从Excel导入的数据进行处理

public class Import {
    @ExcelProperty(index = 0)
    private String index1;
    @ExcelProperty(index = 1)
    private String index2;
}

public class ExcelListener extends AnalysisEventListener<Import> {
    private Service service;
    private List<Object> list = new ArrayList<>();
    public ExcelListener(Service service) {
        super();
        this.service = service;
    }
    @Override
    public void invoke(Import data, AnalysisContext analysisContext) {
        //将Import数据转换成Object对象
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //调用service方法，将list中数据存入数据库
    }
}

2、定义SpringMVC代码如何处理上传的Excel文件

    @RequestMapping(value = "/import")
    @ResponseBody
    public Response import(MultipartFile file, HttpServletRequest request, HttpServletResponse response, ModelAndView mav) throws Exception {
    	//创建自定义AnalysisEventListener实现
        AnalysisEventListener listener = new ExcelListener(service);
        EasyExcel.read(multipartFileToFile(file), Import.class, listener).sheet().doRead();
        return Response.ok();
    }
    //将MultipartFile转换成File对象
    public static File multipartFileToFile(MultipartFile file) throws Exception {

        File toFile = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    //获取流文件
    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }