参数

zouyu

2023-11-04 a915c14dafebeb823294935755b6646a2ca76ff9

参数

 mes-basic/src/main/java/com/chinaztt/mes/basic/controller/ParamController.java

@@ -28,6 +28,10 @@
import com.chinaztt.mes.basic.service.ParamService;
import com.chinaztt.mes.basic.util.DictUtils;
import com.chinaztt.mes.common.util.JsonUtil;
import com.chinaztt.mes.common.util.StringUtils;
import com.chinaztt.mes.common.util.easyexcel.EasyExcelUtils;
import com.chinaztt.mes.common.util.easyexcel.ExcelListener;
import com.chinaztt.mes.common.util.easyexcel.template.ParamTemplate;
import com.chinaztt.mes.common.wrapper.QueryWrapperUtil;
import com.chinaztt.ztt.admin.api.entity.SysDictItem;
import com.chinaztt.ztt.common.core.util.R;
@@ -39,6 +43,7 @@
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;
@@ -70,12 +75,14 @@
    @GetMapping("/page")
    @PreAuthorize("@pms.hasPermission('technology_param_view','product_workbench')")
    public R getParamPage(Page page, ParamDTO param) {
        List<Map<String,Object>> all = new ArrayList<>();
        List<SysDictItem> dict = dictUtils.getDict("technology_param");
        System.out.println(dict);
        Page page1 = paramService.page(page, QueryWrapperUtil.gen(param));
        QueryWrapper<Param> gen = QueryWrapperUtil.gen(param);
        gen.lambda().eq(Param::getParentId,0L);
        Page page1 = paramService.page(page, gen);
        List records = page1.getRecords();
        List<Map>all = new ArrayList<Map>();
        records.forEach(l->{
            System.out.println("一级数据--"+l);
            Map pojo = JsonUtil.jsonToPojo(JsonUtil.jsonToString(l), Map.class);
            Optional.ofNullable(pojo.get("type")).ifPresent(o->{
                pojo.put("type",dict.stream().filter(d-> Objects.equals(String.valueOf(d.getValue()),pojo.get("type"))).collect(Collectors.toList()).get(0).getLabel());
@@ -83,27 +90,54 @@
            QueryWrapper<Param>queryWrapper=new QueryWrapper<>();
            queryWrapper.lambda().eq(Param::getParentId,pojo.get("id"));
            List<Param> secondList = paramService.list(queryWrapper);
            List<Map>secondMapList = new ArrayList<>();
            //没有children直接添加
            if(secondList.isEmpty()){
                Map<String,Object> oneMap = new HashMap<>();
                oneMap.put("code",pojo.get("code"));
                oneMap.put("paramItem",pojo.get("parameterItem"));
                oneMap.put("paramItemTwo","");
                oneMap.put("paramItemThree","");
                oneMap.put("oneId",pojo.get("id"));
                oneMap.put("paramType",StringUtils.defaultString((String) pojo.get("type"),""));
                oneMap.put("paramFormat",StringUtils.defaultString((String) pojo.get("parameterFormat"),""));
                oneMap.put("unit",StringUtils.defaultString((String) pojo.get("unit"),""));
                all.add(oneMap);
            }
            secondList.forEach(s->{
                System.out.println("二级数据--"+s);
                Map second = JsonUtil.jsonToPojo(JsonUtil.jsonToString(s), Map.class);
                Optional.ofNullable(second.get("type")).ifPresent(o->{
                    second.put("type",dict.stream().filter(d-> Objects.equals(String.valueOf(d.getValue()),second.get("type"))).collect(Collectors.toList()).get(0).getLabel());
                });
                System.out.println(second);
                QueryWrapper<Param>queryWrapperSecond=new QueryWrapper<>();
                queryWrapperSecond.lambda().eq(Param::getParentId,second.get("id"));
                List<Param> third = paramService.getBaseMapper().selectList(queryWrapperSecond);
                if(third.isEmpty()){
                    Param p = new Param();
                    p.setType(s.getType());
                    p.setParameterFormat(s.getParameterFormat());
                    p.setUnit(s.getUnit());
                    third.add(p);
                }
                third.forEach(t->{
                    System.out.println("三级数据--"+t);
                    Optional.ofNullable(t.getType()).ifPresent(o->{
                        t.setType(dict.stream().filter(d-> Objects.equals(String.valueOf(d.getValue()),t.getType())).collect(Collectors.toList()).get(0).getLabel());
                    });
                    Map<String,Object> allMap = new HashMap<>();
                    allMap.put("code",pojo.get("code"));
                    allMap.put("paramItem",StringUtils.defaultString((String) pojo.get("parameterItem"),""));
                    allMap.put("oneId",pojo.get("id"));
                    allMap.put("twoId",second.get("id"));
                    allMap.put("threeId",t.getId());
                    allMap.put("paramItemTwo",StringUtils.defaultString((String) second.get("parameterItem"),""));
                    allMap.put("paramItemThree",StringUtils.defaultString(t.getParameterItem(),""));
                    allMap.put("paramType",StringUtils.defaultString(t.getType(),""));
                    allMap.put("paramFormat",StringUtils.defaultString(t.getParameterFormat(),""));
                    allMap.put("unit", StringUtils.defaultString(t.getUnit(),""));
                    all.add(allMap);
                });
                System.out.println(third);
                second.put("children",third);
                secondMapList.add(second);
            });
            pojo.put("children",secondMapList);
            all.add(pojo);
        });
        page1.setRecords(all);
        return R.ok(page1);
@@ -186,9 +220,11 @@
     * @return
     */
    @PostMapping("/upload")
    @ApiOperation(value = "导入", notes = "导入")
    public R upload(@RequestParam("file") MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(), ParamData.class, new ParamUploadListener(paramService)).sheet().doRead();
//            EasyExcel.read(file.getInputStream(), ParamTemplate.class, new ExcelListener<ParamTemplate>()).sheet().doRead();
            EasyExcelUtils.readModelExcel(ParamTemplate.class,file);
        } catch (IOException e) {
            e.printStackTrace();
        }

 mes-basic/src/main/java/com/chinaztt/mes/basic/entity/Param.java

@@ -108,7 +108,4 @@
    @TableField("parent_id")
    private Long parentId;

    public Param() {
        this.parentId=0L;
    }
}

 mes-basic/src/main/java/com/chinaztt/mes/basic/enums/FileEnums.java

@@ -13,7 +13,8 @@
    crew("crew","班组"),

    workstation("workstation","工作站"),
    structure("structure","产品结构")
    structure("structure","产品结构"),
    param("param","参数")
    ;

    FileEnums(String name, String fileName) {

 mes-basic/src/main/java/com/chinaztt/mes/basic/service/ParamService.java

@@ -26,6 +26,7 @@
import com.chinaztt.mes.basic.dto.ParamPlusDto;
import com.chinaztt.ztt.common.core.util.R;

import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
@@ -87,4 +88,5 @@
     * @return
     */
    String checkItemRepeat(Param param);

}

 mes-basic/src/main/java/com/chinaztt/mes/basic/service/impl/ParamServiceImpl.java

@@ -17,6 +17,9 @@
package com.chinaztt.mes.basic.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
@@ -30,6 +33,9 @@
import com.chinaztt.mes.basic.mapper.ParamMapper;
import com.chinaztt.mes.basic.service.ParamService;
import com.chinaztt.mes.basic.util.DictUtils;
import com.chinaztt.mes.common.util.easyexcel.CustemHandler;
import com.chinaztt.mes.common.util.easyexcel.EasyExcelUtils;
import com.chinaztt.mes.common.util.easyexcel.template.ParamTemplate;
import com.chinaztt.ztt.admin.api.entity.SysDictItem;
import com.chinaztt.ztt.common.core.util.R;
import lombok.AllArgsConstructor;
@@ -37,6 +43,8 @@
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.stream.Collectors;


 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/CustemHandler.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,80 @@
package com.chinaztt.mes.common.util.easyexcel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.CollectionUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 12:56
**/
public class CustemHandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    private static final int COLUMN_WIDTH = 20;
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public CustemHandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                } else {
                    if (columnWidth < COLUMN_WIDTH) {
                        columnWidth = columnWidth * 2;
                    }
                }

                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/EasyExcelUtils.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,441 @@
