新建德森项目分支

skyouc

2024-12-21 c635d78b479510ebe2556a420948effcd30a0731

 zy-asrs-wms/src/main/java/com/zy/asrs/wms/utils/ExcelUtil.java

@@ -1,216 +1,216 @@
package com.zy.asrs.wms.utils;

import com.zy.asrs.framework.common.Cools;
import com.zy.asrs.wms.asrs.entity.MatField;
import com.zy.asrs.wms.asrs.entity.template.OrderTemplate;
import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by vincent on 2/17/2024
 */
public class ExcelUtil {

    public static void build(Workbook workbook, HttpServletResponse response) {
        response.reset();
        Utils.cors(response);
        response.setContentType("application/octet-stream; charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException ignore) {}
    }

    public static <T> Workbook create(List<T> list, Class<T> clz) {
        return create(list, clz, null);
    }

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

        Row header = sheet.createRow(0);
        Field[] fields = Cools.getAllFields(clz);
        int headerIdx = 0;
        for (Field field : fields) {
            if (Modifier.isFinal(field.getModifiers())
                    || Modifier.isStatic(field.getModifiers())
                    || Modifier.isTransient(field.getModifiers())) {
                continue;
            }
            String memo = "未知";
            if (field.isAnnotationPresent(ApiModelProperty.class)) {
                memo = field.getAnnotation(ApiModelProperty.class).value() + "[" + field.getName() + "]";
            }
            header.createCell(headerIdx).setCellValue(memo);
            headerIdx++;
        }

        //动态字段
        if(dynamicFields != null){
            for (MatField field : dynamicFields) {
                header.createCell(headerIdx).setCellValue(field.getDescribe() + "[" + field.getName() + "]");
                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));
                    } else {
                        row.createCell(cellIndex).setCellValue(value.toString());
                    }
                }
                cellIndex++;
            }

            //动态字段
            if(dynamicFields != null){
                for (Field field : fields) {
                    if (field.getName().equals("dynamicFields")) {
                        try {
                            Map<String, Object> map = (Map<String, Object>) field.get(t);
                            for (MatField matField : dynamicFields) {
                                Object value = map.get(matField.getName());
                                if(value != null){
                                    row.createCell(cellIndex).setCellValue(value.toString());
                                    cellIndex++;
                                }
                            }
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }

        }

        for (int i = 0; i <= fields.length; i++) {
            sheet.autoSizeColumn(i);
        }

        return workbook;
    }

    public static <T> List<T> parseExcelFile(MultipartFile file, Class<T> clz) {
        List<T> dataList = new ArrayList<>();
        try (Workbook workbook = new HSSFWorkbook(file.getInputStream())) {
            ArrayList<HashMap<String, Object>> list = new ArrayList<>();
            ArrayList<String> fields = new ArrayList<>();

            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                HashMap<String, Object> map = new HashMap<>();
                if (!fields.isEmpty()) {
                    for (String field : fields) {
                        map.put(field, null);
                    }
                    list.add(map);
                }

                for (Cell cell : row) {
                    if (cell.getRowIndex() == 0) {
                        Pattern pattern = Pattern.compile("\\[(.*?)\\]");
                        Matcher matcher = pattern.matcher(cell.getStringCellValue());
                        while (matcher.find()) {
                            fields.add(matcher.group(1));
                        }
                    }else {
                        String value = "";
                        if(cell.getCellType() == CellType.NUMERIC.getCode()) {
                            // 先将数字转换为字符串
                            value = String.valueOf(cell.getNumericCellValue());
                            // 处理字符串value
                        } else if(cell.getCellType() == CellType.STRING.getCode()) {
                            // 直接获取字符串值
                            value = cell.getStringCellValue();
                            // 处理字符串value
                        }
                        map.put(fields.get(cell.getColumnIndex()), value);
                    }
                }

            }

            for (HashMap<String, Object> map : list) {
                T t = ExcelUtil.parseData(clz, map);
                dataList.add(t);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public static <T> T parseData(Class<T> clz, Map<String, Object> map) {
        try {
            T newInstance = clz.newInstance();
            Field[] fields = Cools.getAllFields(clz);
            for (Field field : fields) {
                field.setAccessible(true);
                if (!map.containsKey(field.getName())) {
                    if (Modifier.isTransient(field.getModifiers()) && field.getName().equals("dynamicFields")) {
                        Method method = clz.getMethod("syncFieldMap", Map.class);
                        method.invoke(newInstance, map);
                    }
                    continue;
                }
                if (map.get(field.getName()) == null) {
                    continue;
                }
                // 判断字段类型并赋值
                if (field.getType() == String.class) {
                    field.set(newInstance, String.valueOf(map.get(field.getName())));
                } else if (field.getType() == Integer.class) {
                    field.set(newInstance, Integer.parseInt(map.get(field.getName()).toString()));
                } else if (field.getType() == Float.class) {
                    field.set(newInstance, Float.parseFloat(map.get(field.getName()).toString()));
                } else if (field.getType() == Double.class) {
                    field.set(newInstance, Double.parseDouble(map.get(field.getName()).toString()));
                } else if (field.getType() == Boolean.class) {
                    field.set(newInstance, Boolean.parseBoolean(map.get(field.getName()).toString()));
                }
            }
            return newInstance;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}
package com.zy.asrs.wms.utils;



import com.zy.asrs.framework.common.Cools;

import com.zy.asrs.wms.asrs.entity.MatField;

import com.zy.asrs.wms.asrs.entity.template.OrderTemplate;

import io.swagger.annotations.ApiModelProperty;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.*;

import org.springframework.web.multipart.MultipartFile;



import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.lang.reflect.Modifier;

import java.net.URLEncoder;

import java.text.SimpleDateFormat;

import java.util.*;

import java.util.regex.Matcher;

import java.util.regex.Pattern;



/**

 * Created by vincent on 2/17/2024

 */

public class ExcelUtil {



    public static void build(Workbook workbook, HttpServletResponse response) {

        response.reset();

        Utils.cors(response);

        response.setContentType("application/octet-stream; charset=utf-8");

        try {

            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export", "UTF-8"));

            workbook.write(response.getOutputStream());

        } catch (IOException ignore) {}

    }



    public static <T> Workbook create(List<T> list, Class<T> clz) {

        return create(list, clz, null);

    }



    public static <T> Workbook create(List<T> list, Class<T> clz, List<MatField> dynamicFields) {

        HSSFWorkbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet(clz.getSimpleName());



        Row header = sheet.createRow(0);

        Field[] fields = Cools.getAllFields(clz);

        int headerIdx = 0;

        for (Field field : fields) {

            if (Modifier.isFinal(field.getModifiers())

                    || Modifier.isStatic(field.getModifiers())

                    || Modifier.isTransient(field.getModifiers())) {

                continue;

            }

            String memo = "未知";

            if (field.isAnnotationPresent(ApiModelProperty.class)) {

                memo = field.getAnnotation(ApiModelProperty.class).value() + "[" + field.getName() + "]";

            }

            header.createCell(headerIdx).setCellValue(memo);

            headerIdx++;

        }



        //动态字段

        if(dynamicFields != null){

            for (MatField field : dynamicFields) {

                header.createCell(headerIdx).setCellValue(field.getDescribe() + "[" + field.getName() + "]");

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

                    } else {

                        row.createCell(cellIndex).setCellValue(value.toString());

                    }

                }

                cellIndex++;

            }



            //动态字段

            if(dynamicFields != null){

                for (Field field : fields) {

                    if (field.getName().equals("dynamicFields")) {

                        try {

                            Map<String, Object> map = (Map<String, Object>) field.get(t);

                            for (MatField matField : dynamicFields) {

                                Object value = map.get(matField.getName());

                                if(value != null){

                                    row.createCell(cellIndex).setCellValue(value.toString());

                                    cellIndex++;

                                }

                            }

                        } catch (IllegalAccessException e) {

                            e.printStackTrace();

                        }

                    }

                }

            }



        }



        for (int i = 0; i <= fields.length; i++) {

            sheet.autoSizeColumn(i);

        }



        return workbook;

    }



    public static <T> List<T> parseExcelFile(MultipartFile file, Class<T> clz) {

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

        try (Workbook workbook = new HSSFWorkbook(file.getInputStream())) {

            ArrayList<HashMap<String, Object>> list = new ArrayList<>();

            ArrayList<String> fields = new ArrayList<>();



            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {

                HashMap<String, Object> map = new HashMap<>();

                if (!fields.isEmpty()) {

                    for (String field : fields) {

                        map.put(field, null);

                    }

                    list.add(map);

                }



                for (Cell cell : row) {

                    if (cell.getRowIndex() == 0) {

                        Pattern pattern = Pattern.compile("\\[(.*?)\\]");

                        Matcher matcher = pattern.matcher(cell.getStringCellValue());

                        while (matcher.find()) {

                            fields.add(matcher.group(1));

                        }

                    }else {

                        String value = "";

                        if(cell.getCellType() == CellType.NUMERIC.getCode()) {

                            // 先将数字转换为字符串

                            value = String.valueOf(cell.getNumericCellValue());

                            // 处理字符串value

                        } else if(cell.getCellType() == CellType.STRING.getCode()) {

                            // 直接获取字符串值

                            value = cell.getStringCellValue();

                            // 处理字符串value

                        }

                        map.put(fields.get(cell.getColumnIndex()), value);

                    }

                }



            }



            for (HashMap<String, Object> map : list) {

                T t = ExcelUtil.parseData(clz, map);

                dataList.add(t);

            }



        } catch (IOException e) {

            e.printStackTrace();

        }

        return dataList;

    }



    public static <T> T parseData(Class<T> clz, Map<String, Object> map) {

        try {

            T newInstance = clz.newInstance();

            Field[] fields = Cools.getAllFields(clz);

            for (Field field : fields) {

                field.setAccessible(true);

                if (!map.containsKey(field.getName())) {

                    if (Modifier.isTransient(field.getModifiers()) && field.getName().equals("dynamicFields")) {

                        Method method = clz.getMethod("syncFieldMap", Map.class);

                        method.invoke(newInstance, map);

                    }

                    continue;

                }

                if (map.get(field.getName()) == null) {

                    continue;

                }

                // 判断字段类型并赋值

                if (field.getType() == String.class) {

                    field.set(newInstance, String.valueOf(map.get(field.getName())));

                } else if (field.getType() == Integer.class) {

                    field.set(newInstance, Integer.parseInt(map.get(field.getName()).toString()));

                } else if (field.getType() == Float.class) {

                    field.set(newInstance, Float.parseFloat(map.get(field.getName()).toString()));

                } else if (field.getType() == Double.class) {

                    field.set(newInstance, Double.parseDouble(map.get(field.getName()).toString()));

                } else if (field.getType() == Boolean.class) {

                    field.set(newInstance, Boolean.parseBoolean(map.get(field.getName()).toString()));

                }

            }

            return newInstance;

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }



}