基础数据-物料清单维护-导出

zouyu

2023-09-27 75b2904fbaf4fab6a9fb3baf065d2c927cc7a7a2

基础数据-物料清单维护-导出

 base-server/pom.xml

@@ -27,6 +27,18 @@
            <artifactId>user-server</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </dependency>

    </dependencies>


 base-server/src/main/java/com/yuanchu/mom/controller/MbomModelController.java

@@ -2,7 +2,7 @@


import com.yuanchu.mom.pojo.dto.MbomModelDto;
import com.yuanchu.mom.pojo.dto.TechniqueModelDto;
import com.yuanchu.mom.utils.JackSonUtil;
import com.yuanchu.mom.vo.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
@@ -12,6 +12,11 @@
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import com.yuanchu.mom.service.MbomModelService;

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


/**
@@ -70,5 +75,17 @@
        return Result.success("批量删除成功!");
    }

    @ApiOperation(value = "下载模板")
    @PostMapping("/downloadTemplate")
    public void downloadMBomTemplate(@RequestBody Map<String,Object> map,HttpServletResponse response){
        mbomModelService.downloadTemplate(map,response);
    }

    @ApiOperation(value = "导出物料清单")
    @PostMapping("/exportMBom")
    public void exportMBom(@RequestBody Map<String,Object> map, HttpServletResponse response){
        mbomModelService.exportMBom(map,response);
    }

}


 base-server/src/main/java/com/yuanchu/mom/service/MbomModelService.java

@@ -4,6 +4,7 @@
import com.yuanchu.mom.pojo.MbomModel;
import com.yuanchu.mom.pojo.dto.MbomModelDto;

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

@@ -40,5 +41,13 @@
     * @param ids
     */
    void delAllMbom(String ids);

    void downloadTemplate(Map<String, Object> map,HttpServletResponse response);

    /**
     * å¯¼å‡º
     * @param map
     */
    void exportMBom(Map<String, Object> map,HttpServletResponse response);
}


 base-server/src/main/java/com/yuanchu/mom/service/impl/MbomModelServiceImpl.java

@@ -1,17 +1,32 @@
package com.yuanchu.mom.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuanchu.mom.mapper.MbomModelMapper;
import com.yuanchu.mom.pojo.MbomModel;
import com.yuanchu.mom.pojo.dto.MbomModelDto;
import com.yuanchu.mom.pojo.dto.MbomModelDto2;
import com.yuanchu.mom.service.MbomModelService;
import com.yuanchu.mom.service.TechnicalModelService;
import com.yuanchu.mom.utils.StringUtils;
import com.yuanchu.mom.utils.easyexcel.CustemHandler;
import com.yuanchu.mom.utils.easyexcel.EasyExcelUtils;
import com.yuanchu.mom.utils.easyexcel.ExcelMergeStrategy;
import com.yuanchu.mom.utils.easyexcel.converter.LocalDateConverter;
import com.yuanchu.mom.utils.easyexcel.template.ExcelMBomTemplate;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.map.HashedMap;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

