导出工资xlsx

huminmin

昨天 ff09f8e85d51c5d127796bfbd097f5fad9267d4b

导出工资xlsx

 src/main/java/com/ruoyi/production/mapper/ProductionAccountMapper.java

@@ -31,4 +31,6 @@

    List<Map<String, Object>> selectDailyWagesStats(@Param("startDate") String startDate, @Param("endDate") String endDate);

    List<String> selectAccountingMonths();

}

 src/main/java/com/ruoyi/staff/excel/StaffSalaryExportData.java

@@ -20,6 +20,9 @@
    @Excel(name = "员工姓名")
    private String staffName;

    @Excel(name = "入司日期")
    private String entryDate;

    @Excel(name = "部门名称")
    private String deptName;

@@ -29,30 +32,36 @@
    @Excel(name = "全勤奖金")
    private BigDecimal attendanceBonus;

    @Excel(name = "计件工资")
    private BigDecimal pieceSalary;
    @Excel(name = "日计工资")
    private BigDecimal dailySalary;

    @Excel(name = "计时工资")
    private BigDecimal hourlySalary;
    @Excel(name = "出勤天数")
    private Integer workDays;

    @Excel(name = "月工作日天数")
    private Integer monthWorkDays;

    @Excel(name = "月标准工时")
    private BigDecimal standardHours;
    @Excel(name = "效益")
    private BigDecimal conversionRatio;

    @Excel(name = "月时间合计")
    private BigDecimal monthTimeTotal;

    @Excel(name = "折合倍数")
    private BigDecimal conversionRatio;

    @Excel(name = "工序折算工时")
    private BigDecimal convertedWorkHours;

    @Excel(name = "其他收入")
    private BigDecimal otherIncome;

    @Excel(name = "事假")
    private BigDecimal personalLeave;

    @Excel(name = "病假")
    private BigDecimal sickLeave;

    @Excel(name = "丧假")
    private BigDecimal bereavementLeave;

    @Excel(name = "婚假")
    private BigDecimal marriageLeave;

    @Excel(name = "津贴")
    private BigDecimal allowance;

    @Excel(name = "社保个人")
    private BigDecimal socialPersonal;


 src/main/java/com/ruoyi/staff/service/impl/StaffSalaryMainServiceImpl.java

@@ -5,6 +5,8 @@
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.basic.mapper.ProductMapper;
import com.ruoyi.basic.pojo.Product;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.project.system.domain.SysDept;
import com.ruoyi.project.system.mapper.SysDeptMapper;
@@ -49,6 +51,7 @@
import java.time.YearMonth;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.text.SimpleDateFormat;
@@ -73,6 +76,7 @@
    private final StaffLeaveMapper staffLeaveMapper;
    private final StaffContractMapper staffContractMapper;
    private final SysDeptMapper sysDeptMapper;
    private final ProductMapper productMapper;
    private final ProductionAccountMapper productionAccountMapper;
    private final ProductionProductMainMapper productionProductMainMapper;
    private final TechnologyOperationMapper technologyOperationMapper;