package com.chinaztt.mes.common.util.easyexcel;


import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.metadata.*;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.chinaztt.mes.common.util.easyexcel.exception.ExcelException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 13:18
**/
public class EasyExcelUtils {

    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // èƒŒæ™¯è®¾ç½®ä¸ºç°è‰²
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(false);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // å†…容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // è¿™é‡Œéœ€è¦æŒ‡å®š FillPatternType ä¸ºFillPatternType.SOLID_FOREGROUND ä¸ç„¶æ— æ³•æ˜¾ç¤ºèƒŒæ™¯é¢œè‰².头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // èƒŒæ™¯ç™½è‰²
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 11);
        // å­—体样式
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // å†…容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框设置
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // è¿™ä¸ªç­–略是 å¤´æ˜¯å¤´çš„样式 å†…容是内容的样式 å…¶ä»–的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getStyleFixedStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // èƒŒæ™¯è®¾ç½®ä¸ºç°è‰²
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("Calibri");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(false);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // å†…容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // è¿™é‡Œéœ€è¦æŒ‡å®š FillPatternType ä¸ºFillPatternType.SOLID_FOREGROUND ä¸ç„¶æ— æ³•æ˜¾ç¤ºèƒŒæ™¯é¢œè‰².头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // èƒŒæ™¯ç™½è‰²
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // å­—体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // å†…容左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // è¿™ä¸ªç­–略是 å¤´æ˜¯å¤´çš„样式 å†…容是内容的样式 å…¶ä»–的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    public static HorizontalCellStyleStrategy getArveStyleStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // èƒŒæ™¯è®¾ç½®ä¸º
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("Calibri");
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);

        headWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM);
        headWriteCellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(true);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // å†…容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // è¿™é‡Œéœ€è¦æŒ‡å®š FillPatternType ä¸ºFillPatternType.SOLID_FOREGROUND ä¸ç„¶æ— æ³•æ˜¾ç¤ºèƒŒæ™¯é¢œè‰².头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // èƒŒæ™¯ç™½è‰²
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // å­—体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // è¿™ä¸ªç­–略是 å¤´æ˜¯å¤´çš„样式 å†…容是内容的样式 å…¶ä»–的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * è®¾ç½®response
     * @param response
     * @param targetName
     * @return
     */
    public static HttpServletResponse getResponse(HttpServletResponse response, String targetName){
        String fileName;
        try {
            fileName = String.valueOf(new StringBuilder()
                    .append(targetName)
                    .append("_")
                    .append(DateUtil.today())
                    .append(ExcelTypeEnum.XLS.getValue()));
            fileName =  URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            return response;
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * excel文件读取
     *
     * @param clazz model类型Class对象
     * @param excel excel文件对象
     * @param <T>   model类型泛型
     * @return model类型数据集合
     * @throws IOException IO异常
     */
    public static <T extends BaseRowModel> List<T> readModelExcel(Class<T> clazz, MultipartFile excel) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader reader = getReader(excel, excelListener);
        try {
            reader.getSheets().forEach(sheet -> {
                sheet.setClazz(clazz);
                reader.read(sheet);
            });
        } catch (Exception e) {
            e.printStackTrace();
            throw new IOException("请检查导入模板！");
        }
        return excelListener.getDataList();
    }

    /**
     *
     * @param clazz model类型Class对象
     * @param excel excel文件对象
     * @param sheetNumber æŒ‡å®šsheet页
     * @param <T> model类型泛型
     * @return model类型数据集合
     * @throws IOException
     */
    public static <T extends BaseRowModel> List<T> readModelSheetExcel(Class<T> clazz, MultipartFile excel, int sheetNumber) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader reader = getReader(excel, excelListener);
        try {
            Sheet sheet = reader.getSheets().get(sheetNumber);
            sheet.setClazz(clazz);
            reader.read(sheet);
        } catch (Exception e) {
            throw new IOException("请检查导入模板！");
        }
        return excelListener.getDataList();
    }

    /**
     * èŽ·å–excel文件读取对象
     *
     * @param excel         excel文件对象
     * @param excelListener excel事件处理
     * @return excel è¯»å–对象
     * @throws IOException IO异常
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx") && !filename.toLowerCase().endsWith(".xltm") && !filename.toLowerCase().endsWith(".xlsm"))) {
            throw new ExcelException(ResponseResultEnum.EXCEL_FILE_EXT_ERROR);
        }
        InputStream inputStream = new BufferedInputStream(excel.getInputStream());
        return new ExcelReader(inputStream, null, excelListener, false);
    }

    /**
     * å†™å…¥æ•°æ®åˆ°å¯¼å‡ºæ–‡ä»¶
     *
     * @param clazz       model类型Class对象
     * @param sheetNo     sheet序号
     * @param headLineMun head行数
     * @param startRow    å¼€å§‹å†™å…¥è¡Œæ•°
     * @param tableNo     table序号
     * @param modelList   model数据集合
     * @param temp        ä¸´æ—¶æ–‡ä»¶å¯¹è±¡
     * @param export      å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param <T>         model泛型
     */
    public static <T extends BaseRowModel> void writeModelExport(Class<T> clazz, Integer sheetNo, Integer headLineMun, Integer startRow, Integer tableNo, List<T> modelList, File temp, File export) {
        try {
            InputStream inputStream = new FileInputStream(temp);
            FileOutputStream OutputStream = new FileOutputStream(export);
            ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream, OutputStream, ExcelTypeEnum.XLSX, false);
            writer.write(modelList, initSheet(sheetNo, headLineMun, startRow), initTable(tableNo, clazz));
            writer.finish();
            OutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Sheet初始化
     *
     * @param sheetNo     sheet序号
     * @param headLineMun head行数
     * @param startRow    å¼€å§‹å†™å…¥è¡Œæ•°
     * @return Sheet对象
     */
    public static Sheet initSheet(Integer sheetNo, Integer headLineMun, Integer startRow) {
        Sheet sheet = new Sheet(sheetNo, headLineMun);
        sheet.setStartRow(startRow);
        return sheet;
    }

    /**
     * Table初始化
     *
     * @param tableNo table序号
     * @param clazz   model类型Class对象
     * @param <T>     model泛型
     * @return Table对象
     */
    public static <T extends BaseRowModel> Table initTable(Integer tableNo, Class<T> clazz) {
        Table table = new Table(tableNo);
        table.setClazz(clazz);
        TableStyle tableStyle = new TableStyle();
        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setBold(false);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        tableStyle.setTableContentFont(font);
        table.setTableStyle(tableStyle);
        return table;
    }

    /**
     * æ›¿æ¢æ¨¡æ¿æ ‡è®°
     *
     * @param export     å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param index      sheet序号
     * @param rows       å•å…ƒæ ¼è¡Œåº
     * @param columns    å•å…ƒæ ¼åˆ—序
     * @param replaceMap å¤šä¸ªæ ‡è®°é›†åˆ
     */
    public static void editModelWorkBook(File export, Integer index, Integer rows, Integer columns, Map<String, String> replaceMap) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(export);
//            editModelWorkBook(workBook,index,0,0,replaceMap);
            editModelWorkBook(workBook, index, rows, columns, replaceMap);
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * æ›¿æ¢æ¨¡æ¿æ ‡è®°
     *
     * @param workBook   excel工作册对象
     * @param index      sheet序号
     * @param rows       å•å…ƒæ ¼è¡Œåº
     * @param columns    å•å…ƒæ ¼åˆ—序
     * @param replaceMap å¤šä¸ªæ ‡è®°é›†åˆ
     */
    public static void editModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns, Map<String, String> replaceMap) {
        XSSFSheet sheet = workBook.getSheetAt(index);
        Row row = sheet.getRow(rows);
        Cell cell = row.getCell(columns);
        String replaceString = cell.toString();
        for (Map.Entry<String, String> entry : replaceMap.entrySet()) {
            replaceString = replaceString.replace(entry.getKey(), entry.getValue());
        }
        cell.setCellValue(replaceString);
        workBook.setSheetName(index, replaceString.replace(":", ""));
    }

    /**
     * æ¨¡æ¿sheet克隆及命名
     *
     * @param temp           ä¸´æ—¶æ–‡ä»¶å¯¹è±¡
     * @param export         å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param cloneSheetNo   å…‹éš†sheet序列
     * @param rows           å•å…ƒæ ¼è¡Œåº
     * @param columns        å•å…ƒæ ¼åˆ—序
     * @param replaceMapList å¤šä¸ªæ ‡è®°é›†åˆ
     */
    public static void writeModelCloneSheet(File temp, File export, Integer cloneSheetNo, Integer rows, Integer columns, List<Map<String, String>> replaceMapList) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(temp);
            OutputStream outputStream = new FileOutputStream(export);
            /** å¦‚果你需要6份相同模板的sheet é‚£ä¹ˆä½ åªéœ€è¦å…‹éš†5份即可*/
            for (int index = 0; index < replaceMapList.size() - 1; index++) {
                /** å…‹éš†æ¨¡æ¿æ–‡ä»¶ */
                XSSFSheet sheet = workBook.cloneSheet(cloneSheetNo);
            }
            for (int index = 0; index < replaceMapList.size(); index++) {
                editModelWorkBook(workBook, index, rows, columns, replaceMapList.get(index));
            }
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * ä¸´æ—¶æ–‡ä»¶å†™å…¥å¯¼å‡ºæ–‡ä»¶
     *
     * @param temp    ä¸´æ—¶æ–‡ä»¶å¯¹è±¡
     * @param export  å¯¼å‡ºæ–‡ä»¶å¯¹è±¡
     * @param index   sheet序号
     * @param rows    å•å…ƒæ ¼è¡Œåº
     * @param columns å•å…ƒæ ¼åˆ—序
     * @param isTime  æ˜¯å¦æœ‰æ—¶é—´æ ‡è®°
     */
    public static void writeModelWorkBook(File temp, File export, Integer index, Integer rows, Integer columns, Boolean isTime) {
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(temp);
            OutputStream outputStream = new FileOutputStream(export);
            if (isTime) {
                writeModelWorkBook(workBook, index, rows, columns);
            }
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workBook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    /**
     * æ›¿æ¢æ—¶é—´æ ‡è®°ï¼ˆyear,month,day）
     *
     * @param workBook excel工作册对象
     * @param index    sheet序号
     * @param rows     å•å…ƒæ ¼è¡Œåº
     * @param columns  å•å…ƒæ ¼åˆ—序
     */
    public static void writeModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns) {
        XSSFSheet xssfSheet = workBook.getSheetAt(index);
        Row row = xssfSheet.getRow(rows);
        Cell cell = row.getCell(columns);
        String cellString = cell.toString();
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        int month = calendar.get(Calendar.MONTH) + 1;
        int day = calendar.get(Calendar.DAY_OF_MONTH);
        String yearString = cellString.replace("year", String.valueOf(year));
        String monthString = yearString.replace("month", String.valueOf(month));
        String dayString = monthString.replace("day", String.valueOf(day));
        cell.setCellValue(dayString);
    }

    /**
     * é’ˆå¯¹UserModel导出方法
     *
     * @param temp
     * @param export
     * @param isTime
     */
    public static void writeModelWorkBook(File temp, File export, Boolean isTime) {
        writeModelWorkBook(temp, export, 0, 1, 9, isTime);
    }


    public static void writeModelCloneSheet(File temp, File export, List<Map<String, String>> replaceMapList) {
        writeModelCloneSheet(temp, export, 0, 0, 0, replaceMapList);
    }

}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/ExcelListener.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,58 @@
package com.chinaztt.mes.common.util.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * excel数据导入数据处理
 *
 * @param <T>
 * @author Zou, Yu
 */
