From 723705c43f6e87e9f31e4c18eee1c163c14139bb Mon Sep 17 00:00:00 2001
From: Crunchy <3114200645@qq.com>
Date: 星期五, 10 五月 2024 16:55:48 +0800
Subject: [PATCH] 2024-5-10 绩效管理-班次-文件导出
---
system-run/src/test/java/com/yuanchu/mom/SystemRunApplicationTest.java | 21 ------
performance-server/src/main/java/com/yuanchu/mom/service/impl/PerformanceShiftServiceImpl.java | 42 ++++++++++----
performance-server/src/main/java/com/yuanchu/mom/utils/StyleUtils.java | 83 +++++++++++++++++++++++++++
performance-server/src/main/java/com/yuanchu/mom/service/PerformanceShiftService.java | 2
performance-server/src/main/java/com/yuanchu/mom/controller/PerformanceShiftController.java | 27 ++++++++-
5 files changed, 139 insertions(+), 36 deletions(-)
diff --git a/performance-server/src/main/java/com/yuanchu/mom/controller/PerformanceShiftController.java b/performance-server/src/main/java/com/yuanchu/mom/controller/PerformanceShiftController.java
index 30f16ae..45a48c6 100644
--- a/performance-server/src/main/java/com/yuanchu/mom/controller/PerformanceShiftController.java
+++ b/performance-server/src/main/java/com/yuanchu/mom/controller/PerformanceShiftController.java
@@ -1,14 +1,24 @@
package com.yuanchu.mom.controller;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
+import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanchu.mom.dto.PerformanceShiftAddDto;
import com.yuanchu.mom.pojo.PerformanceShift;
import com.yuanchu.mom.service.PerformanceShiftService;
+import com.yuanchu.mom.utils.StyleUtils;
import com.yuanchu.mom.vo.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
+
+import javax.servlet.http.HttpServletResponse;
+import javax.validation.constraints.NotNull;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
/**
@@ -55,8 +65,19 @@
@ApiOperation(value = "缁╂晥绠＄悊-鐝-瀵煎嚭")
@GetMapping("update")
- public Result<?> exportToExcel(String time, String userName, String laboratory) {
- performanceShiftService.exportToExcel(time, userName, laboratory);
- return Result.success();
+ public void exportToExcel(@NotNull(message = "鏃堕棿涓嶈兘涓虹┖锛�") String time, String userName, String laboratory, HttpServletResponse response) throws Exception {
+ Map<Object, Object> data = performanceShiftService.exportToExcel(time, userName, laboratory);
+ // 璁剧疆鍗曞厓鏍兼牱寮�
+ HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());
+ // 淇濆瓨鍒扮涓�涓猻heet涓�
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setHeader("requestType","excel");
+ response.setHeader("Access-Control-Expose-Headers", "requestType");
+ EasyExcel.write(response.getOutputStream())
+ .head((List<List<String>>) data.get("header"))
+ .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 鑷�傚簲鍒楀
+ .registerWriteHandler(horizontalCellStyleStrategy)
+ .sheet("妯℃澘")
+ .doWrite((Collection<?>) data.get("data"));
}
}
diff --git a/performance-server/src/main/java/com/yuanchu/mom/service/PerformanceShiftService.java b/performance-server/src/main/java/com/yuanchu/mom/service/PerformanceShiftService.java
index 25cd719..e671f1a 100644
--- a/performance-server/src/main/java/com/yuanchu/mom/service/PerformanceShiftService.java
+++ b/performance-server/src/main/java/com/yuanchu/mom/service/PerformanceShiftService.java
@@ -26,5 +26,5 @@
IPage<Map<String, Object>> performanceShiftPageYear(Page<Object> page, String time, String userName, String laboratory);
- Map<Object, Object> exportToExcel(String time, String userName, String laboratory);
+ Map<Object, Object> exportToExcel(String time, String userName, String laboratory) throws Exception;
}
diff --git a/performance-server/src/main/java/com/yuanchu/mom/service/impl/PerformanceShiftServiceImpl.java b/performance-server/src/main/java/com/yuanchu/mom/service/impl/PerformanceShiftServiceImpl.java
index b0f4c58..dde83c3 100644
--- a/performance-server/src/main/java/com/yuanchu/mom/service/impl/PerformanceShiftServiceImpl.java
+++ b/performance-server/src/main/java/com/yuanchu/mom/service/impl/PerformanceShiftServiceImpl.java
@@ -14,6 +14,7 @@
import com.yuanchu.mom.pojo.PerformanceShift;
import com.yuanchu.mom.service.EnumService;
import com.yuanchu.mom.service.PerformanceShiftService;
+import com.yuanchu.mom.utils.JackSonUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -24,7 +25,6 @@
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
-import java.util.stream.Collectors;
/**
* <p>
@@ -184,7 +184,7 @@
// 骞村垎椤典笌瀵煎嚭鍏卞悓浣跨敤
public List<Map<String, Object>> annualAttendanceProcessing(List<Map<String, Object>> mapYearList, List<Enums> shiftType){
for (Map<String, Object> map : mapYearList) {
- Map<String, Object> resultMap = new HashMap<>();
+ Map<String, Object> resultMap = new LinkedHashMap<>();
Map<String, Object> hashMapYear = new LinkedHashMap<>();
int totalYearAttendance = 0;
// 涓�骞�12涓湀
@@ -234,17 +234,33 @@
return count;
}
- public void dataRequiredForProcessingIntoExcel(List<Map<String, Object>> list){
- Map<Object, List<Map<String, Object>>> userId = list.stream().collect(Collectors.groupingBy(i -> i.get("user_id")));
- userId.forEach((k, v) -> {
- System.out.println(k);
- System.out.println("=============");
- System.out.println(v);
- });
+ public List<List<Object>> dataRequiredForProcessingIntoExcel(List<Map<String, Object>> list, List<Enums> enums) throws Exception {
+ List<List<Object>> data = new ArrayList<>();
+ for (int i = 0; i < list.size(); i++) {
+ List<Object> excelRowList = new ArrayList<>();
+ excelRowList.add(i + 1);
+ excelRowList.add(list.get(i).get("account"));
+ excelRowList.add(list.get(i).get("name"));
+ Map<String, Object> year = JackSonUtil.unmarshal(JackSonUtil.marshal(list.get(i).get("year")), Map.class);
+ excelRowList.add(year.get("totalYearAttendance"));
+ enums.forEach(j -> {
+ excelRowList.add(year.get(j.getLabel()));
+ });
+ Map<String, Map<String, Object>> month = JackSonUtil.unmarshal(JackSonUtil.marshal(list.get(i).get("month")), Map.class);
+ for (int j = 1; j < 13; j++) {
+ Object totalMonthAttendance = month.get(j + "").get("totalMonthAttendance");
+ excelRowList.add(totalMonthAttendance);
+ for (Enums anEnum : enums) {
+ excelRowList.add(month.get(j + "").get(anEnum.getLabel()));
+ }
+ }
+ data.add(excelRowList);
+ }
+ return data;
}
@Override
- public Map<Object, Object> exportToExcel(String time, String userName, String laboratory) {
+ public Map<Object, Object> exportToExcel(String time, String userName, String laboratory) throws Exception {
Map<Object, Object> map = new HashMap<>();
List<Enums> shiftType = enumService.selectEnumByCategory("鐝绫诲瀷");
DateTimeFormatter formatters = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
@@ -253,8 +269,8 @@
map.put("header", getHeader(localDateTime.getYear() + " 骞�", shiftType));
List<Map<String, Object>> mapYearList = baseMapper.performanceShiftYearList(time, userName, laboratory);
annualAttendanceProcessing(mapYearList, shiftType);
- dataRequiredForProcessingIntoExcel(mapYearList);
- map.put("data", mapYearList);
+ List<List<Object>> lists = dataRequiredForProcessingIntoExcel(mapYearList, shiftType);
+ map.put("data", lists);
return map;
}
@@ -319,12 +335,14 @@
line.add(Arrays.asList("鑰冨嫟姹囨��", "搴忓彿", "搴忓彿"));
line.add(Arrays.asList("鑰冨嫟姹囨��", "宸ュ彿", "宸ュ彿"));
line.add(Arrays.asList("鑰冨嫟姹囨��", "濮撳悕", "濮撳悕"));
+ line.add(Arrays.asList("鍑哄嫟璇︽儏", month, "鍑哄嫟"));
// 骞� header
for (Enums anEnum : enums) {
line.add(Arrays.asList("鑰冨嫟姹囨��", month, anEnum.getLabel()));
}
// 鏈坔eader
for (int i = 1; i < 13; i++) {
+ line.add(Arrays.asList("鍑哄嫟璇︽儏", i + " 鏈�", "鍑哄嫟"));
for (Enums anEnum : enums) {
line.add(Arrays.asList("鍑哄嫟璇︽儏", i + " 鏈�", anEnum.getLabel()));
}
diff --git a/performance-server/src/main/java/com/yuanchu/mom/utils/StyleUtils.java b/performance-server/src/main/java/com/yuanchu/mom/utils/StyleUtils.java
new file mode 100644
index 0000000..dc8a300
--- /dev/null
+++ b/performance-server/src/main/java/com/yuanchu/mom/utils/StyleUtils.java
@@ -0,0 +1,83 @@
+package com.yuanchu.mom.utils;
+
+import com.alibaba.excel.write.metadata.style.WriteCellStyle;
+import com.alibaba.excel.write.metadata.style.WriteFont;
+import org.apache.poi.ss.usermodel.*;
+
+public class StyleUtils {
+ /**
+ * 鏍囬鏍峰紡
+ * @return
+ */
+ public static WriteCellStyle getHeadStyle(){
+ // 澶寸殑绛栫暐
+ WriteCellStyle headWriteCellStyle = new WriteCellStyle();
+ // 鑳屾櫙棰滆壊
+ headWriteCellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
+ headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
+
+ // 瀛椾綋
+ WriteFont headWriteFont = new WriteFont();
+ headWriteFont.setFontName("浠垮畫");//璁剧疆瀛椾綋鍚嶅瓧
+ headWriteFont.setFontHeightInPoints((short)9);//璁剧疆瀛椾綋澶у皬
+ headWriteFont.setBold(true);//瀛椾綋鍔犵矖
+ headWriteFont.setColor((short) 1);
+ headWriteCellStyle.setWriteFont(headWriteFont); //鍦ㄦ牱寮忕敤搴旂敤璁剧疆鐨勫瓧浣�;
+
+ // 鏍峰紡
+ headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//璁剧疆搴曡竟妗�;
+ headWriteCellStyle.setBottomBorderColor((short) 1);//璁剧疆搴曡竟妗嗛鑹�;
+ headWriteCellStyle.setBorderLeft(BorderStyle.THIN); //璁剧疆宸﹁竟妗�;
+ headWriteCellStyle.setLeftBorderColor((short) 1);//璁剧疆宸﹁竟妗嗛鑹�;
+ headWriteCellStyle.setBorderRight(BorderStyle.THIN);//璁剧疆鍙宠竟妗�;
+ headWriteCellStyle.setRightBorderColor((short) 1);//璁剧疆鍙宠竟妗嗛鑹�;
+ headWriteCellStyle.setBorderTop(BorderStyle.THIN);//璁剧疆椤惰竟妗�;
+ headWriteCellStyle.setTopBorderColor((short) 1); //璁剧疆椤惰竟妗嗛鑹�;
+
+ headWriteCellStyle.setWrapped(false); //璁剧疆鑷姩鎹㈣;
+
+ headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//璁剧疆姘村钩瀵归綈鐨勬牱寮忎负灞呬腑瀵归綈;
+ headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //璁剧疆鍨傜洿瀵归綈鐨勬牱寮忎负灞呬腑瀵归綈;
+ headWriteCellStyle.setShrinkToFit(true);//璁剧疆鏂囨湰鏀剁缉鑷冲悎閫�
+
+ return headWriteCellStyle;
+ }
+
+
+ /**
+ * 鍐呭鏍峰紡
+ * @return
+ */
+ public static WriteCellStyle getContentStyle(){
+ // 鍐呭鐨勭瓥鐣�
+ WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
+
+ // 杩欓噷闇�瑕佹寚瀹� FillPatternType 涓篎illPatternType.SOLID_FOREGROUND 涓嶇劧鏃犳硶鏄剧ず鑳屾櫙棰滆壊.澶撮粯璁や簡 FillPatternType鎵�浠ュ彲浠ヤ笉鎸囧畾
+ contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
+ contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
+
+ // 璁剧疆瀛椾綋
+ WriteFont contentWriteFont = new WriteFont();
+ contentWriteFont.setFontHeightInPoints((short) 9);//璁剧疆瀛椾綋澶у皬
+ contentWriteFont.setFontName("浠垮畫"); //璁剧疆瀛椾綋鍚嶅瓧
+ contentWriteCellStyle.setWriteFont(contentWriteFont);//鍦ㄦ牱寮忕敤搴旂敤璁剧疆鐨勫瓧浣�;
+
+ //璁剧疆鏍峰紡;
+// contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//璁剧疆搴曡竟妗�;
+// contentWriteCellStyle.setBottomBorderColor((short) 1);//璁剧疆搴曡竟妗嗛鑹�;
+ contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //璁剧疆宸﹁竟妗�;
+ contentWriteCellStyle.setLeftBorderColor((short) 1);//璁剧疆宸﹁竟妗嗛鑹�;
+ contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//璁剧疆鍙宠竟妗�;
+ contentWriteCellStyle.setRightBorderColor((short) 1);//璁剧疆鍙宠竟妗嗛鑹�;
+ contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//璁剧疆椤惰竟妗�;
+ contentWriteCellStyle.setTopBorderColor((short) 1); ///璁剧疆椤惰竟妗嗛鑹�;
+
+ contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 姘村钩灞呬腑
+ contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 鍨傜洿灞呬腑
+ contentWriteCellStyle.setWrapped(false); //璁剧疆鑷姩鎹㈣;
+
+// contentWriteCellStyle.setShrinkToFit(true);//璁剧疆鏂囨湰鏀剁缉鑷冲悎閫�
+
+ return contentWriteCellStyle;
+ }
+}
diff --git a/system-run/src/test/java/com/yuanchu/mom/SystemRunApplicationTest.java b/system-run/src/test/java/com/yuanchu/mom/SystemRunApplicationTest.java
index 1f22691..05a3381 100644
--- a/system-run/src/test/java/com/yuanchu/mom/SystemRunApplicationTest.java
+++ b/system-run/src/test/java/com/yuanchu/mom/SystemRunApplicationTest.java
@@ -1,33 +1,14 @@
package com.yuanchu.mom;
-import com.yuanchu.mom.service.PerformanceShiftService;
import org.junit.jupiter.api.Test;
-import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
-
-import java.util.Map;
@SpringBootTest
class SystemRunApplicationTest {
- private static String fileName = "D:\\" + System.currentTimeMillis() + ".xlsx";
-
- @Autowired
- private PerformanceShiftService performanceShiftService;
-
@Test
- void contextLoads() {
- Map<Object, Object> header = performanceShiftService.exportToExcel("2024-05-04 00:00:00", null, null);
+ void contextLoads() throws Exception {
-// EasyExcel.write(fileName)
-// .head(header)
-// // 鍚堝苟绛栫暐锛氬悎骞剁浉鍚屾暟鎹殑琛屻�傜涓�涓弬鏁拌〃绀轰粠鍝竴琛屽紑濮嬭繘琛屽悎骞讹紝鐢变簬琛ㄥご鍗犱簡涓よ锛屽洜姝や粠绗�2琛屽紑濮嬶紙绱㈠紩浠�0寮�濮嬶級
-// // 绗簩涓弬鏁版槸鎸囧畾鍝簺鍒楄杩涜鍚堝苟
-//// .registerWriteHandler(new MergeSameRowsStrategy(2, new int[]{0, 1, 2, 3, 8, 9}))
-// // 娉ㄦ剰锛氶渶瑕佸厛璋冪敤registerWriteHandler()鍐嶈皟鐢╯heet()鏂规硶鎵嶈兘浣垮悎骞剁瓥鐣ョ敓鏁堬紒锛侊紒
-// .sheet("妯℃澘")
-// .doWrite(data());
}
-
}
--
Gitblit v1.9.3