导入excel工具类

package com.smec.mts.core.utils;


import com.smec.mts.core.annotation.FileAttributes;
import com.smec.mts.core.annotation.FileProperty;
import com.smec.mts.core.exception.BizException;
import com.smec.mts.modular.pojo.model.DiagCode;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static java.util.regex.Pattern.*;

/**
 * @author psp
 * @description 解析文件工具类
 * @date 2020/3/26
 */
public class ParsingFileUtils {
    private ParsingFileUtils(){}
    //文件后缀
    private static final String FILE_CONTENT_TYPE_XLSX="xlsx";
    private static final String FILE_CONTENT_TYPE_XLS="xls";
    private static Logger logger= LoggerFactory.getLogger(ParsingFileUtils.class);
    /**
     * 将Excel文件转换成list集合
     * @param in 文件输入流
     * @param suffix 文件后缀
     * @param clazz  文件转换对象类型
     * @return list集合
     * @throws IOException 流加载异常
     * @throws IllegalAccessException 非法参数
     * @throws InstantiationException 实例化异常
     */

    public static List<Object> parsingFileXls(InputStream in,String suffix,Class clazz) throws IOException, IllegalAccessException, InstantiationException {
        //获得所有属性对象
        Field[] fields = clazz.getDeclaredFields();
        ConcurrentHashMap<String,Integer> titlesMap=new ConcurrentHashMap<>(8);
        Workbook book;
        try {
            //判断文件类型，进行对象构造
            if (FILE_CONTENT_TYPE_XLSX.equals(suffix)) {
                book = new XSSFWorkbook(in);
            } else if (FILE_CONTENT_TYPE_XLS.equals(suffix)) {
                book = new HSSFWorkbook(in);
            }else {
                throw new IllegalArgumentException("file type error!");
            }
        }catch (IOException e){
            logger.error(e.getLocalizedMessage());
            throw e;
        }
        //此处获取第一个sheet页进行操作，如需要多sheet页操作，将其变成for循环
        Sheet sheet =book.getSheetAt(0);
        //从类中属性注解进行字段-标题映射
        for (Field field:fields){
            //field 代表类的成员变量
            // getAnnotation(Class<A> annotationClass)返回该类中与参数类型匹配的公有注解对象
            FileAttributes fileAttributes = field.getAnnotation(FileAttributes.class);
            if (fileAttributes==null){
                continue;
            }
            titlesMap.put(fileAttributes.value(),-1);
        }
        //将字段对应标题所述的列进行映射   获取第一行标题
        mappingColumns(titlesMap,sheet.getRow(sheet.getFirstRowNum()));
        checkTemplate(titlesMap);
        List<Object> result =new ArrayList<>(8);
        //遍历所有行，进行添加值，此处优化可改为多线程操作
        for (int i=sheet.getFirstRowNum()+1;i<sheet.getLastRowNum()+1;i++){
            //获取行数
            Row row = sheet.getRow(i);
            //创建类的实例
            Object target = clazz.newInstance();
            //解析每一列，并将列属性给对象赋值
            if (row!=null) {
                parsingRow(titlesMap, row, fields, target);
            }
            if(target!=null) {
                result.add(target);
            }
        }
        return result;
    }

    /**
     * description:
     * @param titleMap 列与字段映射集合
     * @param row 列标题
     * @author psp
     * @date 2020/3/26 23:13
     */
    private static void mappingColumns(ConcurrentHashMap<String,Integer> titleMap, Row row){
        if(row==null){
            throw new BizException("44002","文档为空！");
        }
        for (int i=row.getFirstCellNum();i<row.getLastCellNum();i++){
            //获取当前单元格
            Cell cell = row.getCell(i);
            Integer integer = titleMap.get(cell.getStringCellValue());
            if (integer != null){
                //重新赋值 key(标题) 的value会被重新覆盖 之前都是-1
                titleMap.put(cell.getStringCellValue(),i);
            }
        }

    }

    private static void checkTemplate(ConcurrentHashMap<String,Integer> titleMap){
        for (Map.Entry<String,Integer> entry:titleMap.entrySet()){
            if (entry.getValue()==-1){
                throw new BizException("44001","我们模板中含有标题:"+entry.getKey()+",但是这个文档中没有！");
            }
        }

    }

