#新增 1. 添加EXcel导入功能模板

skyouc

2025-03-03 c466f4d8c054270821ed0263f390178995621a74

#新增
1. 添加EXcel导入功能模板

 rsf-admin/src/page/basicInfo/matnr/MatnrList.jsx

@@ -43,7 +43,7 @@
import MyField from "@/page/components/MyField";
import { PAGE_DRAWER_WIDTH, OPERATE_MODE, DEFAULT_PAGE_SIZE } from '@/config/setting';
import * as Common from '@/utils/common';
import ImportButton from "@/page/components/ImportButton";
import ImportButton from "../../components/ImportButton";
import MatListAside from './MatnrListAside';

const StyledDatagrid = styled(DatagridConfigurable)(({ theme }) => ({
@@ -228,7 +228,9 @@
                        <FilterButton />
                        <MyCreateButton onClick={() => { setCreateDialog(true) }} />
                        <SelectColumnsButton preferenceKey='matnr' />
                        <ImportButton />
                        <MatnrList.Context.Provider value={'matnr'}>
                            <ImportButton />
                        </MatnrList.Context.Provider>
                        <MyExportButton />
                    </TopToolbar>
                )}
@@ -251,4 +253,6 @@
    )
}

MatnrList.Context = React.createContext()

export default MatnrList;

 rsf-admin/src/page/components/ImportModal.jsx

@@ -18,6 +18,7 @@
import { Link } from 'react-router-dom';
import DialogCloseButton from './DialogCloseButton';
import { usePapaParse } from './usePapaParse';
import MatnrList from '../basicInfo/matnr/MatnrList';

const ImportModal = ({ open, onClose, importTemp, useCodeImport, onceBatch = 10 }) => {
    const refresh = useRefresh();
@@ -144,22 +145,30 @@
                                <Alert
                                    severity="info"
                                    action={
                                        <Button
                                            component={Link}
                                            label="common.action.import.download"
                                            color="info"
                                            to={importTemp}
                                            download={'import_template.csv'}
                                        />
                                        <MatnrList.Context.Consumer>
                                            {context => (
                                                <Button
                                                    component={Link}
                                                    onClick={() => {
                                                        downloadTemplate(context)
                                                    }}
                                                    label="common.action.import.download"
                                                    color="info"
                                                    to={importTemp}
                                                    download={'import_template.csv'}
                                                />
                                            )}

                                        </MatnrList.Context.Consumer>
                                    }
                                >
                                    {translate('common.action.import.msg')}
                                </Alert>

                                <FileInput
                                    source="csv"
                                    label="CSV File"
                                    accept={{ 'text/csv': ['.csv'] }}
                                    source="xlsx"
                                    label="Xlsx File"
                                    accept={{ 'text/xlsx': ['.xls', '.xlsx'] }}
                                    onChange={handleFileChange}
                                >
                                    <FileField source="src" title="title" />
@@ -201,6 +210,15 @@
        </Dialog>
    );
}
{/**下载打印模板，传入type类型，调用下载模板接口 */}
const downloadTemplate = (type) => {
    // 下载物料模板
    if (type != undefined &&  type == 'matnr') {
        
    } 
    console.log('======>');
    console.log(type);
}

function millisecondsToTime(ms) {
    var seconds = Math.floor((ms / 1000) % 60);

 rsf-server/pom.xml

@@ -137,7 +137,12 @@
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
    </dependencies>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.2.0</version>
        </dependency>
    </dependencies>

    <build>
        <finalName>rsf-server</finalName>

 rsf-server/src/main/java/com/vincent/rsf/server/common/utils/ExcelUtil.java

@@ -1,5 +1,6 @@
package com.vincent.rsf.server.common.utils;

import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.vincent.rsf.framework.common.Cools;
import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@@ -64,7 +65,8 @@
                    continue;
                }

                field.setAccessible(true);  // 此行很重要，特别是字段为private时
                // 此行很重要，特别是字段为private时
                field.setAccessible(true);
                Object value = null;
                try {
                    value = field.get(t);
@@ -91,76 +93,13 @@
    }

    /**
     * Excel 导入
     * @param file 文件
     * @param keys 数据顺序
     * 添加导入excel配置参数
     * 注：默认配置可满足当前需求
     * @return
     */
    public static  List<Map<String, Object>>  importExcel(MultipartFile file, String[] keys) throws Exception{
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith("xls")) {
            POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
            wb = new HSSFWorkbook(pois);
        } else if (fileName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(file.getInputStream());
        }
        Sheet sheet = wb.getSheetAt(0);
        int rowCount = sheet.getPhysicalNumberOfRows();
        if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){
            throw new RuntimeException("导入的Excel和模板的列不匹配");
        }
        List<Map<String,Object>> result = new ArrayList<>();
        for (int i = 0; i < rowCount - 1; i++) {
            Row row = sheet.getRow(i + 1);
            Map<String,Object> tmp = new HashMap<>();
            for (int j = 0;j < keys.length; j++){
                Cell cell = row.getCell(j);
                // 把类型转行String
//                cell.setCellType(CellType.STRING);
                tmp.put(keys[j], cell.getStringCellValue());
            }
            result.add(tmp);
        }
        return result;
    }

    /**
     * 表头样式
     */
    private static CellStyle HeaderStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 内容样式
     */
    private static CellStyle contentStyle(Workbook wb){
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = commonStyle(wb);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 公共样式
     */
    private static CellStyle commonStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setWrapText(true);// 自动换行
        return style;
    public static ImportParams getDefaultImportParams() {
        ImportParams params = new ImportParams();
        return params;
    }

}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/controller/MatnrController.java

