Java解析excel的通用方法--基础版

 /**
     *解析excel
     * @param multipartFile 文件流
     * @param webAPP 项目webapp路径
     * @param t 指返回的集合中的entity的类型
     * @param jsonResult 结果信息
     * @param dateFormate 日期格式
     * @param file 配置文件对象
     * @return 解析集合
     * @throws Exception
     */
    public  List<T> resolveExcel(MultipartFile multipartFile, String webAPP, T t, JsonResult jsonResult,String dateFormate,File file) throws Exception {
        List<T> list = new ArrayList<>();
        //1.创建Reader对象
        SAXReader reader = new SAXReader();
        //2.加载xml
        Document document = reader.read(file);
        //获得根元素，entitys
        Element rootElement = document.getRootElement();
        //获得根元素的子元素，entity
        List<Element> elements = rootElement.elements();
        if(elements.size() == 1 &&"entity".equals( elements.get(0).getName())){
            //获得entity的子元素 column
            List<Element> elementList = elements.get(0).elements();
            //本次测试用的文件路径
            String excelPath = "F:\\demo.xls";
            //创建这个文件
            File excel = new File(excelPath);
            //以下注释为实际项目时的如何创建从前端接收的文件
//            String upload = webAPP+"//upload//"+multipartFile.getOriginalFilename();
//            File file = new File(upload);
//            File excel = new FileUploadUtil(file,multipartFile).uploadFile(false);
            //判断文件是否存在
            if(excel.isFile() && excel.exists()){
                String[] split =excel.getName().split("\\.");
                Workbook wb = null;
                if("xls".equals(split[1])){
                    FileInputStream fis = new FileInputStream(excel);
                    wb  = new HSSFWorkbook(fis);
                }else if("xlsx".equals(split[1])){
                    wb = new HSSFWorkbook(POIFSFileSystem.create(excel));
                }else {
                    jsonResult.setFailReason("文件格式错误");
                    return null;
                }
                Sheet sheet = wb.getSheetAt(0);
                //第一行一般是文字，所以不读取第一行
                int fistRowNumber = sheet.getFirstRowNum()+1;
                //得到最后一行
                int lastRowNumber = sheet.getLastRowNum();
                //遍历所有行
                for(int rIndex = fistRowNumber;rIndex<=lastRowNumber;rIndex++){
                    //实例化一个entity用来存储遍历excel得到的数据
                    Class classes = (Class<T>) t.getClass();
                    t = (T) classes.newInstance();
                    //获得行
                    Row row =sheet.getRow(rIndex);
                    if(row != null){
                        int cel = 0;
                        while (true){
                            //判断第几行第几列的数据是否为空
                            if(row.getCell(cel) != null){
                                //遍历xml中的column中的标签
                                for(Element element :elementList){
                                    //如果对应的cel和当前正在解析的列 相等
                                    if(Integer.parseInt(element.attributeValue("cel"))-1==cel){
                                        //得到这个类的全部属性

                                        Field [] fields = classes.getDeclaredFields();
                                        //遍历属性
                                        for(Field field : fields){
                                            //如果cel对应的value存在该entity中 就赋值
                                            if(element.attributeValue("value").equals(field.getName())){
                                                field.setAccessible(true);
                                                //将值赋到entity中
                                                conversionType(field,row.getCell(cel).toString(),t,dateFormate);
                                            }
                                        }
                                    }
                                }
                                //列数加一
                                cel++;
                                //如果当前列为空并且已经大于最后一列
                            }else if(cel >= row.getLastCellNum()){
                                //将entity加到list中
                                list.add(t);
                                break;
                            }
                        }

                    }
                }
            }
        }
        return list;
    }

    /**
     * 判断entity属性是什么类型(只能判断基本类型)
     * @param field 当前属性
     * @param value 当前属性的值
     * @param t 所在的entity
     * @param dateFormat 如果有日期类型所对应的日期格式
     * @throws Exception
     */
    public void conversionType(Field field,String value,T t,String dateFormat) throws Exception {
        //或得到该属性的类型
        Type genericType = field.getGenericType();
        if(genericType.toString().endsWith("String")){
            field.set(t,value);
        }else if(genericType.toString().endsWith("int")||"Integer".endsWith(genericType.toString())){
            field.set(t,Integer.parseInt(value));
        }else if("Long".endsWith(genericType.toString()) || "long".endsWith(genericType.toString())){
            field.set(t,new Long(value));
        }else if("char".endsWith(genericType.toString())){
            field.set(t,value.charAt(0));
        }else if("boolean".endsWith(genericType.toString()) || "Boolean".endsWith(genericType.toString())){
            field.set(t,Boolean.valueOf(value));
        }else if("double".endsWith(genericType.toString()) || "Double".endsWith(genericType.toString())){
            field.set(t,Double.valueOf(value));
        }else if("float".endsWith(genericType.toString()) || "Float".endsWith(genericType.toString())){
            field.set(t,Float.valueOf(value));
        }else if("java.util.Date".equals(genericType.getTypeName())){
            field.set(t,new SimpleDateFormat(dateFormat).parse(value));
        }

    }

然后我们创建一个excel表格

public static void main(String[] args) {
    try {
        //根据配置文件位置创建文件
        File file = new File("src/main/resource/ExcelUtils.xml");
        /**
         * 调用我们所写的方法
         * 第一个参数:前端传入的文件流，用的本地文件测试因此是null
         * 第二个参数：本地测试，所以也没有文件在项目中对应的路径
         * 第三个参数：转换之后对应的实体类
         * 第四个参数：存储错误信息类
         * 第五个参数：如果有日期格式的解析对应的日期格式
         * 第六个参数：创建的xml配置文件
         */
         List<User> users=  new ExcelUtils().resolveExcel(null,"",new User(),new JsonResult(),"yyyy.MM.dd",file);
    for(User user :users){
        System.out.print("username:"+user.getUsername()+"\t");
        System.out.print("password:"+user.getPassword()+"\t");
        System.out.print("startTime:"+user.getStartTime()+"\r\n");
        System.out.println("================================================");
    }
    } catch (Exception e) {
        e.printStackTrace();
    }
}