    /**
     *
     * @param titleMap 列与字段映射集合
     * @param row 每一行数据
     * @param fields 目标对象属性集合
     * @param target 目标对象
     * @throws IllegalArgumentException 非法参数异常
     * @throws IllegalAccessException   Excel无法访问
     */
    private static void parsingRow(ConcurrentHashMap<String,Integer> titleMap, Row row, Field[] fields, Object target) throws IllegalArgumentException, IllegalAccessException {

        for (Field field:fields){
            //field 代表类的成员变量
            FileAttributes fieldAnnotation = field.getAnnotation(FileAttributes.class);
            if (fieldAnnotation == null){
                continue;
            }
            //作用就是让我们在用反射时 可以访问private修饰的私有属性
            field.setAccessible(true);
            if (!StringUtils.isEmpty(fieldAnnotation.defualtValue())){
                field.set(target,fieldAnnotation.defualtValue());
            }
            //通过标题名拿到对应标题的value索引值
            Integer integer = titleMap.get(fieldAnnotation.value());
            if (integer==-1){
                if (!fieldAnnotation.isNull()&&StringUtils.isEmpty(fieldAnnotation.defualtValue())){
                    throw new BizException("44005","we need  "+fieldAnnotation.value()+" ,but the excel doesn't!");
                }
                continue;
            }
            //获取标题名这一列值
            Cell cell = row.getCell(integer);
            //o 接受的是匹配类型后的单元格内容
            Object o = parsingCell(cell, fieldAnnotation);
            if (o instanceof  String){
                if (((String) o).length()>fieldAnnotation.length()){
                    throw new BizException("44003","the （" + row.getRowNum() + "," + integer + "）cell is to long");
                }
            }
            if (o==null&&!fieldAnnotation.isNull()) {
                throw new BizException("44003","the （" + row.getRowNum() + "," + integer + "）cell Not allowed to be empty");
            }
            if (fieldAnnotation.isBatch()&& o instanceof String){
                if (fieldAnnotation.isCollection()) {
                    o = getCollectionProperty(fieldAnnotation.spilt(), o);
                }else {
                    o = getArrayProperty(fieldAnnotation.spilt(),o);
                }
            }
            if (fieldAnnotation.hasMap()&&o instanceof String){
               o= mappingProperty(fieldAnnotation,o);
            }
            field.set(target,o);
        }
    }

    /**
     *
     * @param cell 单元格
     * @param fileAttributes 注解类型
     * @return 注解中type属性类型
     */
    private static Object parsingCell(Cell cell, FileAttributes fileAttributes){
        if (cell==null){
            return null;
        }
        Class type = fileAttributes.type();
        if (type ==Date.class){
            return cell.getDateCellValue();
        }else if(type== Boolean.class){
            return cell.getBooleanCellValue();
        }else if(type==Double.class){
            return cell.getNumericCellValue();
        }else if(type== RichTextString.class){
            return cell.getRichStringCellValue();
        }else if(type==Integer.class){
            String stringCellValue = cell.getStringCellValue();
            return Integer.parseInt(stringCellValue);
        } else{
            try {
                return cell.getStringCellValue();
            }catch (Exception e){
                if (cell.getCellType()==CellType.NUMERIC) {
                    BigDecimal bigDecimal= BigDecimal.valueOf(cell.getNumericCellValue());
                    return bigDecimal.toPlainString();
                }
                return "";
            }
        }
    }

    /**
     *  由于存在一个字段的列内容与数据存储的 数据有一定的映射关系，该方法就是对于解析单元格内容与数据库存储数据的映射关系
     * @param fileAttributes 注解属性
     * @param o 单元格内容
     * @return 映射后的数据库存储值
     */
    private static String mappingProperty(FileAttributes fileAttributes,Object o){
        String str= (String) o;
        for (FileProperty fileProperty:fileAttributes.propertyList()){
            if (fileProperty.key().equals(str)){
                return fileProperty.value();
            }
        }
        return str;
    }

    private static List getCollectionProperty(String spilt,Object o){
        String str= (String) o;
        if (StringUtils.isEmpty(str)){
            return Collections.emptyList();
        }
        str = str.replaceAll("：", ":").replaceAll("，", ",");
        str=replaceBlank(str);
        String[] split = str.split(spilt);
        return Arrays.asList(split);
    }

    private static String[] getArrayProperty(String spilt,Object o){
        String str= (String) o;
        if (StringUtils.isEmpty(str)){
            return new String[0];
        }
        str = str.replaceAll("：", ":").replaceAll("，", ",");
        str=replaceBlank(str);
        return str.split(spilt);
    }
    /**
     * 去除空格之类的字符
     * @param str 源字符
     * @return 去除空格，回车，换行符之后的字符串
     */
    public static String replaceBlank(String str) {
        String dest = "";
        if (str!=null) {
            final Pattern p = compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }

}