一个对象集合（列表内容），一个对象（该对象后面需要我合并）
注释：这个只是一个参考，并不通用的，提供一种思路
工具类方法
public static Workbook getWorkBookEntry(Map> maps, Map map2, int[] mergeIndex) {
XSSFWorkbook workbook = new XSSFWorkbook();
int n = 0;
for (Map.Entry> entry : maps.entrySet()) {
Sheet sheet = null;
try {
sheet = workbook.createSheet();
sheet.setColumnWidth(0, 20 * 256);
workbook.setSheetName(n, entry.getKey());
workbook.setSelectedTab(0);
n++;
} catch (Exception e) {
e.printStackTrace();
}
List lists = entry.getValue();
List list = null;
list = lists.stream().map(nineClassSubclassVo -> {
NineClassSubclass2Vo nineClassSubclass2Vo = new NineClassSubclass2Vo();
//将userCheckPO拷贝给userCheckData
BeanUtils.copyProperties(nineClassSubclassVo, nineClassSubclass2Vo);
return nineClassSubclass2Vo;
}).collect(Collectors.toList());
// 获取实体所有属性
Field[] fields = list.get(0).getClass().getDeclaredFields();
// 列索引
int index = 0;
// 列名称
String name = "";
MyAnnotation myAnnotation;
// 创建表头
Row row = sheet.createRow(0);
//设置每列的宽度
sheet.setColumnWidth(1, 100 * 70);
sheet.setColumnWidth(2, 100 * 40);
sheet.setColumnWidth(3, 100 * 70);
sheet.setColumnWidth(4, 100 * 40);
sheet.setColumnWidth(6, 100 * 70);
for (Field f : fields) {
// 是否是注解
if (f.isAnnotationPresent(MyAnnotation.class)) {
// 获取注解
myAnnotation = f.getAnnotation(MyAnnotation.class);
// 获取列索引
index = myAnnotation.columnIndex();
// 列名称
name = myAnnotation.columnName();
// 创建单元格
Cell cell = row.createCell(index, Cell.CELL_TYPE_STRING);
cell.setCellValue(name);
//处理单元格等样式问题
XSSFCellStyle style = handleCellStyle(workbook);
cell.setCellStyle(style);
}
}
//根据第一列的内容判断后面的列内容需要合并 声明需要除了第一类外的合并的单元格数据
List mergeCellIndexVos = new ArrayList<>();
//有序的分组
linkedHashMap> collect = list.stream().collect(Collectors.groupingBy(NineClassSubclass2Vo::getLabelParentName, linkedHashMap::new, Collectors.toList()));
List strings = new ArrayList<>();
collect.forEach((k, v) -> {
strings.add(k);
});
int t = 1;
for (int i = 0; i < strings.size(); i++) {
if (i == 0) {
if (collect.get(strings.get(i)).size() == 1) {
t = t + 1;
} else {
MergeCellIndexVo mergeCellIndexVo = MergeCellIndexVo.builder()
.firsCol(6)
.lastCol(6)
.firstRow(t)
.lastRow(collect.get(strings.get(i)).size() + t -1)
.build();
t = collect.get(strings.get(i)).size() + t - 1;
mergeCellIndexVos.add(mergeCellIndexVo);
}
} else {
if (collect.get(strings.get(i)).size() == 1) {
t = t + 1;
} else {
MergeCellIndexVo mergeCellIndexVo = MergeCellIndexVo.builder()
.firsCol(6)
.lastCol(6)
.firstRow(t + 1)
.lastRow(collect.get(strings.get(i)).size() + t)
.build();
t = t + collect.get(strings.get(i)).size();
mergeCellIndexVos.add(mergeCellIndexVo);
}
}
}
List poiModels = new ArrayList();
// 行索引 因为表头已经设置，索引行索引从1开始
int rowIndex = 1;
for (Object obj : list) {
// 创建新行，索引加1,为创建下一行做准备
row = sheet.createRow(rowIndex);
for (int i = 0; i < fields.length; i++) {
// 设置属性可访问
fields[i].setAccessible(true);
// 判断是否是注解
if (fields[i].isAnnotationPresent(MyAnnotation.class)) {
// 获取注解
myAnnotation = fields[i].getAnnotation(MyAnnotation.class);
try {
String old = "";
if (rowIndex > 1) {
old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
}
for (int j = 0; j < mergeIndex.length; j++) {
if (rowIndex == 1) {
PoiModel poiModel = new PoiModel();
poiModel.setOldContent(String.valueOf(fields[i].get(obj)));
poiModel.setContent(String.valueOf(fields[i].get(obj)));
poiModel.setRowIndex(1);
poiModel.setCellIndex(i);
poiModels.add(poiModel);
break;
} else if (i > 0 && mergeIndex[j] == i) {
if (!poiModels.get(i).getContent().equals(String.valueOf(fields[i].get(obj))) || poiModels.get(i).getContent().equals(String.valueOf(fields[i].get(obj))) && !poiModels.get(i - 1).getOldContent().equals(String.valueOf(fields[i - 1].get(obj)))) {
//合并单元格区域只有一个单元格时，不合并
if (poiModels.get(i).getRowIndex() == rowIndex - 1 && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()) {
continue;
}
CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), rowIndex - 1, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
//在sheet里增加合并单元格
sheet.addMergedRegion(cra);
poiModels.get(i).setContent(String.valueOf(fields[i].get(obj)));
poiModels.get(i).setRowIndex(rowIndex);
poiModels.get(i).setCellIndex(i);
}
}
if (mergeIndex[j] == i && i == 0 && !poiModels.get(i).getContent().equals(String.valueOf(fields[i].get(obj)))) {
CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), rowIndex - 1, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
//在sheet里增加合并单元格
sheet.addMergedRegion(cra);
poiModels.get(i).setContent(String.valueOf(fields[i].get(obj)));
poiModels.get(i).setRowIndex(rowIndex);
poiModels.get(i).setCellIndex(i);
}
if (mergeIndex[j] == i && rowIndex == list.size()) {
//合并单元格区域只有一个单元格时，不合并
if (poiModels.get(i).getRowIndex() == rowIndex && poiModels.get(i).getCellIndex() == poiModels.get(i).getCellIndex()) {
continue;
}
CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), rowIndex, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
//在sheet里增加合并单元格
sheet.addMergedRegion(cra);
}
}
Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
cell.setCellValue(String.valueOf(fields[i].get(obj)));
XSSFCellStyle style = handleCellStyle(workbook);
cell.setCellStyle(style);
poiModels.get(i).setOldContent(old);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
rowIndex++;
}
//合并每个大类的发现率的单元格数据
if (!CollectionUtils.isEmpty(mergeCellIndexVos)) {
for (int i = 0; i < mergeCellIndexVos.size(); i++) {
sheet.addMergedRegion(new CellRangeAddress(mergeCellIndexVos.get(i).getFirstRow(),mergeCellIndexVos.get(i).getLastRow(),mergeCellIndexVos.get(i).getFirsCol(),mergeCellIndexVos.get(i).getLastCol()));
}
}
//计算总体发现率
int lastRowNum = lists.size() + 1;
Row lastrow = sheet.createRow(lastRowNum);
//一会需要合并0到5
Cell cellFirst1 = lastrow.createCell(0, Cell.CELL_TYPE_STRING);
cellFirst1.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
Cell cellFirst2 = lastrow.createCell(1, Cell.CELL_TYPE_STRING);
cellFirst2.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
Cell cellFirst3 = lastrow.createCell(2, Cell.CELL_TYPE_STRING);
cellFirst3.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
Cell cellFirst4 = lastrow.createCell(3, Cell.CELL_TYPE_STRING);
cellFirst4.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
Cell cellFirst5 = lastrow.createCell(4, Cell.CELL_TYPE_STRING);
cellFirst5.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
Cell cellFirst6 = lastrow.createCell(5, Cell.CELL_TYPE_STRING);
cellFirst6.setCellValue(map2.get(entry.getKey()).getAllFoundRateName());
Cell cellLast = lastrow.createCell(6, Cell.CELL_TYPE_STRING);
cellLast.setCellValue(map2.get(entry.getKey()).getAllFoundRateNum());
//处理单元格等样式问题
XSSFCellStyle style = handleCellStyle(workbook);
cellFirst1.setCellStyle(style);
cellFirst2.setCellStyle(style);
cellFirst3.setCellStyle(style);
cellFirst4.setCellStyle(style);
cellFirst5.setCellStyle(style);
cellFirst6.setCellStyle(style);
cellLast.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(lastRowNum,lastRowNum,0,5));
lastrow.setHeight((short) (25 * 20));
}
return workbook;
}
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
}
private static XSSFCellStyle handleCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
// 指定单元格居中对齐
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 指定单元格自动换行
style.setWrapText(true);
// 设置字体
XSSFFont font = workbook.createFont();
//font.setBoldweight(fontbold);
font.setFontName("宋体");
font.setFontHeight((short) 300);
style.setFont(font);
setBorder(style, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
return style;
}
public static void setResponseStream(HttpServletResponse response, String fileName) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception e) {
new RuntimeException("导出失败，原因：" + e.getMessage());
}
}
实体类
package com.jydw.drone.entity.statistics.respvo; import com.jydw.drone.statistics.MyAnnotation; import lombok.*; @Data @ToString @AllArgsConstructor @NoArgsConstructor @Builder public class NineClassSubclassVo implements Comparable{ private Integer factoryId; private String factoryName; private String labelId; @MyAnnotation(columnIndex=1,columnName="缺陷描述") private String labelName; private String labelParentId; @MyAnnotation(columnIndex=0,columnName="缺陷类别") private String labelParentName; private Integer yesNum; private Integer leakNum; private Integer errorNum; private Integer foundSon; @MyAnnotation(columnIndex=2,columnName="缺陷数量") private Integer foundMother; @MyAnnotation(columnIndex=4,columnName="发现率") private String foundRate; private Integer falseSon; private Integer falseMother; @MyAnnotation(columnIndex=5,columnName="误检比") private String falseRate; private Double totalFoundRate; private String totalFoundRateName; private String totalFalseRate; @MyAnnotation(columnIndex=3,columnName="占缺陷数量比例") private String oneTotalNumRate; private Integer orderParent; private Integer orderSon; private String foundAllRate; @Override public int compareTo(NineClassSubclassVo o) { if (this.getFactoryId() > o.getFactoryId()) { return 1; } else if (this.getFactoryId() < o.getFactoryId()) { return -1; } else { if (this.getOrderParent() > o.getOrderParent()) { return 1; } else if (this.getOrderParent() < o.getOrderParent()) { return -1; } else { if (this.getOrderSon() > o.getOrderSon()) { return 1; } else if (this.getOrderSon() < o.getOrderSon()) { return -1; } else { return 0; } } } } }
注解
package com.jydw.drone.statistics;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface MyAnnotation {
public int columnIndex() default 0;
public String columnName() default "";
}
中间转换类
package com.jydw.drone.entity.statistics.respvo;
import lombok.Data;
@Data
public class PoiModel {
public String oldContent;
public String content;
public Integer rowIndex;
public Integer cellIndex;
}
结果如下图
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）