@@ -308,14 +312,35 @@
                exportData.setSalaryMonth(main.getSalaryMonth());
                exportData.setSalaryTitle(main.getSalaryTitle());
                exportData.setStaffName(detail.getStaffName());
                if (detail.getEntryDate() != null) {
                    exportData.setEntryDate(detail.getEntryDate());
                } else if (detail.getStaffOnJobId() != null) {
                    StaffContract staffContract = staffContractMapper.selectOne(new LambdaQueryWrapper<StaffContract>()
                            .eq(StaffContract::getStaffOnJobId, detail.getStaffOnJobId())
                            .orderByAsc(StaffContract::getContractStartTime)
                            .orderByAsc(StaffContract::getId)
                            .last("limit 1"));
                    if (staffContract != null && staffContract.getContractStartTime() != null) {
                        exportData.setEntryDate(formatEntryDate(staffContract.getContractStartTime()));
                    }
                }
                exportData.setDeptName(detail.getDeptName());
                exportData.setBasicSalary(detail.getBasicSalary());
                exportData.setAttendanceBonus(detail.getAttendanceBonus());
                exportData.setPieceSalary(detail.getPieceSalary());
                exportData.setHourlySalary(detail.getHourlySalary());
                exportData.setDailySalary(detail.getBasicSalary() != null && detail.getWorkDays() != null && detail.getWorkDays() > 0
                        ? detail.getBasicSalary().divide(new BigDecimal(detail.getWorkDays()), 3, java.math.RoundingMode.HALF_UP)
                        : null);
                exportData.setWorkDays(detail.getWorkDays());
                exportData.setConversionRatio(detail.getConversionRatio() == null
                        ? null
                        : detail.getConversionRatio().setScale(4, java.math.RoundingMode.HALF_UP));
                exportData.setMonthTimeTotal(detail.getMonthTimeTotal());
                exportData.setConversionRatio(detail.getConversionRatio());
                exportData.setOtherIncome(detail.getOtherIncome());
                exportData.setPersonalLeave(detail.getPersonalLeave());
                exportData.setSickLeave(detail.getSickLeave());
                exportData.setBereavementLeave(detail.getBereavementLeave());
                exportData.setMarriageLeave(detail.getMarriageLeave());
                exportData.setAllowance(detail.getAllowance());
                exportData.setSocialPersonal(detail.getSocialPersonal());
                exportData.setFundPersonal(detail.getFundPersonal());
                exportData.setOtherDeduct(detail.getOtherDeduct());
