最终效果
自定义构建表信息(实体类中)
构建表样式
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置边框样式
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 14);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
//contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置 自动换行
contentWriteCellStyle.setWrapped(true);
//设置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置 水平居中
// contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
构建表头
在相邻格add相同内容时会自动合并单元格
public static List<List<String>> buildHeaders(String guid,String equipmentCode,String address) {
String pointCodeHeader = "管道编号:"+guid;
String equipmentCodeHeader = "设备编号:"+equipmentCode;
String addressHeader = "地址:"+address;
List<List<String>> list = new ArrayList<>();
List<String> time = new ArrayList<>();
time.add(pointCodeHeader);
time.add(addressHeader);
time.add("监测时间");
List<String> ch4Value = new ArrayList<>();
ch4Value.add(pointCodeHeader);
ch4Value.add(addressHeader);
ch4Value.add("通电点位(V)");
List<String> temperature = new ArrayList<>();
temperature.add(pointCodeHeader);
temperature.add(addressHeader);
temperature.add("断电点位(V)");
List<String> humidity = new ArrayList<>();
humidity.add(equipmentCodeHeader);
humidity.add(addressHeader);
humidity.add("交流电压(V)");
List<String> voltage = new ArrayList<>();
voltage.add(equipmentCodeHeader);
voltage.add(addressHeader);
voltage.add("电池电压(V)");
list.add(time);
list.add(ch4Value);
list.add(temperature);
list.add(humidity);
list.add(voltage);
return list;
}
自定义表头样式
public static SheetWriteHandler getSheetWriteHandler() {
return new SheetWriteHandler() {
//配置参数
public int colSplit = 0, rowSplit = 3, leftmostColumn = 0, topRow = 4;
//表头大概范围
public String autoFilterRange = "A3:E3";
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
}
};
}
4.自定义输出宽度
public static AbstractColumnWidthStyleStrategy getColumnWidthStyleStrategy() {
return new AbstractColumnWidthStyleStrategy() {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 测试为 COLUMN 宽度定制.
if (isHead && cell.getRowIndex() == 2) {
int columnWidth = cell.getStringCellValue().getBytes().length;
int cellIndex = cell.getColumnIndex();
//第一列宽度为18,其他列为10
switch (cellIndex) {
case 0:
columnWidth = 18;
break;
default:
columnWidth = 10;
break;
}
writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
}
};
};
}
加载表实体数据(逻辑代码中)
加载表头数据
public void deviceHistoryData(DeviceHistoryQuery query, HttpServletResponse response) throws IOException {
//数据获取
List<StrayCurrentHistoryVO> strayCurrentHistoryVos = new ArrayList<>();
//不同数据分表处理
List<String> guids = query.getGuids();
for (String guid : guids) {
query.setGuids(Collections.singletonList(guid));
List<StrayCurrentHistoryVO> list = strayCurrentDeviceMapper.getHistoryByGuids(query);
if (list.size() == 0) {
List<StrayCurrentDeviceDO> strayCurrentDeviceDOS = strayCurrentDeviceMapper.getByGuid(guid);
StrayCurrentHistoryVO strayCurrentHistoryVO = new StrayCurrentHistoryVO();
strayCurrentHistoryVO.setGuid(guid);
strayCurrentHistoryVO.setReportAddr(strayCurrentDeviceDOS.get(0).getAddress());
strayCurrentHistoryVO.setReportTime(LocalDateTime.now());
strayCurrentHistoryVO.setEquipmentCode(strayCurrentDeviceDOS.get(0).getEquipmentCode());
list.add(strayCurrentHistoryVO);
}
strayCurrentHistoryVos.addAll(list);
}
if (CollectionUtil.isNotEmpty(strayCurrentHistoryVos)) {
log.info("[监测数据导出] 查询到监测数据[{}]条", strayCurrentHistoryVos.size());
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("杂散电流数据-" + DateUtil.formatChineseDate(new Date(), false, true), "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
response.setHeader("fileName", fileName);
// ExcelWriter build = EasyExcel.write(response.getOutputStream(), DeviceHistoryDataVO.class).build();
ExcelWriter build = EasyExcel.write(response.getOutputStream())
.registerWriteHandler(DeviceHistoryDataVO.getStyleStrategy())
.registerWriteHandler(DeviceHistoryDataVO.getSheetWriteHandler())
.registerWriteHandler(DeviceHistoryDataVO.getColumnWidthStyleStrategy())
.build();
Map<String, List<StrayCurrentHistoryVO>> map = strayCurrentHistoryVos.stream().sorted(Comparator.comparing(StrayCurrentHistoryVO::getReportTime).reversed()).collect(Collectors.groupingBy(StrayCurrentHistoryVO::getGuid));
Set<String> strings = map.keySet();
int j = 0;
for (String string : strings) {
List<StrayCurrentHistoryVO> VOS = map.get(string);
List<List<String>> headers = DeviceHistoryDataVO.buildHeaders(VOS.get(0).getGuid(), VOS.get(0).getEquipmentCode(), VOS.get(0).getReportAddr());
WriteSheet writeSheet = EasyExcel.writerSheet(j, VOS.get(0).getGuid()).head(headers).build();
List<DeviceHistoryDataVO> rows = new ArrayList<>();
for (StrayCurrentHistoryVO vo : VOS) {
DeviceHistoryDataVO row = new DeviceHistoryDataVO();
row.setReportTime(vo.getReportTime().toString());
row.setBatteryVoltage(String.valueOf(vo.getBatteryVoltage()));
row.setEnergizingPotential(String.valueOf(vo.getEnergizingPotential()));
row.setInterferenceVoltage(String.valueOf(vo.getInterferenceVoltage()));
row.setOffPotential(String.valueOf(vo.getOffPotential()));
rows.add(row);
}
build.write(rows, writeSheet);
j++;
}
build.close();
response.flushBuffer();
} else {
log.warn("[监测数据导出] 无法根据查询条件[{}]获取到数据", query);
}
}