/**
@@ -25,6 +40,9 @@

    @Resource
    MbomModelMapper mbomModelMapper;

    @Autowired
    private TechnicalModelService technicalModelService;


    //查询物料清单维护列表-->右边展示该工艺所需要的原材料
@@ -61,5 +79,124 @@
    public void delAllMbom(String ids) {
        mbomModelMapper.delAllMbom(ids);
    }

    @Override
    public void downloadTemplate(Map<String, Object> map,HttpServletResponse response) {
        try{
            List<String> typeList = getTypeList(map);
            ExcelWriter excelWriter = EasyExcel.write(EasyExcelUtils.getResponse(response,"物料清单导入模板").getOutputStream())
                    .excelType(ExcelTypeEnum.XLS)
                    .build();
            // å¯¼å‡ºå¤šä¸ªsheet页
            for (int i = 0; i < typeList.size(); i++) {
                WriteSheet sheet = EasyExcel.writerSheet(i, typeList.get(i))
                        .head(ExcelMBomTemplate.class)
                        .registerConverter(new LocalDateConverter())
                        .registerWriteHandler(new CustemHandler())
                        .registerWriteHandler(EasyExcelUtils.getStyleStrategy())
                        .build();
                excelWriter.write(null, sheet);
            }
            // å…³é—­ ExcelWriter，完成导出
            excelWriter.finish();
        }catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void exportMBom(Map<String, Object> map,HttpServletResponse response) {
        try{
            //类型列表
            List<String> typeList = getTypeList(map);
            //sheet数据
            Map<String,List<ExcelMBomTemplate>> exportListByType = new HashedMap<>();
            typeList.forEach((type)->{
                List<Map<String,Object>> techTemList =
                        technicalModelService.selectAllTechTem(type, "");
                List<ExcelMBomTemplate> list = new ArrayList<>();
                techTemList.forEach(tech -> {
                    String father = "";
                    if(StringUtils.isNotEmpty(tech.get("name").toString())){
                        father = tech.get("name").toString();
                    }
                    if(StringUtils.isNotEmpty(tech.get("children").toString())){
                        List<Map<String,Object>> childrenList = (List<Map<String, Object>>) tech.get("children");
                        String finalFather = father;
                        childrenList.forEach(c -> {
                            String name = c.get("name").toString();
                            Integer id = Integer.parseInt(c.get("id").toString());
                            List<Map<String, Object>> mbom = mbomModelMapper.selectAllMbom(id);
                            if(CollectionUtils.isNotEmpty(mbom)){
                                mbom.forEach(m->{
                                    ExcelMBomTemplate temp = new ExcelMBomTemplate();
                                    temp.setFather(finalFather);
                                    temp.setName(name);
                                    temp.setMaterialName(m.get("name").toString());
                                    temp.setUnit(m.get("unit").toString());
                                    list.add(temp);
                                });
                            }
                        });
                    }
                });
                exportListByType.put(type,list);
            });
            ExcelWriter excelWriter = EasyExcel.write(
                        EasyExcelUtils.getResponse(response, "物料清单导出").getOutputStream()
                    )
                    .excelType(ExcelTypeEnum.XLS)
                    .build();
            // å¯¼å‡ºå¤šä¸ªsheet页
            for (int i = 0; i < typeList.size(); i++) {
                String type = typeList.get(i);
                List<String> fathers = exportListByType.get(type).stream()
                        .map(ExcelMBomTemplate::getFather).collect(Collectors.toList());
                List<String> names = exportListByType.get(type).stream()
                        .map(ExcelMBomTemplate::getName).collect(Collectors.toList());
                WriteSheet sheet = EasyExcel.writerSheet(i, typeList.get(i))
                        .head(ExcelMBomTemplate.class)
                        .registerConverter(new LocalDateConverter())
                        .registerWriteHandler(new CustemHandler())
                        .registerWriteHandler(EasyExcelUtils.getStyleStrategy())
                        .registerWriteHandler(new ExcelMergeStrategy(fathers,0))
                        .registerWriteHandler(new ExcelMergeStrategy(names,1))
                        .build();
                excelWriter.write(exportListByType.get(type), sheet);
            }
            // å…³é—­ ExcelWriter，完成导出
            excelWriter.finish();
        }catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * èŽ·å–类型列表
     * @param map
     * @return
     */
    private List<String> getTypeList(Map<String, Object> map){
        if(!map.isEmpty()){
            String arrStr = String.valueOf(map.get("typeList"));
            String[] strings = arrStr.split(",");
            return Arrays.asList(strings);
        }
        return null;
    }

}


 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/CustemHandler.java

@@ -20,7 +20,6 @@
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);


 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/EasyExcelUtils.java

@@ -1,6 +1,7 @@
package com.yuanchu.mom.utils.easyexcel;


import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
@@ -15,13 +16,17 @@
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.yuanchu.mom.exception.ExcelException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.functions.T;
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;
@@ -33,7 +38,6 @@
**/
public class EasyExcelUtils {


    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // å¤´çš„ç­–ç•¥
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
@@ -42,7 +46,7 @@
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // å­—体样式
        headWriteFont.setFontName("Frozen");
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(false);
@@ -59,12 +63,19 @@
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // å­—体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteFont.setFontHeightInPoints((short) 11);
        // å­—体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // å†…容左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // å†…容居中对齐
        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);
    }