@@ -475,85 +500,32 @@

    @Override
    public void exportWorkHours(HttpServletResponse response, String salaryMonth) {
        List<ProductionProductMainDto> accountList = queryProductionDetails(salaryMonth);
        List<String> operationNames = accountList.stream()
                .map(ProductionProductMainDto::getProcess)
                .filter(StringUtils::isNotEmpty)
                .distinct()
                .sorted()
                .collect(Collectors.toList());
        Workbook workbook = new XSSFWorkbook();
        CellStyle titleStyle = createStyle(workbook, true, HorizontalAlignment.CENTER, 14, false);
        CellStyle headerStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 11, false);
        CellStyle headerStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 11, true);
        CellStyle labelStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 10, false);
        CellStyle numStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 10, false);
        DataFormat dataFormat = workbook.createDataFormat();
        numStyle.setDataFormat(dataFormat.getFormat("0.00"));

        if (CollectionUtils.isEmpty(operationNames)) {
            operationNames = new ArrayList<>();
        List<String> monthList = new ArrayList<>();
        if (StringUtils.isNotEmpty(salaryMonth)) {
            monthList.add(salaryMonth);
        } else {
            monthList = productionAccountMapper.selectAccountingMonths();
        }

        int sheetIndex = 1;
        for (String operationName : operationNames) {
            TechnologyOperation operation = technologyOperationMapper.selectOne(
                    new LambdaQueryWrapper<TechnologyOperation>().eq(TechnologyOperation::getName, operationName).last("limit 1"));
            String sheetName = operationName;
            if (sheetName.length() > 31) {
                sheetName = sheetName.substring(0, 31);
        if (CollectionUtils.isEmpty(monthList)) {
            throw new IllegalArgumentException("没有可导出的生产核算月份");
            }
            Sheet sheet = workbook.createSheet(sheetName);
            int rowIdx = 0;
            Row titleRow = sheet.createRow(rowIdx++);
            titleRow.setHeightInPoints(52.5f);
            titleRow.createCell(0).setCellValue(operationName + "工时统计");
            titleRow.getCell(0).setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

            Row workDayRow = sheet.createRow(rowIdx++);
            workDayRow.createCell(0).setCellValue("工作日");
            workDayRow.getCell(0).setCellStyle(headerStyle);
            Integer monthWorkDays = calculateWorkDays(salaryMonth);
            createNumberCell(workDayRow, 2, monthWorkDays == null ? null : new java.math.BigDecimal(monthWorkDays), headerStyle);
            workDayRow.createCell(3).setCellValue("天");
            workDayRow.getCell(3).setCellStyle(headerStyle);
            createNumberCell(workDayRow, 4, monthWorkDays == null ? null : new java.math.BigDecimal(monthWorkDays * 8), headerStyle);
            workDayRow.createCell(5).setCellValue("小时");
            workDayRow.getCell(5).setCellStyle(headerStyle);

            Row headRow = sheet.createRow(rowIdx++);
            headRow.createCell(0).setCellValue("人员");
            headRow.getCell(0).setCellStyle(headerStyle);
            headRow.createCell(1).setCellValue("月产量");
            headRow.getCell(1).setCellStyle(headerStyle);
            headRow.createCell(2).setCellValue("折算时长");
            headRow.getCell(2).setCellStyle(headerStyle);
            headRow.createCell(3).setCellValue("工资");
            headRow.getCell(3).setCellStyle(headerStyle);

            Map<String, java.math.BigDecimal> standardMap = buildStandardQuantityMap(List.of(operationName));
            Map<String, java.math.BigDecimal> opQtyMap = buildOperationQuantityMap(accountList, operationName, operation);
            Set<String> employeeNames = accountList.stream()
                    .filter(item -> operationName.equals(item.getProcess()))
                    .map(ProductionProductMainDto::getSchedulingUserName)
                    .filter(StringUtils::isNotEmpty)
                    .collect(Collectors.toCollection(LinkedHashSet::new));
            for (String staffName : employeeNames) {
                Row row = sheet.createRow(rowIdx++);
                row.createCell(0).setCellValue(staffName);
                row.getCell(0).setCellStyle(labelStyle);
                java.math.BigDecimal qty = opQtyMap.getOrDefault(staffName, java.math.BigDecimal.ZERO);
                createNumberCell(row, 1, qty, numStyle);
                java.math.BigDecimal standardQty = standardMap.getOrDefault(operationName, java.math.BigDecimal.ONE);
                createNumberCell(row, 2, standardQty.compareTo(java.math.BigDecimal.ZERO) == 0 ? java.math.BigDecimal.ZERO : qty.divide(standardQty, 2, java.math.RoundingMode.HALF_UP).multiply(new java.math.BigDecimal("8")), numStyle);
                java.math.BigDecimal wage = calculateSheetWage(operation, operationName, qty, standardQty);
                createNumberCell(row, 3, wage, numStyle);
            }

            for (int i = 0; i <= 3; i++) {
                sheet.autoSizeColumn(i);
            }
            sheetIndex++;
        Sheet nonInstrumentSheet = workbook.createSheet("琴盒部");
        Sheet instrumentSheet = workbook.createSheet("成品部");
        int nonInstrumentRowIdx = 0;
        int instrumentRowIdx = 0;
        for (String month : monthList) {
            List<ProductionProductMainDto> accountList = queryProductionDetails(month);
            Integer monthWorkDays = calculateWorkDays(month);
            nonInstrumentRowIdx = appendWorkHoursSection(nonInstrumentSheet, nonInstrumentRowIdx, month, false, accountList, monthWorkDays, titleStyle, headerStyle, labelStyle, numStyle, nonInstrumentRowIdx > 0);
            instrumentRowIdx = appendWorkHoursSection(instrumentSheet, instrumentRowIdx, month, true, accountList, monthWorkDays, titleStyle, headerStyle, labelStyle, numStyle, instrumentRowIdx > 0);
        }

        try {
@@ -574,6 +546,295 @@
        }
        // 通过部门获取人员id
        return sysUserDeptMapper.setSchemeApplicableStaffUserInfo(ids);
    }

    private int appendWorkHoursSection(Sheet sheet,
                                       int startRowIdx,
                                       String salaryMonth,
                                       boolean instrumentSheet,
                                       List<ProductionProductMainDto> accountList,
                                       Integer monthWorkDays,
                                       CellStyle titleStyle,
                                       CellStyle headerStyle,
                                       CellStyle labelStyle,
                                       CellStyle numStyle,
                                       boolean addSectionTitle) {
        int rowIdx = startRowIdx;
        List<Map<String, Object>> columns = buildWorkHourColumns(instrumentSheet);
        int lastCol = 1 + columns.size() + 2;
        Row titleRow = sheet.createRow(rowIdx++);
        titleRow.setHeightInPoints(56f);
        String sectionTitle = (StringUtils.isEmpty(salaryMonth) ? "未知月份" : salaryMonth) + "工时统计";
        titleRow.createCell(0).setCellValue(sectionTitle);
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), 0, lastCol));

        Row workDayRow = sheet.createRow(rowIdx++);
        createTextCell(workDayRow, 0, "工作日", numStyle);
        createDisplayNumberCell(workDayRow, 1, monthWorkDays == null ? null : new java.math.BigDecimal(monthWorkDays), numStyle);
        createTextCell(workDayRow, 2, "天", numStyle);
        createDisplayNumberCell(workDayRow, 3, monthWorkDays == null ? null : new java.math.BigDecimal(monthWorkDays * 8), numStyle);
        createTextCell(workDayRow, 4, "小时", numStyle);

        Row headRow = sheet.createRow(rowIdx++);
        Row standardRow = sheet.createRow(rowIdx++);
        createTextCell(headRow, 0, "人员", numStyle);
        createTextCell(headRow, 1, "标准数量", numStyle);
        CellRangeAddress personRegion = new CellRangeAddress(headRow.getRowNum(), standardRow.getRowNum(), 0, 0);
        CellRangeAddress standardRegion = new CellRangeAddress(headRow.getRowNum(), standardRow.getRowNum(), 1, 1);
        sheet.addMergedRegion(personRegion);
        sheet.addMergedRegion(standardRegion);
        RegionUtil.setBorderTop(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, standardRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, standardRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, standardRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, standardRegion, sheet);
        int col = 2;
        for (Map<String, Object> column : columns) {
            createTextCell(headRow, col, String.valueOf(column.get("label")), numStyle);
            createDisplayNumberCell(standardRow, col, (java.math.BigDecimal) column.get("standardQty"), numStyle);
            col++;
        }
        createTextCell(headRow, col, "月时间合计", numStyle);
        createTextCell(standardRow, col, "", numStyle);
        col++;
        createTextCell(headRow, col, "折合倍数", numStyle);
        createTextCell(standardRow, col, "", numStyle);

        Map<String, List<ProductionProductMainDto>> grouped = accountList.stream()
                .filter(item -> item != null && item.getSchedulingUserName() != null)
                .filter(item -> instrumentSheet == isInstrumentProduct(item.getProductName()))
                .sorted(java.util.Comparator
                        .comparing(ProductionProductMainDto::getSchedulingUserName, java.util.Comparator.nullsLast(String::compareTo))
                        .thenComparing(item -> StringUtils.isEmpty(item.getProductName()) ? "" : item.getProductName())
                        .thenComparing(item -> StringUtils.isEmpty(item.getProcess()) ? "" : item.getProcess()))
                .collect(Collectors.groupingBy(
                        ProductionProductMainDto::getSchedulingUserName,
                        LinkedHashMap::new,
                        Collectors.toList()
                ));

        for (Map.Entry<String, List<ProductionProductMainDto>> entry : grouped.entrySet()) {
            String staffName = entry.getKey();
            List<ProductionProductMainDto> staffItems = entry.getValue();
            java.math.BigDecimal staffMonthTimeTotal = java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
            Map<String, java.math.BigDecimal> quantityMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> convertedMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> foldedMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> violinQuantityMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> violaQuantityMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> bassQuantityMap = new LinkedHashMap<>();
            for (Map<String, Object> column : columns) {
                String key = String.valueOf(column.get("key"));
                quantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                convertedMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                foldedMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                violinQuantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                violaQuantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                bassQuantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
            }
            for (ProductionProductMainDto item : staffItems) {
                String key = instrumentSheet ? item.getProcess() : item.getProductName();
                if (!quantityMap.containsKey(key)) {
                    continue;
                }
                java.math.BigDecimal quantity = item.getQuantity() == null ? java.math.BigDecimal.ZERO : item.getQuantity();
                TechnologyOperation operation = technologyOperationMapper.selectOne(
                        new LambdaQueryWrapper<TechnologyOperation>()
                                .eq(TechnologyOperation::getName, item.getProcess())
                                .last("limit 1"));
                java.math.BigDecimal standardQty = instrumentSheet
                        ? (operation == null || operation.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : operation.getStandardQuantity())
                        : (item.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : item.getStandardQuantity());
                java.math.BigDecimal foldedQty = instrumentSheet ? calculateFoldedQuantity(item, operation) : quantity;
                java.math.BigDecimal convertedHours = calculateExportConvertedHours(foldedQty, standardQty, item.getRepairWorkHour());
                quantityMap.merge(key, quantity, java.math.BigDecimal::add);
                foldedMap.merge(key, foldedQty, java.math.BigDecimal::add);
                convertedMap.merge(key, convertedHours, java.math.BigDecimal::add);
                if (instrumentSheet) {
                    if ("小提琴".equals(item.getProductName())) {
                        violinQuantityMap.merge(key, quantity, java.math.BigDecimal::add);
                    } else if ("大提琴".equals(item.getProductName())) {
                        violaQuantityMap.merge(key, quantity, java.math.BigDecimal::add);
                    } else if ("贝斯".equals(item.getProductName())) {
                        bassQuantityMap.merge(key, quantity, java.math.BigDecimal::add);
                    }
                }
                staffMonthTimeTotal = staffMonthTimeTotal.add(convertedHours).setScale(2, java.math.RoundingMode.HALF_UP);
            }

            if (!staffItems.isEmpty()) {
                Row qtyRow = sheet.createRow(rowIdx++);
                qtyRow.setHeightInPoints(24f);
                createEdgeCell(sheet.getWorkbook(), qtyRow, 0, staffName, numStyle, true, false, true, true);
                createTextCell(qtyRow, 1, instrumentSheet ? "月产小" : "月产量", numStyle);
                int dataCol = 2;
                for (Map<String, Object> column : columns) {
                    String key = String.valueOf(column.get("key"));
                    createDisplayNumberCell(qtyRow, dataCol++, instrumentSheet ? violinQuantityMap.getOrDefault(key, java.math.BigDecimal.ZERO) : quantityMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                }
                createDisplayNumberCell(qtyRow, dataCol++, null, numStyle);
                createDisplayNumberCell(qtyRow, dataCol, null, numStyle);

                if (instrumentSheet) {
                    Row violaRow = sheet.createRow(rowIdx++);
                    violaRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), violaRow, 0, "", numStyle, false, false, true, true);
                    createTextCell(violaRow, 1, "月产大", numStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(violaRow, dataCol++, violaQuantityMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                    }
                    createDisplayNumberCell(violaRow, dataCol++, null, numStyle);
                    createDisplayNumberCell(violaRow, dataCol, null, numStyle);

                    Row bassRow = sheet.createRow(rowIdx++);
                    bassRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), bassRow, 0, "", numStyle, false, false, true, true);
                    createTextCell(bassRow, 1, "月产贝", numStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(bassRow, dataCol++, bassQuantityMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                    }
                    createDisplayNumberCell(bassRow, dataCol++, null, numStyle);
                    createDisplayNumberCell(bassRow, dataCol, null, numStyle);

                    Row foldedQtyRow = sheet.createRow(rowIdx++);
                    foldedQtyRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), foldedQtyRow, 0, "", numStyle, false, false, true, true);
                    createTextCell(foldedQtyRow, 1, "月产折合小提", numStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(foldedQtyRow, dataCol++, foldedMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                    }
                    createDisplayNumberCell(foldedQtyRow, dataCol++, null, numStyle);
                    createDisplayNumberCell(foldedQtyRow, dataCol, null, numStyle);

                    Row hoursRow = sheet.createRow(rowIdx++);
                    hoursRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), hoursRow, 0, "", headerStyle, false, true, true, true);
                    createTextCell(hoursRow, 1, "折算时长", headerStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(hoursRow, dataCol++, convertedMap.getOrDefault(key, java.math.BigDecimal.ZERO), headerStyle);
                    }
                    createDisplayNumberCell(hoursRow, dataCol++, staffMonthTimeTotal, headerStyle);
                    createDisplayNumberCell(hoursRow, dataCol, monthWorkDays == null || monthWorkDays <= 0
                            ? null
                            : staffMonthTimeTotal.divide(new java.math.BigDecimal(monthWorkDays * 8), 8, java.math.RoundingMode.HALF_UP).setScale(4, java.math.RoundingMode.HALF_UP), headerStyle);
                    CellRangeAddress staffRegion = new CellRangeAddress(qtyRow.getRowNum(), hoursRow.getRowNum(), 0, 0);
                    sheet.addMergedRegion(staffRegion);
                    RegionUtil.setBorderTop(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, staffRegion, sheet);
                } else {
                    Row hoursRow = sheet.createRow(rowIdx++);
                    hoursRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), hoursRow, 0, "", headerStyle, false, true, true, true);
                    createTextCell(hoursRow, 1, "折算时长", headerStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(hoursRow, dataCol++, convertedMap.getOrDefault(key, java.math.BigDecimal.ZERO), headerStyle);
                    }
                    createDisplayNumberCell(hoursRow, dataCol++, staffMonthTimeTotal, headerStyle);
                    createDisplayNumberCell(hoursRow, dataCol, monthWorkDays == null || monthWorkDays <= 0
                            ? null
                            : staffMonthTimeTotal.divide(new java.math.BigDecimal(monthWorkDays * 8), 8, java.math.RoundingMode.HALF_UP).setScale(4, java.math.RoundingMode.HALF_UP), headerStyle);
                    CellRangeAddress staffRegion = new CellRangeAddress(qtyRow.getRowNum(), hoursRow.getRowNum(), 0, 0);
                    sheet.addMergedRegion(staffRegion);
                    RegionUtil.setBorderTop(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, staffRegion, sheet);
                }
            }
        }

        int[] widths = buildWorkHourColumnWidths(columns.size(), instrumentSheet);
        for (int i = 0; i < widths.length; i++) {
            sheet.setColumnWidth(i, widths[i] * 256);
        }
        return rowIdx;
    }

    private List<Map<String, Object>> buildWorkHourColumns(boolean instrumentSheet) {
        Map<String, Map<String, Object>> columnMap = new LinkedHashMap<>();
        if (instrumentSheet) {
            List<TechnologyOperation> operations = technologyOperationMapper.selectList(
                    new LambdaQueryWrapper<TechnologyOperation>()
                            .orderByAsc(TechnologyOperation::getId));
            for (TechnologyOperation operation : operations) {
                if (operation == null || StringUtils.isEmpty(operation.getName())) {
                    continue;
                }
                String key = operation.getName();
                if (columnMap.containsKey(key)) {
                    continue;
                }
                Map<String, Object> column = new LinkedHashMap<>();
                column.put("key", key);
                column.put("label", operation.getName());
                column.put("standardQty", operation.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : operation.getStandardQuantity());
                columnMap.put(key, column);
            }
        } else {
            List<Product> products = productMapper.selectList(
                    new LambdaQueryWrapper<Product>()
                            .isNotNull(Product::getParentId)
                            .orderByAsc(Product::getId));
            for (Product product : products) {
                if (product == null || StringUtils.isEmpty(product.getProductName())) {
                    continue;
                }
                if (isInstrumentProduct(product.getProductName())) {
                    continue;
                }
                String key = product.getProductName();
                if (columnMap.containsKey(key)) {
                    continue;
                }
                Map<String, Object> column = new LinkedHashMap<>();
                column.put("key", key);
                column.put("label", product.getProductName());
                column.put("standardQty", product.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : product.getStandardQuantity());
                columnMap.put(key, column);
            }
        }
        return new ArrayList<>(columnMap.values());
    }

    private int[] buildWorkHourColumnWidths(int columnCount, boolean instrumentSheet) {
        int baseSize = 2;
        int total = baseSize + columnCount;
        int[] widths = new int[total];
        widths[0] = 16;
        widths[1] = 14;
        for (int i = 2; i < total; i++) {
            widths[i] = 11;
        }
        return widths;
    }

    private java.math.BigDecimal calculateExportConvertedHours(java.math.BigDecimal foldedQty,
                                                               java.math.BigDecimal standardQty,
                                                               java.math.BigDecimal repairWorkHour) {
        if (foldedQty == null || standardQty == null || standardQty.compareTo(java.math.BigDecimal.ZERO) <= 0) {
            return repairWorkHour == null ? java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP)
                    : repairWorkHour.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        return foldedQty.divide(standardQty, 8, java.math.RoundingMode.HALF_UP)
                .multiply(new java.math.BigDecimal("8"))
                .add(repairWorkHour == null ? java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP) : repairWorkHour.setScale(2, java.math.RoundingMode.HALF_UP))
                .setScale(2, java.math.RoundingMode.HALF_UP);
    }

    @Override
