package com.ruoyi.system.utils;
|
|
import cn.afterturn.easypoi.excel.ExcelExportUtil;
|
import cn.afterturn.easypoi.excel.annotation.Excel;
|
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
|
import cn.afterturn.easypoi.excel.entity.ExportParams;
|
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
|
import cn.hutool.core.annotation.AnnotationUtil;
|
import cn.hutool.core.util.ReflectUtil;
|
import com.alibaba.fastjson.JSONObject;
|
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
|
import com.ruoyi.system.vo.system.ProjectDeptDetailsChildVO;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.springframework.beans.BeanUtils;
|
import org.springframework.stereotype.Service;
|
import org.springframework.util.StringUtils;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.BufferedOutputStream;
|
import java.io.UnsupportedEncodingException;
|
import java.lang.reflect.Field;
|
import java.net.URLEncoder;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Optional;
|
import java.util.concurrent.CompletableFuture;
|
import java.util.concurrent.ExecutorService;
|
import java.util.concurrent.Executors;
|
import java.util.concurrent.TimeUnit;
|
import java.util.stream.Collectors;
|
|
/**
|
* @Author: 一杯美式
|
* @version: V1.0
|
* @Date: 2024-04-23 10:38
|
* @Package: com.victory.jfe.pdm.srm.config
|
*/
|
@Slf4j
|
@Service
|
public class ExcelPoiUtils {
|
/**
|
* 动态列导出
|
* @param response 响应
|
* @param fileName 文件名
|
* @param title 标题
|
* @param sheetName sheet名称
|
* @param dataList 导出数据
|
* @param headerName 动态列标题
|
* @param headerValue 动态列值
|
* @param <T>
|
* @throws Exception
|
*/
|
public static <T> void exportExcel(HttpServletResponse response, String fileName, String title, String sheetName,
|
List<T> dataList, String headerName, String headerValue) throws Exception {
|
if (CollectionUtils.isNotEmpty(dataList)) {
|
T dataInstance = dataList.get(0);
|
List<ExcelExportEntity> entityList = buildExcelExportEntityList(dataInstance, headerName, "");
|
List<Object> objList = convertDataListToObjList(dataList, headerName,headerValue);
|
downloadExcelEntityDynamic(response, entityList, objList, fileName, title, sheetName);
|
}
|
}
|
public static <T> void exportExcel(HttpServletResponse response, String fileName, String title, String sheetName,
|
List<T> dataList, List<ProjectDeptDetailsChildVO> projectDeptDetailsChildVOS, String headerName, String headerValue) throws Exception {
|
if (CollectionUtils.isNotEmpty(dataList)) {
|
ProjectDeptDetailsChildVO dataInstance = projectDeptDetailsChildVOS.get(0);
|
List<ExcelExportEntity> entityList = buildExcelExportEntityList(dataInstance, headerName, "");
|
List<Object> objList = convertDataListToObjList(dataList, headerName,headerValue);
|
downloadExcelEntityDynamic(response, entityList, objList, fileName, title, sheetName);
|
}
|
}
|
|
public static <T> void exportExcel(HttpServletResponse response, String fileName, String title, String sheetName,
|
List<T> dataList, String headerName, String headerValue, String ignoreCol) throws Exception {
|
if (CollectionUtils.isNotEmpty(dataList)) {
|
T dataInstance = dataList.get(0);
|
List<ExcelExportEntity> entityList = buildExcelExportEntityList(dataInstance, headerName, ignoreCol);
|
List<Object> objList = convertDataListToObjList(dataList, headerName,headerValue);
|
downloadExcelEntityDynamic(response, entityList, objList, fileName, title, sheetName);
|
}
|
}
|
|
/**
|
* 构建Excel导出实体列表
|
*
|
* @param t 取数据集第一条数据 做实体列表构建
|
* @param headerName 动态列标题
|
* @param <T> 数据类型
|
* @return Excel导出实体列表
|
* @throws IllegalAccessException 如果无法访问字段
|
*/
|
public static <T> List<ExcelExportEntity> buildExcelExportEntityList(T t, String headerName, String ignoreCol) throws IllegalAccessException {
|
List<ExcelExportEntity> entityList = new ArrayList<>();
|
Field[] fields = t.getClass().getDeclaredFields();
|
int index = 0;
|
|
for (Field field : fields) {
|
field.setAccessible(true);
|
Optional<Excel> excelOpt = Optional.ofNullable(field.getAnnotation(Excel.class));
|
Optional<ExcelCollection> excelCollectionOpt = Optional.ofNullable(field.getAnnotation(ExcelCollection.class));
|
|
if (excelOpt.isPresent()) {
|
// 处理固定导出列
|
if (ignoreCol.contains(field.getName())) {
|
index++;
|
} else {
|
index = handleFixedExportColumn(entityList, field, excelOpt.get(), index);
|
}
|
} else if (excelCollectionOpt.isPresent() && List.class.isAssignableFrom(field.getType())) {
|
// 处理自定义导出列
|
index = handleCustomExportColumn(t, entityList, field, headerName, index);
|
}
|
}
|
|
return entityList;
|
}
|
|
/**
|
* 处理固定导出列
|
*
|
* @param entityList 实体列表
|
* @param field 字段
|
* @param excel Excel注解
|
* @param index 索引
|
* @return 更新后的索引
|
*/
|
private static int handleFixedExportColumn(List<ExcelExportEntity> entityList, Field field, Excel excel, int index) {
|
Object name = AnnotationUtil.getAnnotationValue(field, Excel.class, "name");
|
ExcelExportEntity entity = createExcelExportEntity(field, name.toString(), field.getName(), index);
|
entityList.add(entity);
|
return index + 1;
|
}
|
|
/**
|
* 处理自定义导出列
|
*
|
* @param t 数据对象
|
* @param entityList 实体列表
|
* @param field 字段
|
* @param headerName 动态列标题
|
* @param index 索引
|
* @param <T> 数据类型
|
* @return 更新后的索引
|
* @throws IllegalAccessException 如果无法访问字段
|
*/
|
private static <T> int handleCustomExportColumn(T t, List<ExcelExportEntity> entityList, Field field, String headerName, int index) throws IllegalAccessException {
|
List<?> dynamicColl = (List<?>) field.get(t);
|
for (Object arr : dynamicColl) {
|
Field[] typeFields = arr.getClass().getDeclaredFields();
|
for (Field typeField : typeFields) {
|
typeField.setAccessible(true);
|
Excel excelItem = typeField.getAnnotation(Excel.class);
|
if (excelItem != null && headerName.equals(typeField.getName())) {
|
Object value = typeField.get(arr);
|
ExcelExportEntity entity = createExcelExportEntity(typeField, value.toString(), value.toString(), index);
|
entityList.add(entity);
|
index++;
|
}
|
}
|
}
|
return index;
|
}
|
|
/**
|
* 将数据对象列表转换为对象列表，通过异步处理每个项。
|
*
|
* @param dataList 需要处理的数据对象列表。
|
* @param headerName 用于从对象中提取特定值的标题名称。
|
* @param headerValue 用于从对象中提取特定值的标题值。
|
* @param <T> 数据对象列表中的对象类型。
|
* @return 表示处理过的数据对象的映射列表。
|
*/
|
public static <T> List<Object> convertDataListToObjList(List<T> dataList, String headerName, String headerValue) {
|
// 创建一个固定大小的线程池 =处理器数量
|
ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
|
long start = System.currentTimeMillis();
|
List<CompletableFuture<Object>> futures = new ArrayList<>();
|
// 提交每个数据对象进行异步处理
|
for (T data : dataList) {
|
futures.add(CompletableFuture.supplyAsync(() -> processData(data, headerName, headerValue), executorService));
|
}
|
// 收集处理结果
|
List<Object> objList = futures.stream()
|
.map(CompletableFuture::join)
|
.collect(Collectors.toList());
|
// 关闭线程池
|
executorService.shutdown();
|
try {
|
if (!executorService.awaitTermination(2, TimeUnit.MINUTES)) {
|
executorService.shutdownNow();
|
}
|
} catch (InterruptedException e) {
|
executorService.shutdownNow();
|
Thread.currentThread().interrupt();
|
}
|
|
log.info("数据处理时间:" + (System.currentTimeMillis() - start) + "ms");
|
return objList;
|
}
|
|
/**
|
* 处理单个数据对象以提取相关字段及其值。
|
*
|
* @param data 要处理的数据对象。
|
* @param headerName 用于从对象中提取特定值的标题名称。
|
* @param headerValue 用于从对象中提取特定值的标题值。
|
* @param <T> 数据对象的类型。
|
* @return 表示数据对象的映射。
|
*/
|
private static <T> Object processData(T data, String headerName, String headerValue) {
|
Map<String, Object> dataMap = new HashMap<>();
|
Field[] fields = ReflectUtil.getFields(data.getClass());
|
|
for (Field field : fields) {
|
field.setAccessible(true);
|
try {
|
// 将字段名和值放入dataMap中
|
dataMap.put(field.getName(), field.get(data));
|
ExcelCollection excelCollection = field.getAnnotation(ExcelCollection.class);
|
|
// 如果字段是ExcelCollection并且是List类型，进一步处理
|
if (excelCollection != null && field.getType().getName().equals(List.class.getName())) {
|
List<?> dynamicColl = (List<?>) field.get(data);
|
|
for (Object arr : dynamicColl) {
|
String key = null;
|
String val = null;
|
Field[] typeFields = arr.getClass().getDeclaredFields();
|
|
for (Field typeField : typeFields) {
|
typeField.setAccessible(true);
|
Excel excelItem = typeField.getAnnotation(Excel.class);
|
// 根据注解提取key和value
|
if (excelItem != null) {
|
if (headerName.equals(typeField.getName())) {
|
key = String.valueOf(typeField.get(arr));
|
} else if (headerValue.equals(typeField.getName())) {
|
val = String.valueOf(typeField.get(arr));
|
}
|
}
|
}
|
dataMap.put(key, val);
|
}
|
}
|
} catch (IllegalAccessException e) {
|
log.error("无法访问字段值：", e.getMessage());
|
return null;
|
}
|
}
|
|
return dataMap;
|
}
|
|
|
/**
|
* 动态生成并下载Excel文件。
|
*
|
* @param response HttpServletResponse对象，用于发送响应。
|
* @param entityList Excel导出实体列表。
|
* @param list 数据列表。
|
* @param fileName 文件名。
|
* @param title 标题。
|
* @param sheetName 工作表名称。
|
* @throws Exception 可能抛出的异常。
|
*/
|
public static void downloadExcelEntityDynamic(HttpServletResponse response, List<ExcelExportEntity> entityList,
|
List<Object> list, String fileName, String title,
|
String sheetName) throws Exception {
|
setResponseHeadersForDownload1(response, fileName);
|
ExportParams exportParams = StringUtils.hasText(title) ? new ExportParams(title, sheetName) : new ExportParams();
|
if (!StringUtils.hasText(title)) {
|
exportParams.setSheetName(sheetName);
|
}
|
|
int pageSize = 12000;
|
int totalPages = (list.size() + pageSize - 1) / pageSize;
|
Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, entityList, (obj, page) -> {
|
if (((int) obj) < page) {
|
return null;
|
}
|
log.info("当前查询第{}页数据", page);
|
int fromIndex = (page - 1) * pageSize;
|
int toIndex = Math.min(page * pageSize, list.size());
|
return list.subList(fromIndex, toIndex);
|
}, totalPages);
|
|
try (ServletOutputStream output = response.getOutputStream();
|
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output)) {
|
workbook.write(bufferedOutPut);
|
bufferedOutPut.flush();
|
}
|
}
|
|
private static void setResponseHeadersForDownload1(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
|
response.setCharacterEncoding("UTF-8");
|
response.setContentType("application/vnd.ms-excel");
|
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
|
}
|
|
|
public static void setResponseHeadersForDownload(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
|
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
|
response.setContentType("application/octet-stream;charset=utf-8");
|
}
|
|
/**
|
* 将@Excel或者@ExcelCollection修饰的字段转为ExcelExportEntity
|
*/
|
private static ExcelExportEntity createExcelExportEntity(Field typeField, String name, String key, int index) {
|
Map<String, Object> annotationValueMap = AnnotationUtil.getAnnotationValueMap(typeField, Excel.class);
|
ExcelExportEntity entity = JSONObject.parseObject(JSONObject.toJSONBytes(annotationValueMap), ExcelExportEntity.class);
|
// 字段名和@Excel的name一致，视为动态表头列
|
entity.setName(name);
|
entity.setKey(key);
|
entity.setOrderNum(index);
|
return entity;
|
}
|
|
|
private static ExcelExportEntity createExcelExportEntity1(Field typeField, String name, String key, int index) {
|
ExcelExportEntity entity = new ExcelExportEntity();
|
// 设置基本信息
|
entity.setName(name);
|
entity.setKey(key);
|
entity.setOrderNum(index);
|
// 设置注解中的属性
|
Excel excel = typeField.getAnnotation(Excel.class);
|
if (excel != null) {
|
try {
|
BeanUtils.copyProperties(entity, excel); // 复制注解中的属性到ExcelExportEntity对象
|
} catch (Exception e) {
|
log.error(e.getMessage());
|
throw new RuntimeException("无法从Excel注解复制属性：", e);
|
}
|
}
|
|
return entity;
|
}
|
|
}
|
