api/src/main/java/com/xjrsoft/module/veb/util/ImportExcelUtil.java

package com.xjrsoft.module.veb.util;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.xjrsoft.common.annotation.FiledWidth;
import com.xjrsoft.common.annotation.Required;
import com.xjrsoft.common.enums.DeleteMark;
import com.xjrsoft.common.enums.YesOrNoEnum;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.generator.entity.ImportConfig;
import com.xjrsoft.module.system.entity.DictionaryDetail;
import com.xjrsoft.module.system.entity.DictionaryItem;
import com.xjrsoft.module.system.mapper.DictionarydetailMapper;
import com.xjrsoft.module.system.mapper.DictionaryitemMapper;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Supplier;

/**
 * @author phoenix
 * @Description 导入工具类
 * 2023/12/5
 */
public class ImportExcelUtil {

    /**
     * 下载模板写入
     *
     * @param obj
     * @return
     * @throws IOException
     */
    public static ByteArrayOutputStream writeTemplateSheet(Object obj) throws IOException {
        // 开始写入
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表(sheet)
        String sheetName = "sheet1";
        Sheet sheet = workbook.createSheet(sheetName);

        List<ImportConfig> importConfigs = allFields(obj);

        // 表头
        createHead(workbook, sheet, importConfigs, IndexedColors.YELLOW.getIndex(), IndexedColors.RED.getIndex(), 0);

        // 提示必填
        String content = "黄底红字为必填项，导入时请删除本行。";
        createCautionHead(workbook, sheet, 1, content, importConfigs.size() - 1, 12, IndexedColors.RED.getIndex());

        // 自动列宽
        for (int i = 0; i < importConfigs.size(); i++) {
            sheet.autoSizeColumn(i);
        }

        //写入文件
        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        workbook.write(bot);
        return bot;
    }

    /**
     * 获取写入对象的所有字段
     *
     * @param obj
     * @return
     */
    public static List<ImportConfig> allFields(Object obj) {
        List<ImportConfig> importConfigs = new ArrayList<>();
        if (obj == null) {
            return importConfigs;
        }

        Class<?> clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();

        int index = 0;
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            ImportConfig importConfig = new ImportConfig();
            field.setAccessible(true); // 访问私有字段
            if (field.isAnnotationPresent(Required.class)) {
                Required required = field.getAnnotation(Required.class);
                Boolean value = required.value();
                importConfig.setRequired(value);
            }

            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                String[] annotationValues = excelProperty.value();
                importConfig.setLabel(annotationValues.length > 0 ? annotationValues[0] : "");
            }

            if (field.isAnnotationPresent(ExcelIgnore.class)) {
                continue;
            }
            importConfig.setFieldName(field.getName());
            importConfig.setSortCode(index++);

            if (field.isAnnotationPresent(FiledWidth.class)) {
                FiledWidth excelProperty = field.getAnnotation(FiledWidth.class);
                int annotationValue = excelProperty.value();
                importConfig.setWidth(annotationValue);
            }else {
                importConfig.setWidth(0);
            }

