前言
在项目上实现一个导出功能往往需要进行一定量的代码开发，但如果能够使用一个通用的导出功能那么将会事半功倍，本人基于easyExcel实现了一个前后端分离的通用导出功能（仅适用于单表，多表关联导出后续将再实现），此功能通过配置信息引用编码及传入查询条件/不传入查询条件实现数据导出到excel表格。
导出文件
前端
动态信息管理页面
添加动态信息
配置动态表
新增动态表
配置动态属性
新增动态属性
以上是前端的页面配置信息
后端
引入依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>4.0.2</version></dependency>
导出接口
package com.easyadmin.pro.modules.base.controller;import com.easyadmin.pro.common.constants.HttpConstants;import com.easyadmin.pro.common.controller.BaseController;import com.easyadmin.pro.common.vo.RestResult;import com.easyadmin.pro.modules.base.query.DownloadExcelQuery;import com.easyadmin.pro.tool.ExcelUtils;import lombok.RequiredArgsConstructor;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;/** * packageName com.easyadmin.pro.modules.base.controller * * @author 骑着蚂蚁去上天 * @version JDK 17 * @className BaseController * @date 2024/12/30 * @description 基础视图层 */@RestController@RequestMapping(HttpConstants.BASE_MODEL_PATH)@RequiredArgsConstructor(onConstructor = @__(@Autowired))public class BaseDownloadController extends BaseController { private final ExcelUtils excelUtils; /** * 导出文件 * @param downloadExcelQuery * @return */ @PostMapping("download") public RestResult downloadExcel(@RequestBody DownloadExcelQuery downloadExcelQuery) { excelUtils.downloadExcel(downloadExcelQuery.getDownloadCode(), downloadExcelQuery.getQueryCondition()); return success(); }}
导出工具（excelUtils）
package com.easyadmin.pro.tool;import com.easyadmin.pro.common.constants.StatusConstants;import com.easyadmin.pro.common.enums.HttpCodeEnum;import com.easyadmin.pro.common.exception.BusinessException;import com.easyadmin.pro.common.thread.DownloadThread;import com.easyadmin.pro.modules.config.entity.DynamicDeriveEntity;import com.easyadmin.pro.modules.config.entity.DynamicDeriveItemEntity;import com.easyadmin.pro.modules.config.entity.DynamicDeriveTableEntity;import com.easyadmin.pro.modules.config.service.IDynamicDeriveItemService;import com.easyadmin.pro.modules.config.service.IDynamicDeriveService;import com.easyadmin.pro.modules.config.service.IDynamicDeriveTableService;import com.mybatisflex.core.query.QueryWrapper;import lombok.RequiredArgsConstructor;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Value;import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;import org.springframework.stereotype.Component;import org.springframework.util.CollectionUtils;import javax.sql.DataSource;import java.util.List;import java.util.Map;import java.util.Objects;/** * packageName com.easyadmin.pro.tool * * @author 骑着蚂蚁去上天 * @version JDK 17 * @className ExcelUtils * @date 2024/9/18 * @description 通用导出工具类 */@Component@RequiredArgsConstructor(onConstructor = @__(@Autowired))public class ExcelUtils { private final DataSource dataSource; private final IDynamicDeriveService dynamicDeriveService; private final IDynamicDeriveTableService dynamicDeriveTableService; private final IDynamicDeriveItemService dynamicDeriveItemService; private final ThreadPoolTaskExecutor threadPoolTaskExecutor; @Value("${local.download.path}") private String localDownloadPath; /** * 导出方法 * @param downloadCode * @param queryCondition */ public void downloadExcel(String downloadCode, Map<String,Object> queryCondition) { download(downloadCode, queryCondition); } /** * 私有导出方法 * @param downloadCode * @param queryCondition */ private void download(String downloadCode, Map<String,Object> queryCondition) { DynamicDeriveEntity dynamicDerive = dynamicDeriveService.getOne(new QueryWrapper() .eq(DynamicDeriveEntity::getDeriveCode, downloadCode) .eq(DynamicDeriveEntity::getStatus, StatusConstants.STATUS_1)); if (Objects.nonNull(dynamicDerive)) { Long deriveId = dynamicDerive.getId(); String excelName = dynamicDerive.getDeriveFileName(); DynamicDeriveTableEntity deriveTable = dynamicDeriveTableService.getOne(new QueryWrapper() .eq(DynamicDeriveTableEntity::getDeriveId, deriveId)); if (Objects.nonNull(deriveTable)) { String tableName = deriveTable.getTableCode(); Long deriveTableId = deriveTable.getId(); List<DynamicDeriveItemEntity> deriveItemInfos = dynamicDeriveItemService.list(new QueryWrapper() .eq(DynamicDeriveItemEntity::getDeriveTableId, deriveTableId) .eq(DynamicDeriveItemEntity::getStatus, StatusConstants.STATUS_1)); if (!CollectionUtils.isEmpty(deriveItemInfos)) { DownloadThread downloadThread = new DownloadThread(); downloadThread.setExcelName(excelName) .setTableName(tableName) .setDataSource(dataSource) .setDeriveItemInfos(deriveItemInfos) .setQueryCondition(queryCondition) .setLocalDownloadPath(localDownloadPath); threadPoolTaskExecutor.execute(downloadThread); } else { throw new BusinessException(HttpCodeEnum.NOT_FOUND_DERIVE_ITEM_INFO); } } else { throw new BusinessException(HttpCodeEnum.NOT_FOUND_DERIVE_TABLE_INFO); } } else { throw new BusinessException(HttpCodeEnum.NOT_FOUND_DERIVE_INFO); } }}
导出线程（DownloadThread）
package com.easyadmin.pro.common.thread;import com.alibaba.excel.EasyExcel;import com.easyadmin.pro.common.constants.StatusConstants;import com.easyadmin.pro.modules.config.entity.DynamicDeriveItemEntity;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.SneakyThrows;import lombok.experimental.Accessors;import lombok.extern.slf4j.Slf4j;import org.springframework.util.StringUtils;import javax.sql.DataSource;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import java.util.*;import java.util.stream.Collectors;/** * packageName com.easyadmin.pro.common.thread * * @author 骑着蚂蚁去上天 * @version JDK 17 * @className DownloadThread * @date 2024/9/18 * @description 导出线程 */@Slf4j@Data@Accessors(chain = true)@AllArgsConstructor@NoArgsConstructorpublic class DownloadThread extends Thread { private String tableName; private String excelName; private List<DynamicDeriveItemEntity> deriveItemInfos; private DataSource dataSource; private String executeSql = "select %s from %s where is_deleted = '0' "; private Map<String, Object> queryCondition; private Map<String, String> anotherConditionMap; private Map<String, String> conditionMap; private String sortItem; private String localDownloadPath; @Override public void run() { EasyExcel.write(localDownloadPath.concat(excelName.concat(".xlsx"))) .head(createHead()).sheet(excelName) .doWrite(data()); } /** * 构造数据 * @return */ private List<List<String>> data() { String queryItem = deriveItemInfos.stream() .filter(e -> Objects.equals(StatusConstants.STATUS_1, e.getIsDeriveCondition())) .sorted(Comparator.comparingInt(DynamicDeriveItemEntity::getSort)) .map(DynamicDeriveItemEntity::getItemCode) .collect(Collectors.joining(",")); anotherConditionMap = deriveItemInfos.stream() .filter(e -> Objects.nonNull(e.getItemAnotherCode()) && Objects.equals(StatusConstants.STATUS_1, e.getIsQueryCondition())) .collect(Collectors.toMap(DynamicDeriveItemEntity::getItemAnotherCode, DynamicDeriveItemEntity::getQueryCondition)); conditionMap = deriveItemInfos.stream() .filter(e -> Objects.isNull(e.getItemAnotherCode()) && Objects.equals(StatusConstants.STATUS_1, e.getIsQueryCondition())) .collect(Collectors.toMap(DynamicDeriveItemEntity::getItemAnotherCode, DynamicDeriveItemEntity::getQueryCondition)); sortItem = deriveItemInfos.stream() .filter(e -> Objects.equals(StatusConstants.STATUS_1, e.getSortItemStatus())) .map(DynamicDeriveItemEntity::getItemCode) .collect(Collectors.joining(",")); Map<String, String> queryConditionMap = deriveItemInfos.stream() .collect(Collectors.toMap(DynamicDeriveItemEntity::getItemAnotherCode, DynamicDeriveItemEntity::getItemCode)); String executeSql = createExecuteSql(queryItem, queryConditionMap); return queryObj(executeSql); } /** * 查询数据 * @param executeSql */ @SneakyThrows private List<List<String>> queryObj(String executeSql) { Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(executeSql); ResultSetMetaData metaData = rs.getMetaData(); List<String> columnNames = getColumnName(metaData); List<List<String>> resultInfos = new ArrayList<>(); while (rs.next()) { List<String> valInfos = new ArrayList<>(); for (int i = 0; i < columnNames.size(); i++) { String val = rs.getString(columnNames.get(i)); valInfos.add(val); } resultInfos.add(valInfos); } statement.close(); return resultInfos; } /** * 获取字段 * @param metaData * @return */ @SneakyThrows private List<String> getColumnName(ResultSetMetaData metaData) { List<String> columnNames; columnNames = new ArrayList<>(metaData.getColumnCount()); for (int i = 0; i < metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName((i + 1)); columnNames.add(columnName); } return columnNames; } /** * 创建查询sql * @param queryItem * @param queryConditionMap * @return */ private String createExecuteSql(String queryItem, Map<String, String> queryConditionMap) { executeSql = String.format(executeSql, queryItem, tableName); // 有查询条件 if (Objects.nonNull(queryCondition)) { queryCondition.forEach((k, v) -> { executeSql.concat(" AND "); String queryItemKey = queryConditionMap.get(k); if (StringUtils.hasLength(queryItemKey)) { executeSql.concat(queryItemKey).concat(anotherConditionMap.get(queryItemKey)).concat(v.toString()); } else { executeSql.concat(k).concat(conditionMap.get(k)).concat(v.toString()); } }); } if (StringUtils.hasLength(sortItem)) { executeSql.concat(" ORDER BY ").concat(sortItem).concat(" DESC "); } return executeSql; } /** * 创建表头行 * @return */ private List<List<String>> createHead() { List<List<String>> result = new ArrayList<>(1); List<String> headInfos = deriveItemInfos.stream() .filter(e -> Objects.equals(StatusConstants.STATUS_1, e.getIsDeriveCondition())) .sorted(Comparator.comparingInt(DynamicDeriveItemEntity::getSort)) .map(DynamicDeriveItemEntity::getDeriveName) .collect(Collectors.toList()); headInfos.forEach(head -> result.add(Arrays.asList(head))); return result; }}
以上是通用导出的后端程序
其它信息
线程池配置（AsyncConfig）
package com.easyadmin.pro.common.configuration;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.scheduling.annotation.EnableAsync;import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;import java.util.concurrent.ThreadPoolExecutor;/** * packageName com.easyadmin.pro.common.configuration * * @author 骑着蚂蚁去上天 * @version JDK 17 * @className AsyncConfig * @date 2024/9/18 * @description 线程池配置 */@Configuration@EnableAsyncpublic class AsyncConfig { @Bean("threadPoolTaskExecutor") public ThreadPoolTaskExecutor threadPoolExecutor() { ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor(); // 核心线程数 executor.setCorePoolSize(10); // 最大线程数 executor.setMaxPoolSize(20); // 队列容量 executor.setQueueCapacity(500); // 线程空闲时间 executor.setKeepAliveSeconds(60); // 线程名前缀 executor.setThreadNamePrefix("easyAdminTreadPool-"); // 拒绝策略 executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy()); executor.initialize(); return executor; }}
文件导出入参对象（DownloadExcelQuery）
package com.easyadmin.pro.modules.base.query;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;import java.util.Map;/** * packageName com.easyadmin.pro.modules.base.query * * @author 骑着蚂蚁去上天 * @version JDK 17 * @className DownloadExcelQuery * @date 2024/12/30 * @description 文件导出查询对象 */@Data@Accessors(chain = true)@NoArgsConstructor@AllArgsConstructorpublic class DownloadExcelQuery { private String downloadCode; private Map<String, Object> queryCondition;}
以上就是本次基于easyExcel工具的通用导出功能实现，属于个人想法与实现，有问题请及时指出，谢谢！！！
