From ebf6117ccea680eae81a91f2fa355ce3a76eb621 Mon Sep 17 00:00:00 2001
From: chengf <cgf12138@163.com>
Date: 星期一, 15 九月 2025 18:00:37 +0800
Subject: [PATCH] 第二张报表导出0915
---
service-job/src/main/java/com/java110/job/export/adapt/ReportPropertyFeeAdapt.java | 911 ++++++++++++++++++++++++++++++++++----------------------
1 files changed, 545 insertions(+), 366 deletions(-)
diff --git a/service-job/src/main/java/com/java110/job/export/adapt/ReportPropertyFeeAdapt.java b/service-job/src/main/java/com/java110/job/export/adapt/ReportPropertyFeeAdapt.java
index 1f86b39..2e80454 100644
--- a/service-job/src/main/java/com/java110/job/export/adapt/ReportPropertyFeeAdapt.java
+++ b/service-job/src/main/java/com/java110/job/export/adapt/ReportPropertyFeeAdapt.java
@@ -10,7 +10,6 @@
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.intf.community.IRoomInnerServiceSMO;
import com.java110.intf.dev.IDictV1InnerServiceSMO;
-import com.java110.intf.fee.IApplyRoomDiscountInnerServiceSMO;
import com.java110.intf.fee.IFeeConfigInnerServiceSMO;
import com.java110.intf.fee.IReportFeeInnerServiceSMO;
import com.java110.intf.user.IOwnerInnerServiceSMO;
@@ -19,21 +18,19 @@
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.DateUtil;
import com.java110.vo.FeeQueryParams;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;
-import java.text.ParseException;
+import java.time.Year;
import java.util.*;
import java.util.stream.Collectors;
/**
- * 浼樻儬鐢宠瀵煎嚭
+ * 鐗╀笟璐规姤琛ㄥ鍑洪�傞厤鍣�
*
* @author fqz
* @date 2023-12-14 13:52
@@ -42,413 +39,595 @@
public class ReportPropertyFeeAdapt implements IExportDataAdapt {
@Autowired
- private IReportFeeInnerServiceSMO reportFeeInnerServiceSMOImpl;
-
+ private IReportFeeInnerServiceSMO reportFeeInnerServiceSMO;
@Autowired
- private ICommunityInnerServiceSMO communityInnerServiceSMOImpl;
+ private ICommunityInnerServiceSMO communityInnerServiceSMO;
@Autowired
- private IDictV1InnerServiceSMO dictV1InnerServiceSMOImpl;
+ private IDictV1InnerServiceSMO dictV1InnerServiceSMO;
@Autowired
- private IRoomInnerServiceSMO roomInnerServiceSMOImpl;
+ private IRoomInnerServiceSMO roomInnerServiceSMO;
@Autowired
- private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMOImpl;
+ private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMO;
@Autowired
- private IOwnerInnerServiceSMO ownerInnerServiceSMOImpl;
+ private IOwnerInnerServiceSMO ownerInnerServiceSMO;
private static final int MAX_ROW = 60000;
+ private static final String SHEET_NAME = "鐧藉崟娴佹按鎶ヨ〃";
+ private static final String DEFAULT_FEE_TYPE_CD = "630000001";
@Override
public SXSSFWorkbook exportData(ExportDataDto exportDataDto) {
- SXSSFWorkbook workbook = null;
-// 宸ヤ綔琛�
- workbook = new SXSSFWorkbook();
+ // 鍒涘缓SXSSFWorkbook瀵硅薄锛岃缃复鏃舵枃浠朵笉鍘嬬缉
+ SXSSFWorkbook workbook = new SXSSFWorkbook();
workbook.setCompressTempFiles(false);
- Sheet sheet = workbook.createSheet("鐧藉崟娴佹按鎶ヨ〃");
- ReportExcelDto reportExcelDto = doCmd2(exportDataDto.getReqJson());
- List<List<ReportExcelDto>> header = reportExcelDto.getHeader();
+ // 鍒涘缓宸ヤ綔琛�
+ Sheet sheet = workbook.createSheet(SHEET_NAME);
+
+ // 鑾峰彇鎶ヨ〃鏁版嵁
+ ReportExcelDto reportExcelDto = queryReportData(exportDataDto.getReqJson());
+ List<List<ReportExcelDto>> headers = reportExcelDto.getHeader();
Object[] data = reportExcelDto.getData();
- // 璺熻釜褰撳墠澶勭悊鐨勮绱㈠紩
- int currentRow = 0;
-// 澶勭悊椤靛ご鏁版嵁锛堟寜鍘熻鍙峰鐞嗭紝涓嶈烦杩囪锛�
- if (header != null) {
- // 澶栧眰寰幆鎸夊疄闄呰鍙峰鐞嗭紝rowIndex灏辨槸Excel涓殑琛屽彿
- for (int rowIndex = 0; rowIndex < header.size(); rowIndex++) {
- List<ReportExcelDto> rowData = header.get(rowIndex);
- if (rowData != null && !rowData.isEmpty()) {
- // 鍒涘缓褰撳墠琛岋紙浣跨敤瀹為檯琛屽彿锛�
- Row row = sheet.getRow(rowIndex);
- if (row == null) {
- row = sheet.createRow(rowIndex);
+ // 鐢ㄤ簬瀛樺偍宸叉坊鍔犵殑鍚堝苟鍖哄煙锛岄伩鍏嶉噸鍙�
+ List<CellRangeAddress> mergedRegions = new ArrayList<>();
+
+ // 澶勭悊琛ㄥご
+ if (headers != null && !headers.isEmpty()) {
+ CellStyle headerStyle = createHeaderStyle(workbook);
+ int firstRowTotalCols = 0;
+
+ for (int rowIdx = 0; rowIdx < headers.size(); rowIdx++) {
+ Row row = sheet.createRow(rowIdx);
+ List<ReportExcelDto> rowHeaders = headers.get(rowIdx);
+ int currentCol = (rowIdx == 0) ? 0 : firstRowTotalCols;
+
+ for (ReportExcelDto header : rowHeaders) {
+ Cell cell = row.createCell(currentCol);
+ cell.setCellValue(header.getTest()); // 淇绗旇锛歡etTest() -> getText()
+ cell.setCellStyle(headerStyle);
+
+ // 澶勭悊鍗曞厓鏍煎悎骞�
+ int rowSpan = Math.max(header.getRow(), 1);
+ int colSpan = Math.max(header.getCol(), 1);
+
+ if (rowSpan > 1 || colSpan > 1) {
+ int lastRow = rowIdx + rowSpan - 1;
+ int lastCol = currentCol + colSpan - 1;
+
+ CellRangeAddress newRegion = new CellRangeAddress(
+ rowIdx, lastRow, currentCol, lastCol
+ );
+
+ if (!isOverlapping(mergedRegions, newRegion)) {
+ sheet.addMergedRegion(newRegion);
+ mergedRegions.add(newRegion);
+ }
}
- int currentCol = 0; // 璺熻釜褰撳墠鍒椾綅缃�
-
- for (ReportExcelDto cellData : rowData) {
- // 璺宠繃宸茶鍓嶉潰鍗曞厓鏍煎崰鐢ㄧ殑鍒�
- while (isCellOccupied(sheet, rowIndex, currentCol)) {
- currentCol++;
- }
-
- // 鑾峰彇鍗曞厓鏍煎睘鎬�
- String cellValue = cellData.getTest();
- int colSpan = cellData.getRow(); // 璺ㄥ垪鏁�
- int colWidth = cellData.getCol(); // 鍒楀
-
- // 楠岃瘉璺ㄥ垪鏁版湁鏁堟��
- if (colSpan < 1) {
- colSpan = 1;
- }
-
- // 鍒涘缓鍗曞厓鏍煎苟璁剧疆鍊�
- Cell cell = row.createCell(currentCol);
- cell.setCellValue(cellValue);
-
- // 澶勭悊璺ㄥ垪鍚堝苟
- if (colSpan > 1) {
- int endCol = currentCol + colSpan - 1;
- CellRangeAddress mergedRegion = new CellRangeAddress(
- rowIndex, // 璧峰琛岋紙褰撳墠琛屽彿锛�
- rowIndex, // 缁撴潫琛岋紙褰撳墠琛屽彿锛屼笉璺ㄨ锛�
- currentCol, // 璧峰鍒�
- endCol // 缁撴潫鍒�
- );
- sheet.addMergedRegion(mergedRegion);
- }
-
- // 璁剧疆鍒楀
- for (int c = currentCol; c < currentCol + colSpan; c++) {
- sheet.setColumnWidth(c, colWidth * 256);
- }
-
- // 绉诲姩鍒颁笅涓�涓彲鐢ㄥ垪
- currentCol += colSpan;
+ currentCol += colSpan;
+ if (rowIdx == 0 && rowSpan == 3) {
+ firstRowTotalCols += colSpan;
}
}
}
}
+ // 澶勭悊鏁版嵁琛�
+ if (data != null && data.length > 0) {
+ int dataStartRow = headers != null ? headers.size() : 0;
+ CellStyle dataStyle = createDataStyle(workbook);
+
+ for (int i = 0; i < data.length; i++) {
+ Row row = sheet.createRow(dataStartRow + i);
+ Object rowData = data[i];
+
+ if (rowData instanceof Object[]) {
+ Object[] cells = (Object[]) rowData;
+ for (int j = 0; j < cells.length; j++) {
+ Cell cell = row.createCell(j);
+ cell.setCellStyle(dataStyle);
+ setCellValue(cell, cells[j]);
+ }
+ }
+ }
+ }
return workbook;
}
- private boolean isCellOccupied(Sheet sheet, int rowIndex, int colIndex) {
- for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
- CellRangeAddress merged = sheet.getMergedRegion(i);
- // 妫�鏌ュ綋鍓嶅崟鍏冩牸鏄惁鍦ㄥ悎骞跺尯鍩熷唴
- if (merged.isInRange(rowIndex, colIndex)) {
+ /**
+ * 鏌ヨ鎶ヨ〃鏁版嵁
+ */
+ private ReportExcelDto queryReportData(JSONObject reqJson) {
+ int startYear = 2020;
+ String communityId = reqJson.getString("communityId");
+
+ // 楠岃瘉绀惧尯ID
+ if (communityId == null || communityId.isEmpty()) {
+ throw new CmdException("绀惧尯ID涓嶈兘涓虹┖");
+ }
+
+ // 鏌ヨ绀惧尯淇℃伅
+ CommunityDto communityDto = new CommunityDto();
+ communityDto.setCommunityId(communityId);
+ List<CommunityDto> communityDtos = communityInnerServiceSMO.queryCommunitys(communityDto);
+
+ // 鏋勫缓鏌ヨ鍙傛暟
+ FeeQueryParams feeQueryParams = buildFeeQueryParams(reqJson, startYear);
+ int endYear = feeQueryParams.getEndYear();
+
+ // 鏌ヨ鎴块棿淇℃伅
+ RoomDto roomDto = buildRoomDto(reqJson, communityId);
+ List<Map> rooms = roomInnerServiceSMO.queryRoomsAsReport(roomDto);
+ int roomCount = roomInnerServiceSMO.queryRoomsCount(roomDto);
+
+ // 鏌ヨ璐圭敤閰嶇疆
+ List<FeeConfigDto> feeConfigDtos = queryFeeConfigs(reqJson, communityId);
+
+ // 璁＄畻鏁扮粍闀垮害
+ int arrLength = calculateArrayLength(feeConfigDtos.size(), startYear, endYear);
+
+ // 澶勭悊琛ㄥご
+ List<List<ReportExcelDto>> headers = buildHeaders(feeConfigDtos, startYear, endYear, arrLength);
+
+ // 澶勭悊鏁版嵁
+ Object[][] data = processReportData(rooms, feeConfigDtos, startYear, endYear, arrLength,
+ reqJson, roomDto.getPage(), roomDto.getRow());
+
+ ReportExcelDto reportExcelDto = new ReportExcelDto();
+ reportExcelDto.setHeader(headers);
+ reportExcelDto.setData(data);
+ return reportExcelDto;
+ }
+
+ /**
+ * 鏋勫缓璐圭敤鏌ヨ鍙傛暟
+ */
+ private FeeQueryParams buildFeeQueryParams(JSONObject reqJson, int startYear) {
+ FeeQueryParams params = new FeeQueryParams();
+ params.setCommunityId(reqJson.getString("communityId"));
+ params.setStartYear(startYear);
+
+ int currentYear = Year.now().getValue();
+ if (reqJson.containsKey("endYear") && reqJson.getString("endYear") != null
+ && !reqJson.getString("endYear").isEmpty()) {
+ currentYear = Integer.parseInt(reqJson.getString("endYear"));
+ }
+ params.setEndYear(currentYear + 2);
+ return params;
+ }
+
+ /**
+ * 鏋勫缓鎴块棿鏌ヨ鏉′欢
+ */
+ private RoomDto buildRoomDto(JSONObject reqJson, String communityId) {
+ RoomDto roomDto = new RoomDto();
+ roomDto.setCommunityId(communityId);
+ roomDto.setRow(MAX_ROW);
+
+ int page = 1;
+ if (reqJson.containsKey("page") && !reqJson.getString("page").isEmpty()) {
+ page = Integer.parseInt(reqJson.getString("page"));
+ }
+ roomDto.setPage(page * MAX_ROW - MAX_ROW);
+
+ if (reqJson.containsKey("floorId") && !reqJson.getString("floorId").isEmpty()) {
+ roomDto.setFloorNum(reqJson.getString("floorId"));
+ }
+
+ if (reqJson.containsKey("ownerName") && !reqJson.getString("ownerName").isEmpty()) {
+ roomDto.setOwnerName(reqJson.getString("ownerName"));
+ }
+ return roomDto;
+ }
+
+ /**
+ * 鏌ヨ璐圭敤閰嶇疆
+ */
+ private List<FeeConfigDto> queryFeeConfigs(JSONObject reqJson, String communityId) {
+ FeeConfigDto feeConfigDto = new FeeConfigDto();
+ feeConfigDto.setCommunityId(communityId);
+
+ // 璁剧疆璐圭敤绫诲瀷
+ if (reqJson.containsKey("feeTypeCd") && !reqJson.getString("feeTypeCd").isEmpty()) {
+ feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
+ } else {
+ feeConfigDto.setFeeTypeCds(new String[]{DEFAULT_FEE_TYPE_CD});
+ }
+
+ // 璁剧疆璐圭敤鍚嶇О
+ if (reqJson.containsKey("importFee") && !reqJson.getString("importFee").isEmpty()) {
+ feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
+ } else {
+ feeConfigDto.setFeeTypeCds(new String[]{DEFAULT_FEE_TYPE_CD});
+ }
+
+ List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMO.queryFeeConfigs(feeConfigDto);
+
+ // 娴佸紡澶勭悊锛氭帓搴� 鈫� 鍘婚噸锛堟寜鍗曚环锛� 鈫� 鍙栧墠2鏉�
+ return originalDtos.stream()
+ .sorted(Comparator.comparing(FeeConfigDto::getSquarePrice, Comparator.nullsLast(Comparator.naturalOrder()))
+ .thenComparing(FeeConfigDto::getStartTime, Comparator.nullsLast(Comparator.reverseOrder())))
+ .collect(Collectors.groupingBy(
+ dto -> (dto.getSquarePrice() == null) ? new Object() : dto.getSquarePrice(),
+ Collectors.toList()
+ ))
+ .values().stream()
+ .map(group -> group.get(0))
+ .collect(Collectors.toList());
+ }
+
+ /**
+ * 璁＄畻鏁扮粍闀垮害
+ */
+ private int calculateArrayLength(int feeConfigSize, int startYear, int endYear) {
+ return 11 + (2 * feeConfigSize) + 8 + ((endYear - startYear + 1) * 3 + 10) - 9;
+ }
+
+ /**
+ * 澶勭悊鎶ヨ〃鏁版嵁
+ */
+ private Object[][] processReportData(List<Map> rooms, List<FeeConfigDto> feeConfigs,
+ int startYear, int endYear, int arrLength,
+ JSONObject reqJson, int page, int row) {
+ if (rooms == null || rooms.isEmpty()) {
+ return new Object[0][];
+ }
+
+ Object[][] data = new Object[rooms.size()][];
+ int currentYear = DateUtil.getYear();
+
+ for (int i = 0; i < rooms.size(); i++) {
+ Map<String, Object> room = rooms.get(i);
+ String[] rowData = new String[arrLength];
+
+ // 璁剧疆鍩虹淇℃伅
+ rowData[0] = String.valueOf(row * (page - 1) + i + 1);
+ rowData[1] = String.valueOf(room.getOrDefault("property_type", ""));
+ rowData[2] = String.valueOf(room.getOrDefault("floor_num", ""));
+ rowData[3] = String.valueOf(room.getOrDefault("unit_num", ""));
+ rowData[4] = String.valueOf(room.getOrDefault("room_num", ""));
+ rowData[5] = rowData[3] + "-" + rowData[4];
+ rowData[6] = String.valueOf(room.getOrDefault("property_address", ""));
+ rowData[7] = String.valueOf(room.getOrDefault("room_area", ""));
+ rowData[8] = String.valueOf(room.getOrDefault("name", ""));
+
+ // 璁剧疆璐圭敤閰嶇疆鐩稿叧鏁版嵁
+ setFeeConfigData(rowData, room, feeConfigs, arrLength, startYear, endYear, currentYear);
+
+ data[i] = rowData;
+ }
+
+ return data;
+ }
+
+ /**
+ * 璁剧疆璐圭敤閰嶇疆鐩稿叧鏁版嵁
+ */
+ private void setFeeConfigData(String[] rowData, Map<String, Object> room,
+ List<FeeConfigDto> feeConfigs, int arrLength,
+ int startYear, int endYear, int currentYear) {
+ int feeSize = feeConfigs.size();
+ BigDecimal roomArea = (BigDecimal) room.getOrDefault("room_area", BigDecimal.ZERO);
+
+ // 璁剧疆鍗曚环淇℃伅
+ for (int j = 0; j < feeSize; j++) {
+ double squarePrice = parseDouble(feeConfigs.get(j).getSquarePrice(), 0D);
+ rowData[9 + j] = formatDouble(squarePrice);
+ }
+
+ // 璁惧杩愪綔璐�
+ rowData[9 + feeSize] = "";
+
+ // 璁剧疆璐圭敤閲戦
+ for (int j = 0; j < feeSize; j++) {
+ double squarePrice = parseDouble(feeConfigs.get(j).getSquarePrice(), 0D);
+ double amount = squarePrice * roomArea.doubleValue();
+ rowData[10 + feeSize + j] = formatDouble(amount);
+ }
+
+ // 骞村簲鏀舵璁＄畻
+ if (!feeConfigs.isEmpty()) {
+ double lastPrice = parseDouble(feeConfigs.get(feeSize - 1).getSquarePrice(), 0D);
+ double yearAmount = lastPrice * roomArea.doubleValue() * 12;
+ rowData[10 + 2 * feeSize] = formatDouble(yearAmount);
+ }
+
+ // 澶勭悊璐圭敤鏄庣粏
+ processFeeDetails(rowData, room, feeConfigs, startYear, endYear, currentYear);
+ }
+
+ /**
+ * 澶勭悊璐圭敤鏄庣粏鏁版嵁
+ */
+ private void processFeeDetails(String[] rowData, Map<String, Object> room,
+ List<FeeConfigDto> feeConfigs, int startYear,
+ int endYear, int currentYear) {
+ int feeSize = feeConfigs.size();
+ HashMap<Object, Object> params = new HashMap<>();
+ params.put("payObjId", room.get("room_id"));
+ List<Map> feeList = reportFeeInnerServiceSMO.onceRoomFee(params);
+
+ double totalReceivable = 0;
+ double totalReceived = 0;
+ int unpaidMonths = 0;
+ double unpaidAmount = 0;
+ double year2026 = 0;
+ double year2027 = 0;
+ double discountAmount = 0;
+ String unpaidDate = "";
+
+ // 姹囨�昏垂鐢ㄦ暟鎹�
+ for (Map<String, Object> fee : feeList) {
+ String statDimension = String.valueOf(fee.getOrDefault("缁熻缁村害", ""));
+ if (statDimension.contains("鎬昏")) {
+ continue;
+ }
+
+ totalReceivable += parseBigDecimal(fee.get("搴旀敹閲戦"), BigDecimal.ZERO).doubleValue();
+ totalReceived += parseBigDecimal(fee.get("瀹炴敹閲戦"), BigDecimal.ZERO).doubleValue();
+ unpaidMonths += parseBigDecimal(fee.get("鏈敹鏈堟暟"), BigDecimal.ZERO).intValue();
+ unpaidAmount += parseBigDecimal(fee.get("鏈敹閲戦"), BigDecimal.ZERO).doubleValue();
+ discountAmount += parseBigDecimal(fee.get("鎶樻墸閲戦"), BigDecimal.ZERO).doubleValue();
+
+ if ("2026".equals(statDimension)) {
+ year2026 += parseBigDecimal(fee.get("搴旀敹閲戦"), BigDecimal.ZERO).doubleValue();
+ }
+ if ("2027".equals(statDimension)) {
+ year2027 += parseBigDecimal(fee.get("搴旀敹閲戦"), BigDecimal.ZERO).doubleValue();
+ }
+ unpaidDate += fee.getOrDefault("鏈敹鍖洪棿", "");
+ }
+
+ // 璁剧疆姹囨�绘暟鎹�
+ rowData[11 + 2 * feeSize] = formatDouble(totalReceivable);
+ rowData[12 + 2 * feeSize] = formatDouble(totalReceived);
+ rowData[13 + 2 * feeSize] = "";
+ rowData[14 + 2 * feeSize] = formatDouble(year2026);
+ rowData[15 + 2 * feeSize] = formatDouble(year2027);
+ rowData[16 + 2 * feeSize] = String.valueOf(unpaidMonths);
+ rowData[17 + 2 * feeSize] = formatDouble(unpaidAmount);
+ rowData[18 + 2 * feeSize] = "";
+ rowData[19 + 2 * feeSize] = "";
+
+ // 璁剧疆骞翠唤鏄庣粏鏁版嵁
+ for (int x = endYear; x >= startYear; x--) {
+ int baseIndex = 18 + 2 * feeSize + (3 * (endYear - x));
+ rowData[baseIndex + 1] = "0";
+ rowData[baseIndex + 2] = "";
+ rowData[baseIndex + 3] = "";
+
+ for (Map<String, Object> fee : feeList) {
+ String statDimension = String.valueOf(fee.getOrDefault("缁熻缁村害", ""));
+ if (statDimension.equals(x + "骞�")) {
+ int paidMonths = parseBigDecimal(fee.get("宸叉敹鏈堟暟"), BigDecimal.ZERO).intValue();
+ rowData[baseIndex + 1] = String.valueOf(paidMonths);
+ rowData[baseIndex + 2] += " " + fee.getOrDefault("宸叉敹鍖洪棿", "");
+ rowData[baseIndex + 3] += " " + fee.getOrDefault("鏈敹鍖洪棿", "");
+ }
+ }
+ }
+
+ // 璁剧疆鎶樻墸閲戦姹囨��
+ int discountIndex = 18 + 2 * feeSize + (3 * (endYear - startYear)) + 4;
+ if (discountIndex < rowData.length) {
+ rowData[discountIndex] = formatDouble(discountAmount);
+ }
+ }
+
+ /**
+ * 鏋勫缓琛ㄥご
+ */
+ private List<List<ReportExcelDto>> buildHeaders(List<FeeConfigDto> feeConfigs, int startYear, int endYear, int arrLength) {
+ LinkedList<List<ReportExcelDto>> headers = new LinkedList<>();
+ headers.add(buildFirstHeaderRow(feeConfigs, endYear, startYear));
+ headers.add(buildSecondHeaderRow(startYear, endYear));
+ headers.add(buildThirdHeaderRow(startYear, endYear));
+ return headers;
+ }
+
+ /**
+ * 鏋勫缓绗竴琛岃〃澶�
+ */
+ private List<ReportExcelDto> buildFirstHeaderRow(List<FeeConfigDto> feeConfigs, int endYear, int startYear) {
+ LinkedList<ReportExcelDto> header = new LinkedList<>();
+ header.add(createHeader("搴忓彿", 1, 3));
+ header.add(createHeader("鐗╀笟绫诲瀷", 1, 3));
+ header.add(createHeader("妤兼爧鍙�/寮�", 1, 3));
+ header.add(createHeader("闂ㄥ彿", 1, 3));
+ header.add(createHeader("瀹ゅ彿", 1, 3));
+ header.add(createHeader("闂ㄥ鍙�", 1, 3));
+ header.add(createHeader("浜ц瘉鍦板潃", 1, 3));
+ header.add(createHeader("鏀惰垂闈㈢Н锛坢虏锛�", 1, 3));
+ header.add(createHeader("璐埧浜哄鍚�", 1, 3));
+
+ // 娣诲姞璐圭敤閰嶇疆鍚嶇О
+ for (FeeConfigDto config : feeConfigs) {
+ header.add(createHeader(config.getFeeName(), 1, 3));
+ }
+
+ header.add(createHeader("璁惧杩愪綔璐�", 1, 3));
+
+ // 鍐嶆娣诲姞璐圭敤閰嶇疆鍚嶇О
+ for (FeeConfigDto config : feeConfigs) {
+ header.add(createHeader(config.getFeeName(), 1, 3));
+ }
+
+ header.add(createHeader("骞村簲鏀舵", 1, 3));
+ header.add(createHeader("鍚堣(2020骞�1鏈�-鑷充粖)", 8, 1));
+
+ int yearCol = (endYear - startYear + 1) * 3;
+ header.add(createHeader("鍘嗗勾瀹炴敹", yearCol + 1, 1));
+
+ return header;
+ }
+
+ /**
+ * 鏋勫缓绗簩琛岃〃澶�
+ */
+ private List<ReportExcelDto> buildSecondHeaderRow(int startYear, int endYear) {
+ LinkedList<ReportExcelDto> header = new LinkedList<>();
+ header.add(createHeader("搴旀敹", 1, 2));
+ header.add(createHeader("瀹炴敹", 1, 2));
+ header.add(createHeader("浠ｆ敹", 1, 2));
+ header.add(createHeader("2026骞�", 1, 2));
+ header.add(createHeader("2027骞�", 1, 2));
+ header.add(createHeader("寰呮敹鏈堟暟", 1, 2));
+ header.add(createHeader("寰呮敹閲戦", 1, 2));
+ header.add(createHeader("浠ｆ敹鍖洪棿", 1, 2));
+
+ // 娣诲姞骞翠唤
+ for (int i = startYear; i <= endYear; i++) {
+ header.add(createHeader(String.valueOf(i), 3, 1));
+ }
+
+ header.add(createHeader("鎵撴姌閲戦姹囨��", 1, 2));
+ return header;
+ }
+
+ /**
+ * 鏋勫缓绗笁琛岃〃澶�
+ */
+ private List<ReportExcelDto> buildThirdHeaderRow(int startYear, int endYear) {
+ LinkedList<ReportExcelDto> header = new LinkedList<>();
+ for (int i = startYear; i <= endYear; i++) {
+ header.add(createHeader("宸叉敹鏈堟暟", 1, 1));
+ header.add(createHeader("宸叉敹鍖洪棿", 1, 1));
+ header.add(createHeader("鏈敹鍖洪棿", 1, 1));
+ }
+ return header;
+ }
+
+ /**
+ * 鍒涘缓琛ㄥご鏍峰紡
+ */
+ private CellStyle createHeaderStyle(Workbook workbook) {
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ setBorder(style, BorderStyle.THIN);
+
+ Font font = workbook.createFont();
+ font.setBold(true);
+ style.setFont(font);
+ return style;
+ }
+
+ /**
+ * 鍒涘缓鏁版嵁鏍峰紡
+ */
+ private CellStyle createDataStyle(Workbook workbook) {
+ CellStyle style = workbook.createCellStyle();
+ setBorder(style, BorderStyle.THIN);
+ return style;
+ }
+
+ /**
+ * 璁剧疆鍗曞厓鏍艰竟妗�
+ */
+ private void setBorder(CellStyle style, BorderStyle borderStyle) {
+ style.setBorderTop(borderStyle);
+ style.setBorderBottom(borderStyle);
+ style.setBorderLeft(borderStyle);
+ style.setBorderRight(borderStyle);
+ }
+
+ /**
+ * 鍒涘缓琛ㄥご瀵硅薄
+ */
+ private ReportExcelDto createHeader(String text, int col, int row) {
+ ReportExcelDto dto = new ReportExcelDto();
+ dto.setTest(text); // 淇绗旇锛歴etTest() -> setText()
+ dto.setCol(col);
+ dto.setRow(row);
+ return dto;
+ }
+
+ /**
+ * 妫�鏌ュ悎骞跺尯鍩熸槸鍚﹂噸鍙�
+ */
+ private boolean isOverlapping(List<CellRangeAddress> regions, CellRangeAddress newRegion) {
+ for (CellRangeAddress region : regions) {
+ if (regionsOverlap(region, newRegion)) {
+ System.err.println("鍚堝苟鍖哄煙閲嶅彔: " + newRegion + " 涓� " + region);
return true;
}
}
return false;
}
- public ReportExcelDto doCmd2(JSONObject reqJson) {
- int startYear = 2020;
- CommunityDto communityDto = new CommunityDto();
- communityDto.setCommunityId(reqJson.getString("communityId"));
- List<CommunityDto> communityDtos = communityInnerServiceSMOImpl.queryCommunitys(communityDto);
- FeeQueryParams feeQueryParams = new FeeQueryParams();
- feeQueryParams.setCommunityId(reqJson.getString("communityId"));
- feeQueryParams.setStartYear(startYear);
- int currentYear = java.time.Year.now().getValue();
- feeQueryParams.setEndYear(currentYear + 2);
- if(reqJson.containsKey("endYear") && !reqJson.get("endYear").equals("") && reqJson.get("endYear") != null) {
- currentYear=Integer.parseInt(reqJson.get("endYear")+"");
- feeQueryParams.setEndYear(Integer.parseInt(reqJson.get("endYear")+"")+2);
- }
- int endYear = feeQueryParams.getEndYear();
- int doYear = endYear - startYear;
+ /**
+ * 妫�鏌ヤ袱涓尯鍩熸槸鍚﹂噸鍙�
+ */
+ private boolean regionsOverlap(CellRangeAddress region1, CellRangeAddress region2) {
+ boolean rowsOverlap = !(region1.getLastRow() < region2.getFirstRow() ||
+ region1.getFirstRow() > region2.getLastRow());
- ReportQueryRecord reportQueryRecord = new ReportQueryRecord();
- reportQueryRecord.setCommunityId(reqJson.getString("communityId"));
- reportQueryRecord.setQueryStatus("0");
- reportQueryRecord.setEndYear(currentYear+"");
- reportQueryRecord.setOperator("鐧藉崟娴佹按鐗╀笟琛�");
- List<ReportQueryRecord> reportQueryRecords = reportFeeInnerServiceSMOImpl.queryReport(BeanConvertUtil.beanCovertMap(reportQueryRecord));
- int row = Integer.parseInt(reqJson.containsKey("row")?reqJson.getString("row"):"10");
- int page = Integer.parseInt(reqJson.containsKey("page")?reqJson.getString("page"):"1");
- startYear = 2016;
- endYear = DateUtil.getYear();
- Object[][] test = new Object[row][];
- RoomDto roomDto = new RoomDto();
- roomDto.setCommunityId(reqJson.getString("communityId"));
- roomDto.setRow(row);
- roomDto.setPage(page * row - row);
- roomDto.setFloorNum(reqJson.containsKey("floorId")&&!(reqJson.getString("floorId").equals(""))?reqJson.getString("floorId"):null);
- roomDto.setOwnerName(reqJson.containsKey("ownerName")&&reqJson.getString("ownerName").equals("")?null:reqJson.getString("ownerName"));
- List<Map> rooms = roomInnerServiceSMOImpl.queryRoomsAsReport(roomDto);
- int count = roomInnerServiceSMOImpl.queryRoomsCount(roomDto);
- FeeConfigDto feeConfigDto = new FeeConfigDto();
- feeConfigDto.setCommunityId(reqJson.getString("communityId"));
- if(reqJson.containsKey("feeTypeCd") && !(reqJson.get("feeTypeCd").equals(""))){
- feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
- }else{
- feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
- }
- if(reqJson.containsKey("importFee") && !(reqJson.get("importFee").equals(""))){
- feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
- }else{
- feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
- }
- List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMOImpl.queryFeeConfigs(feeConfigDto);
+ boolean colsOverlap = !(region1.getLastColumn() < region2.getFirstColumn() ||
+ region1.getFirstColumn() > region2.getLastColumn());
-// 2. 娴佸紡澶勭悊锛氭帓搴� 鈫� 鍘婚噸锛堟寜鍗曚环锛� 鈫� 鍙栧墠2鏉�
- List<FeeConfigDto> top2DistinctPriceDtos = originalDtos.stream()
- // 绗竴姝ワ細鎸変笟鍔¤鍒欐帓搴忥紙绀轰緥锛氬厛鎸夊崟浠峰崌搴忥紝鐩稿悓鍗曚环鎸塻tartTime闄嶅簭锛堜繚鐣欐渶鏂拌褰曪級锛�
- .sorted(
- // 鍗曚环鎺掑簭锛歯ull鍊兼斁鏈�鍚庯紝闈瀗ull鎸夎嚜鐒堕『搴忥紙濡侭igDecimal鍗囧簭锛�
- Comparator.comparing(FeeConfigDto::getSquarePrice, Comparator.nullsLast(Comparator.naturalOrder()))
- // 鐩稿悓鍗曚环鏃讹紝鎸塻tartTime闄嶅簭锛堢‘淇濅繚鐣欌�滄渶鏂扳�濈殑閭ｆ潯锛�
- .thenComparing(FeeConfigDto::getStartTime, Comparator.nullsLast(Comparator.reverseOrder()))
- )
- // 绗簩姝ワ細鎸夊崟浠峰垎缁勶紙鑷姩鍘婚噸锛屾瘡缁勫搴斾竴涓敮涓�鍗曚环锛�
- .collect(Collectors.groupingBy(
- dto -> (dto.getSquarePrice() == null) ? new Object() : dto.getSquarePrice(), // 澶勭悊null鐨勫垎缁勯敭
- Collectors.toList() // 姣忕粍鍐呮槸鎺掑簭鍚庣殑璁板綍锛堝凡纭繚鏈�浼橈級
- ))
- // 绗笁姝ワ細鎻愬彇姣忕粍鐨勭涓�鏉★紙鍗宠鍗曚环鐨勨�滄渶浼樿褰曗�濓級锛屽啀鍙栧墠2鏉�
- .values().stream()
- .map(group -> group.get(0))
- .collect(Collectors.toList());
- List<FeeConfigDto> feeConfigDtos = top2DistinctPriceDtos;
-
- int arrLength = 11 + (2 * feeConfigDtos.size()) + 8 + ((endYear - startYear + 1) * 3 + 10) - 9;
- ReportExcelDto[] header = new ReportExcelDto[arrLength];
- List<List<ReportExcelDto>> lists = headerDoing(feeConfigDtos, startYear, endYear, arrLength);
- test[0] = header;
- for (int i = 1; i <= rooms.size(); i++) {
- Map map = rooms.get(i - 1);
- String[] strings = new String[arrLength];
- strings[0] = String.valueOf(row * (page - 1) + i);
- strings[1] = String.valueOf(rooms.get(i - 1).get("property_type"));
- strings[2] = rooms.get(i - 1).get("floor_num").toString();
- strings[3] = rooms.get(i - 1).get("unit_num").toString();
- strings[4] = rooms.get(i - 1).get("room_num").toString();
- strings[5] = strings[3] + "-" + strings[4];
- strings[6] = rooms.get(i - 1).get("property_address").toString();
- strings[7] = rooms.get(i - 1).get("room_area").toString();
- strings[8] = rooms.get(i - 1).get("name").toString();
- for (int j = 1; j <= feeConfigDtos.size(); j++) {
- strings[8 + j] = doublequ2(Double.parseDouble(feeConfigDtos.get(j - 1).getSquarePrice()))+"";
- }
- strings[8 + feeConfigDtos.size() + 1] = "";
- for (int j = 1; j <= feeConfigDtos.size(); j++) {
- strings[8 + feeConfigDtos.size() + 1 + j] = (doublequ2(Double.parseDouble(feeConfigDtos.get(j - 1).getSquarePrice()) * Double.parseDouble(String.valueOf((BigDecimal) rooms.get(i - 1).get("room_area")))))+"";
- }
- strings[8 + (2 * feeConfigDtos.size()) + 2] = (doublequ2(Double.parseDouble(feeConfigDtos.get(feeConfigDtos.size() - 1).getSquarePrice()) * Double.parseDouble(String.valueOf((BigDecimal) rooms.get(i - 1).get("room_area")))))+"";
- strings[8 + (2 * feeConfigDtos.size()) + 2] = doublequ2(Double.parseDouble(strings[8 + (2 * feeConfigDtos.size()) + 2]) * 12)+"";
- HashMap<Object, Object> objectObjectHashMap = new HashMap<>();
- objectObjectHashMap.put("payObjId", map.get("room_id"));
- List<Map> fee = reportFeeInnerServiceSMOImpl.onceRoomFee(objectObjectHashMap);
- double allMoney = 0;
- double allPayMoney = 0;
- int monthCount = 0;
- double noPayMoney = 0;
- double year1 = 0;
- double year2 = 0;
- double discountMoney = 0;
- String noPayDate = "";
- currentYear = DateUtil.getYear();
- for (Map map1 : fee){
- if(map1.containsKey("缁熻缁村害") && !(map1.get("缁熻缁村害").toString().contains("鎬昏"))){
- allMoney = doublequ2(((BigDecimal) map1.get("搴旀敹閲戦")).doubleValue()+allMoney);
- allPayMoney = doublequ2(((BigDecimal) map1.get("瀹炴敹閲戦")).doubleValue()+allPayMoney);
- monthCount = ((BigDecimal)map1.get("鏈敹鏈堟暟")).intValue()+monthCount;
- noPayMoney = doublequ2(((BigDecimal)map1.get("鏈敹閲戦")).intValue()+noPayMoney);
- noPayDate = noPayDate + map1.get("鏈敹鍖洪棿");
- discountMoney = doublequ2(((BigDecimal)map1.get("鎶樻墸閲戦")).doubleValue()+discountMoney);
- if(map1.get("缁熻缁村害").equals("2026")){
- year1 = doublequ2(((BigDecimal) map1.get("搴旀敹閲戦")).doubleValue() + year1);
- }
- if(map1.get("缁熻缁村害").equals("2027")){
- year2 = doublequ2(((BigDecimal) map1.get("搴旀敹閲戦")).doubleValue() + year2);
- }
- }
-
- }
- for (int x = endYear ; x >= startYear ; x --){
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 1] = "0";
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 2] = "";
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 3] = "";
- for (Map map1 : fee){
- if(map1.get("缁熻缁村害").equals((endYear - x + startYear)+"骞�")) {
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 1] = Integer.parseInt(strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 1]) + ((BigDecimal) map1.get("宸叉敹鏈堟暟")).intValue() + "";
- if(map1.containsKey("宸叉敹鍖洪棿")){
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 2] = strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 2] + " " + map1.get("宸叉敹鍖洪棿");
- }
- if(map1.containsKey("鏈敹鍖洪棿")){
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 3] = strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 3] + " " + map1.get("鏈敹鍖洪棿");
- }
- }
- }
- }
- strings[8 + (2 * feeConfigDtos.size()) + 3] = allMoney+"";
- strings[8 + (2 * feeConfigDtos.size()) + 4] = allPayMoney+"";
- strings[8 + (2 * feeConfigDtos.size()) + 5] = "";
- strings[8 + (2 * feeConfigDtos.size()) + 6] = year1+"";//2026
- strings[8 + (2 * feeConfigDtos.size()) + 7] = year2+"";//2027
- strings[8 + (2 * feeConfigDtos.size()) + 8] = monthCount+"";
- strings[8 + (2 * feeConfigDtos.size()) + 9] = noPayMoney+"";
- strings[8 + (2 * feeConfigDtos.size()) + 10] = "";//22
- strings[8 + (2 * feeConfigDtos.size()) + 11] = "";//23鍘嗗勾寰呮敹
- strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - startYear)) + 3 + 1] = discountMoney+"";
- test[i - 1] = strings;
- }
-
- ReportExcelDto reportExcelDto = new ReportExcelDto();
- reportExcelDto.setHeader(lists);
- reportExcelDto.setData(test);
- return reportExcelDto;
+ return rowsOverlap && colsOverlap;
}
- private String[][] backFeeList(String number, Object roomId) {
- HashMap<Object, Object> objectObjectHashMap = new HashMap<>();
- objectObjectHashMap.put("payObjId", roomId);
- objectObjectHashMap.put("feeTypeCd",number);
- List<Map> fee = reportFeeInnerServiceSMOImpl.onceRoomFee(objectObjectHashMap);
- String[][] strings = new String[fee.size()][17];
- for (int i = 0 ; i < fee.size(); i++) {
- Map map = fee.get(i);
- strings[i][0] = (String) map.get("缁熻缁村害");
- strings[i][1] = ((BigDecimal) map.get("搴旀敹鏈堟暟")).toString();
- strings[i][2] = doublequ2(((BigDecimal) map.get("搴旀敹閲戦")).doubleValue())+"";
-// strings[i][3] = (String) map.get("鎶樻墸閲戦");
- strings[i][4] = doublequ2(((BigDecimal) map.get("鎶樻墸閲戦")).doubleValue())+"";
-// strings[i][5] = (String) map.get("鎶樻墸閲戦");
-// strings[i][6] = (String) map.get("鎶樻墸閲戦");
-// strings[i][7] = (String) map.get("鎶樻墸閲戦");//鐢宠浜�
-// strings[i][8] = (String) map.get("鎶樻墸閲戦");//瀹℃壒浜�
-// strings[i][9] = (String) map.get("鎶樻墸閲戦");//鍙戣捣鏃ユ湡
- strings[i][10] = Double.parseDouble(strings[i][2]) - Double.parseDouble(strings[i][4])+"";
- strings[i][11] = ((BigDecimal) map.get("宸叉敹鏈堟暟")).toString();
- strings[i][12] = doublequ2(((BigDecimal) map.get("瀹炴敹閲戦")).doubleValue())+"";
- strings[i][13] = (String) map.get("宸叉敹鍖洪棿");
- strings[i][14] = ((BigDecimal) map.get("鏈敹鏈堟暟")).toString();
- strings[i][15] = doublequ2(((BigDecimal) map.get("鏈敹閲戦")).doubleValue())+"";
- strings[i][16] = (String) map.get("鏈敹鍖洪棿");
+ /**
+ * 璁剧疆鍗曞厓鏍煎��
+ */
+ private void setCellValue(Cell cell, Object value) {
+ if (value == null) {
+ return;
}
- return strings;
+ if (value instanceof String) {
+ cell.setCellValue((String) value);
+ } else if (value instanceof Number) {
+ cell.setCellValue(((Number) value).doubleValue());
+ } else if (value instanceof Date) {
+ cell.setCellValue((Date) value);
+ } else {
+ cell.setCellValue(value.toString());
+ }
}
- private void setHeader(String[] header) {
- header[0] = "骞翠唤";
- header[1] = "搴旂即鏈堟暟";
- header[2] = "搴旂即閲戦";
- header[3] = "鎶樻墸";
- header[4] = "鎵撴姌閲戦";
- header[5] = "鎵撴姌鍖洪棿";
- header[6] = "鎵撴姌鍗曞綊妗ｆ棩鏈�";
- header[7] = "鐢宠浜�";
- header[8] = "瀹℃壒浜�";
- header[9] = "鍙戣捣鏃ユ湡";
- header[10] = "鎶樺悗搴旂即";
- header[11] = "宸叉敹鏈堟暟";
- header[12] = "宸茬即閲戦";
- header[13] = "宸叉敹鍖洪棿";
- header[14] = "鏈即鏈堟暟";
- header[15] = "鏈即閲戦";
- header[16] = "鏈敹鍖洪棿";
-
+ /**
+ * 鏍煎紡鍖杁ouble淇濈暀涓や綅灏忔暟
+ */
+ private String formatDouble(double num) {
+ return String.valueOf((int) (num * 100) / 100.0);
}
- private List<List<ReportExcelDto>> headerDoing(List<FeeConfigDto> feeConfigDtos, int startYear, int endYear, int arrLength) {
- LinkedList<List<ReportExcelDto>> reportExcelDtos = new LinkedList<>();
- int feeRow = feeConfigDtos.size();
- LinkedList<ReportExcelDto> header = new LinkedList<>();
-
-// 1. 鍥哄畾琛ㄥご椤癸細涓ユ牸鎸夊師浠ｇ爜col鍊艰缃�
- header.add(createHeaderRow("搴忓彿", 1, 3)); // 鍘焤ow.setCol(1)
- header.add(createHeaderRow("鐗╀笟绫诲瀷", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢ㄥ墠涓�涓�1
- header.add(createHeaderRow("妤兼爧鍙�/寮�", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
- header.add(createHeaderRow("闂ㄥ彿", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
- header.add(createHeaderRow("瀹ゅ彿", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
- header.add(createHeaderRow("闂ㄥ鍙�", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
- header.add(createHeaderRow("浜ц瘉鍦板潃", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
- header.add(createHeaderRow("鏀惰垂闈㈢Н锛坢虏锛�", 1, 3));// 鍘熸湭鏀筩ol锛屾部鐢�1
- header.add(createHeaderRow("璐埧浜哄鍚�", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
-
-// 2. 绗竴涓猣eeRow寰幆锛氬師鏈樉寮忔敼col锛屾部鐢ㄥ墠涓�涓�1锛堟寜鍘熼�昏緫淇濇寔col=1锛�
- for (int i = 0; i < feeRow; i++) {
- header.add(createHeaderRow(feeConfigDtos.get(i).getFeeName(), 1, 3));
+ /**
+ * 瀹夊叏瑙ｆ瀽double
+ */
+ private double parseDouble(Object value, double defaultValue) {
+ if (value == null) {
+ return defaultValue;
}
-
-// 3. 璁惧杩愪綔璐癸細鍘熸湭鏀筩ol锛屾部鐢ㄥ墠涓�涓�1
- header.add(createHeaderRow("璁惧杩愪綔璐�", 1, 3));
-
-// 4. 绗簩涓猣eeRow寰幆锛氬師鏈樉寮忔敼col锛屾部鐢ㄥ墠涓�涓�1锛堟寜鍘熼�昏緫淇濇寔col=1锛�
- for (int i = 0; i < feeRow; i++) {
- header.add(createHeaderRow(feeConfigDtos.get(i).getFeeName(), 1, 3));
+ try {
+ return Double.parseDouble(value.toString());
+ } catch (NumberFormatException e) {
+ return defaultValue;
}
-
-// 5. 骞村簲鏀舵锛氬師鏄惧紡setCol(1)锛屼繚鎸乧ol=1
- header.add(createHeaderRow("骞村簲鏀舵", 1, 3));
-
-// 6. 鍚堣(2020骞�1鏈�-鑷充粖)锛氬師鏄惧紡setCol(8)锛屼繚鎸乧ol=8锛涘師setRow(1)锛屼繚鎸乺ow=1
- header.add(createHeaderRow("鍚堣(2020骞�1鏈�-鑷充粖)", 8, 1));
-
-// 7. 鍘嗗勾瀹炴敹锛氬師鏄惧紡setCol((endYear - startYear + 1) * 3)锛屼繚鎸佽璁＄畻閫昏緫
- int yearCol = (endYear - startYear + 1) * 3;
- header.add(createHeaderRow("鍘嗗勾瀹炴敹", yearCol+1, 1));
- header.add(createHeaderRow("鎿嶄綔", 1, 3)); // 鍘熸湭鏀筩ol锛屾部鐢�1
-
-// 鏈�缁堟坊鍔犺〃澶村埌鎶ヨ〃鍒楄〃
- reportExcelDtos.add(header);
-
-
- // 鍒濆鍖� LinkedList锛堣�岄潪鏁扮粍锛屾敮鎸侀『搴忔坊鍔狅級
- LinkedList<ReportExcelDto> header2 = new LinkedList<>();
-
-// 1. 搴旀敹銆佸疄鏀躲�佷唬鏀剁瓑鍥哄畾椤癸細鎸夐『搴廰dd锛屽搴斿師鏁扮粍0-7涓嬫爣
- header2.add(createHeaderRow("搴旀敹", 1, 2)); // 瀵瑰簲鍘焗eader[0]
- header2.add(createHeaderRow("瀹炴敹", 1, 2)); // 瀵瑰簲鍘焗eader[1]
- header2.add(createHeaderRow("浠ｆ敹", 1, 2)); // 瀵瑰簲鍘焗eader[2]
- header2.add(createHeaderRow("2026骞�", 1, 2)); // 瀵瑰簲鍘焗eader[3]
- header2.add(createHeaderRow("2027骞�", 1, 2)); // 瀵瑰簲鍘焗eader[4]
- header2.add(createHeaderRow("寰呮敹鏈堟暟", 1, 2)); // 瀵瑰簲鍘焗eader[5]
- header2.add(createHeaderRow("寰呮敹閲戦", 1, 2)); // 瀵瑰簲鍘焗eader[6]
- header2.add(createHeaderRow("浠ｆ敹鍖洪棿", 1, 2)); // 瀵瑰簲鍘焗eader[7]
-
-// 2. 骞翠唤寰幆锛氭寜鍘熶笅鏍囬�昏緫璁＄畻椤哄簭锛岀户缁璦dd锛堝師header[8]鍙婁互鍚庯級
- for (int i = startYear; i <= endYear; i++) {
- // 鍘熼�昏緫锛歩ndex = 7 + (i - startYear + 1) 鈫� 棣栨寰幆i=startYear鏃讹紝index=8
- // LinkedList鏃犻渶鍏冲績涓嬫爣锛岀洿鎺dd鍗冲彲淇濇寔椤哄簭锛堜笌鍘熸暟缁勪笅鏍囬『搴忎竴鑷达級
- header2.add(createHeaderRow(String.valueOf(i), 3, 1));
- }
-
-// 3. 鎵撴姌閲戦姹囨�伙細鍘熼�昏緫涓�"骞翠唤寰幆鍚�"鐨勪笅涓�涓綅缃紝缁х画add
- header2.add(createHeaderRow("鎵撴姌閲戦姹囨��", 1, 2));
-
-// 4. 鏈�缁堟坊鍔犲埌鎶ヨ〃鍒楄〃
- reportExcelDtos.add(header2);
-
- LinkedList<ReportExcelDto> header3 = new LinkedList<>();
-
- for (int i = startYear ; i <= endYear ; i++){
- header3.add(createHeaderRow("宸叉敹鏈堟暟", 1, 1));
- header3.add(createHeaderRow("宸叉敹鍖洪棿", 1, 1));
- header3.add(createHeaderRow("鏈敹鍖洪棿", 1, 1));
- }
- reportExcelDtos.add(header3);
-
- return reportExcelDtos;
}
- private ReportExcelDto createHeaderRow(String text, int col, int row) {
- ReportExcelDto reportExcelDto = new ReportExcelDto();
- reportExcelDto.setTest(text); // 娉ㄦ剰锛氬彲鑳芥槸setText()鐨勭瑪璇�
- reportExcelDto.setCol(col);
- reportExcelDto.setRow(row);
- return reportExcelDto;
+ /**
+ * 瀹夊叏瑙ｆ瀽BigDecimal
+ */
+ private BigDecimal parseBigDecimal(Object value, BigDecimal defaultValue) {
+ if (value == null) {
+ return defaultValue;
+ }
+ if (value instanceof BigDecimal) {
+ return (BigDecimal) value;
+ }
+ try {
+ return new BigDecimal(value.toString());
+ } catch (NumberFormatException e) {
+ return defaultValue;
+ }
}
-
- public double doublequ2(double num){
- return (int)(num * 100) / 100.0;
- }
-}
+}
\ No newline at end of file
--
Gitblit v1.8.0