预览的导出效果
实现代码
Workbook wb = new XSSFWorkbook();
String sheetName = "全过程咨询";
List<ExcelTitle> excelTitles = new ArrayList<>();
excelTitles.add(new ExcelTitle("序号" , null));
excelTitles.add(new ExcelTitle("业务部/分公司" , null));
excelTitles.add(new ExcelTitle("工程实施项目部" , null));
List<String> columns = new ArrayList<String>(){{
add("公司整体形象");
add("合同履行情况及信誉度");
}};
excelTitles.add(new ExcelTitle("工程监理过程控制能力/招标项目服务水平能力/项目咨询造价控制能力" , columns));
excelTitles.add(new ExcelTitle("阿达瓦达瓦萨达" , null));
columns = new ArrayList<String>(){{
add("项目服务质量、进度控制/咨询服务质量、进度控制");
add("项目服务的造价控制/咨询业务能力");
add("项目服务的安全控制/咨询服务的时间安排");
}};
excelTitles.add(new ExcelTitle("公司整体实力" , columns));
excelTitles.add(new ExcelTitle("发干哈阿斯达斯的" , null));
columns = new ArrayList<String>(){{
add("项目服务质量、进度控制/咨询服务质量、进度控制");
add("项目服务的造价控制/咨询业务能力");
add("项目服务的安全控制/咨询服务的时间安排");
add("项目服务的造价控制/咨询业务能力");
add("项目服务的安全控制/咨询服务的时间安排");
}};
excelTitles.add(new ExcelTitle("4566666666546456564564" , columns));
// 计算实际列数
int countColNums = 0;
for (ExcelTitle excelTitle : excelTitles) {
if(excelTitle.getColumns() != null){
// 如果有子项，那么不统计父项的数量 不然会多出列数
countColNums += excelTitle.getColumns().size();
}else{
countColNums++;
}
}
Sheet sheet = wb.createSheet(sheetName);
int rowSize = 1;
Row row = sheet.createRow(rowSize);
row.setHeight((short) (50*20));
// 第四步，创建单元格，并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
//设置样式对齐方式：水平\垂直居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.GOLD.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 单元格边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
// 文本自动换行
style.setWrapText(true);
int columnWidth = 150*20;
// 记录父项开始的列数
Map<String, Integer> titleStartColNumMap = new HashMap<>();
// 创建标题
int n = 0;
for (int i = 0; i < countColNums; i++) {
ExcelTitle excelTitle = excelTitles.get(n);
// 合并开始列
int startRangeSize = i;
// 合并结束列
int endRangeColSize = i;
if(excelTitle.getColumns() != null){
titleStartColNumMap.put(excelTitle.getName() , i);
for (int k = 0; k < excelTitle.getColumns().size(); k++) {
Cell cell = row.createCell(i + k);
cell.setCellStyle(style);
cell.setCellValue(excelTitle.getName());
sheet.setColumnWidth((short) i + k , columnWidth);
}
i += excelTitle.getColumns().size() - 1;
endRangeColSize = i;
}else{
Cell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(excelTitle.getName());
sheet.setColumnWidth((short) i , columnWidth);
}
int rangeRowSize = excelTitle.getColumns() == null ? row.getRowNum() + 1 : row.getRowNum();
// 合并单元格
CellRangeAddress rangeAddress = new CellRangeAddress(row.getRowNum(), rangeRowSize, startRangeSize, endRangeColSize);
sheet.addMergedRegion(rangeAddress);
// 增加合并后的单元格边框
RegionUtil.setBorderBottom(BorderStyle.THIN, rangeAddress, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, rangeAddress, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, rangeAddress, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, rangeAddress, sheet); // 上边框
n++;
}
row = sheet.getRow(++rowSize);
row.setHeight((short) (50*20));
// 将子项内容添加至父项下面的单元格
for (int i = 0; i < excelTitles.size(); i++) {
ExcelTitle excelTitle = excelTitles.get(i);
if(excelTitle.getColumns() != null){
for (int j = 0; j < excelTitle.getColumns().size(); j++) {
Cell cell = row.createCell(titleStartColNumMap.get(excelTitle.getName()) + j);
cell.setCellStyle(style);
cell.setCellValue(excelTitle.getColumns().get(j));
sheet.setColumnWidth((short) i , columnWidth);
}
}
}
row = sheet.createRow(0);
row.setHeight((short) (30*20));
style = wb.createCellStyle();
//设置样式对齐方式：水平\垂直居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 标题
Cell cell = row.createCell(0);
cell.setCellValue("2020年上半年（ ）分公司（部门）监理、咨询客户满意度调查情况明细");
cell.setCellStyle(style);
CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, countColNums - 1);
sheet.addMergedRegion(rangeAddress);
file.getParentFile().mkdirs();
FileOutputStream os = new FileOutputStream(file);
wb.write(os);
os.flush();
os.close();
public static class ExcelTitle {
private String name;
private List<String> columns;
public ExcelTitle() {
}
public ExcelTitle(String name, List<String> columns) {
this.name = name;
this.columns = columns;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<String> getColumns() {
return columns;
}
public void setColumns(List<String> columns) {
this.columns = columns;
}
}
pom.xml 版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
原文地址：https://www.cnblogs.com/u10101/p/15163634.html