EasyExcel Export

用户8889406

发布于 2023-03-05 15:24:33

4K0

发布于 2023-03-05 15:24:33

文章被收录于专栏：小呙同学

导入依赖

easyexcel 依赖

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

hutool 依赖

    <dependency>
		<groupId>cn.hutool</groupId>
		<artifactId>hutool-core</artifactId>
		<version>5.6.5</version>
	</dependency>

官方demo

官方-快速开始

按模板导出

模板图

工具类代码

模板填充导出CRM客户信息

/**
 * 导出CRM客户信息
 *
 * @param list1     列表1
 * @param list2     列表2
 * @param dataMap  数据map
 * @param xlsxName 文件名称
 */
public static void exportCrmUserInfoList(String path, String xlsxName, List<?> list1, List<?> list2, Map<Object, Object> dataMap) throws IOException {
    //TODO 后续改为动态获取
    String templateFileName = "/Users/xguo/Desktop/template2.xlsx";
    String fileName = path + "/" + xlsxName + ".xlsx";
    //获取excelWriter实例
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    //填充对应变量
    excelWriter.fill(dataMap, writeSheet);
    //填充列表
    excelWriter.fill(new FillWrapper("data1",list1),fillConfig, writeSheet);
    excelWriter.fill(new FillWrapper("data2",list2), fillConfig,writeSheet);
    //关闭流
    excelWriter.finish();
}

模板路径可以放在项目resources下,也可以通过nacos配置中心配置,这样更加灵活,这里是为了方便测试所以写死在代码中.

压缩文件列表为zip（百度copy的）

/**
 * 压缩文件列表
 *
 * @param zipOutName   zip输出路径
 * @param fileNameList 文件路径列表
 * @throws IOException          IO异常
 * @throws ExecutionException   执行异常
 * @throws InterruptedException 中断异常
 */
public static void compressFileList(String zipOutName, List<String> fileNameList) throws IOException, ExecutionException, InterruptedException {
    ThreadFactory factory = new ThreadFactoryBuilder().setNamePrefix("compressFileList-pool-").build();
    ExecutorService executor = new ThreadPoolExecutor(5, 10, 60, TimeUnit.SECONDS, new LinkedBlockingQueue<>(20), factory);
    ParallelScatterZipCreator parallelScatterZipCreator = new ParallelScatterZipCreator(executor);
    OutputStream outputStream = new FileOutputStream(zipOutName);
    ZipArchiveOutputStream zipArchiveOutputStream = new ZipArchiveOutputStream(outputStream);
    zipArchiveOutputStream.setEncoding("UTF-8");
    for (String fileName : fileNameList) {
        File inFile = new File(fileName);
        final InputStreamSupplier inputStreamSupplier = () -> {
            try {
                return new FileInputStream(inFile);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                return new NullInputStream(0);
            }
        };
        ZipArchiveEntry zipArchiveEntry = new ZipArchiveEntry(inFile.getName());
        zipArchiveEntry.setMethod(ZipArchiveEntry.DEFLATED);
        zipArchiveEntry.setSize(inFile.length());
        zipArchiveEntry.setUnixMode(UnixStat.FILE_FLAG | 436);
        parallelScatterZipCreator.addArchiveEntry(zipArchiveEntry, inputStreamSupplier);
    }
    parallelScatterZipCreator.writeTo(zipArchiveOutputStream);
    zipArchiveOutputStream.close();
    outputStream.close();
    log.info("ParallelCompressUtil->ParallelCompressUtil-> info:{}", JSONObject.toJSONString(parallelScatterZipCreator.getStatisticsMessage()));
}

输出文件流给前端下载

/**
 * 输出文件以流的形式传给前端下载
 *
 * @param filePath 文件路径
 * @param response 响应
 */