@Slf4j
@Getter
@Setter
public class ExcelListener<T> extends AnalysisEventListener<T> {

    private List<T> dataList = new ArrayList<>();

    @Override
    public void invoke(T classType, AnalysisContext analysisContext) {
        System.out.println("导入的数据--"+classType);
        dataList.add(classType);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        String templateHead = "";

//        List<String> headList = new ArrayList<>();
//        for (Integer i : headMap.keySet()) {
//            headList.add(headMap.get(i));
//        }
//        String importHead = headMap.values().toString();
//        log.info("importHead:{}", importHead);
//        String templateName = context.getCurrentSheet().getClazz().getSimpleName();
//        log.info("templateName:{}", templateName);
//        if("ExcelMBomTemplate".equals(templateName)){
////            templateHead = mBomHead;
//        }
//        if (!templateHead.equals(importHead)) {
//            throw new RuntimeException(ResponseResultEnum.EXCEL_MODEL_ERROR.getMessage());
//        }

    }


}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/ExcelMergeStrategy.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,85 @@
package com.chinaztt.mes.common.util.easyexcel;

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 org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * åˆå¹¶å•å…ƒæ ¼
 * @Author: Zou, Yu
 * @DATE: 2023/9/26 0026 10:16
 */
public class ExcelMergeStrategy extends AbstractMergeStrategy {

