前言
我们在日常的开发中，经常会遇到一些要 导出的需求
当然一般的单个sheet页的导出的实现我相信大家应该都没什么问题。
但是对于一些稍微复杂点儿的多sheet页以及存在一些行和列的样式调整的excel呢，除了将代码实现之外，更多的时候需要考虑到导出的性能问题以及样式的美观。
开搞
例如现在给一个需求，要求连表查询出一系列的汇总数据，然后要求将页面上的列表汇总数据按照 样式1:1的复刻导出到Excel中；
image.png
如上图
首先，对于sql的优化，尽量使用一个实体类存储一行所需的属性便于后期作为导出的实体类；
然后研究sq[图片上传中...(image.png-9149ee-1749778578526-0)]
l的编写：我是这样写的
image.png
因为按照需求要将所有的子类进行汇总，而且还要将类型汇总成一个全部区域。全部区域需要独成一行置顶。并且列里的大类需要单独的将"全部"作为一个类展示出全部的总计、已办结、处置中、代办、处置率、好评率....
因此要先汇总town_list 和 type_list 后面对 属性进行分组聚合
首先看同步方法：
同步方法 不过多赘述了。
接口如下：
@GetMapping("/exportQueryDatas")
public void exportQueryDatas(HttpServletResponse response, QuerysVO querysVO) throws Exception {
if (null != querysVO.getStartTime() && !querysVO.getStartTime().equals("")) {
querysVO.setStartTime(querysVO.getStartTime() + " 00:00:00");
}
if (null != querysVO.getEndTime() && !querysVO.getEndTime().equals("")) {
querysVO.setEndTime(querysVO.getEndTime() + " 23:59:59");
}
JSONArray dataArr = ticketProblemInfoService.queryDatas(querysVO);
List<DataAnalysisExportVO> exportList = handleDataList(dataArr);
handExportMethod("xxxxx数据统计",response,querysVO,exportList);
}
异步方法
@GetMapping("/exportAllDatas")
public AjaxResult exportQueryDatasAsync(QuerysVO querysVO) {
String taskId = UUID.randomUUID().toString();
exportTaskService.createTask(taskId);
// 异步执行导出
CompletableFuture.runAsync(() -> {
try {
String[] sheetNames = {"AA市","BB街道","CC镇","DD镇","EE镇","FF镇","GG镇","HH镇","II镇","JJ镇","KK街道","LL街道","MM街道"};
JSONArray dataArr = ticketProblemInfoService.queryDatas(querysVO);
List<DataAnalysisExportVO> exportList = handleDataList(dataArr);
querysVO.setStreetName("BB街道");
JSONArray TZJDArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> TZJDList = handleDataList(TZJDArr);
querysVO.setStreetName("CC镇");
JSONArray MCZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> MCZList = handleDataList(MCZArr);
querysVO.setStreetName("DD镇");
JSONArray HQZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> HQZList = handleDataList(HQZArr);
querysVO.setStreetName("EE镇");
JSONArray DJZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> DJZList = handleDataList(DJZArr);
querysVO.setStreetName("FF镇");
JSONArray YCZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> YCZList = handleDataList(YCZArr);
querysVO.setStreetName("GG镇");
JSONArray SWZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> SWZList = handleDataList(SWZArr);
querysVO.setStreetName("HH镇");
JSONArray SFSZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> SFSZList = handleDataList(SFSZArr);
querysVO.setStreetName("II镇");
JSONArray DFSZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> DFSZList = handleDataList(DFSZArr);
querysVO.setStreetName("JJ镇");
JSONArray LPZArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> LPZDList = handleDataList(LPZArr);
querysVO.setStreetName("KK街道");
JSONArray KJJDArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> KJJDList = handleDataList(KJJDArr);
querysVO.setStreetName("LL街道");
JSONArray WZHJDArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> WZHJDList = handleDataList(WZHJDArr);
querysVO.setStreetName("MM街道");
JSONArray WXJDArr = ticketProblemInfoService.queryVillageDatas(querysVO);
List<DataAnalysisExportVO> WXJDList = handleDataList(WXJDArr);
List<List<DataAnalysisExportVO>> dataList = new ArrayList<>();
dataList.add(exportList);
dataList.add(TZJDList);
dataList.add(MCZList);
dataList.add(HQZList);
dataList.add(DJZList);
dataList.add(YCZList);
dataList.add(SWZList);
dataList.add(SFSZList);
dataList.add(DFSZList);
dataList.add(LPZDList);
dataList.add(KJJDList);
dataList.add(WZHJDList);
dataList.add(WXJDList);
// 2. 生成Excel到本地
String parentPath = System.getProperty("user.dir") + File.separator + "temp" + File.separator;
File file = new File(parentPath);
if (!file.exists()) {
file.mkdirs();
}
String outPath = parentPath + taskId + ".xlsx";
File excelFile = new File(outPath);
if (excelFile.exists()) {
excelFile.delete();
}
generateExcelToFile(sheetNames, dataList, querysVO, outPath);
exportTaskService.finishTask(taskId, outPath);
} catch (Exception e) {
exportTaskService.failTask(taskId, e.getMessage());
}
}, exportExecutor);
return AjaxResult.success("任务已提交", taskId);
}
其中需引入：Executor
@Autowired
@Qualifier("exportExecutor")
private Executor exportExecutor;
并且配置异步线程：
@Configuration
public class AsyncConfig {
@Bean("exportExecutor")
public Executor exportExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
executor.setCorePoolSize(2);
executor.setMaxPoolSize(4);
executor.setQueueCapacity(10);
executor.setThreadNamePrefix("export-excel-");
executor.initialize();
return executor;
}
}
新建ExportTask ，异步任务实体
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ExportTask {
private String taskId;
private Date createTime;
private Date finishTime;
private String filePath;
private String message;
private String status;
}
根据TaskId 下载Excel文件：
@GetMapping("/downloadExport")
public void downloadExport(@RequestParam String taskId, HttpServletResponse response) throws IOException {
ExportTask task = exportTaskService.getTask(taskId);
if (task == null || !"已完成".equals(task.getStatus())) {
response.setContentType("text/plain;charset=UTF-8");
response.getWriter().write("文件未生成或任务不存在");
return;
}
String filePath = task.getFilePath();
String fileName = taskId + new Date().toString() + ".xlsx";
String encodedFileName = java.net.URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + encodedFileName);
try (FileInputStream in = new FileInputStream(filePath);
ServletOutputStream out = response.getOutputStream()) {
byte[] buffer = new byte[4096];
int n;
while ((n = in.read(buffer)) != -1) {
out.write(buffer, 0, n);
}
}
}
另外还可以建立一个查询异步文件是否生成的状态查询接口：
@GetMapping("/exportTaskStatus")
public AjaxResult exportTaskStatus(@RequestParam String taskId) {
ExportTask task = exportTaskService.getTask(taskId);
if (task == null) return AjaxResult.error("任务不存在");
return AjaxResult.success(task);
}
Excel的组装核心关键在于创建多sheet页并将异步查询的数据填充进去：
public void generateExcelToFile(String[] sheetNames, List<List<DataAnalysisExportVO>> dataList, QuerysVO querysVO, String filePath) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
try (FileOutputStream out = new FileOutputStream(filePath)) {
for (int k = 0; k < sheetNames.length; k++) {
int totalColumns = 43;
String sheetName = sheetNames[k];
Sheet sheet = workbook.createSheet(sheetNames[k]);
List<DataAnalysisExportVO> list = dataList.get(k);
sheet.createRow(0); // groupRow
sheet.createRow(1); // subRow
sheet.createRow(2); // subtitleRow
// 4. 副标题（第三行，index=2）
Row subtitleRow = sheet.getRow(2);
Cell subtitleCell = subtitleRow.createCell(0);
String subtitle = "查询起始时间：" + (querysVO.getStartTime() == null ? "" : querysVO.getStartTime())
+ " - " + (querysVO.getEndTime() == null ? "" : querysVO.getEndTime());
subtitleCell.setCellValue(subtitle);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, totalColumns - 1));
CellStyle subtitleStyle = workbook.createCellStyle();
Font subtitleFont = workbook.createFont();
subtitleFont.setBold(true);
subtitleFont.setFontHeightInPoints((short) 12);
subtitleStyle.setFont(subtitleFont);
subtitleStyle.setAlignment(HorizontalAlignment.LEFT);
subtitleCell.setCellStyle(subtitleStyle);
// 5. 多级表头内容
Row groupRow = sheet.getRow(0);
Row subRow = sheet.getRow(1);
int col = 0;
groupRow.createCell(col).setCellValue("序号");
subRow.createCell(col++).setCellValue("序号");
groupRow.createCell(col).setCellValue("地区");
subRow.createCell(col++).setCellValue("地区");
String[] groups = {"全部", "A类", "B类", "C类", "D类"};
String[] subs = {"总计", "已办结", "处理中", "待办", "办结率", "好评率"};
for (String group : groups) {
for (int i = 0; i < subs.length; i++) {
groupRow.createCell(col + i).setCellValue(group);
subRow.createCell(col + i).setCellValue(subs[i]);
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, col, col + subs.length - 1));
col += subs.length;
}
String[] levelSubs = {"一般", "较大", "重大", "触发敏感词"};
for (int i = 0; i < levelSubs.length; i++) {
groupRow.createCell(col + i).setCellValue("诉求分级");
subRow.createCell(col + i).setCellValue(levelSubs[i]);
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, col, col + levelSubs.length - 1));
col += levelSubs.length;
String[] sourceSubs = {"上报", "摸排", "交办", "录入"};
for (int i = 0; i < sourceSubs.length; i++) {
groupRow.createCell(col + i).setCellValue("排查来源");
subRow.createCell(col + i).setCellValue(sourceSubs[i]);
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, col, col + sourceSubs.length - 1));
col += sourceSubs.length;
String[] warnSubs = {"预警总数", "超时未受理预警", "不满意评价预警"};
for (int i = 0; i < warnSubs.length; i++) {
groupRow.createCell(col + i).setCellValue("预警统计");
subRow.createCell(col + i).setCellValue(warnSubs[i]);
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, col, col + warnSubs.length - 1));
col += warnSubs.length;
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
// 6. 设置表头样式（交替颜色）
XSSFCellStyle headStyle1 = workbook.createCellStyle();
headStyle1.setAlignment(HorizontalAlignment.CENTER);
headStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headFont1 = workbook.createFont();
headFont1.setBold(true);
headFont1.setColor(IndexedColors.WHITE.getIndex());
headStyle1.setFont(headFont1);
headStyle1.setFillForegroundColor(new XSSFColor(new java.awt.Color(123, 82, 42), null));
XSSFCellStyle headStyle2 = workbook.createCellStyle();
headStyle2.setAlignment(HorizontalAlignment.CENTER);
headStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headFont2 = workbook.createFont();
headFont2.setBold(true);
headFont2.setColor(IndexedColors.WHITE.getIndex());
headStyle2.setFont(headFont2);
headStyle2.setFillForegroundColor(new XSSFColor(new java.awt.Color(170, 143, 116), null));
// 设置表头行高
groupRow.setHeightInPoints(28);
subRow.setHeightInPoints(24);
// 交替设置大类表头颜色
int colIdx = 2;
boolean useFirst = true;
int[] groupLens = {6,6,6,6,6,4,4,3}; // 各大类/分组的列数
for (int g = 0; g < groupLens.length; g++) {
CellStyle style = useFirst ? headStyle1 : headStyle2;
for (int i = 0; i < groupLens[g]; i++) {
if (groupRow.getCell(colIdx) != null) groupRow.getCell(colIdx).setCellStyle(style);
if (subRow.getCell(colIdx) != null) subRow.getCell(colIdx).setCellStyle(style);
colIdx++;
}
useFirst = !useFirst;
}
// 序号、地区表头样式
if (groupRow.getCell(0) != null) groupRow.getCell(0).setCellStyle(headStyle1);
if (subRow.getCell(0) != null) subRow.getCell(0).setCellStyle(headStyle1);
if (groupRow.getCell(1) != null) groupRow.getCell(1).setCellStyle(headStyle1);
if (subRow.getCell(1) != null) subRow.getCell(1).setCellStyle(headStyle1);
// 7. 写入数据（从第4行开始，index=3），并设置居中和行高
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
int rowIdx = 3;
int index = 1;
for (DataAnalysisExportVO vo : list) {
Row row = sheet.createRow(rowIdx++);
row.setHeightInPoints(22);
int c = 0;
Cell cell = row.createCell(c++); cell.setCellValue(index++); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getArea() == null ? "" : vo.getArea()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getTotal() == null ? 0 : vo.getTotal()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getCompleted() == null ? 0 : vo.getCompleted()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getUnderDisposal() == null ? 0 : vo.getUnderDisposal()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getTodo() == null ? 0 : vo.getTodo()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDisposalRate() == null ? "0.0%" : vo.getDisposalRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getApplauseRate() == null ? "0.0%" : vo.getApplauseRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getAppeal_total() == null ? 0 : vo.getAppeal_total()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getAppeal_completed() == null ? 0 : vo.getAppeal_completed()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getAppeal_underDisposal() == null ? 0 : vo.getAppeal_underDisposal()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getAppeal_todo() == null ? 0 : vo.getAppeal_todo()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getAppeal_disposalRate() == null ? "0.0%" : vo.getAppeal_disposalRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getAppeal_applauseRate() == null ? "0.0%" : vo.getAppeal_applauseRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDispute_total() == null ? 0 : vo.getDispute_total()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDispute_completed() == null ? 0 : vo.getDispute_completed()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDispute_underDisposal() == null ? 0 : vo.getDispute_underDisposal()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDispute_todo() == null ? 0 : vo.getDispute_todo()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDispute_disposalRate() == null ? "0.0%" : vo.getDispute_disposalRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDispute_applauseRate() == null ? "0.0%" : vo.getDispute_applauseRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSafety_total() == null ? 0 : vo.getSafety_total()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSafety_completed() == null ? 0 : vo.getSafety_completed()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSafety_underDisposal() == null ? 0 : vo.getSafety_underDisposal()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSafety_todo() == null ? 0 : vo.getSafety_todo()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSafety_disposalRate() == null ? "0.0%" : vo.getSafety_disposalRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSafety_applauseRate() == null ? "0.0%" : vo.getSafety_applauseRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getHelp_total() == null ? 0 : vo.getHelp_total()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getHelp_completed() == null ? 0 : vo.getHelp_completed()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getHelp_underDisposal() == null ? 0 : vo.getHelp_underDisposal()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getHelp_todo() == null ? 0 : vo.getHelp_todo()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getHelp_disposalRate() == null ? "0.0%" : vo.getHelp_disposalRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getHelp_applauseRate() == null ? "0.0%" : vo.getHelp_applauseRate()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getCommon() == null ? 0 : vo.getCommon()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getBig() == null ? 0 : vo.getBig()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getLarge() == null ? 0 : vo.getLarge()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSensitive() == null ? 0 : vo.getSensitive()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getPubReport() == null ? 0 : vo.getPubReport()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getCadreInvest() == null ? 0 : vo.getCadreInvest()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getSuperAssigned() == null ? 0 : vo.getSuperAssigned()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getPlatEntry() == null ? 0 : vo.getPlatEntry()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getTotalTips() == null ? 0 : vo.getTotalTips()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getOverTimeTips() == null ? 0 : vo.getOverTimeTips()); cell.setCellStyle(dataStyle);
cell = row.createCell(c++); cell.setCellValue(vo.getDissatisfiedTips() == null ? 0 : vo.getDissatisfiedTips()); cell.setCellStyle(dataStyle);
}
// 8. 自动列宽
for (int i = 0; i < totalColumns; i++) {
sheet.setColumnWidth(i, 256 * 12);
}
}
workbook.write(out);
}
workbook.close();
}
完成此异步方法的核心在于熟练使用XSSFWorkbook或者SXSSFWorkbook，Sheet sheet = workbook.createSheet(sheetNames[k]);以及其他.....
其次的就是导出数据的拼装：
public List<DataAnalysisExportVO> handleDataList(JSONArray dataArr){
List<DataAnalysisExportVO> exportList = new ArrayList<>();
for (int i = 0; i < dataArr.size(); i++) {
JSONObject areaObj = dataArr.getJSONObject(i);
String area = areaObj.getString("area");
DataAnalysisExportVO vo = new DataAnalysisExportVO();
int num = i + 1;
vo.setId((long)num);
vo.setArea(area);
xxxx
xxxx
xxxx
}
}
至此，完成多sheet页丰富样式的导出Excel功能。