public static void exportFileStream(String filePath, HttpServletResponse response) {
    File file = new File(filePath);
    if (!file.exists()) {
        //文件不存在,提示404 - 请求的资源（网页等）不存在
        response.setStatus(HttpServletResponse.SC_NOT_FOUND);
    }
    try (FileInputStream is = new FileInputStream(file); OutputStream os = response.getOutputStream()) {
        response.setCharacterEncoding(CharEncoding.UTF_8);
        response.addHeader("Content-Disposition", "attachment;filename=" + file.getName().getBytes());
        response.addHeader("Content-Length", "" + file.length());
        response.setContentType("application/x-zip-compressed");
        os.write(IoUtil.readBytes(is));
    } catch (IOException e) {
        log.error("文件传输失败:{}", e.getMessage());
    }
}

实体类代码

变量对应实体类

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * crm用户信息导出视图对象
 *
 * @author xGuo
 * @date 2022/06/28
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel("crm用户信息导出视图对象")
public class CrmUserInfoExportVo {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("客户资金账号")
    private String account;

    @ApiModelProperty("客户手机号码")
    private String phone;

    @ApiModelProperty("联系地址")
    private String address;

    @ApiModelProperty("身份证号码")
    private String idCard;

    @ApiModelProperty("身份证有效期")
    private String idCardValidTime;

    @ApiModelProperty("序号")
    private String id;

    @ApiModelProperty("合同状态")
    private String state;

    @ApiModelProperty("合同有效期")
    private String contractValidTime;
}

对应模板图上的客户基本情况 合同信息 变量

列表填充对应实体类

import lombok.Data;

import java.util.Date;

/**
 * 列表填充对象
 *
 * @author xGuo
 * @date 2022/06/28
 */
@Data
public class ListFill {
    /**
     * 标题1
     */
    private String title;
    /**
     * 社区
     */
    private String room;
    /**
     * 标题2
     */
    private String title1;
    /**
     * 开通时间
     */
    private Date openTime;
    /**
     * 标题3
     */
    private String title2;
    /**
     * 用户名称
     */
    private String user;
    /**
     * 备注
     */
    private String remarks;
}

对应模板图上的权限信息 备注与合规要求 变量

业务逻辑代码

Service层

/**
 * crm用户信息导出
 *
 * @param response 响应
 * @param ids      id
 * @throws IOException          IO异常
 * @throws ExecutionException   执行异常
 * @throws InterruptedException 中断异常
 */
void crmUserInfoExport(HttpServletResponse response , List<Long> ids) throws IOException, ExecutionException, InterruptedException;

ServiceImpl层