    /**
     * åˆ†ç»„，每几行合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * ç›®æ ‡åˆå¹¶åˆ—index
     */
    private Integer targetColumnIndex;

    // éœ€è¦å¼€å§‹åˆå¹¶å•å…ƒæ ¼çš„首行index
    private Integer rowIndex;

    // exportDataList为待合并目标列的值
    public ExcelMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) {
        this.exportFieldGroupCountList = getGroupCountList(exportDataList);
        this.targetColumnIndex = targetColumnIndex;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        if (null == rowIndex) {
            rowIndex = cell.getRowIndex();
        }
        // ä»…从首行以及目标列的单元格开始合并，忽略其他
        if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {
            mergeGroupColumn(sheet);
        }
    }

    private void mergeGroupColumn(Sheet sheet) {
        int rowCount = rowIndex;
        for (Integer count : exportFieldGroupCountList) {
            if(count == 1) {
                rowCount += count;
                continue ;
            }
            // åˆå¹¶å•å…ƒæ ¼
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);
            sheet.addMergedRegionUnsafe(cellRangeAddress);
            rowCount += count;
        }
    }

    // è¯¥æ–¹æ³•å°†ç›®æ ‡åˆ—根据值是否相同连续可合并，存储可合并的行数
    private List<Integer> getGroupCountList(List<String> exportDataList){
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }

        List<Integer> groupCountList = new ArrayList<>();
        int count = 1;

        for (int i = 1; i < exportDataList.size(); i++) {
            if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // å¤„理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }
}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/ResponseResultEnum.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,28 @@
package com.chinaztt.mes.common.util.easyexcel;

