Controller代码
@ApiOperation(value = "通用导出Excel文件")
@GetMapping("/commonExportExcelFile")
public ResponseData<Object> commonExportExcelFile(@RequestParam("exportExcelRows") int exportExcelRows, HttpServletResponse response) {
return simulateService.commonExportExcelFile(response);
}
Service Impl代码
@Override
public ResponseData<Object> commonExportExcelFile(int exportExcelRows, HttpServletResponse response) {
List<EntityExcel> dimensionDataList = CompletableFuture.supplyAsync(() -> dimService.createDimensionDataList(exportExcelRows)).join();
List<String> dimTypeList = Arrays.asList(ConstEnum.DimType.DIM_TYPE_CURRENCY,
ConstEnum.DimType.DIM_TYPE_CONSUME, ConstEnum.DimType.DIM_TYPE_COUNTRIES,
ConstEnum.DimType.DIM_TYPE_TRANSPORT, ConstEnum.DimType.DIM_TYPE_TOUR_SIGHTS);
CommonExportExcelFile.commonMethod(response, EntityExcel.class, dimensionDataList, dimTypeList);
return ResponseData.success(dimensionDataList.size());
}
/**
* 创建维度数据列表
*
* @param size 大小
* @return {@code List<EntityExcel>}
*/
public List<EntityExcel> createDimensionDataList(Integer size) {
List<DimensionDTO> dimInfosFields = dimInfosMapper.queryDimTypeCodeNameId();
Map<String, Map<String, Long>> dimInfoMap = dimInfosFields.parallelStream().collect(
groupingBy(DimensionDTO::getTypeCode, toMap(DimensionDTO::getCodeAndName, DimensionDTO::getObjectId, (exsit, repeat) -> repeat)));
Map<String, Long> currencyMap = dimInfoMap.get(ConstEnum.DimType.DIM_TYPE_CURRENCY);
Object[] currencyIdArray = currencyMap.values().toArray();
Map<String, Long> consumeMap = dimInfoMap.get(ConstEnum.DimType.DIM_TYPE_CONSUME);
Object[] consumeIdArray = consumeMap.values().toArray();
Map<String, Long> countriesMap = dimInfoMap.get(ConstEnum.DimType.DIM_TYPE_COUNTRIES);
Object[] countriesIdArray = countriesMap.values().toArray();
Map<String, Long> transportMap = dimInfoMap.get(ConstEnum.DimType.DIM_TYPE_TRANSPORT);
Object[] transportIdArray = transportMap.values().toArray();
Map<String, Long> tourSightsMap = dimInfoMap.get(ConstEnum.DimType.DIM_TYPE_TOUR_SIGHTS);
Object[] tourSightsIdArray = tourSightsMap.values().toArray();
int partSize = 100000;
List<EntityExcel> excelList = new ArrayList<>();
if (size > partSize) {
int count = (int) Math.ceil((double) size / partSize);
List<CompletableFuture<List<EntityExcel>>> futureList = new ArrayList<>(count);
for (int i = 0; i < count; i++) {
futureList.add(CompletableFuture.supplyAsync(() -> getDimensionDataList(partSize, consumeIdArray, countriesIdArray,
transportIdArray, currencyIdArray, tourSightsIdArray), GlobalExecutor.getInstance()));
}
CompletableFuture.allOf(futureList.toArray(new CompletableFuture[0])).join();
for (CompletableFuture<List<EntityExcel>> future : futureList) {
try {
excelList.addAll(future.get());
} catch (InterruptedException | ExecutionException e) {
throw new RuntimeException(e);
}
}
} else {
excelList = CompletableFuture.supplyAsync(() -> getDimensionDataList(size, consumeIdArray, countriesIdArray,
transportIdArray, currencyIdArray, tourSightsIdArray), GlobalExecutor.getInstance()).join();
}
return excelList;
}
具体实现类
/**
* 导出Excel文件抽象类
*/
@Component
public class AbstractExportExcelFile<T> {
private static final int DEFAULT_SIZE = 10000;
private static final int USE_CSV_SIZE = 500000;
private static final int CSV_PART_SIZE = 100000;
private static Map<String, String> dimensionMap;
private static final ThreadLocal<SimpleDateFormat> simpleDateFormatThreadLocal = ThreadLocal.withInitial(() -> new SimpleDateFormat(DatePattern.CHINESE_DATE_TIME_PATTERN));
public AbstractExportExcelFile(HttpServletResponse response, Class<?> clazz, List<T> list, List<String> dimTypeList) throws IOException {
// 加载维度
CompletableFuture.runAsync(() -> loadDimensions(dimTypeList), GlobalExecutor.getInstance()).join();
int size = list.size();
if (size < DEFAULT_SIZE) {
// 维度转换
dimensionConvert(list);
// 导出 Excel 文件
CommonUtils.exportExcelFile(response, clazz, list);
} else if (size > DEFAULT_SIZE && size < USE_CSV_SIZE) {
// 转换
List<List<T>> partitionedList = ListUtils.partition(list, DEFAULT_SIZE);
for (List<T> part : partitionedList) {
// 维度转换
dimensionConvert(part);
}
// zip excel 导出
zipExcelExport(response, partitionedList, clazz);
} else if (size > USE_CSV_SIZE) {
// 获取 CSV 内容字符串列表
List<String[]> csvContentStringList = getCsvContentStringList(list);
// 获取 CSV 标题字符串数组
String[] csvTitleStringArray = getCsvTitleStringArray(list);
// zip CSV 导出
zipCsvExport(response, csvContentStringList, csvTitleStringArray);
}
dimensionMap.clear();
simpleDateFormatThreadLocal.remove();
}
/**
* zip CSV 导出
*
* @param response 响应
* @param contentStringList 内容字符串列表
* @param titleList 标题字符串数组
*/
public void zipCsvExport(HttpServletResponse response, List<String[]> contentStringList, String[] titleList) {
// 创建一个字节数组输出流，用于将CSV字符串压缩为ZIP文件
try (ZipOutputStream zipOut = new ZipOutputStream(CommonUtils.setGetZipResponse(response, "导出CSV压缩包"))) {
List<List<String[]>> contentLists = ListUtils.partition(contentStringList, CSV_PART_SIZE);
for (List<String[]> contentList : contentLists) {
csvWriteToZipOutStream(titleList, contentList, zipOut);
}
} catch (Exception e) {
throw new CustomException(e);
}
}
/**
* 获取 CSV 标题字符串数组
*
* @param list 列表
* @return {@code String[]}
*/
private static <T> String[] getCsvTitleStringArray(List<T> list) {
// 获取标题
T t = list.get(0);
Field[] fields = t.getClass().getDeclaredFields();
String[] title = new String[fields.length];
for (int i = 0; i < fields.length; i ++) {
Field field = fields[i];
field.setAccessible(true);
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null && excelProperty.value()[0] != null) {
title[i] = excelProperty.value()[0];
}
}
return title;
}
/**
* 获取 CSV 内容字符串列表
*
* @param list 列表
* @return {@code List<String[]>}
*/
private List<String[]> getCsvContentStringList(List<T> list) {
// 获取内容
List<String[]> listString = Collections.synchronizedList(new ArrayList<>(list.size()));
list.parallelStream().forEach(t -> {
Field[] fields = t.getClass().getDeclaredFields();
String[] array = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
// 维度转换和格式化
array[i] = dimensionConvertAndFormat(field, t);
}
listString.add(array);
});
return listString;
}
/**
* 维度转换和格式化
*
* @param field 字段
* @return {@code String}
*/
private String dimensionConvertAndFormat(Field field, T t) {
field.setAccessible(true);
Object object;
try {
object = field.get(t);
} catch (IllegalAccessException e) {
throw new CustomException(e);
}
String value = ObjectUtils.isNotEmpty(object) ? object.toString() : StringUtils.EMPTY;
String finalValue;
Relation relation = field.getAnnotation(Relation.class);
if (relation != null && StringUtils.isNotBlank(relation.type())) {
finalValue = assignmentFinalValue(value, relation.type());
} else {
finalValue = value;
}
return finalValue;
}
private static String assignmentFinalValue(String value, String type) {
String finalValue;
switch (type) {
case ConstEnum.FieldType.DIM -> {
String orDefault = dimensionMap.getOrDefault(value, "");
if (StringUtils.isBlank(orDefault)) {
throw new CustomException(value + "查询维度名称为空");
}
finalValue = orDefault;
}
case ConstEnum.FieldType.DATETIME -> {
Date date = DateUtil.date(Long.parseLong(value));
finalValue = simpleDateFormatThreadLocal.get().format(date);
}
case ConstEnum.FieldType.AMOUNT -> {
if (value.contains("|")) {
String[] values = value.split("\|");
if (values.length == 2) {
String orDefault = dimensionMap.getOrDefault(values[0], "");
if (StringUtils.isBlank(orDefault)) {
throw new CustomException(value + "查询维度名称为空");
}
finalValue = orDefault + " " + values[1];
} else {
throw new CustomException(String.format("【%s】币种和金额需要用'|'分隔", value));
}
} else {
throw new CustomException(String.format("【%s】币种和金额没有用'|'分隔", value));
}
}
default -> finalValue = value;
}
return finalValue;
}
/**
* zip excel 导出
*
* @param response 响应
* @param partitionedList 分区列表
* @param clazz 克拉兹
*/
public void zipExcelExport(HttpServletResponse response, List<List<T>> partitionedList, Class<?> clazz) {
try (ServletOutputStream os = CommonUtils.setGetZipResponse(response, "导出Excel压缩包");
ZipOutputStream zipOut = new ZipOutputStream(new BufferedOutputStream(os))) {
partitionedList.parallelStream().forEach(list -> {
try {
ByteArrayOutputStream baOut = new ByteArrayOutputStream();
EasyExcelFactory.write(baOut, clazz).sheet().doWrite(list);
ZipEntry entry = new ZipEntry(UUID.randomUUID() + ".xlsx");
synchronized (zipOut) {
zipOut.putNextEntry(entry);
zipOut.write(baOut.toByteArray());
zipOut.closeEntry();
}
baOut.close();
} catch (IOException e) {
throw new CustomException(e);
}
});
} catch (IOException e) {
throw new CustomException(e);
}
}
/**
* 维度转换
*
* @param dimensionDataList 维度数据列表
*/
private void dimensionConvert(List<T> dimensionDataList) {
dimensionDataList.parallelStream().forEach(excel -> {
Field[] fields = excel.getClass().getDeclaredFields();
for (Field field : fields) {
try {
// 维度转换和格式化
field.set(excel, dimensionConvertAndFormat(field, excel));
} catch (IllegalAccessException e) {
throw new CustomException(e);
}
}
});
}
/**
* 加载维度
*
* @param dimTypeList DIM 类型列表
*/
public static void loadDimensions(List<String> dimTypeList) {
DimInfosMapper dimInfosMapper = SpringUtils.getBean(DimInfosMapper.class);
List<DimensionInfoDTO> dimInfosList = dimInfosMapper.queryDimIdName(dimTypeList);
dimensionMap = dimInfosList.parallelStream().collect(toMap(DimensionInfoDTO::getObjectId, DimensionInfoDTO::getDimName, (exist, repeat) -> repeat));
}
}
常用工具类
/**
* 导出 Excel 文件
*/
public static void exportExcelFile(HttpServletResponse response, Class<?> clazz, List<?> excelList) {
try (ServletOutputStream os = CommonUtils.xlsxOutStream(response, "导出Excel文件")) {
EasyExcelFactory.write(os, clazz).sheet(0, "Sheet1").doWrite(excelList);
} catch (Exception e) {
throw new CustomException(e);
}
}
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.boot.practice.annotations.Relation;
import com.boot.practice.enums.ConstEnum;
import lombok.Data;
@Data
@HeadFontStyle(fontHeightInPoints = 16)
@ContentFontStyle(fontHeightInPoints = 12)
@HeadRowHeight(value = 30)
@ColumnWidth(value = 20)
@HeadStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN)
@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN)
public class EntityExcel {
@ExcelProperty("消费类型")
@Relation(type = ConstEnum.FieldType.DIM, code = ConstEnum.DimType.DIM_TYPE_CONSUME, verify = true)
private String consumeType;
@ExcelProperty("消费金额总和")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.AMOUNT, verify = true)
private String consumeAmountTotal;
@ExcelProperty("旅行开始时间")
@ColumnWidth(value = 40)
@Relation(type = ConstEnum.FieldType.DATETIME, verify = true)
private String tourStartTime;
@ExcelProperty("旅行结束时间")
@ColumnWidth(value = 40)
@Relation(type = ConstEnum.FieldType.DATETIME, verify = true)
private String tourEndTime;
@ExcelProperty("消费所在国家")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.DIM, code = ConstEnum.DimType.DIM_TYPE_COUNTRIES, verify = true)
private String location;
@ExcelProperty("交通工具")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.DIM, code = ConstEnum.DimType.DIM_TYPE_TRANSPORT, verify = true)
private String transport;
@ExcelProperty("交通费总和")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.AMOUNT, verify = true)
private String transportAmountTotal;
@ExcelProperty("住宿费总和")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.AMOUNT, verify = true)
private String lodgingAmountTotal;
@ExcelProperty("餐饮费总和")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.AMOUNT, verify = true)
private String diningAmountTotal;
@ExcelProperty("购物费总和")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.AMOUNT, verify = true)
private String shoppingAmountTotal;
@ExcelProperty("旅游目的地")
@ColumnWidth(value = 30)
@Relation(type = ConstEnum.FieldType.DIM, code = ConstEnum.DimType.DIM_TYPE_TOUR_SIGHTS, verify = true)
private String tourDestination;
@ExcelProperty("同行人数")
@ColumnWidth(value = 15)
private String partners;
@ExcelProperty("校验失败信息")
@ColumnWidth(value = 40)
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 42, fillBackgroundColor = 42)
@ContentStyle(borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN,
fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 42, fillBackgroundColor = 42)
private String errorInfo;
常量枚举类
public class ConstEnum {
public static class DimType {
public static final String DIM_TYPE_CURRENCY = "DIM_TYPE_CURRENCY";
public static final String DIM_TYPE_COUNTRIES = "DIM_TYPE_COUNTRIES";
public static final String DIM_TYPE_CONSUME = "DIM_TYPE_CONSUME";
public static final String DIM_TYPE_TRANSPORT = "DIM_TYPE_TRANSPORT";
public static final String DIM_TYPE_TOUR_SIGHTS = "DIM_TYPE_TOUR_SIGHTS";
}
public static class FieldType {
public static final String DIM = "DIM";
public static final String AMOUNT = "AMOUNT";
public static final String DATETIME = "DATETIME";
}
}
自定义注解
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface Relation {
/**
* 字段类型编码:
* DIM: 维度关联; 关联的维度类型编码
* AMOUNT: 金额;
* DATETIME: 时间
*/
String code() default "";
/**
* 字段类型:
* DIM: 维度关联;
* AMOUNT: 金额;
* DATETIME: 时间
*/
String type() default "";
/**
* 校验是否必填
*/
boolean verify() default false;
}
