POI导入Excel文档

使用POI导入Excel文档

简介

实现

1. 导入依赖

	<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

2. 输入文件路径，获取文件对象

	Scanner sc = new Scanner(System.in);
	String path = sc.next();
	File file = new File(path);

3. 导入文件，判断文件是否是Excel文件

	String fileName = file.getName();
	//判断文件名结尾是否是指定的文件类型
    if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")){
          System.out.println("文件格式错误");
          return;
	}

4. 读取Excel文件，获取数据集合

	//参数Excel文件和对应的数据实体类
	ExcelReader.readExcel(file, User.class);
	//自定义工具类，获取读取成功的数据
    List<User> successList = ExcelReader.getSuccessList();

工具类源码（4.X版本需要改变Cell的数据类型）：

/**
 * @author zzh
 * @version 1.0
 * @date 2019/12/17 11:37
 */
public class ReaderExcelUtils {

    private static Logger logger = Logger.getLogger(ReaderExcelUtils.class);

    //线程不安全的工作簿对象
    private static Workbook workbook = null;

    //线程不安全的成功数据集合
    private static List<Object> successList = null;

    //线程不安全的失败数据集合
    private static List<Object> failList = null;

    //线程安全的成功数据集合
    private static volatile List<Object> successListOfThread = null;

    //线程安全的失败数据集合
    private static volatile List<Object> failListOfThread = null;

    //过滤器管理
    private static FilterManager filterManager = new FilterManager(new FilterChain());

    private ReaderExcelUtils(){}

    //获取导入成功数据集合
    public static <T> List<T> getSuccessList() {
        return (List<T>) successList;
    }

    //获取导入失败数据集合
    public static <T> List<T> getFailList() {
        return (List<T>) failList;
    }

    /**
     * 判断文件是否不为null，不为null返回true，null抛出异常
     * @param file
     * @return
     * @throws FileNotFoundException
     */
    private static boolean isNotEmpty(File file) throws FileNotFoundException {
        if (file == null){
            throw new FileNotFoundException();
        }
        return true;
    }

    /**
     * 获取文件类型
     * @param file
     * @return
     */
    private static String getFileType(File file){
        String name = file.getName();
        //获取文件类型
        String fileType = name.substring(name.lastIndexOf(".") + 1);
        return fileType;
    }

    /**
     * 获取线程不安全的工作簿对象，工作效率高
     * @return
     */
    public static Workbook getWorkbook(File file) throws IOException {
        isNotEmpty(file);
        String fileType = getFileType(file);
        //获取Excel文件的输入流，将文件内容输入到内存中
        InputStream inputStream = new FileInputStream(file);
        if (fileType != null && fileType.equalsIgnoreCase("xls")){
            if (workbook == null){
                workbook = new HSSFWorkbook(inputStream);
            }
        } else if (fileType != null && fileType.equalsIgnoreCase("xlsx")){
            if (workbook == null){
                workbook = new XSSFWorkbook(inputStream);
            }
        }
        return workbook;
    }

    /**
     * 获取线程安全的工作簿对象，效率较低
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbookOfThread(File file) throws IOException {
        isNotEmpty(file);
        String fileType = getFileType(file);
        //获取Excel文件的输入流，将文件内容输入到内存中
        InputStream inputStream = new FileInputStream(file);
        if (fileType != null && fileType.equalsIgnoreCase("xls")){
            if(workbook == null){
                synchronized (ReaderExcelUtils.class){
                    if (workbook == null){
                        workbook = new HSSFWorkbook(inputStream);
                    }
                }
            }
        } else if(fileType != null && fileType.equalsIgnoreCase("xlsx")){
            if(workbook == null){
                synchronized (ReaderExcelUtils.class){
                    if (workbook == null){
                        workbook = new XSSFWorkbook(inputStream);
                    }
                }
            }
        }
        return workbook;
    }

    public static Workbook getWorkbookOfThread(Boolean bool, File file) throws IOException {
        if(bool == true){
            return getWorkbookOfThread(file);
        }else {
            return getWorkbook(file);
        }
    }

    /**
     * 创建成功数据的集合
     * @param bool 是否需要支持线程安全
     * @param <T>
     * @return
     */
    public static <T> List<T> createSuccessListOfThread(Boolean bool){
        if (bool == true){
            if (successListOfThread == null){
                synchronized (ReaderExcelUtils.class){
                    if (successListOfThread == null){
                        successListOfThread = new ArrayList<>();
                        return (List<T>) successListOfThread;
                    }
                }
            }
        } else{
            successList = new ArrayList<>();
            return (List<T>) successList;
        }
        return null;
    }