import lombok.Getter;

/**
 * å¼‚常信息枚举
 * @auther Zou, Yu
 * @create 2023-09-24
 */
@Getter
public enum ResponseResultEnum {
    EXCEL_FILE_EXT_ERROR(1000,"文件格式错误"),
    EXCEL_FILE_READ_FAIL(1001,"excel文件读取失败,请检查模板!"),
    EXCEL_FILE_IS_EMPTY(1002,"excel文件为空"),
    MODEL_FILE_NOT_EXIT(1003,"模版文件不存在"),
    EXCEL_ACTIVE_DATE_ERROR(1004,"生效时间错误"),
    EXCEL_MODEL_ERROR(1005,"导入模板有误，请检查！"),
    EXCEL_FILE_EXCESS(1006,"批量导入数据量不可超过500条");

    private Integer status;
    private String message;

    ResponseResultEnum(Integer status, String message) {
        this.status = status;
        this.message = message;
    }

}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/converter/LocalDateConverter.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,43 @@
package com.chinaztt.mes.common.util.easyexcel.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.springframework.stereotype.Component;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
*@author: Zou, Yu
*@description:
*@date: 2023/9/24 0024 13:18
**/
@Component
public class LocalDateConverter implements Converter<LocalDate> {

    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) {
        return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }

    @Override
    public CellData<String> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }

}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/converter/SaleTypeConverter.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,48 @@
package com.chinaztt.mes.common.util.easyexcel.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.springframework.stereotype.Component;

