Velocity模板引擎实现通用导出
基础配置
在数据库中配置导出sql模板,导出文件名,sheet名,字段对应中文名等。表结构如下
CREATE TABLE `finance_download_config` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`config_desc` varchar(50) NOT NULL DEFAULT '' COMMENT '下载模板描述',
`config_sql` text COMMENT '维护下载模板',
`file_name` varchar(50) NOT NULL DEFAULT '' COMMENT '生成文件名称',
`sheet_name` varchar(50) NOT NULL DEFAULT '' COMMENT '生成文件sheet名称',
`data_source_key` varchar(30) NOT NULL DEFAULT '' COMMENT '下载文件使用数据源key',
`database_name` varchar(50) NOT NULL DEFAULT '' COMMENT '下载文件使用数据源',
`column_mapping` varchar(500) NOT NULL DEFAULT '' COMMENT '文件导出中文映射名称',
`page_size` int(11) NOT NULL DEFAULT '0' COMMENT '分页条数',
`limit_max_size` int(11) NOT NULL DEFAULT '0' COMMENT '下载上限数量',
`is_async_download` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否异步下载',
`create_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`last_access` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
`version` int(11) NOT NULL DEFAULT '0' COMMENT '版本信息',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
SQL举例
SELECT tf.accId '承兑汇票ID',tf.accNo '票据号',tf.endorsee '被背书人',tf.endorseDate '背书日期',tf.accDueAmt '票据到期值',tf.accDueAmt '背书金额',tf.adminName '操作人',tf.adminDeptName '操作人部门',
tf.createTime '创建时间',tf.preReceiver '上手持票人',acc.oldName '原上手持票人',tf.billingDate '开票日期',tf.accDate '票据到期日',tf.accBank '承兑行',tf.acceptor '承兑人', ('$platformName') as '平台主体'
FROM finance_buz_acceptance_endorse_transfer tf
LEFT JOIN finance_buz_self_acceptance acc
ON tf.accId = acc.id AND tf.accNo = acc.accNo
WHERE tf.isSelf = 1
#if($id) AND tf.id = '$id' #end
#if($endorsee) AND tf.endorsee LIKE CONCAT('%','$endorsee','%') #end
#if($endorseDate) AND tf.endorseDate = '$endorseDate' #end
#if($accNo) AND tf.accNo = '$accNo' #end
#if($accDueAmt) AND tf.accDueAmt = '$accDueAmt' #end
#if($accType) AND tf.accType = '$accType' #end
#if($minDueAmt) AND tf.accDueAmt >= '$minDueAmt'*1 #end
#if($maxDueAmt) AND tf.accDueAmt <= '$maxDueAmt'*1 #end
#if($startEndorseTime) AND tf.endorseDate >= '$startEndorseTime' #end
#if($endEndorseTime) AND tf.endorseDate <= '$endEndorseTime' #end
#if($platformId) AND tf.platformId = '$platformId' #end
ORDER BY tf.id DESC
方法入参dto,configId为配置的sql在数据库中的id,queryMap即为velocity模板解析的参数map
public class CommonExportDTO implements Serializable {
private static final long serialVersionUID = 3814552811047261576L;
@Schema(description = "通用导出配置id")
@NotNull
private Long configId;
@Schema(description = "通用导出参数map")
private Map<String, Object> queryMap;
}
初始化velocity引擎
@Configuration
public class VelocityConfig{
@Bean
public VelocityEngine velocityEngine(){
VelocityEngine ve =new VelocityEngine();
ve.setProperty(RuntimeConstants.RESOURCE_LOADER, "classpath");
ve.setProperty("classpath.resource.loader.class", ClasspathResourceLoader.class.getName());
ve.init();
return ve;
}
}
流程
- 从数据库中查询出对应的sql导出模板
- 使用velocity将queryMap中的参数解析到sql模板中,生成最终导出sql
- 根据要求异步或者同步导出
代码如下
public boolean commonExportExcel(HttpServletResponse response,CommonExportDTO dto, AdminContext adminContext, String platformId)
throws Exception {
DownloadConfig downloadConfig = downloadConfigMapper.selectById(dto.getConfigId());
if(null == downloadConfig){
log.warn(NOT_EXISTS_INFO);
throw new RuntimeException("目标信息不存在!");
}
// 默认同步导出
if(null == downloadConfig.getIsAsyncDownload()){
downloadConfig.setIsAsyncDownload(true);
}
Map<String, Object> queryMap = dto.getQueryMap();
// 查询配置的sql,模板解析
String sql = sqlTemplateAnalysisService.getSql(downloadConfig.getConfigSql(), queryMap);
log.info("commonExportExcel->本次导出SQL为:{}",sql);
// 携带的参数
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("sql", sql);
dataMap.put("downloadConfig", downloadConfig);
if(Boolean.FALSE.equals(downloadConfig.getIsAsyncDownload())){
// 同步导出
downloadService.exportExcel(response, dataMap);
return false;
}
// 异步发起
CompletableFuture.runAsync(() -> downloadService.exportExcel(null, dataMap));
return true;
}
重要的是velocity模板引擎解析过程
velocity模板引擎解析
@Service("sqlTemplateVelocityAnalysisService")
public class SqlTemplateVelocityAnalysisServiceImpl implements SqlTemplateAnalysisService {
@Autowired
private VelocityEngine velocityEngine;
public String getSql(String templateSql, Map<String, Object> queryMap) {
// velocity上下文传递
VelocityContext velocityContext = new VelocityContext();
if(CollectionUtils.isEmpty(queryMap) {
return "";
}
// 将参数queryMap中的键值对放入velocity上下文
paramMap.keySet().forEach(key -> {
velocityContext.put(key.toString(), paramMap.get(key));
});
// 解析后数据的输出目标,java.io.Writer的子类
StringWriter w = new StringWriter();
// 模板sql根据键名匹配velocity上下文中的键值对(获取相同键对应的值)
velocityEngine.evaluate(velocityContext, w, "", templateSql);
return w.toString();
}
}
导出
public void exportExcel(HttpServletResponse response, Map<String, Object> map){
if(CollectionUtils.isEmpty(map)){
log.error(EXPORT_CONDITION_NULL);
throw new RuntimeException(EXPORT_CONDITION_NULL);
}
DownloadConfig downloadConfig = (DownloadConfig) map.get("downloadConfig");
//创建Excel对象
try(SXSSFWorkbook workbook = new SXSSFWorkbook()) {
//查询结果Map
ConcurrentSkipListMap<Integer, List<Map<String, Object>>> dataMap = new ConcurrentSkipListMap<>();
//文件名称
String fileName = downloadConfig.getFileName();
//去掉文件名称中的后缀名
if(fileName.contains(".")){
fileName = fileName.substring(0, fileName.indexOf("."));
}
//创建Sheet
Sheet dataSheet = workbook.createSheet();
//设置Sheet的名称
String sheetName = downloadConfig.getSheetName();
if(StringUtils.isNotEmpty(sheetName)){
workbook.setSheetName(0, sheetName);
}
//封装Excel对应Sheet的字段title
List<String> sheetTitle = setSheetTitle(downloadConfig.getColumnMapping());
ExcelUnits.writeRowData(dataSheet, sheetTitle, 0);
//获取当前查询的数据源Key
String dataSourceKey = downloadConfig.getDataSourceKey();
//得到当前Sheet的查询sql
String sql = (String)map.get("sql");
//如果count查询语句为空,手工拼接查询记录总条数的SQL
String countSql = "select count(*) "+sql.substring(sql.toUpperCase().indexOf("FROM "));
//查询记录总条数
Long dataCount = commonQueryService.getCount(dataSourceKey, countSql);
//判断是否超过最大行数
Integer maxSize = downloadConfig.getLimitMaxSize() == null ? 200000 : downloadConfig.getLimitMaxSize();
if(dataCount > maxSize){
log.error(DATA_COUNT +dataCount+",超过最大设置");
throw new FinanceCustomizeException("最多导出"+maxSize+"条");
}else if(dataCount > 200_000L){
log.error(DATA_COUNT +dataCount+",超过默认最大设置");
throw new FinanceCustomizeException("超过默认最大设置值");
}
//查询对应数据,并封装到Excel的每行中
queryDataAndWriteRow(dataMap, dataSheet, downloadConfig, sql, dataCount);
log.info("写完通用excel数据");
//将封装的Excel进行导出
if(Boolean.TRUE.equals(downloadConfig.getIsAsyncDownload())){
// 异步导出
exportExcelFile(workbook, fileName, map);
}else{
// 同步导出
exportExcelFile(response, workbook, fileName);
}
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
log.error("通用Excel下载服务异常:",e);
throw new RuntimeException("通用Excel下载服务异常!");
}
}
/**
* 封装excel的title
* @param title
* @return
*/
private List<String> setSheetTitle(String title) {
List<String> sheetTitle = new LinkedList<>();
if(StringUtils.isNotEmpty(title)){
String[] titles = title.split(",");
sheetTitle.addAll(Arrays.asList(titles));
}
return sheetTitle;
}
查询数据并封装到excel中方法
* 查询对应数据,并封装到Excel的每行中
* @param dataMap
* @param dataSheet
* @param sql
* @param dataCount
*/
private void queryDataAndWriteRow(ConcurrentSkipListMap<Integer, List<Map<String, Object>>> dataMap,
Sheet dataSheet, DownloadConfig downloadConfig, String sql,Long dataCount) {
//获取excel的查询字段
String[] columns = downloadConfig.getColumnMapping().split(",");
int pageSize = downloadConfig.getPageSize() == null ? 5000 : downloadConfig.getPageSize();
//计算一共有多少页数
long pageCount = (dataCount - 1) / pageSize + 1;
//创建定长为5的线程池,来同步处理数据查询
ExecutorService threadPoolExecutor = Executors.newFixedThreadPool(5);
//循环所有的分页,将每页查询放到线程池中单独查询
for(int pageNum=1; pageNum <= pageCount; pageNum++){
String sqlParm = "";
if(pageNum == 1){
sqlParm = sql + " limit "+pageSize;
}else{
sqlParm = sql + " limit "+pageSize*(pageNum-1)+","+pageSize;
}
CommonDataQueryThread thread = new CommonDataQueryThread(commonQueryService, pageNum, sqlParm, downloadConfig.getDataSourceKey(), dataMap);
threadPoolExecutor.execute(thread);
}
//监听线程结束后开始处理Excel数据
threadPoolExecutor.shutdown();
while(!threadPoolExecutor.isTerminated());
log.info("线程执行完毕");
List<String> datas = new LinkedList<>();
int pageNum = 0;
//封装Excel行数据
for (Map.Entry<Integer, List<Map<String, Object>>> entry : dataMap.entrySet()) {
List<Map<String, Object>> dataList = entry.getValue();
int j=1;
for (Map<String, Object> dataObject : dataList) {
setSheetData(datas, dataObject, columns);
ExcelUnits.writeRowData(dataSheet, datas, pageNum * pageSize + j);
datas.clear();
j++;
}
log.info("第{}页的条数{}",entry.getKey(),dataList.size());
pageNum++;
}
//清空map中存储当前sheet的数据
if(dataMap.size() > 0){
dataMap.clear();
}
}
处理数据时,采用多线程的方式进行处理。每页数据由一个线程进行查询处理
多线程类
public class CommonDataQueryThread implements Runnable{
private static final Logger logger = LoggerFactory.getLogger(CommonDataQueryThread.class);
//通用查询service
private CommonQueryService commonQueryService;
//当前第几页
private int page;
// 查询SQL
private String sqlParam;
// 查询数据源
private String dataSourceKey;
ConcurrentMap<Integer, List<Map<String, Object>>> dataMap;
public CommonDataQueryThread(CommonQueryService commonDataQueryService, int page, String sqlParam, String dataSourceKey, ConcurrentMap<Integer, List<Map<String, Object>>> dataMap){
this.commonQueryService = commonDataQueryService;
this.page = page;
this.sqlParam = sqlParam;
this.dataSourceKey = dataSourceKey;
this.dataMap = dataMap;
}
@Override
public void run() {
logger.info("通用数据查询SQL:{},及页数:{}", sqlParam, page);
List<Map<String, Object>> dataList = commonQueryService.queryPage(dataSourceKey, sqlParam);
if(dataList != null && !dataList.isEmpty()){
dataMap.put(page, dataList);
logger.info("查询{}页的数据结束,及条数{}",page,dataList.size());
}
}
}
进行数据查询时,需要用到多数据源切换。需要注意的是,使用之后,必须要将数据源清空,防止其他使用默认数据源的服务出错
@Service
public class CommonQueryServiceImpl implements CommonQueryService {
@Autowired
private CommonQueryMapper commonQueryMapper;
@Override
public List<Map<String, Object>> queryPage(String dataSourceKey, String sql) {
try {
if(StringUtils.isNotBlank(sql)){
// 设置数据源
DynamicDataSourceContextHolder.push(dataSourceKey);
return commonQueryMapper.queryPage(sql);
}
return null;
} finally {
// 清空数据源
DynamicDataSourceContextHolder.clear();
}
}
@Override
public Long getCount(String dataSourceKey, String sql) {
try {
if(StringUtils.isNotBlank(sql)){
DynamicDataSourceContextHolder.push(dataSourceKey);
return commonQueryMapper.getCount(sql);
}
return null;
} finally {
DynamicDataSourceContextHolder.clear();
}
}
}
同步导出
同步导出需要设置响应头,来达到文件下载的目的
private void exportExcelFile(HttpServletResponse response, SXSSFWorkbook workbook, String fileName) throws UnsupportedEncodingException {
response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;");
response.setHeader("Content-Disposition",
"attachment;filename=" + java.net.URLEncoder.encode(fileName + XLSX,"UTF-8"));
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (IOException e) {
log.error(ERROR,e);
}finally {
try {
if(outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
log.error(DOWNLOAD_STREAM_EXCEPTION,e);
}
}
log.info("通用excel输出流结束");
}
异步导出
异步导出,则将文件上传到公共文件服务上,将url返回,用户需要时,点击下载即可。
由主流程中 CompletableFuture.runAsync(() -> downloadService.exportExcel(null, dataMap));这行代码可知,异步导出是开启异步线程进行导出的。
/**
* 将封装的Excel上传,保存URL
* @param workbook
* @param fileName
* @param map
*/
private void exportExcelFile(SXSSFWorkbook workbook, String fileName, Map<String, Object> map){
InputStream in = null;
try(ByteArrayOutputStream out = new ByteArrayOutputStream()){
// 创建临时文件
workbook.write(out);
byte[] byteArray = out.toByteArray();
in = new ByteArrayInputStream(byteArray);
String fileURL = uploadFileService.uploadFile(in, fileName + XLSX, "report", "commonExportUpload");
log.info("导出excel文件完成,文件地址: {}", fileURL);
FinanceAttachDownloadRecord financeAttachDownloadRecord = new FinanceAttachDownloadRecord();
financeAttachDownloadRecord.setUserId((Long)map.get("loginUID"));
financeAttachDownloadRecord.setUserName((String)map.get("loginName"));
financeAttachDownloadRecord.setPlatformId((String)map.get("platformId"));
financeAttachDownloadRecord.setAttachName(fileName + XLSX);
financeAttachDownloadRecord.setAttachUrl(fileURL);
long timeMillis = System.currentTimeMillis();
financeAttachDownloadRecord.setCreateTime(timeMillis);
financeAttachDownloadRecord.setLastAccess(timeMillis);
financeAttachDownloadRecord.setBuzName(fileName);
financeAttachDownloadRecordMapper.insert(financeAttachDownloadRecord);
} catch (Exception e) {
log.error(ERROR,e);
} finally {
if(in != null){
try{
in.close();
} catch (IOException e) {
log.error(DOWNLOAD_STREAM_EXCEPTION,e);
}
}
}
}
excel工具类
public class ExcelUnits {
/**
* 向excel每行row第column个cell写content数据
* @param row
* @param column
* @param content
*/
public static void writeCell(Row row,int column,String content){
Cell cell = row.createCell(column);
cell.setCellValue(content);
}
/**
* 向每行写数据
* @param row
* @param datas
*/
public static void writeRow(Row row,List<String> datas){
Cell cell = null;
for(int i=0;i<datas.size();i++){
cell = row.createCell(i);
cell.setCellValue(datas.get(i));
}
}
/**
* 向excel行写数据,返回下一行。
* @param sheet
* @param datas
* @param count
* @return
*/
public static int writeRowData(Sheet sheet, List<String> datas,int count){
Row row = null;
if(datas!=null && !datas.isEmpty()){
row = sheet.createRow(count);
writeRow(row,datas);
count++;
datas.clear();
}
return count;
}
/**
*
* @param sheet
* @param rowStart 行开始
* @param rowEnd 行结束
* @param colStart 列开始
* @param colEnd 列结束
* @return
*/
public static Cell region(Sheet sheet,int rowStart,int rowEnd,int colStart,int colEnd){
CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
sheet.addMergedRegion(region);
return sheet.getRow(rowStart).getCell(colStart);
}
}