            importConfigs.add(importConfig);
        }

        return importConfigs;
    }

    /**
     * 写大标题行
     *
     * @param workbook
     * @param sheet
     * @param bigHead
     * @param rowNumber
     * @param lastCol
     */
    public static void createBigHead(Workbook workbook, Sheet sheet, String bigHead, int rowNumber, int lastCol) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 18);

        // 正常样式
        CellStyle normalCellStyle = workbook.createCellStyle();
        normalCellStyle.setFont(font); // 将字体应用到样式
        normalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        normalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式为细线
        normalCellStyle.setBorderTop(BorderStyle.THIN);
        normalCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 设置顶部边框颜色

        normalCellStyle.setBorderBottom(BorderStyle.THIN);
        normalCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 设置底部边框颜色

        normalCellStyle.setBorderLeft(BorderStyle.THIN);
        normalCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 设置左边框颜色

        normalCellStyle.setBorderRight(BorderStyle.THIN);
        normalCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 设置右边框颜色

        // 所在行
        Row row = sheet.createRow(rowNumber);

        Cell cell = row.createCell(0);
        cell.setCellValue(bigHead);
        cell.setCellStyle(normalCellStyle);

        for (int i = 1; i < lastCol + 1; i ++){
            cell = row.createCell(i);
            cell.setCellStyle(normalCellStyle);
        }

        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, lastCol));
    }

    /**
     * 写标题行
     *
     * @param workbook
     * @param sheet
     * @param importConfigs
     * @param rowNumber
     */
    public static void createHead(Workbook workbook, Sheet sheet, List<ImportConfig> importConfigs, short requiredFieldForegroundColor, short requiredFieldFontColor, int rowNumber) {
        Font normalFont = workbook.createFont();
        normalFont.setFontName("宋体");
        normalFont.setFontHeightInPoints((short) 12);

        // 正常样式
        CellStyle normalCellStyle = workbook.createCellStyle();
        normalCellStyle.setFont(normalFont); // 将字体应用到样式
        normalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        normalCellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 设置边框样式为细线
        normalCellStyle.setBorderTop(BorderStyle.THIN);
        normalCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 设置顶部边框颜色

        normalCellStyle.setBorderBottom(BorderStyle.THIN);
        normalCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 设置底部边框颜色

        normalCellStyle.setBorderLeft(BorderStyle.THIN);
        normalCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 设置左边框颜色

        normalCellStyle.setBorderRight(BorderStyle.THIN);
        normalCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 设置右边框颜色

        // 必填样式
        Font requiredFont = workbook.createFont();
        requiredFont.setFontName("宋体");
        requiredFont.setFontHeightInPoints((short) 12);
        requiredFont.setColor(requiredFieldFontColor);

        CellStyle requiredCellStyle = workbook.createCellStyle();
        requiredCellStyle.setFont(requiredFont); // 将字体应用到样式
        requiredCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        requiredCellStyle.setAlignment(HorizontalAlignment.CENTER);
//        requiredCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());//设置背景颜色
        requiredCellStyle.setFillForegroundColor(requiredFieldForegroundColor);//设置背景颜色s
        requiredCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置填充模式

        // 设置边框样式为细线
        requiredCellStyle.setBorderTop(BorderStyle.THIN);
        requiredCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 设置顶部边框颜色

        requiredCellStyle.setBorderBottom(BorderStyle.THIN);
        requiredCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 设置底部边框颜色

        requiredCellStyle.setBorderLeft(BorderStyle.THIN);
        requiredCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 设置左边框颜色

        requiredCellStyle.setBorderRight(BorderStyle.THIN);
        requiredCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 设置右边框颜色

        // 所在行
        Row row = sheet.createRow(rowNumber);

        // 每一列
        for (ImportConfig importConfig : importConfigs) {
            Cell cell = row.createCell(importConfig.getSortCode());
            String content = importConfig.getLabel();
            cell.setCellValue(content);
            if (ObjectUtils.isNotEmpty(importConfig.getRequired()) && importConfig.getRequired()) {
                cell.setCellStyle(requiredCellStyle);
            }
            if (ObjectUtils.isEmpty(importConfig.getRequired()) || !importConfig.getRequired()) {
                cell.setCellStyle(normalCellStyle);
            }
        }
    }

    /**
     * 写入副标题，字号14，合并为一个单元格
     * @param workbook
     * @param sheet
     * @param rowNumber
     * @param lastCol
     */
    public static void createSubtitle(Workbook workbook, Sheet sheet, String bigHead, int rowNumber, int lastCol) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);

        // 正常样式
        CellStyle normalCellStyle = workbook.createCellStyle();
        normalCellStyle.setFont(font); // 将字体应用到样式
        normalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        normalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式为细线
        normalCellStyle.setBorderTop(BorderStyle.THIN);
        normalCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 设置顶部边框颜色

        normalCellStyle.setBorderBottom(BorderStyle.THIN);
        normalCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 设置底部边框颜色

        normalCellStyle.setBorderLeft(BorderStyle.THIN);
        normalCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 设置左边框颜色

        normalCellStyle.setBorderRight(BorderStyle.THIN);
        normalCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 设置右边框颜色

        // 所在行
        Row row = sheet.createRow(rowNumber);

        Cell cell = row.createCell(0);
        cell.setCellValue(bigHead);
        cell.setCellStyle(normalCellStyle);

        for (int i = 1; i < lastCol + 1; i ++){
            cell = row.createCell(i);
            cell.setCellStyle(normalCellStyle);
        }

        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, lastCol));
    }

    /**
     * 写单格
     *
     * @param workbook
     * @param sheet
     * @param content
     * @param rowNumber
     * @param starRow
     * @param endRow
     * @param startcol
     * @param lastCol
     * @param fontSize
     * @param indexedColor
     */
    public static void createOneCell(Workbook workbook, Sheet sheet, String content, int rowNumber, int starRow, int endRow, int startcol, int lastCol, short fontSize, short indexedColor, HorizontalAlignment horizontalAlignment) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints(fontSize);
        font.setColor(indexedColor);

        // 正常样式
        CellStyle normalCellStyle = workbook.createCellStyle();
        normalCellStyle.setFont(font); // 将字体应用到样式
        normalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        normalCellStyle.setAlignment(horizontalAlignment);

        // 设置边框样式为细线
        normalCellStyle.setBorderTop(BorderStyle.THIN);
        normalCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 设置顶部边框颜色

        normalCellStyle.setBorderBottom(BorderStyle.THIN);
        normalCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 设置底部边框颜色

        normalCellStyle.setBorderLeft(BorderStyle.THIN);
        normalCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 设置左边框颜色

        normalCellStyle.setBorderRight(BorderStyle.THIN);
        normalCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 设置右边框颜色

        // 所在行
        Row row = sheet.createRow(rowNumber);

        Cell cell = row.createCell(0);
        cell.setCellValue(content);
        cell.setCellStyle(normalCellStyle);
        sheet.addMergedRegion(new CellRangeAddress(starRow, endRow, startcol, lastCol));
    }




    /**
     * 写提示行
     *
     * @param workbook
     * @param sheet
     * @param rowNumber
     * @param content
     * @param lastCol
     * @param fontSize
     */
    public static void createCautionHead(Workbook workbook, Sheet sheet, int rowNumber, String content, int lastCol, int fontSize, short indexedColor) {
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        font.setColor(indexedColor);
//        font.setColor(IndexedColors.RED.getIndex());

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 将字体应用到样式

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 设置顶部边框颜色

        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 设置底部边框颜色

        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 设置左边框颜色

        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 设置右边框颜色

        Row row = sheet.createRow(rowNumber);
        Cell cell = row.createCell(0);
        cell.setCellValue(content);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, lastCol));
    }

    /**
     * 验证子表字段值的合理性并转换为对应的主键
     *
     * @param getter
     * @param fieldName
     * @param string2Long
     * @param setter
     * @param sb
     * @param i
     * @return
     */
    public static boolean validateAndSetString2LongField(Supplier<String> getter,
                                                         String fieldName,
                                                         Map<String, Long> string2Long,
                                                         Consumer<Long> setter,
                                                         StringBuilder sb, int i) {
        String value = getter.get();
        if (value != null && !value.trim().isEmpty()) {
            Long sublistValue = string2Long.get(value);
            if (sublistValue != null) {
                setter.accept(sublistValue);
                return false;
            } else {
                sb.append("第");
                sb.append(i);
                sb.append("行的");
                sb.append(fieldName);
                sb.append("列的值不存在于系统对应基础数据中，请到基础数据维护");
                return true;
            }
        }
        return false; // 字段为空，不进行验证
    }

    /**
     * 验证子表字段值的合理性并转换为对应的值
     *
     * @param getter
     * @param fieldName
     * @param string2String
     * @param setter
     * @param sb
     * @param i
     * @return
     */
    public static boolean validateAndSetString2StringField(Supplier<String> getter,
                                                         String fieldName,
                                                         Map<String, String> string2String,
                                                         Consumer<String> setter,
                                                         StringBuilder sb, int i) {
        String value = getter.get();
        if (value != null && !value.trim().isEmpty()) {
            String sublistValue = string2String.get(value);
            if (sublistValue != null) {
                setter.accept(sublistValue);
                return false;
            } else {
                sb.append("第");
                sb.append(i);
                sb.append("行的");
                sb.append(fieldName);
                sb.append("列的值不存在于系统对应基础数据中，请到基础数据维护");
                return true;
            }
        }
        return false; // 字段为空，不进行验证
    }

    /**
     * 验证boolean值的合理性并转换为数值
     *
     * @param getter
     * @param fieldName
     * @param setter
     * @param sb
     * @param i
     * @return
     */
    public static boolean validateAndSetBooleanField(Supplier<String> getter,
                                                     String fieldName,
                                                     Consumer<Integer> setter,
                                                     StringBuilder sb, int i) {
        String value = getter.get();
        if (value != null && !value.trim().isEmpty()) {
            Integer booleanValue = YesOrNoEnum.getCode(value);
            if (booleanValue != null) {
                setter.accept(booleanValue);
                return false;
            } else {
                sb.append("第");
                sb.append(i);
                sb.append("行的");
                sb.append(fieldName);
                sb.append("列的值不符合，该列的值只能为是或否");
                return true;
            }
        }
        return false; // 字段为空，不进行验证
    }

    /**
     * 验证字典值的合理性并转换为字典的code
     *
     * @param getter
     * @param prefix
     * @param fieldName
     * @param dictionary
     * @param setter
     * @param sb
     * @param i
     * @return
     */
    public static boolean validateAndSetDictionaryField(Supplier<String> getter,
                                                        String prefix,
                                                        String fieldName,
                                                        Map<String, String> dictionary,
                                                        Consumer<String> setter,
                                                        StringBuilder sb, int i) {
        String value = getter.get();
        if (value != null && !value.trim().isEmpty()) {
            String dictValue = dictionary.get(prefix + value.trim());
            if (dictValue != null && !dictValue.trim().isEmpty()) {
                setter.accept(dictValue.trim());
                return false;
            } else {
                sb.append("第");
                sb.append(i);
                sb.append("行的");
                sb.append(fieldName);
                sb.append("列的值不存在于字典中，请到字典中维护");
                return true;
            }
        }
        return false; // 字段为空，不进行验证
    }

    /**
     * 验证枚举值的合理性并转换为枚举的code
     *
     * @param getter
     * @param fieldName
     * @param setter
     * @param sb
     * @param i
     * @return
     */
    public static <E extends Enum<E>> boolean validateAndSetEnumField(Supplier<String> getter,
                                                                      String fieldName,
                                                                      Map<String, Integer> enumMap,
                                                                      Consumer<Integer> setter,
                                                                      StringBuilder sb, int i) {
        String value = getter.get();
        if (value != null && !value.trim().isEmpty()) {
            Integer enumValue = enumMap.get(value);
            if (enumValue != null) {
                setter.accept(enumValue);
                return false;
            } else {
                sb.append("第");
                sb.append(i);
                sb.append("行的");
                sb.append(fieldName);
                sb.append("列的值不存在于枚举值中，请到枚举值中维护");
                return true;
            }
        }
        return false; // 字段为空，不进行验证
    }

    /**
     * 获取所有的字典值映射
     *
     * @param codeList
     * @return
     */
    public static Map<String, String> initDictionary(List<String> codeList, DictionaryitemMapper dictionaryitemMapper, DictionarydetailMapper dictionarydetailMapper) {
        List<DictionaryDetail> detailList = dictionarydetailMapper.selectJoinList(DictionaryDetail.class,
                new MPJLambdaWrapper<DictionaryDetail>()
                        .select(DictionaryDetail::getId)
                        .select(DictionaryDetail.class, x -> VoToColumnUtil.fieldsToColumns(DictionaryDetail.class).contains(x.getProperty()))
                        .leftJoin(DictionaryItem.class, DictionaryItem::getId, DictionaryDetail::getItemId)
                        .in(DictionaryItem::getCode, codeList)
        );

        List<DictionaryItem> dictionaryItemList = dictionaryitemMapper.selectList(
                new QueryWrapper<DictionaryItem>().lambda()
                        .eq(DictionaryItem::getDeleteMark, DeleteMark.NODELETE.getCode())
        );
        Map<Long, String> itemMap = new HashMap<>();
        for (DictionaryItem dictionaryItem : dictionaryItemList) {
            itemMap.put(dictionaryItem.getId(), dictionaryItem.getCode());
        }

        Map<String, String> resultMap = new HashMap<>();
        for (DictionaryDetail dictionaryDetail : detailList) {
            resultMap.put(itemMap.get(dictionaryDetail.getItemId()) + dictionaryDetail.getName(), dictionaryDetail.getCode());
        }
        return resultMap;
    }

    /**
     * 判断属性是否为必填属性
     *
     * @param instance
     * @param sb
     * @param i
     * @return
     * @throws IllegalAccessException
     */
    public static boolean isRequiredFieldsFilled(Object instance,
                                                 StringBuilder sb,
                                                 int i)
            throws IllegalAccessException {
        if (instance == null) {
            return true; // 如果对象本身为 null，则认为没有通过验证
        }

        for (Field field : instance.getClass().getDeclaredFields()) {
            Required required = field.getAnnotation(Required.class);
            if (ObjectUtils.isNotEmpty(required) && required.value()) { // 如果字段被 @Required 标记
                field.setAccessible(true); // 允许访问私有字段
                Object value = field.get(instance); // 获取字段的值
                if (value == null || (value instanceof String && ((String) value).trim().isEmpty())) {
                    sb.append("第");
                    sb.append(i);
                    sb.append("行的");
                    if (field.isAnnotationPresent(ExcelProperty.class)) {
                        ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                        String[] annotationValues = excelProperty.value();
                        sb.append(annotationValues.length > 0 ? annotationValues[0] : "");
                    }
                    sb.append("列的值为必填");
                    return true; // 如果任何必需字段为空，则返回 true
                }
            }
        }
        return false; // 所有必需字段都不为空
    }

    /**
     * 将对象列表转换为二维字符串列表
     *
     * @param dataList 数据对象列表
     * @param mappers  字段提取函数数组
     * @param <T>      数据对象类型
     * @return 二维字符串列表
     */
    @SafeVarargs
    public static <T> List<List<String>> convertToDataList(List<T> dataList, Function<T, String>... mappers) {
        List<List<String>> result = new ArrayList<>();
        for (T data : dataList) {
            List<String> row = new ArrayList<>();
            for (Function<T, String> mapper : mappers) {
                row.add(mapper.apply(data));
            }
            result.add(row);
        }
        return result;
    }

    public static List<ImportConfig> getAllFieldCN(Class<?> clazz) {
        List<ImportConfig> importConfigs = new ArrayList<>();

        Field[] fields = clazz.getDeclaredFields();

        int index = 0;
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            ImportConfig importConfig = new ImportConfig();
            field.setAccessible(true); // 访问私有字段
            if (field.isAnnotationPresent(Required.class)) {
                Required required = field.getAnnotation(Required.class);
                boolean value = required.value();
                importConfig.setRequired(value);
            }

            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                String[] annotationValues = excelProperty.value();
                importConfig.setLabel(annotationValues.length > 0 ? annotationValues[0] : "");
            }

            if (field.isAnnotationPresent(ExcelIgnore.class)) {
                continue;
            }
            importConfig.setFieldName(field.getName());
            importConfig.setSortCode(index++);
            importConfig.setWidth(0);
            importConfigs.add(importConfig);
        }

        return importConfigs;
    }
}