2024-5-10 绩效管理-班次-文件导出

Crunchy

2024-05-10 723705c43f6e87e9f31e4c18eee1c163c14139bb

2024-5-10 绩效管理-班次-文件导出

 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());
        // ä¿å­˜åˆ°ç¬¬ä¸€ä¸ªsheet中
        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"));
    }
}

 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;
}

 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()));
        }
        // æœˆheader
        for (int i = 1; i < 13; i++) {
            line.add(Arrays.asList("出勤详情", i + " æœˆ", "出勤"));
            for (Enums anEnum : enums) {
                line.add(Arrays.asList("出勤详情", i + " æœˆ", anEnum.getLabel()));
            }

 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 ä¸ºFillPatternType.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;
    }
}

 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()再调用sheet()方法才能使合并策略生效！！！
//                .sheet("模板")
//                .doWrite(data());
    }


}