EasyExcel多sheet的导入和导出

EasyExcel基础例子

EasyExcel对于导入导出的操作十分简洁，方便快捷。其中对于多单文件多sheet的操作比较不同，在此做一下记录

导入数据

// 例子一
public void importDetail(@RequestParam(value = "file") MultipartFile serviceFile) throws IOException {
    ExcelReader excelReader = null;
    InputStream in = null;
    try {
        in = serviceFile.getInputStream();
        ExcelListener excelListener = new ExcelListener();
        excelReader = EasyExcel.read(in, DetailVO.class, excelListener).build();
        //获取sheet0对象
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        //读取数据
        excelReader.read(readSheet);
        DetailVO detail = excelListener.getData();
        //清空list数据
        excelListener.getData().clear();
        //获取sheet1对象
        ReadSheet readDetailSheet = EasyExcel.readSheet(1).build();
        //读取数据
        excelReader.read(readDetailSheet);
        List<PurchaseDetailFeignParam> purchaseDetailFeignParam = ConverterUtils.convertList(excelListener.getData(), PurchaseDetailFeignParam.class);
    } catch (IOException ex) {
        logger.error("import excel to db fail", ex);
    } finally {
        in.close();
        // 这里一定别忘记关闭，读的时候会创建临时文件，到时磁盘会崩
        if (excelReader != null) {
            excelReader.finish();
        }
    }
}

// 方法二简化版
public RestResult importThSupplier(@RequestParam("file") MultipartFile excelFile) {
        List<SupplierImport> supplierImports = null;
        try {
            if (null == excelFile) {
                return RestResult.error("导入文件有误");
            } else {
                supplierImports = ExcelUtil.readExcel(excelFile.getInputStream(), SupplierVo.class);
                if (null == supplierImports || supplierImports.size() == 0) {
                    return RestResult.error("表格为空");
                }
                // 根据实际情况对数据校验或批量出入
                if (supplierImports .size() > 0) {
                    thSupplierService.insertBatch(supplierList);
                }
            }
            return RestResult.ok("导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            return RestResult.error("导入失败: " + e.getMessage());
        }
    }


// ExcelUtil.java
public static <T> List<T> readExcel(InputStream multipartIs, Class<?> model) {
        ExcelListener<T> excelListener = new ExcelListener<>();
        try (InputStream inputStream = new BufferedInputStream(multipartIs)) {
            EasyExcel.read(inputStream, model, excelListener).sheet(0).headRowNumber(1).doRead();
        } catch (Exception e) {
            log.error("catch exception:", e);
        }
        return excelListener.getData();
    }

// ExcelListener.java
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

public class ExcelListener<T> extends AnalysisEventListener {
    private List<T> data = new ArrayList<>();

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "ExcelListener{" +
                "data=" + data +
                '}';
    }

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        T t = (T) o;
        data.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

导出为多sheet

public void export(HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            String fileName = URLEncoder.encode("template", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //新建ExcelWriter 
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            //获取sheet0对象
            WriteSheet mainSheet = EasyExcel.writerSheet(0, "采购单").head(PurchaseVO.class).build();
            //向sheet0写入数据 传入空list这样只导出表头
            excelWriter.write(Lists.newArrayList(),mainSheet);
            //获取sheet1对象
            WriteSheet detailSheet = EasyExcel.writerSheet(1, "采购单明细").head(PurchaseDetailVO.class).build();
            //向sheet1写入数据 传入空list这样只导出表头
            excelWriter.write(Lists.newArrayList(),detailSheet);
            //关闭流
            excelWriter.finish();
        } catch (IOException e) {
            logger.error("导出异常{}", e.getMessage());
        }
    }

多sheet多表头导出

public void export(HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            String fileName = URLEncoder.encode("XX记录表", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //新建ExcelWriter
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            //获取sheet对象
            WriteSheet sheet = EasyExcel.writerSheet(0, "总表").head(SumExportDto.class).build();
            //向sheet中导入数据
            List<SumExportDto> data = weeklyService.Export();
			//多表头导出
			//第一个表默认是有表头的
            WriteTable table1 = EasyExcel.writerTable(0).needHead(Boolean.FALSE).build();
            excelWriter.write(data, sheet, table1);
            
            WriteTable table2 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
            excelWriter.write(data, sheet,table2);
			...
			...
            excelWriter.finish();
        } catch (IOException e) {
            log.error("导出异常{}", e.getMessage());
        }
    }

——仅供参考