数据导入

功能支持

1. 读取excel、CSV
2. 加入自定义注解校验数据防重
3. 加入导入值跟实体类的类型检查
4. 字典表支持(跟导出一个用法)
5. 支持多种数据场景校验(Validation自带方法实体类校验)

使用方法

@ApiOperation("Excel导入")
@PostMapping("uploadExcel")
public void uploadExcel() throws Exception {
    //1. 读取excel
    List<Map<String, Object>> maps = ExcelCSVReadUtils.readBySax(new FileInputStream("C:\\Users\\Admin\\Desktop\\t_sys_user.xls"), 0, 1);
    //or 读取CSV
    //List<Map<String, Object>> maps = ExcelCSVReadUtils.readCsv(new FileInputStream("C:\\Users\\Admin\\Desktop\\t_sys_user.csv"), 1, 1);
    
    //2. 转换结果(支持表头下划线转驼峰)
    Result result = MapUtil.map2Object(maps, User.class,1);
    Object data = result.getData();
    //3. 成功返回true
    if(result.isCode()) {
        //4. 获取对象
        List<User> users = MapUtil.castList(data, User.class);
        for (User user : users) {
            //5. 脚手架自带 数据校验
            ValidatorHelper.validateEntity(user);
        }
    }
}

实体模板

实体注解介绍

@Unique为自定义注解校验数据防重，加在实体类的字段上

@ExcelProperty(converter = GenderConvert.class)字典表用法同导出
不同的是，导入重写的是BaseConverter类的convertToJavaData这个方法

@Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String dictType = getDictType();
        //反向替换
        if (!StringUtils.isEmpty(cellData.getData()) ){
            //告诉我你的字典类型，先从缓存中获取
            Map<String, String> dictItemMap = dictCache.get(dictType);
            if (dictItemMap == null) {
                //没拿到调用实现方法获取字典
                GetDictMapping queryDictItem = ApplicationContextHolder.getBean(GetDictMapping.class);
                if (queryDictItem != null){
                    dictItemMap = queryDictItem.getDictMappingReverse(dictType);
                    //放入缓存
                    dictCache.put(dictType,dictItemMap);
                }
            }
            String v = dictItemMap.get(cellData.getData());
            return (v == null ? Convert.toStr(cellData.getData()) : v);
        }
        return Convert.toStr(cellData.getData());
    }

转换结果

成功返回true

失败返回错误条件提示

代码分析

在common-data-download分支下的upload包下，主要有两个工具类

ExcelCSVReadUtils封装了hutool工具类来读取Excel(03、07格式)、CSV
MapUtil支持表头下划线转驼峰为实体类对象，数据类型读取的值跟实体类的类型不匹配提醒、加入Unique唯一性校验不匹配提醒

附代码

/***
 * @ClassName ExcelCSVReadUtils
 * @Description: 读取大表的excel工具
 * @Author suyuan
 **/
public class ExcelCSVReadUtils {
    private static Logger logger = LoggerFactory.getLogger(ExcelCSVReadUtils.class);
    private List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
    private List<String> headers = new ArrayList<>();
    private Map<String, String> aliasHeader;
    private int headerRow = 1;
    private boolean checkHeader = false;

    public ExcelCSVReadUtils(Map<String, String> aliasHeader, int headerRow) {
        this.aliasHeader = aliasHeader;
        this.headerRow = headerRow;
    }

    public ExcelCSVReadUtils(Map<String, String> aliasHeader, int headerRow, boolean checkHeader) {
        this.aliasHeader = aliasHeader;
        this.headerRow = headerRow;
        this.checkHeader = checkHeader;
    }

    /**
     * 读取大表的实现方法
     *
     * @return
     */
    private RowHandler createRowHandler() {
        return new RowHandler() {
            @Override
            public void handle(int sheetIndex, int rowIndex, List<Object> rowlist) {
                if (isEmpty(rowlist)) {
                    return;
                }
                if (headerRow > rowIndex + 1) {
                    return;
                } else if (headerRow == rowIndex + 1) {
                    rowlist.forEach(e -> headers.add(e.toString()));
                    //比对表头是否正确;
                    if (checkHeader) {
                        boolean headerHandler = checkHeaderHandler();
                        if (!headerHandler) {
                            throw new POIException("表头格式不正确!");
                        }
                    }
                } else {
                    if (aliasHeader == null || aliasHeader.isEmpty()) {
                        rows.add(IterUtil.toMap(headers, rowlist));
                    } else {
                        rows.add(IterUtil.toMap(aliasHeader(headers, aliasHeader), rowlist));
                    }
                }
            }
        };
    }