/** è®¢å•çŠ¶æ€è½¬æ¢
 * @Author: Zou, Yu
 * @DATE: 2023/9/24 0024 14:21
 */
@Component
public class SaleTypeConverter implements Converter<String> {

    @Override
    public Class<String> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if("通过".equals(cellData.getStringValue())){
            return "1";
        }else if("不通过".equals(cellData.getStringValue())){
            return "0";
        }else{
            return "2";
        }
    }

    @Override
    public CellData convertToExcelData(String str, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if("1".equals(str)){
            return new CellData<>("通过");
        }else if("0".equals(str)){
            return new CellData<>("不通过");
        }else{
            return new CellData<>("未审核");
        }
    }
}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/exception/ExcelException.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,25 @@
package com.chinaztt.mes.common.util.easyexcel.exception;

import com.chinaztt.mes.common.util.easyexcel.ResponseResultEnum;
import lombok.Getter;

/**
 * excel全局异常
 * @auther Zou, Yu
 * @create 2023-9-24
 */
@Getter
public class ExcelException extends RuntimeException {

    private Integer status;

    public ExcelException(ResponseResultEnum resultEnum) {
        super(resultEnum.getMessage());
        this.status = resultEnum.getStatus();
    }

    public ExcelException(Integer status, String message) {
        super(message);
        this.status = status;
    }
}

 mes-common/src/main/java/com/chinaztt/mes/common/util/easyexcel/template/ParamTemplate.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,55 @@
