批量导出设备利用率

Lius

2025-05-29 1a22f9f330ef0a51cd2a838932c1499346365dae

批量导出设备利用率

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/controller/MdcEfficiencyReportController.java

@@ -14,8 +14,10 @@
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

/**
 * @author: LiuS
@@ -40,6 +42,12 @@
        return Result.OK(result);
    }

    @ApiOperation(value = "设备效率报表-利用率列表导出", notes = "设备效率报表-利用率列表导出")
    @RequestMapping(value = "/exportEfficiencyXls")
    public ModelAndView exportEfficiencyXls(HttpServletRequest request, MdcEfficiencyReportQueryVo vo) {
        return mdcEfficiencyReportService.exportEfficiencyXls(vo);
    }

    @AutoLog(value = "设备效率报表-开动率列表查询")
    @ApiOperation(value = "设备效率报表-开动率列表查询", notes = "设备效率报表-开动率列表查询")
    @GetMapping("/efficiencyPOList")

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/MdcEfficiencyReportService.java

@@ -3,6 +3,7 @@
import org.jeecg.modules.mdc.dto.ComparativeAnalysisDto;
import org.jeecg.modules.mdc.dto.DayUtilizationRateDto;
import org.jeecg.modules.mdc.vo.*;
import org.springframework.web.servlet.ModelAndView;

import java.math.BigDecimal;
import java.util.List;
@@ -102,4 +103,12 @@
     * @return
     */
    List<BigDecimal> getEfficiencyRate(String equipmentId, String date);

    /**
     * åˆ©ç”¨çŽ‡åˆ—表导出
     *
     * @param vo
     * @return
     */
    ModelAndView exportEfficiencyXls(MdcEfficiencyReportQueryVo vo);
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEfficiencyReportServiceImpl.java

