<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-core</artifactId>
<version>5.6.5</version>
</dependency>
/**
* 导出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配置中心配置,这样更加灵活,这里是为了方便测试所以写死在代码中.
/**
* 压缩文件列表
*
* @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;
}
对应模板图上的权限信息 备注与合规要求 变量
/**
* crm用户信息导出
*
* @param response 响应
* @param ids id
* @throws IOException IO异常
* @throws ExecutionException 执行异常
* @throws InterruptedException 中断异常
*/
void crmUserInfoExport(HttpServletResponse response , List<Long> ids) throws IOException, ExecutionException, InterruptedException;
@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);
}
}
}
/**
* 单体变量数据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;
}
/**
* 导出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");
}
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());
}
}
暂时待定…