循环读取目录下的文件，使用poi读取excel的xlsx，使用jxl读取xls文件

1.使用poi读取

/**
	 * 从年报excel表数据导入到base_target_school_kpi数据库中，表：base_target_school_kpi
	 * 
	 */
	
	public void insertBaseTargetSchoolKpi(){
		String hasRun = new String();
		String nowRun = new String();
		try {
			// 因为jxl不支持xlsx，使用poi
			System.out.println("poi");
			// SCHOOL_COUNT，因为1
			String[] c = new String[]{"TOTAL_PROJECT_FUNDS","SCHOOL_COUNT","CENTRE_LOCAL_MONEY_ADD_UP","CENTRE_MONEY_ADD_UP","PROVINCE_GAIBO_MONEY","CITY_GAIBO_MONEY","COUNTY_MONEY_ADD_UP","XS_MONEY_ADD_UP","MONEY_PERCENT_XS_IN_TOTAL","XS_01_MONEY_ADD_UP","XS_01_XSMJ","XS_01_01_JXJFZYFMJ","XS_01_01_01_PTJSMJ","XS_01_01_02_SYSMJ","XS_01_01_03_TSSMJ","XS_01_01_04_JSJJSMJ","XS_01_01_05_DGNJSMJ","XS_01_01_06_YYJSMJ","XS_01_01_07_MSJSMJ","XS_01_01_10_QTMJ","XS_01_02_SHYFMJ","XS_01_02_01_XSSSMJ","XS_01_02_02_STMJ","XS_01_02_02_01_XSCTMJ","XS_01_02_03_CSMJ","XS_01_02_04_GLFMJ","XS_01_02_05_YSMJ","XS_01_02_06_JGSSMJ","XS_01_02_07_SHYFQTMJ","XS_01_03_XZYFMJ","XS_01_03_01_JSBGSMJ","XS_01_03_02_XZBGSMJ","XS_01_03_03_WYDASMJ","XS_01_03_04_AFJKSMJ","XS_01_03_05_WSBJSMJ","XS_01_03_06_CDZSSMJ","XS_01_03_07_XZYFQTMJ","YDCD_MONEY_ADD_UP","YDCD_SQUARE_ADD_UP","FSSS_MONEY_ADD_UP","FSSS_02_WQCD","FSSS_03_HPTJ","FSSS_04_XYYHMJ","FSSS_06_GLSL","FFSS_OTHER_MONEY","EQ_TOTAL_MONEY","SHSS_MONEY_ADD_UP","SHSS_01_MONEY_XSYC","SHSS_01_XSYCSL","SHSS_02_MONEY_STSB","SHSS_02_STSBSL","SHSS_03_MONEY_YSSS","SHSS_03_YSSSSL","SHSS_04_MONEY_ABSB","SHSS_04_ABSBSL","SHSS_06_MONEY_CNSS","SHSS_06_SSCNSSSL","SHSS_07_MONEY_SHSSQT","SHSS_07_SHSSQTSL","TS_01_MONEY_TS","TS_01_TSSL","KZY_01_MONEY_KZY","KZY_01_KZYSL","XXH_SYYQ_YYM_QTSB_MONEY_ADD_UP","XXH_MONEY_JSJ","XXH_06_JSJSJSL","XXH_MONEY_DMT","XXH_03_WLDMTJSSL","XXH_04_WLJSJJSSL","SYYQ_MONEY_ADD_UP","SYYQ_AMOUNT_ADD_UP","SYYQ_01_MONEY_ADD_UP","SYYQ_01_XXSXKXSYYQSL","SYYQ_03_MONEY_ADD_UP","SYYQ_03_CZWLYQSL","SYYQ_04_MONEY_ADD_UP","SYYQ_04_CZHXYQSL","SYYQ_05_MONEY_ADD_UP","SYYQ_05_CZSWYQSL","SYYQ_06_MONEY_ADD_UP","SYYQ_06_CZDLYQSL","SYYQ_02_MONEY_ADD_UP","SYYQ_02_CZSXYQSL","YTM_MONEY_ADD_UP","YTM_AMOUNT_ADD_UP","YTM_01_MONEY_ADD_UP","YTM_01_YYQCSL","YTM_02_MONEY_ADD_UP","YTM_02_TYQCSL","YTM_03_MONEY_ADD_UP","YTM_03_MSQCSL","QT_01_MONEY_ADD_UP","QT_01_QTSBSL","COMMENTS"};
			
			String sql = "";
			File file = new File("D:/work/文件接收/gb_mis/nb/test");
			XSSFWorkbook xssfWorkbook = null;
			if (file.isDirectory()) {
	            // 获取路径下的所有文件
	            File[] files = file.listFiles();
	            for (int f = 0; f < files.length; f++) {
	            	// 当前执行的文件是：
	            	System.out.println("当前执行的文件是：" + files[f].getName());
	            	hasRun = hasRun + files[f].getName();
	            	nowRun = files[f].getName();
	                // 如果还是文件夹 递归获取里面的文件 文件夹
	            	// 创建输入流，读取Excel
	                InputStream is = new FileInputStream(files[f].getAbsolutePath());
	                // 读取xlsx
	                xssfWorkbook = new XSSFWorkbook(is);
	                // 循环获取第三到第七个sheet
	                for (int numSheet = 2; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
	                    XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
	                    if(xssfSheet == null){
	                    	continue;
	                    }
	                    // 对于每个sheet，从第9行开始读取
	                    Map<String,Object> map = new HashMap<>();
	                    for (int rowNum = 9; rowNum < xssfSheet.getLastRowNum(); rowNum++) {
	                        XSSFRow xssfRow = xssfSheet.getRow(rowNum);
	                        if(xssfRow == null){
		                    	continue;
		                    }
	                        map = new HashMap<>();
	                        // 从第一列开始读取
	                        String schoolName = "";
	                        // 判断是否修改过
	                        String hasChange = "N";
	                        for(int columnNum = 1 ; columnNum<96; columnNum++){
	                        	XSSFCell cell = xssfRow.getCell(columnNum);
//	                        	System.out.println(this.getValue(cell));
	                        	if(cell !=null){
	                        		// 每一行都设置成字符串
//	                        		cell.setCellType(Cell.CELL_TYPE_STRING);
//	                        		sqlBuilder.append(cell.getStringCellValue());
	                        		String v = this.getValue(cell);
	                        		if(StringUtils.isBlank(v)){
	                        			v = "0";
	                        		}
	                        		if(columnNum == 1){
	                        			// 第二列是学校名字
	                        			schoolName = v;
	                        			if(StringUtils.isBlank(schoolName)){
	                        				schoolName = "";
	                        			}
	                        		}else if(columnNum == 2){
	                        			// 项目总资金(万元）金额必须大于0
	                        			if(NumberUtils.isNumber(v)){
	                        				Double TOTAL_PROJECT_FUNDS = Double.valueOf(v);
	                        				if(TOTAL_PROJECT_FUNDS == null) {
	                        					TOTAL_PROJECT_FUNDS = 0.0;
	                        					if(TOTAL_PROJECT_FUNDS < 0.0) {
	                        						continue;
	                        					}
	                        				}
	                                	}else{
	                                		continue;
	                                	}
	                        		}else if(columnNum == 3){
	                        			// 是school_count  不保存
	                        			
	                        		}else if(columnNum == 95){
	                        			// 是备注，可以为字符串
	                        			map.put(c[columnNum-2], v);
	                        		}else{
	                        			// 不能为非数字
	                        			if(NumberUtils.isNumber(v)){
	                                	}else{
	                                		v = "0";
	                                		hasChange = "Y";
	                                	}
	                        			map.put(c[columnNum-2], v);
	                        			
	                        		}
	                        	}
	                        }
	                        // 学校名不能为空
	                        if(StringUtils.isBlank(schoolName)){
	                        	continue;
	                        }
	                        if("null".equals(schoolName)) {
	                        	continue;
	                        }
	                        
	                        
	                        
	                        
	                        // 进行保存数据库操作base_target_school_kpi
	                        // 1.生成uuid
	                        map.put("TS_KPI_ID", UUID.randomUUID().toString());
	                        // 2.根据sheet的来判断是第几年
	                        map.put("T_YEAR", "year_" + (xssfSheet.getSheetName().substring(0,4)));
	                        // 3.重要数据放在comment里
	                        String commits = (String) map.get("COMMENTS");
	                        // 学校名字
	                        commits = commits + "///" + schoolName;
	                        // 哪个文件哪个sheet
	                        commits = commits + "///" + files[f].getName() + ";" + xssfSheet.getSheetName();
	                        // 是否修改的标记
	                        if("Y".equals(hasChange)){
	                        	commits = commits + "///" + "hasChange:" + hasChange;
	                        }
	                        map.put("COMMENTS", commits);
	                        // 4.通过学校名字匹配学校
	                        BaseSchool baseSchool = CacheBaseSchoolManager.getSchooNamel(StringUtils.trim(schoolName));
	                        if(baseSchool != null){
	                        	// 如果查到了学校就可以赋值了
	                        	map.put("SCHOOL_ID", baseSchool.getId());
	                        	map.put("COUNTY_ID", baseSchool.getCountyId());
	                        	map.put("CITY_ID", baseSchool.getCityId());
	                        	map.put("PROVINCE_ID", baseSchool.getProvinceId());
	                        }
	                        System.out.println(schoolName + map.toString());
	                        this.insertData(map, "base_target_school_kpi_test");
	                    }
	                }
	            }
	        } 
			
			
            } catch (Exception e) {
			// TODO: handle exception
            	e.printStackTrace();
            	System.out.println("已经运行的文件为："  + hasRun);
            	System.out.println("当前运行的文件为："  + nowRun);
		}
		
		
		
		
		
	}
	

2.使用jxl读取

/**
	 * 从年报excel表数据导入到数据库中，表：nb_xx_2017
	 * 
	 */
	
	public void insertnianbao(){
		try {
			String sql = "";
			
			File file = new File("D:/000.xls");
			 // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
                // 每个页签创建一个Sheet对象--------对应是第几个sheet
                Sheet sheet = wb.getSheet(3);
                // sheet.getRows()返回该页的总行数
                for (int i = 7; i < sheet.getRows(); i++) {
                	String uid = UUID.randomUUID().toString();
                	String str = " '"+ uid + "', ";
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo = sheet.getCell(j, i).getContents().trim();
                        str = str + "'" +  cellinfo + "',";
                    }
                    
                    //System.out.println(str);
                    str = str.substring(0,str.length()-1);
                    sql = "INSERT INTO NB_TJ_2018 VALUES ("+ str +")";
                    this.exeSql(sql);
                    System.out.println(sql);
                }
            } catch (Exception e) {
			// TODO: handle exception
            	System.out.println(e);
		}
		
		
		
		
		
	}