    /**
     * 创建用来存放失败数据集合
     * @param bool 是否需要支持线程安全
     * @param <T>
     * @return
     */
    public static <T> List<T> createFailListOfThread(Boolean bool){
        if (bool == true){
            if (failListOfThread == null){
                synchronized (ReaderExcelUtils.class){
                    if (failListOfThread == null){
                        failListOfThread = new ArrayList<>();
                        return (List<T>) failListOfThread;
                    }
                }
            }
        } else{
            failList = new ArrayList<>();
            return (List<T>) failList;
        }
        return null;
    }

    /**
     * 读取Excel文件内容
     * @param file 文件对象
     * @param <T> Excel文件对象对应的实体类
     */
    public static <T> void readExcelOfThread(Boolean threadBool, File file, Class<T> clazz, ExcelDataFilter filter) throws Exception {
        isNotEmpty(file);
        Workbook workbook = getWorkbookOfThread(threadBool, file);
        T t = null;
        List<Object> failDataList = createFailListOfThread(threadBool);
        List<Object> successDataList = createSuccessListOfThread(threadBool);
        //遍历sheet页数
        for (int i = 0; i < workbook.getNumberOfSheets(); i ++){
            //获取一个sheet
            Sheet sheet = workbook.getSheetAt(i);
            //获取第一行的数据，表头信息数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (firstRow == null){
                throw new ParsingException("解析Excel数据异常");
            }
            //文档最后一行行号
            int endRowNum = sheet.getPhysicalNumberOfRows();
            //数据开始的行号
            int startRowNum = firstRowNum + 1;
            for(int j = startRowNum; j < endRowNum; j ++){
                //获取sheet页一行的数据
                Row row = sheet.getRow(j);
                HashMap<String, Object> data = paresRowData(sheet, row, clazz, filter);
                Boolean bool = (Boolean) data.get("status");
                Object obj = data.get("data");
                if (bool == true){
                    //判断是否有过滤器
                    successDataList.add(obj);
                }else {
                    failDataList.add(obj);
                }
            }
        }
        if (threadBool == true){
            successListOfThread = successDataList;
            failListOfThread = failDataList;
        }else{
            successList = successDataList;
            failList = failDataList;
        }
    }

    /**
     * 解析Excel中一行的数据
     * 此版本功能不够完善仅支持实体类属性与Excel文档数据顺序一一对应
     * @param row
     * @return
     */
    public static <T> HashMap<String, Object>  paresRowData(Sheet sheet, Row row, Class<T> clazz, ExcelDataFilter filter) throws IllegalAccessException, InstantiationException, InvocationTargetException {
        Object obj = clazz.newInstance();
        HashMap<String, Object> map = new HashMap<>();
        map.put("status", true);
        Cell cell = null;
        //获取实体类所有属性
        Field[] fields = clazz.getDeclaredFields();
        ArrayList<Field> list = new ArrayList<>();
        //将有注解的字段放到集合中
        for (Field field : fields) {
            if(field.isAnnotationPresent(EntityExcel.class)){
                list.add(field);
            }
        }
        //获取实体类所有方法
        Method[] methods = clazz.getDeclaredMethods();
        //获取属性个数
        int size = list.size();
        Row head = sheet.getRow(0);
        for (int i = 0; i < head.getLastCellNum(); i++) {
            Cell cell1 = head.getCell(i);
            //获取第一列的名称
            String cellName = parseCellDataToString(cell1);
            //遍历属性寻找对应的属性字段
            for (int j = 0; j < size; j ++){
                Field field = list.get(j);
                if (cellName.equals(field.getAnnotation(EntityExcel.class).name())){
                    //如果当前数据已有错误，直接跳过这个对象
                    if (map.containsKey("message")){
                        break;
                    }
                    //防止对下个对象有影响
                    map.remove("message");
                    //获取操作一个单元格，这里的单元格所在的列要与head单元格对应
                    cell = row.getCell(i);
                    //获取单元格的值
                    Object value = parseCellDataToString(cell);
                    String valueStr = value + "";
                    //获取属性名
                    String str = list.get(j).getName();
                    //将单元格的值转换为指定数据类型
                    Class<?> type = list.get(j).getType();
                    //获取首字母大写其他字母小写的属性名
                    String fieldName = str.substring(0,1).toUpperCase().concat(str.substring(1).toLowerCase());
                    //获取方法名
                    String methodName = "set" + fieldName;
                    if (type == Byte.class){
                        try {
                            value = Byte.parseByte(valueStr);
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Integer.class){
                        try {
                            value = Integer.parseInt(valueStr);
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Long.class){
                        try {
                            value = Long.parseLong(valueStr);
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Short.class){
                        try {
                            value = Short.parseShort(valueStr);
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Float.class){
                        try {
                            value = Float.parseFloat(valueStr);
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Double.class){
                        try{
                            value = Double.parseDouble(valueStr);
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Boolean.class){
                        try{
                            if ("是".equals(valueStr) || "true".equalsIgnoreCase(valueStr) || "yes".equalsIgnoreCase(valueStr) || "1".equals(valueStr)){
                                value = true;
                            }else {
                                value = false;
                            }
                        } catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }else if (type == Character[].class){
                        try{
                            value = valueStr.toCharArray();
                        }catch (Exception e){
                            map.put("status", false);
                            map.put("message", "数据类型错误");
                            value = null;
                        }
                    }

                    /**
                     * 过滤器对数据进行过滤
                     * 如果过滤器抛出异常，说明数据有误，添加错误信息，并将该数据添加到失败数据集合
                     * 若无异常，则将数据添加到成功数据集合
                     */
                    if (filter != null){
                        filterManager.setFilter(filter);
                        try {
                            filterManager.executeRowExcelData(obj);
                        } catch (Exception e){
                            map.put("message", e.getMessage());
                            map.put("status", false);
                        }
                    }
                    if (map.containsKey("message")){
                        for (Method method : methods) {
                            if (method.getName().equals("setErrorMsg")){
                                method.invoke(obj, map.get("message"));
                                break;
                            }
                        }
                        continue;
                    }
                    for (Method method : methods) {
                        if (method.getName().equals(methodName)){
                            method.invoke(obj, value);
                            break;
                        }
                    }
                    break;
                }
            }
        }
        map.remove("message");
        map.put("data", obj);
        return map;
    }

    /**
     * 处理每一个单元格数据，将数据转换为字符串类型
     * @param cell
     * @return
     */
    public static String parseCellDataToString(Cell cell){
        //如果单元格为null就直接返回null
        if (cell == null){
            return null;
        }
        String value = null;
        //将单元格数据转换为字符串类型
        switch (cell.getCellType()){
                //数字类型
            case Cell.CELL_TYPE_NUMERIC:
                double numericCellValue = cell.getNumericCellValue();
                value = new DecimalFormat("0").format(numericCellValue);
                break;
                //字符类型
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
                //布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
                //公式
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
                //空值
            case Cell.CELL_TYPE_BLANK:
                break;
                //错误
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        return value;
    }

}