#找库位方法

zjj

4 天以前 d5540413a0163383b53d33aea1be93390ea9149c

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

@@ -1,12 +1,20 @@
package com.vincent.rsf.server.common.utils;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.vincent.rsf.framework.common.Cools;
import com.vincent.rsf.framework.common.SpringUtils;
import com.vincent.rsf.server.manager.entity.excel.MatnrsTemplate;
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelComment;
import com.vincent.rsf.server.system.entity.Fields;
import com.vincent.rsf.server.system.service.FieldsService;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@@ -19,8 +27,8 @@
/**
 * Created by vincent on 2/17/2024
 */
@Slf4j
public class ExcelUtil {

    public static void build(Workbook workbook, HttpServletResponse response) {
        response.reset();
        Http.cors(response);
@@ -28,16 +36,27 @@
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException ignore) {}
        } catch (IOException ignore) {
        }
    }

    public static <T> Workbook create(List<T> list, Class<T> clz) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        return create(list, clz, false);
    }

    public static <T> Workbook create(List<T> list, Class<T> clz, boolean flagTemplate) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(clz.getSimpleName());

        Row header = sheet.createRow(0);
        Field[] fields = Cools.getAllFields(clz);
        int headerIdx = 0;
        FieldsService itemService = SpringUtils.getBean(FieldsService.class);
        List<Fields> sysFields = itemService.list(new LambdaQueryWrapper<Fields>()
                .eq(Fields::getStatus, 1)
                .eq(Fields::getFlagEnable, 1));


        for (Field field : fields) {
            if (Modifier.isFinal(field.getModifiers())
                    || Modifier.isStatic(field.getModifiers())
@@ -45,44 +64,66 @@
                continue;
            }
            String memo = "Undefined";
            if (flagTemplate) {
                memo = field.getName();
            } else {
                if (field.isAnnotationPresent(Excel.class)) {
                    memo = field.getAnnotation(Excel.class).name();
                }
            }
            if (field.isAnnotationPresent(ApiModelProperty.class)) {
                memo = field.getAnnotation(ApiModelProperty.class).value();
            }
            header.createCell(headerIdx).setCellValue(memo);
            headerIdx++;
        }

        int rowIndex = 1;
        for (T t : list) {
            Row row = sheet.createRow(rowIndex++);

            int cellIndex = 0;
            for (Field field : fields) {
                if (Modifier.isFinal(field.getModifiers())
                        || Modifier.isStatic(field.getModifiers())
                        || Modifier.isTransient(field.getModifiers())) {
                    continue;
                }

                field.setAccessible(true);  // 此行很重要，特别是字段为private时
                Object value = null;
                try {
                    value = field.get(t);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (value != null) {
                    if (value instanceof Date) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        row.createCell(cellIndex).setCellValue(sdf.format((Date) value));
        if (clz.equals(MatnrsTemplate.class)) {
            //添加扩展字段别名
            if (!sysFields.isEmpty()) {
                for (Fields field : sysFields) {
                    if (flagTemplate) {
                        header.createCell(headerIdx).setCellValue(field.getFields());
                    } else {
                        row.createCell(cellIndex).setCellValue(value.toString());
                        header.createCell(headerIdx).setCellValue(field.getFieldsAlise());
                    }
                    headerIdx++;
                }
                cellIndex++;
            }
        }

        int rowIndex = 1;
        if (!Objects.isNull(list)) {
            for (T t : list) {
                Row row = sheet.createRow(rowIndex++);

                int cellIndex = 0;
                for (Field field : fields) {
                    if (Modifier.isFinal(field.getModifiers())
                            || Modifier.isStatic(field.getModifiers())
                            || Modifier.isTransient(field.getModifiers())) {
                        continue;
                    }

                    // 此行很重要，特别是字段为private时
                    field.setAccessible(true);
                    Object value = null;
                    try {
                        value = field.get(t);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    if (value != null) {
                        if (value instanceof Date) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            row.createCell(cellIndex).setCellValue(sdf.format((Date) value));
                        } else {
                            row.createCell(cellIndex).setCellValue(value.toString());
                        }
                    }
                    cellIndex++;
                }
            }
        }
        for (int i = 0; i <= fields.length; i++) {
            sheet.autoSizeColumn(i);
        }
@@ -91,76 +132,96 @@
    }

    /**
     * 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());
    public static ImportParams getDefaultImportParams() {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
//        importParams.setNeedVerify(true);
        importParams.setHeadRows(1);
        importParams.setSheetNum(1);
        return importParams;
    }

    /**
     * 根据 {@code tClass} 相关成员变量的 {@link ExcelComment#example()} 字段创建模拟数据，暂不支持 复杂类型
     *
     * @param tClass
     * @return
     */
    public static <T> T mockData(Class<T> tClass) {
        if (tClass == null) {
            return null;
        }
        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());
        T instance = null;

        try {
            instance = tClass.newInstance();
            Field[] declaredFields = tClass.getDeclaredFields();
            for (Field declaredField : declaredFields) {
                ExcelComment comment = declaredField.getAnnotation(ExcelComment.class);
                if (comment == null) {
                    continue;
                }
                declaredField.setAccessible(true);
                Class<?> fieldType = declaredField.getType();

                String exampleValue = comment.example();
                Object value = null;

                if (fieldType == int.class || fieldType == Integer.class) {
                    value = StringUtils.isBlank(exampleValue) ? 0 : Integer.parseInt(exampleValue);
                } else if (fieldType == short.class || fieldType == Short.class) {
                    value = StringUtils.isBlank(exampleValue) ? 0 : Short.parseShort(exampleValue);
                } else if (fieldType == long.class || fieldType == Long.class) {
                    value = StringUtils.isBlank(exampleValue) ? 0 : Long.parseLong(exampleValue);
                } else if (fieldType == double.class || fieldType == Double.class) {
                    value = StringUtils.isBlank(exampleValue) ? 0 : Double.parseDouble(exampleValue);
                } else if (fieldType == boolean.class || fieldType == Boolean.class) {
                    value = StringUtils.isNotBlank(exampleValue) && Boolean.parseBoolean(exampleValue);
                } else if (fieldType == String.class) {
                    value = exampleValue;
                } else if (fieldType == Date.class) {
                    value = DateUtils.parse(exampleValue);
                }

                if (value == null && !isBaseType(fieldType)) {
                    declaredField.set(instance, null);
                } else {
                    declaredField.set(instance, value);
                }
            }
            result.add(tmp);
        } catch (Exception e) {
            log.error("数据构造失败，请查询详细信息", e);
            return instance;
        }
        return result;

        return instance;
    }

    /**
     * 表头样式
     */
    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;
    }

    /**
     * 内容样式
     * 是否是基础数据类型
     *
     * @param className
     * @return
     */
    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 boolean isBaseType(Class<?> className) {
        if (className.equals(java.lang.Integer.class) ||
                className.equals(java.lang.Byte.class) ||
                className.equals(java.lang.Long.class) ||
                className.equals(java.lang.Double.class) ||
                className.equals(java.lang.Float.class) ||
                className.equals(java.lang.Character.class) ||
                className.equals(java.lang.Short.class) ||
                className.equals(java.lang.Boolean.class)) {
            return true;
        }
        return false;
    }

    /**
     * 公共样式
     */
    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;
    }

}