package com.chinaztt.mes.common.util.easyexcel.template;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.util.Arrays;

@Data
@HeadRowHeight(40)
@ContentRowHeight(25)
@EqualsAndHashCode(callSuper = true)
public class ParamTemplate extends BaseRowModel implements Serializable {

    @ColumnWidth(30)
    @ExcelProperty(value="参数项", index = 0)
    @JsonSerialize
    private String paramItem;

    @ColumnWidth(30)
    @ExcelProperty(value="二级参数(可为空)", index = 1)
    @JsonSerialize
    private String paramItemTwo;

    @ColumnWidth(30)
    @ExcelProperty(value="三级参数(可为空)", index = 2)
    @JsonSerialize
    private String paramItemThree;

    @ColumnWidth(30)
    @ExcelProperty(value="参数类型(数值/文本/下拉)", index = 3)
    @JsonSerialize
    private String paramType;

    @ColumnWidth(30)
    @ExcelProperty(value="参数格式(可为空)", index = 4)
    @JsonSerialize
    private String paramFormat;

    @ColumnWidth(30)
    @ExcelProperty(value="单位(可为空)", index = 5)
    @JsonSerialize
    private String unit;

    public static String getTemplateHead(){
        return Arrays.asList("参数项","二级参数(可为空)","三级参数(可为空)","参数类型(数值/文本/下拉)","参数格式(可为空)","单位(可为空)").toString();
    }

}