Java Excel多表头导出 及导出对象中包含集合导出
2024更新:可直接运行步骤5的demo代码查看效果,根据效果对代码做出部分调整即可满足自己需求,导出效果图见步骤5
1、导入依赖
这里导入的是常用的Alibaba的easyexcel,其实真正使用的是其依赖的poi相关依赖,导入poi相关依赖也可以
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
2、需求及效果展示
对应导出的一条数据,划线之前为基础数据,就只有一行;而划线后面的数据为list集合,且不同部门的集合长度不一致。
实际导出效果:
3、实体类部分展示
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ContractTimeReport {
//部门时效数据 为集合
private List<CountersignDeptTime> countersignDeptTimes;
private List<FinanceDeptTime> financeDeptTimes;
private List<LawDeptTime> lawDeptTimes;
private List<UndertakeDeptTime> undertakeDeptTimes;
private List<LeaderDeptTime> leaderDeptTimes;
//基础数据 为单行字段 省略很多其他
/**
* 流程型名称
*/
private String approveTypeName;
/**
* 送审日期
*/
private LocalDateTime sendApprovalDate;
private Long sendApprovalDateLong;
/**
* 审批通过日期
*/
private LocalDateTime approveEndTime;
private Long approveEndTimeLong;
}
4、逻辑处理
//要导出的结果集
List<ContractTimeReportExportVo> list = searchHitStream.map(SearchHit::getContent).collect(Collectors.toList());
//需要将集合中的对象转换成map
List<Map<String, Object>> mapList = getMaps(list);
//导出excel的表头,删除了许多字段
List<String> head = Arrays.asList("审签流程类型", "送审日期", "审批通过日期",
"部门名称", "员工姓名", "员工处理时长(小时)", "承办部门内部审签时长(小时)", "审签退回修改时长(小时)",
"部门名称", "员工姓名", "员工审签时长(小时)", "部门审签时长汇总(小时)",
"员工姓名", "员工审签时长(小时)", "部门审签时长汇总(小时)",
"员工姓名", "员工审签时长(小时)", "部门审签时长汇总(小时)");
HSSFWorkbook wb = null;
setResponseData(response);
ServletOutputStream outputStream = response.getOutputStream();
try {
wb = warpSingleWorkbook2("title", mapList, head);
} catch (Exception e) {
}
wb.write(outputStream);
对象转map
所需依赖:
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.4</version>
</dependency>
private List<Map<String, Object>> getMaps(List<ContractTimeReportExportVo> list) {
List<Map<String, Object>> mapList = new ArrayList<>();
for (ContractTimeReportExportVo contractTimeReport : list) {
//使用该方法将对象转成map后,对象中的list并没有同样转成map形式,所以需要再单独的将每个list转成map再赋值进去
BeanMap beanMap = new BeanMap(contractTimeReport);
HashMap map = new HashMap();
map.putAll(beanMap);
List<FinanceDeptTime> financeDeptTimesList = contractTimeReport.getFinanceDeptTimes();
List<HashMap> financeDeptTimes = new ArrayList<>();
if (financeDeptTimesList != null) {
for (FinanceDeptTime deptTime : financeDeptTimesList) {
BeanMap beanMap1 = new BeanMap(deptTime);
HashMap map1 = new HashMap();
map1.putAll(beanMap1);
map1.remove("class");
financeDeptTimes.add(map1);
}
}
List<LawDeptTime> lawDeptTimeList1 = contractTimeReport.getLawDeptTimes();
List<HashMap> financeDeptTimes1 = new ArrayList<>();
if (lawDeptTimeList1 != null) {
for (LawDeptTime deptTime : lawDeptTimeList1) {
BeanMap beanMap1 = new BeanMap(deptTime);
HashMap map1 = new HashMap();
map1.putAll(beanMap1);
map1.remove("class");
financeDeptTimes1.add(map1);
}
}
List<CountersignDeptTime> countersignDeptTimeList = contractTimeReport.getCountersignDeptTimes();
List<HashMap> countersignDeptTimes = new ArrayList<>();
if (countersignDeptTimeList != null) {
for (CountersignDeptTime deptTime : countersignDeptTimeList) {
BeanMap beanMap1 = new BeanMap(deptTime);
HashMap map1 = new HashMap();
map1.putAll(beanMap1);
map1.remove("class");
countersignDeptTimes.add(map1);
}
}
List<UndertakeDeptTime> undertakeDeptTimeList = contractTimeReport.getUndertakeDeptTimes();
List<HashMap> undertakeDeptTimes = new ArrayList<>();
if (undertakeDeptTimeList != null) {
for (UndertakeDeptTime deptTime : undertakeDeptTimeList) {
BeanMap beanMap1 = new BeanMap(deptTime);
HashMap map1 = new HashMap();
map1.putAll(beanMap1);
map1.remove("class");
undertakeDeptTimes.add(map1);
}
}
map.put("financeDeptTimes", financeDeptTimes);
map.put("lawDeptTimes", financeDeptTimes1);
map.put("countersignDeptTimes", countersignDeptTimes);
map.put("undertakeDeptTimes", undertakeDeptTimes);
map.remove("class");
mapList.add(map);
}
return mapList;
}
主要处理逻辑:
public static HSSFWorkbook warpSingleWorkbook2(String title, List<Map<String, Object>> mapList, List<String> head) {
List<List<Integer>> mergeParams = new ArrayList<>();
String[] str1 = {"globalsn", "localsn", "money", "vatRate", "vaTax", "unVatAmount", "contractName", "classifyName",
"weSignName", "otherSignName", "templateName", "moneyTypeName", "priorityName", "degreeName", "selectModelName",
"budgetName", "takerName", "approveTypeName", "sendApprovalDate", "approveEndTime"};
String lawDeptTimes = "lawDeptTimes";
String financeDeptTimes = "financeDeptTimes";
String countersignDeptTimes = "countersignDeptTimes";
String undertakeDeptTimes = "undertakeDeptTimes";
String[] str2 = {"deptName", "staffName", "personTimeHour", "deptTimeHour", "backTimeHour"};
String[] str3 = {"deptName", "staffName", "personTimeHour", "deptTimeHour"};
String[] str4 = {"staffName", "personTimeHour", "deptTimeHour"};
String[] str5 = {"staffName", "personTimeHour", "deptTimeHour"};
String[] str6 = {"allTimeHour", "signTimeHour", "finishTimeHour"};
// 如果要设置背景色 最好用 XSSFWorkbook
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet(title);
sheet.setDefaultColumnWidth(20);
HSSFCellStyle style = book.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle headStyle = book.createCellStyle();
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
// 生成表头第一行
HSSFRow headRow0 = sheet.createRow(0);
setExcelValue(headRow0.createCell(0), "title", headStyle);
mergeParams.add(Arrays.asList(0, 0, 0, 37));
//生成表头第二行
HSSFRow headRow1 = sheet.createRow(1);
mergeParams.add(Arrays.asList(1, 1, 20, 24));
mergeParams.add(Arrays.asList(1, 1, 25, 28));
mergeParams.add(Arrays.asList(1, 1, 29, 31));
mergeParams.add(Arrays.asList(1, 1, 32, 34));
setExcelValue(headRow1.createCell(20), "承办部门", headStyle);
setExcelValue(headRow1.createCell(25), "会签部门", headStyle);
setExcelValue(headRow1.createCell(29), "财务部门", headStyle);
setExcelValue(headRow1.createCell(32), "法律部门", headStyle);
for (int i = 0; i < 20; i++) {
setExcelValue(headRow1.createCell(i), head.get(i), headStyle);
}
for (int i = 35; i < 38; i++) {
setExcelValue(headRow1.createCell(i), head.get(i), headStyle);
}
//生成表头第三行
HSSFRow headRow = sheet.createRow(2);
for (int i = 20; i < 35; i++) {
setExcelValue(headRow.createCell(i), head.get(i), headStyle);
}
for (int i = 0; i < 20; i++) {
List<Integer> mergeParam = Arrays.asList(1, 2, i, i);
mergeParams.add(mergeParam);
}
for (int i = 35; i < 38; i++) {
List<Integer> mergeParam = Arrays.asList(1, 2, i, i);
mergeParams.add(mergeParam);
}
//循环处理数据,主要逻辑是,找出单条数据中所有集合的最大长度,然后循环遍历所有集合给单元格赋值
int rowIndex = 3;
int commonTotalSize = 20;
for (Map<String, Object> map : mapList) {
// 记录合并的开始行
int startRowIndex = rowIndex;
HSSFRow bodyRow = sheet.createRow(rowIndex++);
for (int i = 0; i < str1.length; i++) {
setExcelValue(bodyRow.createCell(i), map.get(str1[i]), style);
}
for (int i = 35; i < 38; i++) {
setExcelValue(bodyRow.createCell(i), map.get(str6[i - 35]), style);
}
List<Map<String, Object>> list1 = (List<Map<String, Object>>) map.get(undertakeDeptTimes);
List<Map<String, Object>> list2 = (List<Map<String, Object>>) map.get(countersignDeptTimes);
List<Map<String, Object>> list3 = (List<Map<String, Object>>) map.get(financeDeptTimes);
List<Map<String, Object>> list4 = (List<Map<String, Object>>) map.get(lawDeptTimes);
int maxLine = list1.size() > list2.size() ? list1.size() : list2.size();
maxLine = maxLine > list3.size() ? maxLine : list3.size();
maxLine = maxLine > list4.size() ? maxLine : list4.size();
if (list1.size() > 0) {
for (int i = 0; i < str2.length; i++) {
setExcelValue(bodyRow.createCell(str1.length + i), list1.get(0).get(str2[i]), style);
}
}
if (list2.size() > 0) {
for (int i = 0; i < str3.length; i++) {
setExcelValue(bodyRow.createCell(str1.length + str2.length + i), list2.get(0).get(str3[i]), style);
}
}
if (list3.size() > 0) {
for (int i = 0; i < str4.length; i++) {
setExcelValue(bodyRow.createCell(str1.length + str2.length + str3.length + i), list3.get(0).get(str4[i]), style);
}
}
if (list4.size() > 0) {
for (int i = 0; i < str5.length; i++) {
setExcelValue(bodyRow.createCell(str1.length + str2.length + str3.length + str4.length + i), list4.get(0).get(str5[i]), style);
}
}
for (int i = 1; i < maxLine; i++) {
HSSFRow bodyRow2 = sheet.createRow(rowIndex++);
if (list1.size() > i) {
for (int j = 0; j < str2.length; j++) {
setExcelValue(bodyRow2.createCell(str1.length + j), list1.get(i).get(str2[j]), style);
}
}
if (list2.size() > i) {
for (int j = 0; j < str3.length; j++) {
setExcelValue(bodyRow2.createCell(str1.length + str2.length + j), list2.get(i).get(str3[j]), style);
}
}
if (list3.size() > i) {
for (int j = 0; j < str4.length; j++) {
setExcelValue(bodyRow2.createCell(str1.length + str2.length + str3.length + j), list3.get(i).get(str4[j]), style);
}
}
if (list4.size() > i) {
for (int j = 0; j < str5.length; j++) {
setExcelValue(bodyRow2.createCell(str1.length + str2.length + str3.length + str4.length + j), list4.get(i).get(str5[j]), style);
}
}
}
//记录需要合并的单元格,后面统一合并
if (maxLine > 1) {
// 依次放入 起始行 结束行 起始列 结束列
for (int i = 0; i < commonTotalSize; i++) {
List<Integer> mergeParam = Arrays.asList(startRowIndex, rowIndex - 1, i, i);
mergeParams.add(mergeParam);
}
for (int i = 35; i < 38; i++) {
List<Integer> mergeParam = Arrays.asList(startRowIndex, rowIndex - 1, i, i);
mergeParams.add(mergeParam);
}
}
}
for (List<Integer> list : mergeParams) {
sheet.addMergedRegion(new CellRangeAddress(list.get(0), list.get(1), list.get(2), list.get(3)));
}
mergeCell(sheet);
return book;
}
//合并表头的单元格,根据自己的实际情况来配置参数
private static void mergeCell(HSSFSheet sheet) {
MergeOne(sheet, new CellRangeAddress(0, 0, 0, 37));
MergeOne(sheet, new CellRangeAddress(1, 1, 20, 24));
MergeOne(sheet, new CellRangeAddress(1, 1, 25, 28));
MergeOne(sheet, new CellRangeAddress(1, 1, 29, 31));
MergeOne(sheet, new CellRangeAddress(1, 1, 32, 34));
for (int i = 0; i < 20; i++) {
CellRangeAddress regionThird = new CellRangeAddress(1, 2, i, i);
MergeOne(sheet, regionThird);
}
for (int i = 20; i < 35; i++) {
CellRangeAddress regionThird = new CellRangeAddress(2, 2, i, i);
MergeOne(sheet, regionThird);
}
for (int i = 35; i < 38; i++) {
CellRangeAddress regionThird = new CellRangeAddress(1, 2, i, i);
MergeOne(sheet, regionThird);
}
}
private static void MergeOne(HSSFSheet sheet, CellRangeAddress region) {
RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
}
public static void setExcelValue(HSSFCell cell, Object value, HSSFCellStyle style) {
// 写数据
if (value == null) {
cell.setCellValue("");
} else {
if (value instanceof Integer || value instanceof Long) {
cell.setCellValue(Long.parseLong(value.toString()));
} else if (value instanceof BigDecimal) {
//((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue()
cell.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
cell.setCellStyle(style);
}
}
5、2024更新
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class ExcelExportExample1 {
public static void exportOrdersToExcel(List<Order> orders, List<String> headNames, List<Integer> mergeNums, List<Integer> colWidths) throws Exception {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Orders");
// 创建一个单元格样式
CellStyle style = workbook.createCellStyle();
// 设置样式,带边框、文字居中
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
int rowNum = 0;
//设置表头
Row headerRow = sheet.createRow(rowNum++);
headerRow.setHeightInPoints(20);
for (int i = 0; i < headNames.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(headNames.get(i));
}
for (int i = 0; i < orders.size(); i++) {
Order order = orders.get(i);
//设置单条数据第一行
Row orderRow = sheet.createRow(rowNum++);
orderRow.setHeightInPoints(20);
Cell cell4 = orderRow.createCell(0);
cell4.setCellValue(order.getOrderId());
Cell cell7 = orderRow.createCell(3);
cell7.setCellValue(order.getOrderNo());
//集合中一条数据与主数据在同一行
List<Item> items = order.getItems();
Item item1 = items.get(0);
Cell cell5 = orderRow.createCell(1);
cell5.setCellValue(item1.getItemId());
Cell cell6 = orderRow.createCell(2);
cell6.setCellValue(item1.getDescription());
//设置样式
cell4.setCellStyle(style);
cell5.setCellStyle(style);
cell6.setCellStyle(style);
cell7.setCellStyle(style);
//集合第一条数据与主数据在同一行,设置值需去除集合第一行数据
items.remove(0);
//设置单集合数据
for (Item item : items) {
Row itemRow = sheet.createRow(rowNum++);
itemRow.setHeightInPoints(20);
Cell cell8 = itemRow.createCell(0);
cell8.setBlank(); // Order ID blank for items
Cell cell9 = itemRow.createCell(1);
cell9.setCellValue(item.getItemId());
Cell cell10 = itemRow.createCell(2);
cell10.setCellValue(item.getDescription());
Cell cell11 = itemRow.createCell(3);
cell11.setBlank(); // Order ID blank for items
cell8.setCellStyle(style);
cell9.setCellStyle(style);
cell10.setCellStyle(style);
cell11.setCellStyle(style);
}
//合并单元格
CellRangeAddress cellRange1;
if (i == 0) {
for (Integer mergeNum : mergeNums) {
cellRange1 = new CellRangeAddress(1, items.size() + 1, mergeNum, mergeNum);
sheet.addMergedRegion(cellRange1);
}
} else {
for (Integer mergeNum : mergeNums) {
cellRange1 = new CellRangeAddress(i + 1 + orders.get(i - 1).getItems().size(), i + 1 + orders.get(i - 1).getItems().size() + items.size(), mergeNum, mergeNum);
sheet.addMergedRegion(cellRange1);
}
}
}
// 设置列宽,之所以固定列宽,是因为poi的自适应列宽对中文支持不太友好
for (int i = 0; i < colWidths.size(); i++) {
sheet.setColumnWidth(i, colWidths.get(i) * 256 + 185);
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("orders.xlsx");
workbook.write(fileOut);
fileOut.close();
workbook.close();
}
public static void main(String[] args) throws Exception {
List<Order> orders = new ArrayList<>();
Order order = new Order();
order.setOrderId("21");
order.setOrderNo("dfffffff");
Item item1 = new Item("封建势力放假配额个", "hahah");
Item item2 = new Item("2", "hahah");
Item item3 = new Item("3", "hahah");
List<Item> itemList = new ArrayList<>();
itemList.add(item1);
itemList.add(item2);
itemList.add(item3);
order.setItems(itemList);
// Populate orders and items...
orders.add(order);
Order order2 = new Order();
order2.setOrderId("23233");
order2.setOrderNo("xxxxxxxx");
Item item4 = new Item("1", "hahah");
Item item5 = new Item("2", "hahah");
Item item6 = new Item("3", "hahah");
List<Item> itemList2 = new ArrayList<>();
itemList2.add(item4);
itemList2.add(item5);
itemList2.add(item6);
order2.setItems(itemList2);
orders.add(order2);
//表头名称集合
List<String> headNames = new ArrayList<>();
headNames.add("order id但是反反复复反反复复方法为");
headNames.add("item id");
headNames.add("item desc");
headNames.add("order no");
//需要合并的列号集合
List<Integer> mergeNums = Arrays.asList(0, 3);
//设置列宽,已字符数为基准
List<Integer> colWidths = Arrays.asList(10,30,15,20);
exportOrdersToExcel(orders, headNames, mergeNums,colWidths);
}
}
实体类
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
@AllArgsConstructor
public class Item {
private String itemId;
private String description;
// 构造函数、getter和setter
}
import lombok.Data;
import java.util.List;
@Data
public class Order {
private String orderId;
private String orderNo;
private List<Item> items;
// 构造函数、getter和setter
}
步骤5得到效果