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，使用poiSystem.out.println("poi");// SCHOOL_COUNT，因为1String[] 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();// 如果还是文件夹 递归获取里面的文件 文件夹// 创建输入流，读取ExcelInputStream is = new FileInputStream(files[f].getAbsolutePath());// 读取xlsxxssfWorkbook = new XSSFWorkbook(is);// 循环获取第三到第七个sheetfor (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){// 项目总资金(万元）金额必须大于0if(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.生成uuidmap.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;// 哪个文件哪个sheetcommits = 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 exceptione.printStackTrace();System.out.println("已经运行的文件为：" + hasRun);System.out.println("当前运行的文件为：" + nowRun);}}
2.使用jxl读取
/*** 从年报excel表数据导入到数据库中，表：nb_xx_* */public void insertnianbao(){try {String sql = "";File file = new File("D:/000.xls");// 创建输入流，读取ExcelInputStream is = new FileInputStream(file.getAbsolutePath());// jxl提供的Workbook类Workbook wb = Workbook.getWorkbook(is);// Excel的页签数量int sheet_size = wb.getNumberOfSheets();// 每个页签创建一个Sheet对象--------对应是第几个sheetSheet 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_ VALUES ("+ str +")";this.exeSql(sql);System.out.println(sql);}} catch (Exception e) {// TODO: handle exceptionSystem.out.println(e);}}
