主要是根据poi包中的 CellRangeAddress 来使用
现在需求是:根据一对多的两表关系生成一个带合并单元格的Excel,使两表在Excel里的数据表现形式也是一对多
类似于这种
解决方案,搜索了半天也只有添加CellRangeAddress这方法,只要计算好起始和终止的行号列号还是可以用的。因为其是直接添加到sheet类里,另外生成的合并单元格似乎也能自动把相同数据的单元格处理掉,所以我们可以先按正常的一对一添加完数据,再去添加合并单元格这两个互不干扰。
CellRangeAddress(int, int, int, int)
参数:起始行号,终止行号, 起始列号,终止列号
代码:
ExcelUtil.java
参数,os是等会要输出excel的流,excelExtName是文件后缀,data是数据(最外面一层Map是工作表名,下面一层List是工作表各行,再下一行List是一行里各单元格),regionData是合并单元格,因为只需要加到sheet层(工作表),map即可
public static void writeExcelWithCollap(OutputStream os,String excelExtName,Map<String,List<List<String>>>data,Map<String,List<CellRangeAddress>> regionsData)throws IOException {
Workbook wb=null;
try {
if ("xls".equals(excelExtName)) {
wb = new HSSFWorkbook();
} else if ("xlsx".equals(excelExtName)) {
wb = new XSSFWorkbook();
} else {
throw new Exception("当前文件不是excel文件");
}
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);
for (String sheetName : data.keySet()) {
Sheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(40);
List<CellRangeAddress> regions = regionsData.get(sheetName);
List<List<String>> rowList = data.get(sheetName);
for (int i=0; i < rowList.size(); i++) {
List<String> cellList = rowList.get(i);
Row row = sheet.createRow(i);
for (int j = 0; j < cellList.size(); j++) {
Cell cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellValue(cellList.get(j));
}
}
for (CellRangeAddress region : regions) {
sheet.addMergedRegion(region);
}
}
wb.write(os);
} catch (Exception e) {
e.printStackTrace();
}finally {
if (wb != null) {
wb.close();
}
}
}
ExcelService.java
关键是要计算好合并单元格的大小
public void downloadCheckProject(ServletOutputStream servletOutputStream) throws IOException {
Map<String, List<List<String>>> data = new HashMap<>();
Map<String, List<CellRangeAddress>> regionData = new HashMap<>();
List<List<String>> rows = new ArrayList<>();
List<CellRangeAddress> regions = new ArrayList<>();
//添加excel的行标题
List<String> titles = new ArrayList<>();
titles.add(ExcelTitle.CPROJECTNAME);
titles.add(ExcelTitle.CLEADER);
titles.add(ExcelTitle.CTASKNAME);
titles.add(ExcelTitle.CDEPARTMENT);
titles.add(ExcelTitle.CPROGRESS);
titles.add(ExcelTitle.CFINISHED);
rows.add(titles);
//取出要导出的数据
List<CheckProject> checkProjects = checkProjectMapper.selectList(new QueryWrapper<>());
List<CheckProjectTask> checkProjectTasks = checkProjectTaskMapper.selectList(new QueryWrapper<>());
//用于标识遍历的位置
int index = 1;
for (CheckProject checkProject : checkProjects) {
//checkProject表通过taskList对应多个task
String taskList = checkProject.getTaskList();
Long id = checkProject.getId();
if (!Util.isNullOrEmpty(taskList) && taskList.trim().length() > 0) {
String[] taskLists = taskList.split(",");
//判断对应task表的长度来决定合并单元格跨越的行数
if (taskLists.length > 1) {
CellRangeAddress region = new CellRangeAddress(index, index - 1 + taskLists.length, 0, 0);
CellRangeAddress region2 = new CellRangeAddress(index, index - 1 + taskLists.length, 1, 1);
regions.add(region);
regions.add(region2);
}
//更新索引
index += taskLists.length;
//task表通过workId 来对应 project,当然也可以用stream的collectors::groupingby(CheckProjectTask::getWorkId()) 方法
List<CheckProjectTask> temp = checkProjectTasks.stream().filter(p -> p.getWorkId().equals(id)).collect(Collectors.toList());
for (CheckProjectTask task : temp) {
List<String> row = new ArrayList<>();
row.add(checkProject.getProjectName());
row.add(checkProject.getLeader());
row.add(task.getTaskName());
row.add(task.getDepartment());
row.add(task.getProgress());
row.add(task.getFinished());
rows.add(row);
}
} else {
List<String> row = new ArrayList<>();
row.add(checkProject.getProjectName());
row.add(checkProject.getLeader());
rows.add(row);
index++;
}
}
//添加数据
data.put(ExcelTitle.CCHECKPROJECT, rows);
//添加合并单元格
regionData.put(ExcelTitle.CCHECKPROJECT, regions);
ExcelUtil.writeExcelWithCollap(servletOutputStream,"xlsx",data,regionData);
}
ExcelApi.java
@PostMapping("/download")
public void downloadCheckProject(HttpServletResponse response)throws IOException {
response.setHeader("Content-disposition","attachment;filename="+
new String("download.xlsx".getBytes("UTF-8"),"iso8859-1"));
ServletOutputStream servletOutputStream = response.getOutputStream();
excelService.downloadCheckProject(servletOutputStream);
servletOutputStream.flush();
servletOutputStream.close();
}