    public boolean isEmpty(List<Object> list) {
        if (list.isEmpty()) {
            return true;
        }
        List<Object> collect = list.stream().filter(e -> e == null || StringUtils.isBlank(e.toString())).collect(Collectors.toList());
        return collect.size() == list.size() ? true : false;
    }

    /**
     * @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param sheet       读取的sheet  第一个sheet为 0
     * @param headerRow   表头行 第一行是 1
     * @param beanType    定义的对象 属性有excel的注解
     * @param <T>
     * @return
     */
    public static <T> List<T> readBySax(InputStream inputStream, int sheet, int headerRow, Class<T> beanType) {
        Map<String, String> aliasHeader = getFiledMap(beanType);
        List<Map<String, Object>> mapList = readBySax(inputStream, sheet, aliasHeader, headerRow, false);
        if (Map.class.isAssignableFrom(beanType)) {
            return (List<T>) mapList;
        } else {
            List<T> beanList = new ArrayList(mapList.size());
            Iterator iterator = mapList.iterator();

            while (iterator.hasNext()) {
                Map<String, Object> map = (Map) iterator.next();
                beanList.add(BeanUtil.mapToBean(map, beanType, false));
            }
            return beanList;
        }
    }


    /**
     * @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param sheet       读取的sheet  第一个sheet为 0
     * @param headerRow   表头行 第一行是 1
     * @param beanType    定义的对象 属性有excel的注解
     * @param <T>
     * @param checkHeader
     * @return
     */
    public static <T> List<T> readBySax(InputStream inputStream, int sheet, int headerRow, Class<T> beanType, boolean checkHeader) {
        Map<String, String> aliasHeader = getFiledMap(beanType);
        List<Map<String, Object>> mapList = readBySax(inputStream, sheet, aliasHeader, headerRow, checkHeader);
        if (Map.class.isAssignableFrom(beanType)) {
            return (List<T>) mapList;
        } else {
            List<T> beanList = new ArrayList(mapList.size());
            Iterator iterator = mapList.iterator();

            while (iterator.hasNext()) {
                Map<String, Object> map = (Map) iterator.next();
                beanList.add(BeanUtil.mapToBean(map, beanType, false));
            }
            return beanList;
        }
    }

    /**
     * @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param sheet       读取的sheet  第一个sheet为 0
     * @param headerRow   表头行 第一行是 1
     * @return
     */
    public static List<Map<String, Object>> readBySax(InputStream inputStream, int sheet, int headerRow) {
        return readBySax(inputStream, sheet, null, headerRow, false);
    }

    /**
     * @param inputStream
     * @param sheet       读取的sheet  第一个sheet为 0
     * @param headerRow   表头行 第一行是 1
     * @param checkHeader
     * @return
     */
    public static List<Map<String, Object>> readBySax(InputStream inputStream, int sheet, int headerRow, boolean checkHeader) {
        return readBySax(inputStream, sheet, null, headerRow, checkHeader);
    }

    /**
     * @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param sheet       读取的sheet  第一个sheet为 0
     * @param aliasHeader 定义的表头转换map {"姓名":name,"年龄":age}
     * @param headerRow   表头行 第一行是 1
     * @return
     */
    public static List<Map<String, Object>> readBySax(InputStream inputStream, int sheet, Map<String, String> aliasHeader, int headerRow, boolean checkHeader) {
        long start = System.currentTimeMillis();
        ExcelCSVReadUtils excelUtils = new ExcelCSVReadUtils(aliasHeader, headerRow, checkHeader);
        ExcelUtil.readBySax(inputStream, sheet, excelUtils.createRowHandler());
        System.out.println("read  used ：" + (System.currentTimeMillis() - start) + " ms");
        return excelUtils.getRows();
    }

