EasyExcel导出导入

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

public class EasyExcelExportDemo {


    public static void main(String[] args) throws IOException {
        EasyExcelExportDemo test = new EasyExcelExportDemo();

        String fileName = new File("").getCanonicalPath() + "/report-single.xlsx";

        //单sheet
        test.write(fileName);
        System.out.println("========");
        test.read(fileName);

        //多个sheet

        fileName = new File("").getCanonicalPath() + "/report-multi.xlsx";
        test.repeatedWrite(fileName);
        System.out.println("========");
        test.repeatedRead(fileName);
    }


    public void write(String fileName) {
        EasyExcel.write(fileName, ExcelDemoModel.class)
                // 过滤不导出 若搭配ExcelProperty(index ) 注意index的连续性，否则会在导出的excel中占一列空白位
//                .excludeColumnFieldNames(Lists.newArrayList("date"))
                // 自动列宽(不太精确)
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("模板")
                .doWrite(data());
    }


    public void repeatedWrite(String fileName) {
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = null;
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = null;

        // 这里 指定文件
        excelWriter = EasyExcel.write(fileName).build();
        // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
        for (int i = 0; i < 5; i++) {
            //  如果写到不同的sheet 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意class 可以每次都变
            writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(ExcelDemoModel.class).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            List<ExcelDemoModel> data = data();
            excelWriter.write(data, writeSheet);
        }
        /// 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }

    public void read(String fileName) {
        EasyExcel.read(fileName, ExcelDemoModel.class, new DemoDataListener()).doReadAll();
    }

    public void repeatedRead(String fileName) {
        try {
            ExcelReader excelReader = EasyExcel.read(fileName).build();
            // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
            ReadSheet readSheet1 =
                    EasyExcel.readSheet(0).head(ExcelDemoModel.class).registerReadListener(new DemoDataListener()).build();
            ReadSheet readSheet2 =
                    EasyExcel.readSheet(1).head(ExcelDemoModel.class).registerReadListener(new DemoDataListener()).build();
            // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
            excelReader.read(readSheet1, readSheet2);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ExcelDemoModel.class).sheet("模板").doWrite(data());
    }

    public void upload(MultipartFile file) throws IOException{
        EasyExcel.read(file.getInputStream(), ExcelDemoModel.class, new DemoDataListener()).sheet().doRead();
    }

    private List<ExcelDemoModel> data() {
        List<ExcelDemoModel> list = new ArrayList<ExcelDemoModel>();
        for (int i = 0; i < 10; i++) {
            ExcelDemoModel data = new ExcelDemoModel();
            data.setName("字符串" + i);
            data.setDate(new Date());
            data.setRate(0.56);
            data.setFlag(true);
            list.add(data);
        }
        return list;
    }


}

public class ConverterCust implements Converter<String> {
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 这里是读的时候会调用 不用管
     *
     * @return
     */
    public String convertToJavaData(ReadConverterContext<?> context) {
        return context.getReadCellData().getStringValue();
    }

    /**
     * 这里是写的时候会调用 不用管
     *
     * @return
     */
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<String>("自定义：" + context.getValue());
    }
}

public class DemoDataListener implements ReadListener<ExcelDemoModel> {

    private List<ExcelDemoModel> list = Lists.newArrayList();

    public void invoke(ExcelDemoModel excelDemoModel, AnalysisContext analysisContext) {
        list.add(excelDemoModel);
        System.out.println("----invoke--" + excelDemoModel.toString());
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("----doAfterAllAnalysed--" + list.size());
    }

    void save(){
        //TODO
    }
}

public class ExcelDemoModel {
    @ExcelProperty(value = "数字")
    @NumberFormat("#.##%")
    private Double rate;
    @ExcelProperty(value = "字符串", converter = ConverterCust.class)
    private String name;
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "日期")
    private Date date;
    @ExcelProperty(value = "是否")
    private boolean flag;

    @Override
    public String toString() {
        return "ExcelDemoModel{" +
                "rate=" + rate +
                ", name='" + name + '\'' +
                ", date=" + date +
                ", flag=" + flag +
                '}';
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Double getRate() {
        return rate;
    }

    public void setRate(Double rate) {
        this.rate = rate;
    }

    public boolean isFlag() {
        return flag;
    }

    public void setFlag(boolean flag) {
        this.flag = flag;
    }
}