easyExcel学习封装使用-excel导出(1)

最近发现了easyExcel工具封装得很好用，为了融入自己的项目进行简单的封装，
如果有更好的方式，欢迎大家点评更正

创建excel写的工具类

public class EasyExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);
    /**
     * 导出excel简单通用的方法
     * @param easyExcelBuilder  基础参数
     * @param obj  对应的实体类
     * @param data  实体对应的数据集
     * @return
     */
    public static boolean writeExcel(EasyExcelBuilder easyExcelBuilder, Class<?> obj, List<List<?>> data){

        try{

            String filePath =  easyExcelBuilder.getFilePath() + easyExcelBuilder.getFileName();
            ExcelWriter excelWriter = EasyExcel.write(filePath, obj).build();
            int flag = 0;
            List<Set<String>> includeColumnFiledNames = easyExcelBuilder.getIncludeColumnFiledNames();
            if(includeColumnFiledNames != null){

            }
            List<Set<String>> excludeColumnFiledNames = easyExcelBuilder.getExcludeColumnFiledNames();
            for (String sheetName : easyExcelBuilder.getSheetNames()) {
                ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet(flag, sheetName).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
                List<?> objects = data.get(flag);
                //导入特定的字段
                if(includeColumnFiledNames != null){
                    if(flag < includeColumnFiledNames.size()){
                        Set<String> includeColumnsName = includeColumnFiledNames.get(flag);
                        excelWriterSheetBuilder.includeColumnFiledNames(includeColumnsName);
                    }
                }else if(excludeColumnFiledNames != null){
                    if(flag < excludeColumnFiledNames.size()){
                        Set<String> excludeColumnName = excludeColumnFiledNames.get(flag);
                        excelWriterSheetBuilder.excludeColumnFiledNames(excludeColumnName);
                    }
                }
                // 头的策略
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                // 背景设置
                headWriteCellStyle.setFillForegroundColor(easyExcelBuilder.getHeadColor().index);
                // 内容的策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                if(easyExcelBuilder.getContentColor() != null){
                    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
                    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                    contentWriteCellStyle.setFillForegroundColor(easyExcelBuilder.getContentColor().index);
                }
                // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
                HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
                excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);
                excelWriter.write(objects, excelWriterSheetBuilder.build());
                flag++;
            }
            /// 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();

            //需要同步到oss
            String ossPath = easyExcelBuilder.getOssPath();
            if(!StringUtils.isEmpty(ossPath)){
                if (ossPath.startsWith("/")) {
                    ossPath = ossPath.substring(1);
                }
                ///######## 该处为上传文件的逻辑 上传完文件会进行删除原文件
//                AliOssClientUtil.upload(filePath, ossPath + easyExcelBuilder.getFileName());
                //删除本地文件
                try{
                    new File(filePath).delete();
                }catch (Exception ex){
                    logger.error("EasyExcelUtil, deleted file error", ex);
                }
            }
            return true;
        }catch (Exception e){
            logger.error("EasyExcelUtil,writeExcel, error : {}", JSONObject.toJSONString(easyExcelBuilder), e);
        }
        return false;
    }
}

EasyExcelBuilder 导入基础数据的创建者

public class EasyExcelBuilder {

    /**
     * 文件名称 （最好加上后缀）
     */
    private String fileName = "模板.xlsx";
    /**
     * sheet
     */
    private List<String> sheetNames = Collections.singletonList("模板");
    /**
     * 数据类型对应的数据
     */
    private List<List<String>> dataModels;

    /**
     * 特定字段导出支持多个sheet 如果有这个数据即会忽略excludeColumnFiledNames
     */
    private List<Set<String>> includeColumnFiledNames;
    /**
     * 忽略特定字段导出 支持多个sheet、
     */
    private List<Set<String>> excludeColumnFiledNames;

    /**
     * 阿里oos存储路径（不包含文件名）无该路径时默认不上传到oos
     */
    private String ossPath;

    /** 服务器文件路径 */
    private String filePath = "/data/export/excel/";

    /** 表头颜色 目前只支持统一颜色 */
    private IndexedColors headColor = IndexedColors.GREY_25_PERCENT;

    /** 内容颜色 目前也只支持统一颜色 */
    private IndexedColors contentColor;


