From 397bfc8424f2b72d6ff40818da09107fbbf28abe Mon Sep 17 00:00:00 2001
From: maven <2163098428@qq.com>
Date: 星期五, 15 八月 2025 17:49:42 +0800
Subject: [PATCH] yys 修改导入导出
---
src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java | 3
src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java | 226 ++++++++++++++++++++++++++----------
src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java | 29 ++++
src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java | 3
src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java | 43 +++++++
5 files changed, 237 insertions(+), 67 deletions(-)
diff --git a/src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java b/src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java
new file mode 100644
index 0000000..6d59d27
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/ConfigurableMergeStrategy.java
@@ -0,0 +1,29 @@
+package com.ruoyi.common.utils.excel;
+
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.write.merge.AbstractMergeStrategy;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import java.util.List;
+
+public class ConfigurableMergeStrategy extends AbstractMergeStrategy {
+ private final List<CellRangeAddress> mergeRegions;
+
+ public ConfigurableMergeStrategy(List<CellRangeAddress> mergeRegions) {
+ this.mergeRegions = mergeRegions;
+ }
+
+ @Override
+ protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
+ // 妫�鏌ュ綋鍓嶅崟鍏冩牸鏄惁鍦ㄤ换浣曚竴涓悎骞跺尯鍩熷唴
+ for (CellRangeAddress region : mergeRegions) {
+ if (cell.getRowIndex() == region.getFirstRow() &&
+ cell.getColumnIndex() == region.getFirstColumn()) {
+ sheet.addMergedRegion(region); // 鎵ц鍚堝苟
+ break;
+ }
+ }
+ }
+}
diff --git a/src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java b/src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java
new file mode 100644
index 0000000..7c095e7
--- /dev/null
+++ b/src/main/java/com/ruoyi/common/utils/excel/CustomCellStyleHandler.java
@@ -0,0 +1,43 @@
+package com.ruoyi.common.utils.excel;
+
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.metadata.data.WriteCellData;
+import com.alibaba.excel.write.handler.CellWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
+import org.apache.poi.ss.usermodel.*;
+
+import java.util.List;
+
+public class CustomCellStyleHandler implements CellWriteHandler {
+
+ @Override
+ public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+
+ }
+
+
+ @Override
+ public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+ int row = cell.getRowIndex();
+ int col = cell.getColumnIndex();
+
+ // 闇�瑕佹帓闄ょ殑鍗曞厓鏍�
+ if ((row == 2 && col == 0) || (row == 10 && col == 0)) {
+ return;
+ }
+
+ // 涓哄叾浠栧崟鍏冩牸璁剧疆鏍峰紡锛堝眳涓�+鑷姩鎹㈣锛�
+ Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setWrapText(true);
+ cell.setCellStyle(style);
+ }
+}
\ No newline at end of file
diff --git a/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java b/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java
index 4428c44..966516f 100644
--- a/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java
+++ b/src/main/java/com/ruoyi/lavorissue/controller/LavorIssueController.java
@@ -26,6 +26,7 @@
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
+import java.io.UnsupportedEncodingException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.List;
@@ -131,7 +132,7 @@
@Log(title = "鍔充繚鍙戞斁-瀵煎嚭", businessType = BusinessType.EXPORT)
@PostMapping("/exportCopy")
@ApiOperation("鍔充繚鍙戞斁-瀵煎嚭")
- public void exportCopy(HttpServletResponse response) {
+ public void exportCopy(HttpServletResponse response) throws UnsupportedEncodingException {
laborIssueService.exportCopy(response);
}
diff --git a/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java b/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java
index c35eaf7..d78d670 100644
--- a/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java
+++ b/src/main/java/com/ruoyi/lavorissue/service/LavorIssueService.java
@@ -7,6 +7,7 @@
import com.ruoyi.lavorissue.pojo.LaborIssue;
import javax.servlet.http.HttpServletResponse;
+import java.io.UnsupportedEncodingException;
/**
* @author :yys
@@ -25,5 +26,5 @@
StatisticsLaborIssue statistics(StatisticsLaborIssue req) throws Exception;
- void exportCopy(HttpServletResponse response);
+ void exportCopy(HttpServletResponse response) throws UnsupportedEncodingException;
}
diff --git a/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java b/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java
index b85975f..3d2c087 100644
--- a/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java
+++ b/src/main/java/com/ruoyi/lavorissue/service/impl/LavorIssueServiceImpl.java
@@ -1,26 +1,37 @@
package com.ruoyi.lavorissue.service.impl;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.ExcelWriter;
+import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.approve.utils.StartAndEndDateDto;
-import com.ruoyi.common.utils.excel.ExcelUtils;
+import com.ruoyi.common.utils.SecurityUtils;
+import com.ruoyi.common.utils.excel.ConfigurableMergeStrategy;
+import com.ruoyi.common.utils.excel.CustomCellStyleHandler;
import com.ruoyi.lavorissue.dto.StatisticsLaborIssue;
import com.ruoyi.lavorissue.mapper.LavorIssueMapper;
import com.ruoyi.lavorissue.pojo.LaborIssue;
import com.ruoyi.lavorissue.service.LavorIssueService;
+import com.ruoyi.project.system.domain.SysDept;
import com.ruoyi.project.system.domain.SysDictData;
-import com.ruoyi.project.system.domain.SysDictType;
+import com.ruoyi.project.system.mapper.SysDeptMapper;
import com.ruoyi.project.system.mapper.SysDictDataMapper;
import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.io.UnsupportedEncodingException;
+import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
+import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
@@ -119,79 +130,164 @@
private SysDictDataMapper sysDictDataMapper;
@Override
- public void exportCopy(HttpServletResponse response) {
+ public void exportCopy(HttpServletResponse response) throws UnsupportedEncodingException {
List<SysDictData> sys_lavor_issue = sysDictDataMapper.selectDictDataByType("sys_lavor_issue");
if(CollectionUtils.isEmpty(sys_lavor_issue)){
throw new RuntimeException("瀛楀吀鏁版嵁涓虹┖");
}
- List<Object> head = new ArrayList<>();
- head.add("閮ㄩ棬");
- head.add(ExcelUtils.COLUMN_MERGE);
- head.add("浼佷笟绠＄悊绉�");
- head.add(ExcelUtils.COLUMN_MERGE);
- head.add("");
- for (int i = 0; i < sys_lavor_issue.size(); i++) {
- head.add(ExcelUtils.COLUMN_MERGE);
- }
-
- List<Object> head1 = new ArrayList<>();
- head1.add("寮�濮嬪勾/鏈�");
- head1.add(ExcelUtils.COLUMN_MERGE);
- head1.add("缁撴潫骞�/鏈�");
- head1.add(ExcelUtils.COLUMN_MERGE);
- head1.add("");
- for (int i = 0; i < sys_lavor_issue.size(); i++) {
- head1.add(ExcelUtils.COLUMN_MERGE);
- }
-
- List<Object> head2 = new ArrayList<>();
- head2.add("2025");
- 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(ExcelUtils.COLUMN_MERGE);
- }
-
- List<Object> head3 = new ArrayList<>();
- head3.add("閮ㄩ棬鍚嶇О");
- head3.add(ExcelUtils.COLUMN_MERGE);
- head3.add("濮撳悕");
- head3.add("宸ュ彿");
- 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)){
+ List<LaborIssue> laborIssues = lavorIssueMapper.list(new LaborIssue());
+ if (CollectionUtils.isEmpty(laborIssues)) {
throw new RuntimeException("鏁版嵁涓虹┖");
}
+ Map<String, List<LaborIssue>> laborIssueMap = laborIssues.stream()
+ .collect(Collectors.groupingBy(LaborIssue::getStaffNo));
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding("UTF-8");
+ // 杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
+ String fileName = URLEncoder.encode("澶栭儴瑁呯鍗�", "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
- 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);
+ try {
+ //鏂板缓ExcelWriter
+ ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
+ .registerWriteHandler(new CustomCellStyleHandler())
+ .registerWriteHandler(new ConfigurableMergeStrategy(exportExternalPackingListGetMerge(4,sys_lavor_issue.size(),laborIssueMap.size())))
+ .build();
+
+ List<List<List<String>>> data = completeExternalPackingList(laborIssueMap, sys_lavor_issue);
+
+ for (int i = 0; i < data.size(); i++) {
+ List<List<String>> list = data.get(i);
+
+ //鑾峰彇sheet0瀵硅薄
+ WriteSheet mainSheet = EasyExcel.writerSheet(i, "澶栭儴瑁呯鍗�" + i).build();
+ //鍚憇heet0鍐欏叆鏁版嵁 浼犲叆绌簂ist杩欐牱鍙鍑鸿〃澶�
+ excelWriter.write(list, mainSheet);
}
- });
+ //鍏抽棴娴�
+ excelWriter.finish();
+ } catch (IOException e) {
+ throw new RuntimeException("瀵煎嚭澶辫触");
+ }
+ }
- 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);
+ @Autowired
+ private SysDeptMapper sysDeptMapper;
+
+
+// 澶勭悊澶栭儴瑁呯鍗曟暟鎹�
+ private List<List<List<String>>> completeExternalPackingList(Map<String, List<LaborIssue>> listMap,List<SysDictData> sys_lavor_issue) {
+ List<List<List<String>>> data = new ArrayList<>();
+ int num = sys_lavor_issue.size();
+
+ List<List<String>> item = new ArrayList<>();
+ List<String> list = new ArrayList<>();
+ list.add("閮ㄩ棬");
+ list.add("");
+ list.add("浼佷笟绠＄悊绉�");
+ list.add("");
+ list.add("浼佷笟绠＄悊绉�2025骞�1鏈�-2025骞�3鏈堝姵淇濆彂鏀捐鍒掕〃");
+ for (int i = 1; i <= num; i++){
+ list.add("");
+ }
+ item.add(list);
+
+ List<String> list1 = new ArrayList<>();
+ list1.add("寮�濮嬪勾/鏈�");
+ list1.add("");
+ list1.add("缁撴潫骞�/鏈�");
+ list1.add("");
+ for (int i = 0; i <= num; i++){
+ list1.add("");
+ }
+ item.add(list1);
+
+ List<String> list2 = new ArrayList<>();
+ list2.add("2025");
+ list2.add("1");
+ list2.add("2025");
+ list2.add("3");
+ for (int i = 0; i <= num; i++){
+ list.add("");
+ }
+ item.add(list2);
+
+ List<String> list3 = new ArrayList<>();
+ list3.add("閮ㄩ棬鍚嶇О");
+ list3.add("");
+ list3.add("濮撳悕");
+ list3.add("宸ュ彿");
+ for (SysDictData sysDictData : sys_lavor_issue) {
+ list3.add(sysDictData.getDictLabel());
+ }
+ list3.add("绛惧悕");
+ item.add(list3);
+
+ // 濉��
+ SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getTenantId());
+ List<String> sumList = new ArrayList<>();
+ AtomicInteger i = new AtomicInteger();
+ listMap.forEach((key, value) -> {
+ List<String> list4 = new ArrayList<>();
+ list4.add(sysDept.getDeptName());
+ list4.add("");
+ list4.add(value.get(0).getStaffName());
+ list4.add(value.get(0).getStaffNo());
+ int j = 0;
+ for (SysDictData sysDictData : sys_lavor_issue) {
+ list4.add(value.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue()))
+ .mapToLong(LaborIssue::getNum)
+ .sum()
+ + "");
+ if(i.get() == 0){
+ sumList.add(value.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue()))
+ .mapToLong(LaborIssue::getNum)
+ .sum()
+ + "");
+ }else {
+ sumList.set(j,(Long.parseLong(sumList.get(j)) + value.stream().filter(laborIssue -> laborIssue.getDictId().equals(sysDictData.getDictValue()))
+ .mapToLong(LaborIssue::getNum)
+ .sum())
+ + "");
+ }
+ j++;
+ }
+ i.getAndIncrement();
+ item.add(list4);
+ });
+ List<String> list5 = new ArrayList<>();
+ list5.add("鍚堣");
+ list5.add("");
+ list5.add("");
+ list5.add("");
+ for (int h = 0; h < sumList.size(); h++){
+ list5.add(sumList.get(h));
+ }
+ item.add(list5);
+ data.add(item);
+ return data;
+ }
+
+ /**
+ * 澶栭儴瑁呯鍗曞悎骞跺崟鍏冩牸
+ *
+ * @return
+ */
+ private List<CellRangeAddress> exportExternalPackingListGetMerge(Integer rowNum,Integer num,Integer listSize) {
+ List<CellRangeAddress> mergeRegions = new ArrayList<>();
+ mergeRegions.add(new CellRangeAddress(0, 0, 0, 1)); // 鍚堝苟A1:Q2
+ mergeRegions.add(new CellRangeAddress(0, 0, 2, 3)); // 鍚堝苟A4:Q4
+
+ mergeRegions.add(new CellRangeAddress(0, 2, rowNum, rowNum + num)); // 鍚堝苟A5:B6
+ mergeRegions.add(new CellRangeAddress(1, 1, 0, 1)); // 鍚堝苟C5:E6
+ mergeRegions.add(new CellRangeAddress(1, 1, 2, 3)); // 鍚堝苟F5:G6
+ mergeRegions.add(new CellRangeAddress(3, 3, 0, 1)); // 鍚堝苟H5:I6
+ for (int i = 1; i <= listSize; i++) {
+ mergeRegions.add(new CellRangeAddress(3 + i, 3 + i, 0, 1));
+ }
+ mergeRegions.add(new CellRangeAddress(rowNum + listSize, rowNum + listSize, 0, 3));
+ return mergeRegions;
}
public Date getLastDayOfMonth(Date date) {
--
Gitblit v1.9.3