@@ -670,10 +931,40 @@
        cell.setCellStyle(style);
    }

    private void createEdgeCell(Workbook workbook,
                                Row row,
                                int colIndex,
                                String value,
                                CellStyle baseStyle,
                                boolean topBorder,
                                boolean bottomBorder,
                                boolean leftBorder,
                                boolean rightBorder) {
        CellStyle edgeStyle = workbook.createCellStyle();
        edgeStyle.cloneStyleFrom(baseStyle);
        edgeStyle.setBorderTop(topBorder ? BorderStyle.THIN : BorderStyle.NONE);
        edgeStyle.setBorderBottom(bottomBorder ? BorderStyle.THIN : BorderStyle.NONE);
        edgeStyle.setBorderLeft(leftBorder ? BorderStyle.THIN : BorderStyle.NONE);
        edgeStyle.setBorderRight(rightBorder ? BorderStyle.THIN : BorderStyle.NONE);
        Cell cell = row.createCell(colIndex);
        cell.setCellValue(value == null ? "" : value);
        cell.setCellStyle(edgeStyle);
    }

    private void createNumberCell(Row row, int colIndex, java.math.BigDecimal value, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        if (value != null) {
            cell.setCellValue(value.doubleValue());
        }
        cell.setCellStyle(style);
    }

    private void createDisplayNumberCell(Row row, int colIndex, java.math.BigDecimal value, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        if (value != null && value.compareTo(java.math.BigDecimal.ZERO) != 0) {
            cell.setCellValue(value.doubleValue());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
    }
@@ -710,7 +1001,6 @@
        dto.setEntryDateStart(start);
        dto.setEntryDateEnd(end);
        dto.setAuditStatus(1);
        dto.setReportType(0);
        Page<ProductionAccountDto> page = new Page<>(1, 100000);
        return productionProductMainMapper.listProductionDetails(dto, page).getRecords();
    }

 src/main/resources/mapper/production/ProductionAccountMapper.xml

@@ -218,4 +218,13 @@
        order by date_format(scheduling_date, '%m-%d')
    </select>

    <select id="selectAccountingMonths" resultType="java.lang.String">
        select distinct date_format(pa.scheduling_date, '%Y-%m') as monthValue
        from production_account pa
                 inner join production_product_main ppm on ppm.id = pa.production_product_main_id
        where ppm.audit_status = 1
          and ppm.report_type in (0, 1)
        order by monthValue
    </select>

</mapper>