yys 修改导入导出

maven

9 天以前 487bbcd45afcc288c27784962d3b40d5015bcd12

yys  修改导入导出

 pom.xml

@@ -289,6 +289,12 @@
            <version>1.12.2</version>

        </dependency>



        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>easyexcel</artifactId>

            <version>4.0.3</version>

        </dependency>



    </dependencies>



    <build>


 src/main/java/com/ruoyi/common/utils/excel/ExcelUtils.java

@@ -843,18 +843,47 @@
                } else {
                    merge = false;
                    if (x1 > 0) {
                        sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
                        // 检查是否与现有合并区域重叠，如果有则删除旧区域，再合并新区域
                        CellRangeAddress newRegion = new CellRangeAddress((x1 - 1), x2, y, y);
                        handleOverlappingRegions(sheet, newRegion);
                    }
                    x1 = 0;
                    x2 = 0;
                }
            }
            if (x1 > 0) {
                sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
                // 检查是否与现有合并区域重叠，如果有则删除旧区域，再合并新区域
                CellRangeAddress newRegion = new CellRangeAddress((x1 - 1), x2, y, y);
                handleOverlappingRegions(sheet, newRegion);
            }
        }
    }
 
    /**
     * 检查是否与现有合并区域重叠，如果有则删除旧区域，再合并新区域
     * @param sheet Excel工作表
     * @param newRegion 新的合并区域
     */
    private static void handleOverlappingRegions(Sheet sheet, CellRangeAddress newRegion) {
        // 移除重叠的现有区域
        List<CellRangeAddress> overlappingRegions = new ArrayList<>();
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress existingRegion = sheet.getMergedRegion(i);
            if (newRegion.intersects(existingRegion)) {
                overlappingRegions.add(existingRegion);
                sheet.removeMergedRegion(i);
            }
        }
        // 合并到新区域（这里简单地将它们添加到新的合并区域列表中，实际应用中可能需要更复杂的逻辑来真正“合并”区域）
        // 注意：POI的CellRangeAddress没有提供直接合并两个区域的方法，所以这里只是示例
        // 如果需要真正的合并，可能需要自定义逻辑来计算新的边界
        for (CellRangeAddress overlappingRegion : overlappingRegions) {
            sheet.addMergedRegion(overlappingRegion);
        }
        sheet.addMergedRegion(newRegion);
    }


    private static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

 src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java

@@ -20,10 +20,7 @@

import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.*;
import java.util.stream.Collectors;

/**
@@ -132,21 +129,19 @@
        head.add(ExcelUtils.COLUMN_MERGE);
        head.add("企业管理科");
        head.add(ExcelUtils.COLUMN_MERGE);
        head.add("企业管理科2025年1月-2025年3月劳保发放计划表");
        head.add("");
        for (int i = 0; i < sys_lavor_issue.size(); i++) {
            if (i == 0) {
                head.add(ExcelUtils.COLUMN_MERGE);
            } else {
                head.add(ExcelUtils.COLUMN_MERGE);
            }
        }

        List<Object> head1 = new ArrayList<>();
        head1.add("开始年/月");
        head1.add(ExcelUtils.COLUMN_MERGE);
        head1.add("结束年/月");
        head1.add(ExcelUtils.COLUMN_MERGE);
        for (int i = 0; i < sys_lavor_issue.size(); i++) {
            head1.add("");
        for (int i = 0; i < sys_lavor_issue.size(); i++) {
            head1.add(ExcelUtils.COLUMN_MERGE);
        }

        List<Object> head2 = new ArrayList<>();
@@ -154,9 +149,11 @@
        head2.add("1");
        head2.add("2025");
        head2.add("3");
        head2.add("企业管理科2025年1月-2025年3月劳保发放计划表");
        for (int i = 0; i < sys_lavor_issue.size(); i++) {
            head2.add("");
            head2.add(ExcelUtils.COLUMN_MERGE);
        }

        List<Object> head3 = new ArrayList<>();
        head3.add("部门名称");
        head3.add(ExcelUtils.COLUMN_MERGE);
@@ -165,12 +162,35 @@
        for (SysDictData sysDictData : sys_lavor_issue) {
            head3.add(sysDictData.getDictLabel());
        }
        head3.add("签名");

        // 填充数据
        List<Object> head4 = new ArrayList<>();
        List<LaborIssue> laborIssues = lavorIssueMapper.selectList(null);
        if(CollectionUtils.isEmpty(laborIssues)){
            throw new RuntimeException("数据为空");
        }

        Map<String, List<LaborIssue>> collect = laborIssues.stream().collect(Collectors.groupingBy(LaborIssue::getStaffNo));
        collect.forEach((k,v)->{
            head4.add(v.get(0).getDeptName());
            head4.add(ExcelUtils.COLUMN_MERGE);
            head4.add(v.get(0).getStaffName());
            head4.add(v.get(0).getStaffNo());
            // 判断字典数据
            for (SysDictData sysDictData : sys_lavor_issue) {
                long sum = v.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue())).mapToLong(LaborIssue::getNum).sum();
                head4.add(sum);
            }

        });

        List<List<Object>> sheetDataList = new ArrayList<>();
        sheetDataList.add(head);
        sheetDataList.add(head1);
        sheetDataList.add(head2);
        sheetDataList.add(head3);
        sheetDataList.add(head4);
        ExcelUtils.export(response, "劳保发放计划表", sheetDataList);
    }


 src/main/resources/mapper/lavorissue/LavorIssueMapper.xml

@@ -55,6 +55,9 @@
            <if test="req.issueDate != null">
                and t1.issue_date &lt; #{req.issueDate}
            </if>
            <if test="req.staffId != null">
                and t1.staff_id = #{req.staffId}
            </if>
        </where>
    </select>
</mapper>