@@ -1,5 +1,9 @@
package com.vincent.rsf.server.manager.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.vincent.rsf.framework.common.Cools;
@@ -21,6 +25,8 @@
import org.springframework.http.HttpHeaders;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

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

@@ -134,4 +140,17 @@
        ExcelUtil.build(ExcelUtil.create(matnrService.list(), Matnr.class), response);
    }

    @PreAuthorize("hasAuthority('manager:matnr:save')")
    @ApiOperation(value = "excel表格导入物料信息")
    @PostMapping("/matnr/import")
    public R listImport(MultipartFile file) {
        try {
            ExcelImportResult<Matnr> objectExcelImportResult = ExcelImportUtil.importExcelMore(file.getInputStream(), Matnr.class, ExcelUtil.getDefaultImportParams());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return R.ok();
    }

}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/controller/MatnrGroupController.java

@@ -109,14 +109,14 @@
    @PostMapping("/matnrGroup/tree")
    public R tree(@RequestBody Map<String, Object> map) {
        List<MatnrGroup> matnrs = new ArrayList<>();
        if (Objects.isNull(map)) {
            matnrs = matnrGroupService.list(new LambdaQueryWrapper<>());
        } else {
            if (Objects.isNull(map.get("condition"))) {
                return R.ok("condition参数不能为空！！");
            }
            matnrs = matnrGroupService.list(new LambdaQueryWrapper<MatnrGroup>().like(MatnrGroup::getName, map.get("condition")));
        }
        matnrs = matnrGroupService.list(new LambdaQueryWrapper<>());
//        if (Objects.isNull(map)) {
//        } else {
//            if (Objects.isNull(map.get("condition"))) {
//                return R.ok("condition参数不能为空！！");
//            }
//            matnrs = matnrGroupService.list(new LambdaQueryWrapper<MatnrGroup>().like(MatnrGroup::getName, map.get("condition")));
//        }
        List<MatnrGroup> treeData = Utils.toTreeData(matnrs, 0L, MatnrGroup::getParentId, MatnrGroup::getId, MatnrGroup::setChildren);
        return R.ok().add(treeData);
    }

 rsf-server/src/main/java/com/vincent/rsf/server/manager/entity/Matnr.java

@@ -57,6 +57,9 @@
    @ApiModelProperty(value= "货主ID")
    private Long shipperId;

//    @ApiModelProperty(value = "货主编码")
//    private String shipperCode;

    /**
     * 分组ID(*)
     */

 rsf-server/src/main/java/com/vincent/rsf/server/manager/entity/excel/MatnrsTemplate.java

New file
@@ -0,0 +1,141 @@
package com.vincent.rsf.server.manager.entity.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelAutoColumnSize;
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelComment;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;
import lombok.experimental.Accessors;

import java.io.Serializable;

/**
 * @author Ryan
 * @version 1.0
 * @title MatnrsTemplate
 * @description
 * @create 2025/3/3 08:40
 */
@Data
@ExcelAutoColumnSize
@Accessors(chain = true)
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
public class MatnrsTemplate implements IExcelModel, IExcelDataModel, Serializable {

    @Excel(name = "物料名称")
    @ExcelComment(example = "华为手机")
    private String name;

    @Excel(name = "货主编码")
    @ExcelComment(example = "PO12625")
    private String shipperCode;

    @Excel(name = "分类名称")
    @ExcelComment(example = "移动设备")
    private String groupName;