    public EasyExcelBuilder buildFileName(String fileName) {
        String newFileName = fileName.replaceAll("/", "");
        if (!newFileName.contains(".")) {
            newFileName += ".xlsx";
        }
        this.fileName = newFileName;
        return this;
    }

    public EasyExcelBuilder buildSheetName(List<String> sheetNames) {
        this.sheetNames = sheetNames;
        return this;
    }

    public EasyExcelBuilder buildDataModels(List<List<String>> dataModels) {
        this.dataModels = dataModels;
        return this;
    }

    public EasyExcelBuilder buildExcludeColumnFiledNames(List<Set<String>> excludeColumnFiledNames) {
        this.excludeColumnFiledNames = excludeColumnFiledNames;
        return this;
    }

    public EasyExcelBuilder buildIncludeColumnFiledNames(List<Set<String>> includeColumnFiledNames) {
        this.includeColumnFiledNames = includeColumnFiledNames;
        return this;
    }

    public EasyExcelBuilder buildOssPath(String ossPath) {
        this.ossPath = ossPath;
        return this;
    }

    public EasyExcelBuilder buildFilePath(String filePath) {
        this.filePath = filePath;
        return this;
    }

    public EasyExcelBuilder buildHeadColor(IndexedColors headColor) {
        this.headColor = headColor;
        return this;
    }

    public EasyExcelBuilder buildContentColor(IndexedColors contentColor) {
        this.contentColor = contentColor;
        return this;
    }
    public EasyExcelBuilder build() {
        return this;
    }

    public String getFileName() {
        return fileName;
    }

    public List<String> getSheetNames() {
        return sheetNames;
    }

    public List<List<String>> getDataModels() {
        return dataModels;
    }

    public List<Set<String>> getIncludeColumnFiledNames() {
        return includeColumnFiledNames;
    }

    public List<Set<String>> getExcludeColumnFiledNames() {
        return excludeColumnFiledNames;
    }

    public String getOssPath() {
        return ossPath;
    }

    public String getFilePath() {
        return filePath;
    }

    public IndexedColors getHeadColor() {
        return headColor;
    }

    public IndexedColors getContentColor() {
        return contentColor;
    }
}

接下来的test类 可以根据需要进行build, 如果需要上传到服务器或者oss等地方 只需要 添加.buildOssPath(“oss的路径”) （oss需要自行配置）

 @Test
    public void writeExcel(){
        //设置需要导出的字段名
        List<Set<String>> includeColumnFiledNamesList = new ArrayList<>();
        Set<String> includeColumnFiledNames = new HashSet<String>(){{add("name");add("phone");}};
        includeColumnFiledNamesList.add(includeColumnFiledNames);
        Set<String> includeColumnFiledNames1 = new HashSet<String>(){{add("name");add("phone");add("email");add("address");}};
        includeColumnFiledNamesList.add(includeColumnFiledNames1);


        //根据需要设置模式
        EasyExcelBuilder write = new EasyExcelBuilder()
                .buildFileName("文件名称")
                .buildSheetName(Arrays.asList("第一个sheet","第二个sheet"))
//                .buildOssPath("excel/default/201906/")
                .buildIncludeColumnFiledNames(includeColumnFiledNamesList)
//                .buildExcludeColumnFiledNames(includeColumnFiledNamesList)
                .buildFilePath("/Users/xuxiong/Downloads/")
                .buildHeadColor(IndexedColors.LIGHT_YELLOW)
//                .buildContentColor(IndexedColors.RED)
                .build();
        List<List<?>> data = new ArrayList<>();
        data.add(data("a列"));
        data.add(data("b列"));
        EasyExcelUtil.writeExcel(write, Demo.class, data);
    }
    private static List<Demo> data(String text){
        List<Demo> demos = new ArrayList<>();
        Demo demo;
        for(int i = 0; i < 10; i++){
            demo = new Demo();
            demo.setName("name" + i + "_"+text);
            demo.setAge(i);
            demo.setAddress("address" + i+ "_"+text);
            demo.setEmail("email" + i+ "_"+text);
            demo.setPhone("phone" + i+ "_"+text);
            demos.add(demo);
        }
        return demos;
    }

导出的截图