在工作中有时候会遇到一些统计功能,需要输出的内容并不是很规则的第一行表头后面全是数据的格式,而是这样
这就导致没法用一些封装好的工具(例如easyExcel)简单快捷的输出,这时候只能使用poi进行原生的制作表格,在工作中我使用到了这部分功能,现在将代码贴在如下进行记录
public void downloadReport(HttpServletResponse response) {
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
//概览
handeleOverView(workbook);
handeleMonthAvgDay(workbook);
handeleMonthLightUsageRation(workbook);
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("光伏运行报告.xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
workbook.close();
} catch (
IOException e) {
e.printStackTrace();
}
}
private void handeleOverView(Workbook workbook) {
Sheet overview = workbook.createSheet("概览");
LocalDateTime now = LocalDateTime.now();
//昨天
LocalDateTime localDateTime = now.minusDays(1);
String nowStr = localDateTime.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
// 创建数据
//设置合并居中和自动换行
// 设置标题行，合并单元格并设置样式
Row row = overview.createRow(0);
overview.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); // 合并
Cell headerCell = row.createCell(0);
headerCell.setCellValue("光伏用电运行数据（统计2023年5月01日-" + nowStr + "）");
Row row2 = overview.createRow(1);
row2.createCell(0).setCellValue("总用电量（kwh)");
//查询昨日的最后一条数据和2023年5月1日的最后一条数据
PLightDevice firstData = lightDeviceDao.selectOne(new LambdaQueryWrapper<>(PLightDevice.class).eq(PLightDevice::getYear, 2023)
.eq(PLightDevice::getMonth, 5).eq(PLightDevice::getDay, 1).orderByDesc(PLightDevice::getCreateTime).last("LIMIT 1"));
PLightDevice lastData = lightDeviceDao.selectOne(new LambdaQueryWrapper<>(PLightDevice.class).eq(PLightDevice::getYear, localDateTime.getYear())
.eq(PLightDevice::getMonth, localDateTime.getMonth()).eq(PLightDevice::getDay, localDateTime.getDayOfMonth()).orderByDesc(PLightDevice::getCreateTime).last("LIMIT 1"));
//总用电量
BigDecimal zero = BigDecimal.ZERO;
if (lastData != null) {
zero = lastData.getTotalUsage();
if (firstData != null) {
zero = zero.subtract(firstData.getTotalUsage()).setScale(0, RoundingMode.DOWN);
}
}
row2.createCell(1).setCellValue(Long.parseLong(zero.setScale(0, RoundingMode.DOWN).toString()));
//昨日日期减2023年5月1日
long daysBetween = ChronoUnit.DAYS.between(LocalDate.of(2023, 5, 1), LocalDate.now());
BigDecimal avgEveryDay = zero.divide(BigDecimal.valueOf(daysBetween), 2, RoundingMode.HALF_UP);
Row row3 = overview.createRow(2);
row3.createCell(0).setCellValue("平均每日用电量（kwh)");
row3.createCell(1).setCellValue(Double.parseDouble(avgEveryDay.toString()));
Row row4 = overview.createRow(3);
row4.createCell(0).setCellValue("其中光伏电用量（kwh)");
BigDecimal lightZero = BigDecimal.ZERO;
BigDecimal nationZero = BigDecimal.ZERO;
if (lastData != null) {
lightZero = lastData.getLightUsage();
nationZero = lastData.getCountryUsage();
if (firstData != null) {
nationZero = nationZero.subtract(firstData.getCountryUsage()).setScale(0, RoundingMode.DOWN);
lightZero = lightZero.subtract(firstData.getLightUsage()).setScale(0, RoundingMode.DOWN);
}
}
row4.createCell(1).setCellValue(Long.parseLong(lightZero.setScale(0, RoundingMode.DOWN).toString()));
row4.createCell(2).setCellValue("占比");
row4.createCell(3).setCellValue(lightZero.multiply(oneHundrend).divide(lightZero.add(nationZero), 0, RoundingMode.HALF_UP) + "%");
row4.createCell(4).setCellValue("折算碳减排（kgCO2e）");
row4.createCell(5).setCellValue(Double.parseDouble(lightZero.multiply(ligthRation).setScale(2, RoundingMode.HALF_UP).toString()));
Row row5 = overview.createRow(4);
row5.createCell(0).setCellValue("其中国网用电量（kwh）");
row5.createCell(1).setCellValue(Long.parseLong(nationZero.setScale(0, RoundingMode.DOWN).toString()));
row5.createCell(2).setCellValue("占比");
row5.createCell(3).setCellValue(nationZero.multiply(oneHundrend).divide(lightZero.add(nationZero), 0, RoundingMode.HALF_UP) + "%");
row5.createCell(4).setCellValue("折算碳减排（kgCO2e）");
row5.createCell(5).setCellValue(Double.parseDouble(nationZero.multiply(ligthRation).setScale(2, RoundingMode.HALF_UP).toString()));
Row row6 = overview.createRow(5);
row6.createCell(0).setCellValue("法庭单日最大用电量（kwh）");
//查询每一天的最后一条数据
List<PLightDevice> list = baseMapper.selectEveryDayData();
//计算最大的一天的用电量
// 计算当日使用量并找出最大的一天
BigDecimal maxUsage = BigDecimal.ZERO;
BigDecimal lightMaxUsage = BigDecimal.ZERO;
LocalDateTime lightMaxUsageData = null;
for (int i = 1; i < list.size(); i++) {
PLightDevice prev = list.get(i - 1);
PLightDevice current = list.get(i);
LocalDate currentLocalDate = LocalDate.of(current.getYear(), current.getMonth(), current.getDay()).minusDays(1);
LocalDate preLocalDate = LocalDate.of(prev.getYear(), prev.getMonth(), prev.getDay());
//判断两个时间想等 如果有时间间隔就跳过
if (!currentLocalDate.equals(preLocalDate)) {
continue;
}
// 计算当日使用量
BigDecimal dailyUsage = current.getTotalUsage().subtract(prev.getTotalUsage());
BigDecimal lightDailyUsage = current.getLightUsage().subtract(prev.getLightUsage());
// 查找最大使用量
if (dailyUsage.compareTo(maxUsage) == 1) {
maxUsage = dailyUsage;
}
// 查找最大光伏用电量
if (lightDailyUsage.compareTo(lightMaxUsage) == 1) {
lightMaxUsage = lightDailyUsage;
lightMaxUsageData = current.getCreateTime();
}
}
//判断第一天的数据和最大数据谁更大
PLightDevice pLightDevice = list.get(0);
if (pLightDevice != null) {
if (pLightDevice.getTotalUsage().compareTo(maxUsage) == 1) {
maxUsage = pLightDevice.getTotalUsage();
}
}
row6.createCell(1).setCellValue(Long.parseLong(maxUsage.setScale(0, RoundingMode.DOWN).toString()));
Row row7 = overview.createRow(6);
row7.createCell(0).setCellValue("光伏单日最大发电量（kwh）");
row7.createCell(1).setCellValue(Long.parseLong(lightMaxUsage.setScale(0, RoundingMode.DOWN).toString()));
row7.createCell(2).setCellValue("出现日期");
if (lightMaxUsageData != null) {
row7.createCell(3).setCellValue(lightMaxUsageData.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")));
}
//设置第一行的样式
setHeaderStyle(workbook, overview);
} private void handeleMonthAvgDay(Workbook workbook) {
Sheet monthAvgDay = workbook.createSheet("每月平均日光伏发电量");
LocalDate now = LocalDate.now();
//获取每个月最后一天的数据
List<PLightDevice> list = baseMapper.selectEveryMonthData();
Row row = monthAvgDay.createRow(0);
row.createCell(0).setCellValue("月份");
row.createCell(1).setCellValue("平均每日发电量");
row.createCell(2).setCellValue("发电天数");
row.createCell(3).setCellValue("总发电量");
//总发电天数
int totalDay = 0;
//总发电量
BigDecimal totalUsage = BigDecimal.ZERO;
for (int i = 0; i < list.size(); i++) {
Row rowi = monthAvgDay.createRow(i + 1);
PLightDevice current = list.get(i);
rowi.createCell(0).setCellValue(current.getYear() + "-" + current.getMonth());
if (i == 0) {
//当月的总天数
int allDay = LocalDate.of(current.getYear(), current.getMonth(), 1).lengthOfMonth();
totalDay += allDay;
rowi.createCell(1).setCellValue(Double.parseDouble(current.getLightUsage().divide(BigDecimal.valueOf(allDay), 2, RoundingMode.HALF_UP).toString()));
rowi.createCell(2).setCellValue(allDay);
rowi.createCell(3).setCellValue(Long.parseLong(current.getLightUsage().setScale(0, RoundingMode.DOWN).toString()));
totalUsage = totalUsage.add(current.getLightUsage());
continue;
}
//上一个月的数据
PLightDevice prev = list.get(i - 1);
// 计算当月使用量
BigDecimal lightDailyUsage = current.getLightUsage().subtract(prev.getLightUsage());
//当月发电天数
int allDay;
if (now.getMonthValue() != current.getMonth()) {
allDay = LocalDate.of(current.getYear(), current.getMonth(), 1).lengthOfMonth();
} else {
allDay = now.getDayOfMonth();
}
rowi.createCell(1).setCellValue(Double.parseDouble(lightDailyUsage.divide(BigDecimal.valueOf(allDay), 2, RoundingMode.HALF_UP).toString()));
rowi.createCell(2).setCellValue(allDay);
rowi.createCell(3).setCellValue(Long.parseLong(lightDailyUsage.setScale(0, RoundingMode.DOWN).toString()));
//累加值
totalDay += allDay;
totalUsage = totalUsage.add(lightDailyUsage);
}
int lastRowNum = monthAvgDay.getLastRowNum() + 1;
Row lastRow = monthAvgDay.createRow(lastRowNum);
lastRow.createCell(0).setCellValue("总计");
lastRow.createCell(1).setCellValue(Double.parseDouble(totalUsage.divide(BigDecimal.valueOf(totalDay), 2, RoundingMode.HALF_UP).toString()));
lastRow.createCell(2).setCellValue(totalDay);
lastRow.createCell(3).setCellValue(Long.parseLong(totalUsage.setScale(0, RoundingMode.DOWN).toString()));
setHeaderStyle(workbook,monthAvgDay);
}
private void handeleMonthLightUsageRation(Workbook workbook) {
Sheet monthLightUsageRation = workbook.createSheet("每月光伏用电量占比");
//获取每个月最后一天的数据
List<PLightDevice> list = baseMapper.selectEveryMonthData();
Row row = monthLightUsageRation.createRow(0);
row.createCell(0).setCellValue("月份");
row.createCell(1).setCellValue("总用电量");
row.createCell(2).setCellValue("光伏用电量");
row.createCell(3).setCellValue("占比");
row.createCell(4).setCellValue("国网用电量");
row.createCell(5).setCellValue("占比");
for (int i = 0; i < list.size(); i++) {
Row rowi = monthLightUsageRation.createRow(i + 1);
PLightDevice current = list.get(i);
rowi.createCell(0).setCellValue(current.getYear() + "-" + current.getMonth());
if (i == 0) {
//当月的总天数
rowi.createCell(1).setCellValue(Long.parseLong(current.getTotalUsage().setScale(0, RoundingMode.DOWN).toString()));
rowi.createCell(2).setCellValue(Long.parseLong(current.getLightUsage().setScale(0, RoundingMode.DOWN).toString()));
rowi.createCell(3).setCellValue(current.getLightUsage().multiply(oneHundrend).divide(current.getTotalUsage(), 0, RoundingMode.HALF_UP)+"%");
rowi.createCell(4).setCellValue(Long.parseLong(current.getCountryUsage().setScale(0, RoundingMode.DOWN).toString()));
rowi.createCell(5).setCellValue(current.getCountryUsage().multiply(oneHundrend).divide(current.getTotalUsage(), 0, RoundingMode.HALF_UP)+"%");
continue;
}
//上一个月的数据
PLightDevice prev = list.get(i - 1);
// 计算当月使用量
BigDecimal totalUsage = current.getTotalUsage().subtract(prev.getTotalUsage());
BigDecimal lightDailyUsage = current.getLightUsage().subtract(prev.getLightUsage());
BigDecimal contryUsage = current.getCountryUsage().subtract(prev.getCountryUsage());
rowi.createCell(1).setCellValue(Long.parseLong(totalUsage.setScale(0, RoundingMode.DOWN).toString()));
rowi.createCell(2).setCellValue(Long.parseLong(lightDailyUsage.setScale(0, RoundingMode.DOWN).toString()));
rowi.createCell(3).setCellValue(lightDailyUsage.multiply(oneHundrend).divide(totalUsage, 0, RoundingMode.HALF_UP)+"%");
rowi.createCell(4).setCellValue(Long.parseLong(contryUsage.setScale(0, RoundingMode.DOWN).toString()));
rowi.createCell(5).setCellValue(contryUsage.multiply(oneHundrend).divide(totalUsage, 0, RoundingMode.HALF_UP)+"%");
}
setHeaderStyle(workbook,monthLightUsageRation);
}
这是设置我需要的格式的代码
private void setHeaderStyle(Workbook workbook,Sheet sheet){
// 创建样式对象
CellStyle headerStyle = workbook.createCellStyle();
// 设置居中对齐和自动换行
headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
headerStyle.setWrapText(true); // 启用自动换行
// 创建一个字体对象，设置加粗（Bold）
Font font = workbook.createFont();
font.setBold(true); // 设置加粗
headerStyle.setFont(font);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 灰色背景
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充方式为实心填充
Row firstRow = sheet.getRow(0); // 第一行的索引是 0
// 给第一行的所有单元格应用该样式
if (firstRow != null) {
for (Cell cell : firstRow) {
cell.setCellStyle(headerStyle); // 设置单元格样式
}
}
}
扫一扫
1051
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