@Override
public void crmUserInfoExport(HttpServletResponse response, List<Long> ids) throws IOException, ExecutionException, InterruptedException {
    if (ids != null && ids.size() > 0) {
        //固定目录
        String parentPath = "/Users/xguo/Desktop/" + "crmFile/";
        // String path = PropertiesConfig.UPLOAD_PATH + "/crmFile/";
        String childPath = String.valueOf(System.currentTimeMillis());
        //存放路径
        String path = parentPath + childPath;
        //不存在即创建
        File pathFile = new File(path);
        if (!pathFile.exists()) {
            pathFile.mkdirs();
        }
        try {
            for (Long id : ids) {
                //获取合同信息
                UserCustomAuthorize validContractInfo = userCustomAuthorizeMapper.getUserValidContractInfo(id);
                //获取权限信息
                List<RoomAuthorize> authorizeList = roomAuthorizeMapper.getByUserId(id);
                //获取合同备注
                List<String> remarksList = userCustomAuthorizeMapper.getRemarksByUserId(id);
                try {
                    //模板填充Excel导出
                    EasyExcelUtil.exportCrmUserInfoList(path, validContractInfo.getName(), date1(authorizeList), date2(remarksList), getMap(validContractInfo));
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
            //获取文件目录下的文件路径列表
            List<String> listFileNames = FileUtil.listFileNames(path);
            List<String> fileNames = new ArrayList<>();
            for (String fileName : listFileNames) {
                fileNames.add(path + "/" + fileName);
            }
            //压缩文件列表
            EasyExcelUtil.compressFileList(path + ".zip", fileNames);
            //输出文件以流的形式传给前端下载
            EasyExcelUtil.exportFileStream(path + ".zip", response);
        }catch (Exception e){
            log.error("CRM客户信息下载失败,失败原因:{}",e.getMessage());
        }finally {
            //删除文件
            FileUtil.del(path + ".zip");
            FileUtil.del(path);
        }
    }
}

ServiceImpl层相关方法

/**
 * 单体变量数据map
 *
 * @param validContractInfo 有效合同信息
 * @return {@link Map}<{@link Object}, {@link Object}>
 */
private Map<Object, Object> getMap(UserCustomAuthorize validContractInfo) {
    Map<Object, Object> map = MapUtils.newHashMap();
    //客户基本信息
    map.put("account", validContractInfo.getFuturesAccount());
    map.put("phone", validContractInfo.getPhone());
    map.put("address", validContractInfo.getAddress());
    map.put("idCard", validContractInfo.getIdCard());
    map.put("idCardValidTime", validContractInfo.getEndTime());
    //合同信息
    map.put("id", validContractInfo.getId());
    map.put("state", validContractInfo.getAuthState());
    map.put("contractValidTime", validContractInfo.getEndTime());
    return map;
}



/**
 * 权限信息数据列表
 *
 * @param authorizeList 授权列表
 * @return {@link List}<{@link ListFill}>
 */
private List<ListFill> date1(List<RoomAuthorize> authorizeList) {
    List<ListFill> data1 = new ArrayList<>();
    for (int i = 0; i < authorizeList.size(); i++) {
        ListFill fill = new ListFill();
        fill.setTitle("开通社区"+(i+1));
        fill.setRoom(authorizeList.get(i).getRoomName());
        fill.setTitle1("开通日期");
        fill.setOpenTime(authorizeList.get(i).getAddTime());
        fill.setTitle2("开通人");
        fill.setUser(authorizeList.get(i).getAddPerson());
        data1.add(fill);
    }
    return data1;
}


//    /**
//     * 权限信息数据列表
//     *
//     * @param authorizeList 授权列表
//     * @return {@link List}<{@link ListFill}>
//     */
//    private List<ListFill> date1(List<RoomAuthorize> authorizeList) {
//        List<ListFill> data1 = new ArrayList<>();
//        if (authorizeList != null && authorizeList.size() > 0) {
//            for (int i = 0; i < authorizeList.size(); i++) {
//                ListFill fill = new ListFill();
//                fill.setTitle("开通社区" + (i + 1));
//                fill.setRoom(Optional.of(authorizeList.get(i).getRoomName()).orElse(""));
//                fill.setTitle1("开通日期");
//                fill.setOpenTime(Optional.of(authorizeList.get(i).getAddTime().toInstant().atZone(ZoneId.systemDefault()).toLocalDate().toString()).orElse(""));
//                fill.setTitle2("开通人");
//                fill.setUser(Optional.of(authorizeList.get(i).getAddPerson()).orElse(""));
//                data1.add(fill);
//            }
//        }
//        return data1;
//    }

/**
 * 备注数据列表
 *
 * @param remarksList 备注列表
 * @return {@link List}<{@link ListFill}>
 */
private List<ListFill> date2(List<String> remarksList) {
    List<ListFill> data2 = new ArrayList<>();
    remarksList.forEach(remarks -> {
        ListFill fill = new ListFill();
        fill.setRemarks(remarks);
        data2.add(fill);
    });
    return data2;
}

Controller层

/**
 * 导出CRM客户信息
 */
@GetMapping("/exportCrm")
public void exportCrm(HttpServletResponse response,@RequestParam("ids") List<Long> ids) throws IOException, ExecutionException, InterruptedException {
    long startTime=System.currentTimeMillis();
    iUsersService.crmUserInfoExport(response, ids);
    long endTime=System.currentTimeMillis();
    System.out.println("程序运行时间： "+(endTime-startTime)+"ms");
}

导出Excel展示

不同对象导出多Sheet

导出实体类

主实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModel;
import lombok.*;

import java.util.Date;

/**
 * 投询授权统计导出视图对象
 *
 * @author xGuo
 * @date 2022/06/30
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel("投询授权统计导出视图对象")
public class AuthorizeContractExportVo {

    private static final long serialVersionUID = 1L;

    @ExcelProperty(value = "合同ID",order = 1)
    @ColumnWidth(15)
    private Long id;

    @ExcelProperty(value = "用户名",order = 2)
    @ColumnWidth(15)
    private String name;

    @ExcelProperty(value = "资金账号",order = 3)
    @ColumnWidth(15)
    private String futuresAccount;

    @ExcelProperty(value = "签署日期",order = 4)
    @ColumnWidth(25)
    private Date addTime;

    @ExcelProperty(value = "过期时间",order = 5)
    @ColumnWidth(25)
    private Date endTime;
}

子实体类（添加扩展字段）

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModel;
import lombok.*;

import java.util.Date;

/**
 * 投询授权统计导出视图对象
 *
 * @author xGuo
 * @date 2022/06/30
 */
@EqualsAndHashCode(callSuper = true)
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("投询授权统计导出视图对象")
public class AuthorizeCancelExportVo extends AuthorizeContractExportVo {

    @ExcelProperty(value = "退订/终止时间",order = 6)
    @ColumnWidth(25)
    private Date updateTime;

    @ExcelProperty(value = "退订/终止原因",order = 7)
    @ColumnWidth(25)
    private String remarks;
}

实现方法

@Override
    public void exportAuthorizeCount(HttpServletResponse response) {
        //设置输入流，设置响应域
        response.setCharacterEncoding(CharEncoding.UTF_8);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        //状态map
        Map<String, String> stateMap = new LinkedHashMap<>();
        stateMap.put("all", "总授权用户");
        stateMap.put("valid", "有效用户");
        stateMap.put("noRoom", "无社区用户");
        stateMap.put("soon", "即将到期用户");
        stateMap.put("expire7", "已过期用户(7天)");
        stateMap.put("expire30", "已过期用户(30天)");
        stateMap.put("stop", "终止用户");
        stateMap.put("cancel", "退订用户");

        // 不同的对象,写到不同的sheet
        try (ExcelWriter excelWriter = EasyExcel.write(new BufferedOutputStream(response.getOutputStream()), AuthorizeContractExportVo.class).build()) {
            int index = 0;
            for (String key : stateMap.keySet()) {
                //根据状态获取对应的数据列表
                List<UserCustomAuthorize> authorizes = getBaseMapper().getByAuthState(key);
                //终止和退订的单独处理
                if ("stop".equals(key) || "cancel".equals(key)) {
                //这里注意.head()方法，这是插入不同实体类的关键，不使用该方法是无法获取扩展字段的。
                    WriteSheet build = EasyExcel.writerSheet(index, stateMap.get(key)).head(AuthorizeCancelExportVo.class).build();
                    List<AuthorizeCancelExportVo> cancelExportVos = BeanUtil.copyToList(authorizes, AuthorizeCancelExportVo.class, new CopyOptions());
                    excelWriter.write(cancelExportVos, build);
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(index, stateMap.get(key)).build();
                List<AuthorizeContractExportVo> exportVos = BeanUtil.copyToList(authorizes, AuthorizeContractExportVo.class, new CopyOptions());
                excelWriter.write(exportVos, writeSheet);

                index++;
            }
        } catch (IOException e) {
            log.error("export error:{}",e.getMessage());
        }
    }

导出示意图

按主实体类导出的sheet

按子实体类导出的sheet

其他的导出方法

暂时待定…

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

编程算法

登录后参与评论

0 条评论

热度

EasyExcel Export

EasyExcel Export

导入依赖

easyexcel 依赖

hutool 依赖

官方demo

按模板导出

模板图

工具类代码

模板填充导出CRM客户信息

压缩文件列表为zip（百度copy的）

输出文件流给前端下载

实体类代码

变量对应实体类

列表填充对应实体类

业务逻辑代码

Service层

ServiceImpl层

ServiceImpl层相关方法

Controller层

导出Excel展示

不同对象导出多Sheet

导出实体类

主实体类

子实体类（添加扩展字段）

实现方法

导出示意图

按主实体类导出的sheet

按子实体类导出的sheet

其他的导出方法

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