@@ -2,12 +2,18 @@

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.modules.mdc.dto.*;
import org.jeecg.modules.mdc.entity.*;
import org.jeecg.modules.mdc.mapper.MdcEfficiencyReportMapper;
import org.jeecg.modules.mdc.service.*;
import org.jeecg.modules.mdc.util.CustomExcelView;
import org.jeecg.modules.mdc.util.DateUtils;
import org.jeecg.modules.mdc.vo.MdcUtilizationRateDto;
import org.jeecg.modules.mdc.vo.*;
@@ -17,8 +23,11 @@
import org.jeecg.modules.system.service.ISysDepartService;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
@@ -196,24 +205,42 @@
     */
    private void setDepartmentLevels(MdcEfficiencyListDto dto, MdcEquDepDto mdcEquDepDto, List<SysDepart> departList) {
        switch (mdcEquDepDto.getOrgType()) {
            case "1": dto.setLevel1(mdcEquDepDto.getDepartName()); break;
            case "2": dto.setLevel2(mdcEquDepDto.getDepartName()); break;
            case "3": dto.setLevel3(mdcEquDepDto.getDepartName()); break;
            case "1":
                dto.setLevel1(mdcEquDepDto.getDepartName());
                break;
            case "2":
                dto.setLevel2(mdcEquDepDto.getDepartName());
                break;
            case "3":
                dto.setLevel3(mdcEquDepDto.getDepartName());
                break;
        }

        Optional<SysDepart> sysDepart = departList.stream().filter(depart -> depart.getId().equals(mdcEquDepDto.getParentId())).findAny();
        sysDepart.ifPresent(depart -> {
            switch (depart.getOrgType()) {
                case "1": dto.setLevel1(depart.getDepartName()); break;
                case "2": dto.setLevel2(depart.getDepartName()); break;
                case "3": dto.setLevel3(depart.getDepartName()); break;
                case "1":
                    dto.setLevel1(depart.getDepartName());
                    break;
                case "2":
                    dto.setLevel2(depart.getDepartName());
                    break;
                case "3":
                    dto.setLevel3(depart.getDepartName());
                    break;
            }
            if (StringUtils.isNotEmpty(depart.getParentId())) {
                departList.stream().filter(d -> d.getId().equals(depart.getParentId())).findAny().ifPresent(parent -> {
                    switch (parent.getOrgType()) {
                        case "1": dto.setLevel1(parent.getDepartName()); break;
                        case "2": dto.setLevel2(parent.getDepartName()); break;
                        case "3": dto.setLevel3(parent.getDepartName()); break;
                        case "1":
                            dto.setLevel1(parent.getDepartName());
                            break;
                        case "2":
                            dto.setLevel2(parent.getDepartName());
                            break;
                        case "3":
                            dto.setLevel3(parent.getDepartName());
                            break;
                    }
                });
            }
@@ -225,24 +252,42 @@
     */
    private void setProductionLevels(MdcEfficiencyListDto dto, MdcEquProDto mdcEquProDto, List<MdcProduction> productionList) {
        switch (mdcEquProDto.getOrgType()) {
            case "1": dto.setLevel1(mdcEquProDto.getProductionName()); break;
            case "2": dto.setLevel2(mdcEquProDto.getProductionName()); break;
            case "3": dto.setLevel3(mdcEquProDto.getProductionName()); break;
            case "1":
                dto.setLevel1(mdcEquProDto.getProductionName());
                break;
            case "2":
                dto.setLevel2(mdcEquProDto.getProductionName());
                break;
            case "3":
                dto.setLevel3(mdcEquProDto.getProductionName());
                break;
        }

        Optional<MdcProduction> mdcProduction = productionList.stream().filter(production -> production.getId().equals(mdcEquProDto.getParentId())).findAny();
        mdcProduction.ifPresent(production -> {
            switch (production.getOrgType()) {
                case "1": dto.setLevel1(production.getProductionName()); break;
                case "2": dto.setLevel2(production.getProductionName()); break;
                case "3": dto.setLevel3(production.getProductionName()); break;
                case "1":
                    dto.setLevel1(production.getProductionName());
                    break;
                case "2":
                    dto.setLevel2(production.getProductionName());
                    break;
                case "3":
                    dto.setLevel3(production.getProductionName());
                    break;
            }
            if (StringUtils.isNotEmpty(production.getParentId())) {
                productionList.stream().filter(p -> p.getId().equals(production.getParentId())).findAny().ifPresent(parent -> {
                    switch (parent.getOrgType()) {
                        case "1": dto.setLevel1(parent.getProductionName()); break;
                        case "2": dto.setLevel2(parent.getProductionName()); break;
                        case "3": dto.setLevel3(parent.getProductionName()); break;
                        case "1":
                            dto.setLevel1(parent.getProductionName());
                            break;
                        case "2":
                            dto.setLevel2(parent.getProductionName());
                            break;
                        case "3":
                            dto.setLevel3(parent.getProductionName());
                            break;
                    }
                });
            }
@@ -1728,10 +1773,6 @@

                    List<MdcUtilizationResultDto> list = new ArrayList<>();
                    for (String date : dateList) {
//                        Date startTime = DateUtils.toDate(date + " 00:00:00", DateUtils.STR_DATE_TIME_SMALL);
//                        Date endTime = DateUtils.toDate(date + " 00:00:00", DateUtils.STR_DATE_TIME_SMALL);
//                        endTime = DateUtils.addDays(endTime, 1);
//                        list.add(this.utilizationRate(mdcEquDepDto.getEquipmentId(), mdcEquDepDto.getEquipmentName(), mdcEquDepDto.getEquipmentType(), startTime, endTime, date, mdcUtilizationRateList));
                        list.add(this.utilizationRateTrend(mdcEquDepDto.getEquipmentId(), mdcEquDepDto.getEquipmentName(), mdcEquDepDto.getEquipmentType(), date, mdcUtilizationRateList));
                    }
                    mdcEfficiencyListDto.setDataList(list);
@@ -1790,6 +1831,408 @@
        return mdcEfficiencyReportMapper.getEfficiencyRate(equipmentId, date);
    }

    /**
     * åˆ©ç”¨çŽ‡åˆ—表导出
     *
     * @param vo
     * @return
     */
    @Override
    public ModelAndView exportEfficiencyXls(MdcEfficiencyReportQueryVo vo){
        LoginUser user = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        // èŽ·å–设备 ID åˆ—表
        List<String> equipmentIds = getEquipmentIds(user.getId(), vo);
        if (vo.getEquipmentIdList() == null || vo.getEquipmentIdList().isEmpty()) {
            vo.setEquipmentIdList(equipmentIds);
        }

        // å¦‚果设备 ID åˆ—表为空，直接返回
        if (vo.getEquipmentIdList() == null || vo.getEquipmentIdList().isEmpty()) {
            throw new JeecgBootException("无数据");
        }
        // èŽ·å–设备 ID åˆ—表
        List<String> equipmentIdList = mdcEquipmentService.listEquipmentId(vo);
        if (equipmentIdList == null || equipmentIdList.isEmpty()) {
            throw new JeecgBootException("无数据");
        }
        vo.setEquipmentIdList(equipmentIdList);

        // 3. å‡†å¤‡è®¾å¤‡æ•°æ®
        List<MdcEfficiencyListDto> exportData = new ArrayList<>();

        // èŽ·å–日期列表并按月分组
        List<String> dates = DateUtils.getDatesStringList2(DateUtils.getShortDate2(vo.getStartTime()), DateUtils.getShortDate2(vo.getEndTime()));

        Map<String, List<String>> groupedByMonth = dates.stream()
                .collect(Collectors.groupingBy(
                        date -> date.substring(0, 6) // æå–年月（如 "202405"）
                ));
        // 2. æå–每月的首尾日期
        List<Map<String, Object>> monthRanges = groupedByMonth.entrySet().stream()
                .map(entry -> {
                    List<String> monthDates = entry.getValue();
                    Map<String, Object> map = new HashMap<>();
                    map.put("startTime", monthDates.get(0));
                    map.put("endTime", monthDates.get(monthDates.size() - 1));
                    map.put("dateList", monthDates);
                    return map;
                })
                .collect(Collectors.toList());

        for (Map<String, Object> monthRange : monthRanges) {
            List<MdcEfficiencyListDto> listDtos = new ArrayList<>();
            vo.setStartTime((String) monthRange.get("startTime"));
            vo.setEndTime((String) monthRange.get("endTime"));
            List<String> dateList = (List<String>) monthRange.get("dateList");
            // æŸ¥è¯¢è®¾å¤‡æ•ˆçŽ‡æ•°æ®
            List<MdcEfficiencyDto> efficiencyList = mdcEfficiencyReportMapper.efficiencyList(vo);
            // å¤„理数据
            // æ ¹æ®ç±»åž‹æ ‘处理不同的层级
            if ("2".equals(vo.getTypeTree())) {
                // éƒ¨é—¨å±‚级
                listDtos = processDeLevel(vo, efficiencyList, dateList);
            } else {
                // äº§çº¿å±‚级
                listDtos = processProLevel(vo, efficiencyList, dateList);
            }
            if (exportData.isEmpty()) {
                exportData.addAll(listDtos);
            } else {
                Map<String, MdcEfficiencyListDto> map = listDtos.stream().collect(Collectors.toMap(MdcEfficiencyListDto::getEquipmentId, a -> a, (k1, k2) -> k1));
                exportData.forEach(mdcEfficiencyListDto -> {
                    List<MdcEfficiencyResultDto> dataList = mdcEfficiencyListDto.getDataList();
                    if (map.containsKey(mdcEfficiencyListDto.getEquipmentId())) {
                        MdcEfficiencyListDto mdcEfficiencyListDto1 = map.get(mdcEfficiencyListDto.getEquipmentId());
                        List<MdcEfficiencyResultDto> dataList1 = mdcEfficiencyListDto1.getDataList();
                        dataList.addAll(dataList1);
                        mdcEfficiencyListDto.setDataList(dataList);
                    }
                });
            }

        }
        // æ•°æ®æŽ’序
        exportData = exportData.stream().sorted(Comparator.comparing(MdcEfficiencyListDto::getLevel2)).sorted(Comparator.comparing(MdcEfficiencyListDto::getLevel3)).collect(Collectors.toList());


        // 1. åˆ›å»ºå·¥ä½œç°¿å’Œå·¥ä½œè¡¨
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("利用率数据");

        // 3. å‡†å¤‡è®¾å¤‡æ•°æ®

        // åˆ›å»ºæ ·å¼
        CellStyle centerStyle = createCenterStyle(workbook);
        CellStyle fixedHeaderStyle = createFixedHeaderStyle(workbook);
        CellStyle dateHeaderStyle = createDateHeaderStyle(workbook);
        CellStyle metricHeaderStyle = createMetricHeaderStyle(workbook);

//        dates.add("合计");
//        dates.add("平均");

        // å†™å…¥æ ‡é¢˜è¡Œ
        writeHeaderRows(sheet, fixedHeaderStyle, dateHeaderStyle, metricHeaderStyle, dates);

        // å†™å…¥æ•°æ®è¡Œ
        writeDataRows(sheet, centerStyle, exportData, dates);

        // æ·»åŠ è‡ªé€‚应列宽（所有列，从0开始到最后一列）
//        autoSizeAllColumns(sheet, 0, sheet.getRow(0).getLastCellNum() - 1);

        // 7. å°†å·¥ä½œç°¿è½¬æ¢ä¸ºå­—节数组输出流
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        }

        // å…·ä½“实现可能需要根据你的项目结构进行调整
        ModelAndView mv = new ModelAndView(new CustomExcelView(bos.toByteArray()));
        return mv;
    }

    /**
     * è‡ªé€‚应所有列宽（处理合并单元格）
     * @param sheet å·¥ä½œè¡¨
     * @param startCol èµ·å§‹åˆ—索引
     * @param endCol ç»“束列索引
     */
    private void autoSizeAllColumns(Sheet sheet, int startCol, int endCol) {
        // å…ˆå¤„理合并单元格对列宽的影响
        adjustMergedCells(sheet);

        // è‡ªåŠ¨è°ƒæ•´åˆ—宽
        for (int i = startCol; i <= endCol; i++) {
            sheet.autoSizeColumn(i);
            // è§£å†³POI自动列宽过窄问题，增加额外宽度（单位：1/256字符宽度，这里增加200相当于约3个字符）
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1800);
        }
    }

    /**
     * å¤„理合并单元格，确保列宽计算正确
     * ï¼ˆPOI的autoSizeColumn对合并单元格支持不佳，需要手动展开计算）
     */
    private void adjustMergedCells(Sheet sheet) {
        List<CellRangeAddress> mergedRegions = new ArrayList<>();
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            mergedRegions.add(sheet.getMergedRegion(i));
        }

        for (CellRangeAddress region : mergedRegions) {
            int firstCol = region.getFirstColumn();
            int lastCol = region.getLastColumn();
            if (firstCol == lastCol) continue; // å•åˆ—合并无需处理

            // è®¡ç®—合并区域的最大宽度
            int maxWidth = 0;
            for (int row = region.getFirstRow(); row <= region.getLastRow(); row++) {
                Row rowData = sheet.getRow(row);
                if (rowData == null) continue;

                Cell cell = rowData.getCell(firstCol);
                if (cell == null) continue;

                int width = getCellWidth(cell);
                if (width > maxWidth) {
                    maxWidth = width;
                }
            }

            // åº”用到所有合并列（只设置首列宽度，POI会自动同步到合并列）
            sheet.setColumnWidth(firstCol, maxWidth);
        }
    }

    /**
     * èŽ·å–单元格内容宽度（考虑字体和内容）
     */
    private int getCellWidth(Cell cell) {
        // é€šè¿‡Sheet获取Workbook，兼容性更好
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle style = cell.getCellStyle();
        Font font = workbook.getFontAt(style.getFontIndex());
        String text = cell.getStringCellValue();

        // è®¡ç®—文本宽度（粗略估计，可根据实际字体调整）
        int width = text.length() * 256;
        if (font.getBold()) width = (int) (width * 1.2); // åŠ ç²—字体增加20%宽度
        return width + 512; // å¢žåŠ é¢å¤–宽度
    }

    /**
     * å¤„理部门层级
     */
    private List<MdcEfficiencyListDto> processDeLevel(MdcEfficiencyReportQueryVo vo, List<MdcEfficiencyDto> efficiencyList, List<String> dates) {
        List<MdcEquDepDto> equipmentList = mdcEquipmentService.findEquDepList(vo.getEquipmentIdList());
        List<SysDepart> departList = sysDepartService.list(new LambdaQueryWrapper<SysDepart>()
                .ne(SysDepart::getDelFlag, CommonConstant.DEL_FLAG_1.toString())
                .orderByAsc(SysDepart::getDepartOrder));

        return equipmentList.stream().map(mdcEquDepDto -> {
            MdcEfficiencyListDto mdcEfficiencyListDto = createEfficiencyListDto(mdcEquDepDto);
            setDepartmentLevels(mdcEfficiencyListDto, mdcEquDepDto, departList);
            mdcEfficiencyListDto.setDataList(processEffData(efficiencyList, dates, mdcEquDepDto.getEquipmentId()));
            return mdcEfficiencyListDto;
        }).collect(Collectors.toList());
    }

    private List<MdcEfficiencyListDto> processProLevel(MdcEfficiencyReportQueryVo vo, List<MdcEfficiencyDto> efficiencyList, List<String> dates) {
        List<MdcEquProDto> equipmentList = mdcEquipmentService.findEquProList(vo.getEquipmentIdList());
        List<MdcProduction> productionList = mdcProductionService.list(new LambdaQueryWrapper<MdcProduction>()
                .ne(MdcProduction::getDelFlag, CommonConstant.DEL_FLAG_1.toString())
                .orderByAsc(MdcProduction::getProductionOrder));

        return equipmentList.stream().map(mdcEquProDto -> {
            MdcEfficiencyListDto mdcEfficiencyListDto = createEfficiencyListDto(mdcEquProDto);
            setProductionLevels(mdcEfficiencyListDto, mdcEquProDto, productionList);
            mdcEfficiencyListDto.setDataList(processEffData(efficiencyList, dates, mdcEquProDto.getEquipmentId()));
            return mdcEfficiencyListDto;
        }).collect(Collectors.toList());
    }

    private List<MdcEfficiencyResultDto> processEffData(List<MdcEfficiencyDto> efficiencyList, List<String> dates, String equipmentId) {
        List<MdcEfficiencyResultDto> list = new ArrayList<>();
//        MdcEfficiencyResultDto avgDto = new MdcEfficiencyResultDto();
//        avgDto.setTheDate("平均值");
//        MdcEfficiencyResultDto sumDto = new MdcEfficiencyResultDto();
//        sumDto.setTheDate("合计");

        for (String date : dates) {
            MdcEfficiencyResultDto dto = effRate(efficiencyList, date, equipmentId);
            list.add(dto);
        }

//        sumDto.setProcessLong(avgDto.getProcessLong());
//        avgDto.setProcessLong(avgDto.getProcessLong().divide(new BigDecimal(dates.size()), 0, RoundingMode.HALF_UP));
//        avgDto.setUtilizationRate(avgDto.getUtilizationRate().divide(new BigDecimal(dates.size()), 4, RoundingMode.HALF_UP));
//        sumDto.setUtilizationRate(avgDto.getUtilizationRate());
//        avgDto.setStartRate(avgDto.getStartRate().divide(new BigDecimal(dates.size()), 4, RoundingMode.HALF_UP));
//        sumDto.setStartRate(avgDto.getStartRate());
//        avgDto.setOpenRate(avgDto.getOpenRate().divide(new BigDecimal(dates.size()), 4, RoundingMode.HALF_UP));
//        sumDto.setOpenRate(avgDto.getOpenRate());
//        sumDto.setOpenLong(avgDto.getOpenLong());
//        avgDto.setOpenLong(avgDto.getOpenLong().divide(new BigDecimal(dates.size()), 0, RoundingMode.HALF_UP));
//        sumDto.setWaitLong(avgDto.getWaitLong());
//        avgDto.setWaitLong(avgDto.getWaitLong().divide(new BigDecimal(dates.size()), 0, RoundingMode.HALF_UP));
//        sumDto.setCloseLong(avgDto.getCloseLong());
//        avgDto.setCloseLong(avgDto.getCloseLong().divide(new BigDecimal(dates.size()), 0, RoundingMode.HALF_UP));
//
//        list.add(sumDto);
//        list.add(avgDto);
        return list;
    }

    private MdcEfficiencyResultDto effRate(List<MdcEfficiencyDto> efficiencyList, String date, String equipmentId) {
        MdcEfficiencyResultDto mdcEfficiencyResultDto = new MdcEfficiencyResultDto();
        if (efficiencyList != null && !efficiencyList.isEmpty()) {
            Map<String, MdcEfficiencyDto> collect = efficiencyList.stream().collect(Collectors.toMap(dto -> dto.getEquipmentId() + "_" + dto.getTheDate(), dto -> dto));
            if (collect.containsKey(equipmentId + "_" + date)) {
                MdcEfficiencyDto efficiencyDto = collect.get(equipmentId + "_" + date);
                mdcEfficiencyResultDto.setTheDate(efficiencyDto.getTheDate());
                mdcEfficiencyResultDto.setProcessLong(efficiencyDto.getProcessLong().divide(new BigDecimal("3600"), 2, RoundingMode.HALF_UP));
                mdcEfficiencyResultDto.setUtilizationRate(efficiencyDto.getUtilizationRate().multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP));
                mdcEfficiencyResultDto.setStartRate(efficiencyDto.getStartRate().multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP));
                mdcEfficiencyResultDto.setOpenRate(efficiencyDto.getOpenRate().multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP));
                mdcEfficiencyResultDto.setOpenLong(efficiencyDto.getOpenLong().divide(new BigDecimal("3600"), 2, RoundingMode.HALF_UP));
                mdcEfficiencyResultDto.setWaitLong(efficiencyDto.getWaitLong().divide(new BigDecimal("3600"), 2, RoundingMode.HALF_UP));
                mdcEfficiencyResultDto.setCloseLong(efficiencyDto.getCloseLong().divide(new BigDecimal("3600"), 2, RoundingMode.HALF_UP));
            } else {
                mdcEfficiencyResultDto.setTheDate(date);
            }

        } else {
            mdcEfficiencyResultDto.setTheDate(date);
            mdcEfficiencyResultDto.setProcessLong(new BigDecimal("0"));
            mdcEfficiencyResultDto.setUtilizationRate(new BigDecimal("0"));
            mdcEfficiencyResultDto.setStartRate(new BigDecimal("0"));
            mdcEfficiencyResultDto.setOpenRate(new BigDecimal("0"));
            mdcEfficiencyResultDto.setOpenLong(new BigDecimal("0"));
            mdcEfficiencyResultDto.setWaitLong(new BigDecimal("0"));
            mdcEfficiencyResultDto.setCloseLong(new BigDecimal("0"));
        }
        return mdcEfficiencyResultDto;
    }

    private void writeHeaderRows(Sheet sheet, CellStyle fixedHeaderStyle, CellStyle dateHeaderStyle, CellStyle metricHeaderStyle, List<String> dateList) {
        Row titleRow1 = sheet.createRow(0);
        Row titleRow2 = sheet.createRow(1);
        int colIndex = 0;

        // å†™å…¥å›ºå®šåˆ—标题（公司、车间等）
        String[] fixedHeaders = {"公司", "车间", "工段", "设备ID", "设备名称", "设备类型"};
        for (int i = 0; i < fixedHeaders.length; i++) {
            createCellWithStyle(titleRow1, colIndex, fixedHeaders[i], fixedHeaderStyle);
            createCellWithStyle(titleRow2, colIndex, "", fixedHeaderStyle);
            // åˆå¹¶å•å…ƒæ ¼ï¼ˆè·¨è¡Œä¸è·¨åˆ—）
            CellRangeAddress region = new CellRangeAddress(0, 1, colIndex, colIndex);
            sheet.addMergedRegion(region);
            colIndex++;
        }

        // å†™å…¥æ—¥æœŸï¼Œè·¨6列合并
        for (String date : dateList) {
            CellRangeAddress dateRegion = new CellRangeAddress(0, 0, colIndex, colIndex + 5);
            sheet.addMergedRegion(dateRegion);
            createCellWithStyle(titleRow1, colIndex, date, dateHeaderStyle);
            colIndex += 6;
        }

        // å†™å…¥æ—¥æœŸæŒ‡æ ‡æ ‡é¢˜ï¼ˆæ¯ä¸ªæ—¥æœŸå¯¹åº”6列）
        colIndex = 6;
        for (String date : dateList) {
            createCellWithStyle(titleRow2, colIndex++, "利用率(%)", metricHeaderStyle);
            createCellWithStyle(titleRow2, colIndex++, "开机率(%)", metricHeaderStyle);
            createCellWithStyle(titleRow2, colIndex++, "开机时长(小时)", metricHeaderStyle);
            createCellWithStyle(titleRow2, colIndex++, "加工时长(小时)", metricHeaderStyle);
            createCellWithStyle(titleRow2, colIndex++, "待机时长(小时)", metricHeaderStyle);
            createCellWithStyle(titleRow2, colIndex++, "关机时长(小时)", metricHeaderStyle);
        }

    }

    private CellStyle createCenterStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // æ·»åŠ å®Œæ•´è¾¹æ¡†
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    private CellStyle createFixedHeaderStyle(Workbook workbook) {
        CellStyle style = createCenterStyle(workbook);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private CellStyle createDateHeaderStyle(Workbook workbook) {
        CellStyle style = createCenterStyle(workbook);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private CellStyle createMetricHeaderStyle(Workbook workbook) {
        CellStyle style = createCenterStyle(workbook);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private void writeDataRows(Sheet sheet, CellStyle style, List<MdcEfficiencyListDto> exportData, List<String> dateList) {
        int rowIndex = 2;
        for (MdcEfficiencyListDto device : exportData) {
            Row dataRow = sheet.createRow(rowIndex++);
            int colIndex = 0;

            // å†™å…¥å›ºå®šåˆ—数据（公司、车间等）
            createCellWithStyle(dataRow, colIndex++, device.getLevel1(), style);
            createCellWithStyle(dataRow, colIndex++, device.getLevel2(), style);
            createCellWithStyle(dataRow, colIndex++, device.getLevel3(), style);
            createCellWithStyle(dataRow, colIndex++, device.getEquipmentId(), style);
            createCellWithStyle(dataRow, colIndex++, device.getEquipmentName(), style);
            createCellWithStyle(dataRow, colIndex++, device.getEquipmentType(), style);


            // å†™å…¥æ—¥æœŸæŒ‡æ ‡æ•°æ®ï¼ˆæ¯ä¸ªæ—¥æœŸå¯¹åº”6列）
            for (String date : dateList) {
                // ç¡®ä¿dateStr对应的Map已初始化
                MdcEfficiencyResultDto dateData = device.getDataList().stream().filter(mdcEfficiencyResultDto -> date.equals(mdcEfficiencyResultDto.getTheDate())).collect(Collectors.toList()).get(0);
                createCellWithStyle(dataRow, colIndex++, dateData.getUtilizationRate().doubleValue(), style);
                createCellWithStyle(dataRow, colIndex++, dateData.getOpenRate().doubleValue(), style);
                createCellWithStyle(dataRow, colIndex++, dateData.getOpenLong().doubleValue(), style);
                createCellWithStyle(dataRow, colIndex++, dateData.getProcessLong().doubleValue(), style);
                createCellWithStyle(dataRow, colIndex++, dateData.getWaitLong().doubleValue(), style);
                createCellWithStyle(dataRow, colIndex++, dateData.getCloseLong().doubleValue(), style);
            }
        }
    }

    private void createCellWithStyle(Row row, int colIndex, Object value, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        }
        cell.setCellStyle(style);
    }

    private MdcUtilizationResultDto utilizationRate(String equipmentId, String equipmentName, String equipmentType, Date startTime, Date endTime, String date, List<MdcUtilizationRate> mdcUtilizationRateList) {
        MdcUtilizationResultDto dto = new MdcUtilizationResultDto();
        dto.setEquipmentId(equipmentId);

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/util/CustomExcelView.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,32 @@
package org.jeecg.modules.mdc.util;

import org.springframework.web.servlet.view.AbstractView;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Map;

/**
 * @Author: Lius
 * @CreateTime: 2025-05-28
 * @Description:
 */
public class CustomExcelView extends AbstractView {
    private byte[] excelData;

    public CustomExcelView(byte[] excelData) {
        this.excelData = excelData;
        setContentType("application/vnd.openxmlformats - officedocument.spreadsheetml.sheet");
    }

    @Override
    protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        response.setHeader("Content - Disposition", "attachment; filename=利用率数据.xlsx");
        response.setContentLength(excelData.length);
        ServletOutputStream out = response.getOutputStream();
        out.write(excelData);
        out.flush();
        out.close();
    }
}

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/mapper/xml/SysUserMapper.xml

@@ -215,7 +215,7 @@
                LEFT JOIN sys_user_role t2 ON t1.id = t2.user_id
                LEFT JOIN sys_role t3 ON t2.role_id = t3.id
        WHERE
            t3.role_code = #{roleCode} AND equipment_ids LIKE concat(concat('%',#{equipmentId}),'%')
            t3.role_code = #{roleCode} AND t1.equipment_ids LIKE concat(concat('%',#{equipmentId}),'%')
    </select>
    <select id="getUserByRoleCodeList" resultType="org.jeecg.modules.system.entity.SysUser">
        SELECT