    @Excel(name = "物料助记码")
    @ExcelComment(example = "P3528461569")
    private String erpCode;

    @Excel(name = "规格")
    @ExcelComment(example = "HW-148*68*10")
    private String spec;

    @Excel(name = "型号")
    @ExcelComment(example = "华为三折叠")
    private String model;

    @Excel(name = "重量")
    @ExcelComment(notNull = false, example = "258g")
    private String weight;

    @Excel(name = "颜色")
    @ExcelComment(notNull = false, example = "紫红色")
    private String color;

    @Excel(name = "大小")
    @ExcelComment(notNull = false, example = "148*68*10")
    private String size;

    @Excel(name = "描述")
    @ExcelComment(notNull = false, example = "新上市三折叠")
    private String describle;

    @Excel(name = "描述")
    @ExcelComment(notNull = false, example = "新上市三折叠")
    private String nromNum;

    @Excel(name = "主单位")
    @ExcelComment(example = "部")
    private String unit;

    @Excel(name = "采购单位")
    @ExcelComment(example = "部")
    private String purUnit;

    @Excel(name = "ABC类")
    @ExcelComment(notNull = false, example = "A")
    private String stockLevel;

    @Excel(name = "安全库存量")
    @ExcelComment(example = "50")
    private String safeQty;

    @Excel(name = "最小库存量")
    @ExcelComment(notNull = false, example = "10")
    private String minQty;

    @Excel(name = "最大库存量")
    @ExcelComment(notNull = false, example = "150")
    private String maxQty;

    @Excel(name = "停滞天数")
    @ExcelComment(notNull = false, example = "25")
    private String stagn;

    @Excel(name = "有效期")
    @ExcelComment(notNull = false, example = "25")
    private String valid;

    @Excel(name = "条形码")
    @ExcelComment(notNull = false, example = "8001354")
    private String barcode;

    @Excel(name = "效期预警阈值")
    @ExcelComment(notNull = false, example = "2025-01-25 14:25:36")
    private String validWarn;

    @Excel(name = "是否免检")
    @ExcelComment(notNull = false, example = "否")
    private String flagCheck;

    private Integer rowNum;

    private String errorMsg;


    @Override
    public Integer getRowNum() {
        return this.rowNum;
    }

    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }

    @Override
    public String getErrorMsg() {
        return this.errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }
}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/entity/excel/annotation/ExcelAutoColumnSize.java

New file
@@ -0,0 +1,20 @@
package com.vincent.rsf.server.manager.entity.excel.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author: Ryan
 * @create: 2025/3/01 13:29
 * @version: 1.0
 * @description: excel 列宽自适应
 */
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAutoColumnSize {

    /** 启用 */
    boolean enable() default true;
}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/entity/excel/annotation/ExcelComment.java

New file
@@ -0,0 +1,30 @@
package com.vincent.rsf.server.manager.entity.excel.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author: Ryan
 * @create: 2025/3/01 13:29
 * @version: 1.0
 * @description: excel 注释
 */
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelComment {

    /** 备注信息 */
    String value() default "";

    /** 是否非空。自动在备注后面拼接 "必填项"*/
    boolean notNull() default true;


    /** 字典值，如果不为空，会在 {@link #value()} 的后面加上 可选值：label1、label2 */
    String dict() default "";

    /** 示例值；调用{@link ExcelUtils#mockData(Class)} 时 将从此字段取值 */
    String example() default "";
}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/entity/excel/annotation/ExcelField.java

New file
@@ -0,0 +1,60 @@
/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.vincent.rsf.server.manager.entity.excel.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel注解定义
 * @author ThinkGem
 * @version 2013-03-10
 */
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {

    /**
     * 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
     */
    String value() default "";
	
    /**
     * 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
     */
    String title();
	
    /**
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）
     */
    int type() default 0;

    /**
     * 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
     */
    int align() default 0;
	
    /**
     * 导出字段字段排序（升序）
     */
    int sort() default 0;

    /**
     * 如果是字典类型，请设置字典的type值
     */
    String dictType() default "";
	
    /**
     * 反射类型
     */
    Class<?> fieldType() default Class.class;
	
    /**
     * 字段归属组（根据分组导出导入）
     */
    int[] groups() default {};

}

 rsf-server/src/main/java/com/vincent/rsf/server/system/controller/BaseController.java

@@ -13,6 +13,7 @@
/**
 * Created by vincent on 1/30/2024
 */

public class BaseController {

    public User getLoginUser() {