@@ -82,7 +93,7 @@
        // è‡ªåŠ¨æ¢è¡Œ
        headWriteCellStyle.setWrapped(false);
        // æ°´å¹³å¯¹é½æ–¹å¼
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // åž‚直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // å†…容的策略
@@ -141,6 +152,31 @@
    }

    /**
     * è®¾ç½®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对象

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/ExcelMergeStrategy.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,87 @@
package com.yuanchu.mom.utils.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;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * åˆå¹¶å•å…ƒæ ¼
 * @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;
    }
}

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/converter/LocalDateConverter.java

ÎļþÃû´Ó framework/src/main/java/com/yuanchu/mom/utils/easyexcel/LocalDateConverter.java ÐÞ¸Ä
@@ -1,4 +1,4 @@
package com.yuanchu.mom.utils.easyexcel;
package com.yuanchu.mom.utils.easyexcel.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;

 framework/src/main/java/com/yuanchu/mom/utils/easyexcel/template/ExcelMBomTemplate.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,45 @@
package com.yuanchu.mom.utils.easyexcel.template;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
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;

/**
 * @Author: Zou, Yu
 * @DATE: 2023/9/25 0025 12:47
 */
@Data
@HeadRowHeight(40)
@ContentRowHeight(25)
@EqualsAndHashCode(callSuper = true)
public class ExcelMBomTemplate extends BaseRowModel implements Serializable {

    @ColumnWidth(30)
    @ExcelProperty(value="工序", index = 0)
    @JsonSerialize
    private String father;

    @ColumnWidth(30)
    @ExcelProperty(value="工艺名称", index = 1)
    @JsonSerialize
    private String name;

    @ColumnWidth(30)
    @ExcelProperty(value="原材料名称", index = 2)
    @JsonSerialize
    private String materialName;

    @ColumnWidth(30)
    @ExcelProperty(value="单位", index = 3)
    @JsonSerialize
    private String unit;

}

 inventory-server/src/main/java/com/yuanchu/mom/controller/SaleController.java

@@ -1,22 +1,15 @@
package com.yuanchu.mom.controller;


import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanchu.mom.pojo.dto.ExportSaleDto;
import com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate;
import com.yuanchu.mom.pojo.dto.SaleDto;
import com.yuanchu.mom.pojo.dto.SaleMaterialDto;
import com.yuanchu.mom.pojo.vo.SaleVo;
import com.yuanchu.mom.service.SaleService;
import com.yuanchu.mom.utils.JackSonUtil;
import com.yuanchu.mom.utils.Jwt;
import com.yuanchu.mom.utils.easyexcel.CustemHandler;
import com.yuanchu.mom.utils.easyexcel.EasyExcelUtils;
import com.yuanchu.mom.utils.easyexcel.LocalDateConverter;
import com.yuanchu.mom.vo.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
@@ -28,9 +21,6 @@

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**

 inventory-server/src/main/java/com/yuanchu/mom/service/impl/SaleServiceImpl.java

@@ -7,21 +7,20 @@
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuanchu.mom.Task.SyncOrder;
import com.yuanchu.mom.mapper.SaleMapper;
import com.yuanchu.mom.mapper.SaleMaterialMapper;
import com.yuanchu.mom.pojo.Sale;
import com.yuanchu.mom.mapper.SaleMapper;
import com.yuanchu.mom.pojo.SaleMaterial;
import com.yuanchu.mom.pojo.dto.*;
import com.yuanchu.mom.pojo.vo.SaleVo;
import com.yuanchu.mom.service.SaleMaterialService;
import com.yuanchu.mom.service.SaleService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yuanchu.mom.utils.easyexcel.CustemHandler;
import com.yuanchu.mom.utils.easyexcel.EasyExcelUtils;
import com.yuanchu.mom.utils.easyexcel.LocalDateConverter;
import com.yuanchu.mom.utils.easyexcel.converter.LocalDateConverter;
import com.yuanchu.mom.utils.easyexcel.template.ExportSaleTemplate;
import com.yuanchu.mom.vo.Result;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -272,16 +271,7 @@
            for (int i = 0; i < exportSaleTemplates.size(); i++) {
                exportSaleTemplates.get(i).setId(i+1);
            }
            String fileName = String.valueOf(new StringBuilder()
                    .append("销售订单_")
                    .append(DateUtil.today())
                    .append(ExcelTypeEnum.XLS.getValue()));
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setContentType("application/octet-stream");
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            EasyExcel.write(response.getOutputStream())
            EasyExcel.write(EasyExcelUtils.getResponse(response,"销售订单").getOutputStream())
                    .head(ExportSaleTemplate.class)
                    .registerConverter(new LocalDateConverter())
                    .autoCloseStream(true)

 pom.xml

@@ -124,6 +124,11 @@
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>${poi.version}</version>
            </dependency>
            <!--log4j-->
            <dependency>
                <groupId>log4j</groupId>