    /**
     * 读取csv文件
     *
     * @param inputStream    文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param headerRowIndex 表头行
     * @param startRowIndex  数据开始行
     * @return
     */
    public static List<Map<String, Object>> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex) {
        return readCsv(inputStream, null, headerRowIndex, startRowIndex, "utf-8");
    }

    /**
     * 读取csv文件
     *
     * @param inputStream    文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param headerRowIndex 表头行
     * @param startRowIndex  数据开始行
     * @param charset        字符集
     * @return
     */
    public static List<Map<String, Object>> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex, String charset) {
        return readCsv(inputStream, null, headerRowIndex, startRowIndex, charset);
    }

    /**
     * 读取csv文件
     *
     * @param inputStream    文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param aliasHeader    转换后的表头
     * @param headerRowIndex 表头行
     * @param startRowIndex  数据开始行
     * @param charset        字符集
     * @return
     */
    public static List<Map<String, Object>> readCsv(InputStream inputStream, Map<String, String> aliasHeader, int headerRowIndex, int startRowIndex, String charset) {
        long start = System.currentTimeMillis();
        List<Map<String, Object>> resList = new ArrayList<>();
        CsvReader reader = CsvUtil.getReader();
        //从文件中读取CSV数据
        InputStreamReader is = null;
        try {
            is = new InputStreamReader(inputStream, charset);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("get inputStreamReader failed");
        }

        CsvData data = reader.read(is);
        List<CsvRow> rows = data.getRows();
        //空表格；
        if (rows.isEmpty() || rows.size() - headerRowIndex == 0) {
            return null;
        }
        //获取表头；
        CsvRow headerRow = rows.get(headerRowIndex - 1);
        //遍历行
        for (int i = startRowIndex - 1; i < rows.size(); i++) {
            CsvRow csvRow = rows.get(i);
            //getRawList返回一个List列表，列表的每一项为CSV中的一个单元格（既逗号分隔部分）
            List<String> rawList = csvRow.getRawList();
            //跳过表头
            if(i<=startRowIndex - 1)
            {
                continue;
            }
            if (aliasHeader == null) {
                Map map = IterUtil.toMap(headerRow, (Iterable) rawList);
                resList.add(map);
            } else {
                Map map = IterUtil.toMap(aliasHeader(headerRow, aliasHeader), (Iterable) rawList);
                resList.add(map);
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("read  used ：" + (end - start) + " ms");
        return resList;
    }

    /**
     * 读取csv文件 返回期望的beanType
     *
     * @param inputStream    文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param headerRowIndex 表头行
     * @param startRowIndex  数据开始行
     * @param beanType       javaBean  属性包含@excel注解
     * @param <T>
     * @return
     */
    public static <T> List<T> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex, Class<T> beanType) {
        return readCsv(inputStream, headerRowIndex, startRowIndex, beanType, "utf-8");
    }

    /**
     * 读取csv文件 返回期望的beanType
     *
     * @param inputStream    文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
     * @param headerRowIndex 表头行
     * @param startRowIndex  数据开始行
     * @param beanType       javaBean  属性包含@excel注解
     * @param charset        字符集
     * @param <T>
     * @return
     */
    public static <T> List<T> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex, Class<T> beanType, String charset) {
        Map<String, String> aliasHeader = getFiledMap(beanType);
        List<Map<String, Object>> mapList = readCsv(inputStream, aliasHeader, headerRowIndex, startRowIndex, charset);
        if (Map.class.isAssignableFrom(beanType)) {
            return (List<T>) mapList;
        } else {
            List<T> beanList = new ArrayList(mapList.size());
            Iterator i$ = mapList.iterator();

            while (i$.hasNext()) {
                Map<String, Object> map = (Map) i$.next();
                beanList.add(BeanUtil.mapToBean(map, beanType, false));
            }
            return beanList;
        }
    }


    /**
     * 校验表头是否是我们需要的格式
     *
     * @return
     */
    public boolean checkHeaderHandler() {
        boolean flag = true;
        List<String> aliasHeaders = CollUtil.newArrayList(aliasHeader.keySet());
        if (headers.size() == 0 || aliasHeaders == null) {
            return false;
        }
        //判断长度；
        if (aliasHeaders.size() > headers.size()) {
            return false;
        }
        //比较每个元素是否相等
        List<String> collect = aliasHeaders.parallelStream().filter(e -> headers.contains(e)).collect(Collectors.toList());
        return collect.size() == aliasHeaders.size();
    }

    /**
     * 表头转换 将excel/csv的表头中文转换成可以入库的表字段
     *
     * @param headerList
     * @param headerAlias
     * @return
     */
    private static List<String> aliasHeader(List<String> headerList, Map<String, String> headerAlias) {
        ArrayList<String> result = new ArrayList();
        if (CollUtil.isEmpty(headerList)) {
            return result;
        } else {
            String alias = null;

            for (Iterator iterator = headerList.iterator(); iterator.hasNext(); result.add(alias)) {
                Object headerObj = iterator.next();
                if (null != headerObj) {
                    String header = headerObj.toString();
                    alias = (String) headerAlias.get(header);
                    if (null == alias) {
                        alias = header;
                    }
                }
            }
            return result;
        }
    }

    /**
     * 根据excel注解将属性名和注解name值转成map
     * el：{"姓名":name,"年龄":age}
     *
     * @param clazz
     * @return
     */
    public static Map<String, String> getFiledMap(Class<?> clazz) {
        BeanDesc beanDesc = BeanUtil.getBeanDesc(clazz);
        Collection<BeanDesc.PropDesc> props = beanDesc.getProps();
        Map<String, String> filedMap = new HashMap<>(props.size());
        for (BeanDesc.PropDesc p : props) {
            Field field = beanDesc.getField(p.getFieldName());
            Excel annotation = field.getAnnotation(Excel.class);
            if (annotation != null) {
                filedMap.put(annotation.name(), p.getFieldName());
            }
        }
        return filedMap;
    }

    public List<Map<String, Object>> getRows() {
        return rows;
    }


}

public class MapUtil {

    /**
     * map转对象
     * 成功
     * {
     *     data:成功list
     *     size:成功条数
     *     code:true
     * }
     * or
     * 失败
     * {
     *     data:失败list警告
     *     size:失败条数
     *     code:false
     * }
     * @param maps      读取的对象
     * @param clazz     需要转换的对象
     * @param headerRow 表头行 第一行是 1
     * @author suyuan
     * @date 2021/5/28 14:16
     */
    public static <T> Result map2Object(List<Map<String, Object>> maps, Class<T> clazz,int headerRow) throws Exception {
        // DO: 2021/5/28 封装一个消息返回成功或者错误信息
        Result result = new Result();
        ArrayList<T> listsuccess = new ArrayList<T>();
        ArrayList<String> listfail = new ArrayList<>();
        Map<Object, Integer> m=new HashMap<>();
        int i=headerRow;
        for(Map<String, Object> map : maps){
            i++;
            T t = clazz.newInstance();
            // DO: 2021/5/28 下划线转驼峰
            Iterator<Map.Entry<String, Object>> entries = map.entrySet().iterator();
            while (entries.hasNext()) {
                Map.Entry<String, Object> entry = entries.next();
                Object fieldValue = entry.getValue();
                // TODO: 2021/6/4 字典转换转换的不对要不要提醒？
                Field newField = ReflectUtil.getField(clazz, StrUtil.toCamelCase(entry.getKey()));
                if (newField != null) {
                    newField.setAccessible(true);
                    if (newField.isAnnotationPresent(ExcelProperty.class)) {
                        Class<? extends Converter> converter1 = newField.getAnnotation(ExcelProperty.class).converter();
                        if(!converter1.equals(AutoConverter.class)){
                            Converter converter = newField.getAnnotation(ExcelProperty.class).converter().newInstance();
                            CellData cellData = new CellData( converter.convertToJavaData(new CellData(fieldValue) , null, null));
                            if (cellData.getData() != null) {
                                fieldValue = cellData.getData();
                            }
                        }
                    }
                    Class<?> fieldType = newField.getType();
                    if (fieldType.equals(String.class) && String.valueOf(fieldValue).contains(".0")) {
                        fieldValue = convertRate2Decimal(fieldValue);
                    }
                    Object convert = Convert.convert(fieldType, fieldValue);
                    newField.set(t, Convert.convert(fieldType, fieldValue));
                    if (convert == null) {
                        listfail.add("第" + i + "行的" + entry.getKey() + "列数据类型有问题，请自行检查");
                    } else {
                        //唯一性校验
                        if (newField.isAnnotationPresent(Unique.class)) {
                            Integer count = m.get(convert);
                            m.put(convert, (count == null) ? 1 : count + 1);
                            if (m.get(convert) != null && m.get(convert) > 1) {
                                listfail.add("第" + i + "行的" + entry.getKey() + "列数据值有重复:" + convert + "，请自行检查");
                            }
                        }
                    }
                }
            }
            listsuccess.add(t);
        }
        if(listfail.size()!=0){
            result.setData(listfail);
            result.setSize(listfail.size());
            result.setCode(false);
        }
        else {
            result.setData(listsuccess);
            result.setSize(listsuccess.size());
            result.setCode(true);
        }
        return result;
    }


    public static String convertRate2Decimal(Object rate) {
        if (rate == null) {
            return null;
        }
        BigDecimal bd = Convert.toBigDecimal(rate);
        if (bd == null) {
            return Convert.toStr(rate);
        }
        return bd.stripTrailingZeros().toPlainString();
    }

    /**
     * Object转List
     * @author suyuan
     * @date 2021/6/8 9:32
     */
    public static <T> List<T> castList(Object obj, Class<T> clazz)
    {
        List<T> result = new ArrayList<T>();
        if(obj instanceof List<?>)
        {
            for (Object o : (List<?>) obj)
            {
                result.add(clazz.cast(o));
            }
            return result;
        }
        return null;
    }

}