//导入 FileInputStream inputStream =new FileInputStream("D:\\资产管理.xls"); HSSFWorkbook workbook =new HSSFWorkbook(inputStream); //需要转换的字段与转换数据建立关联关系 Map<String,Map<String,String>> changeMap = new HashMap();//(很重要,主要就是修改前台需要的键) //资产类型 List<Map> typelist = selectDataService.queryForList("select * from t_assettype"); Map<String, String> typemap = new HashMap<String, String>(); for (Map map : typelist) { typemap.put(map.get("NAME").toString(), map.get("ID").toString()); } //查出资产类型的修改键位 changeMap.put("typeid", typemap); //厂家 List<Map> factorylist = selectDataService.queryForList("select * from T_FACTORY"); Map<String, String> factorymap = new HashMap<String, String>(); for (Map map : factorylist) { factorymap.put(map.get("NAME").toString(), map.get("ID").toString()); } changeMap.put("factory", factorymap); //用户 List<Map> userlist = selectDataService.queryForList("select * from t_user"); Map<String, String> usermap = new HashMap<String, String>(); for (Map map : userlist) { usermap.put(map.get("REALNAME").toString(), map.get("ID").toString()); } changeMap.put("userid", usermap); //将中文字段与英文字段建立关系 List<Map> list = selectDataService.queryForList("select COLUMN_NAME,COMMENTS from user_col_comments where table_name='T_ASSET' AND comments is not null"); Map<String, String> colmap = new HashMap<String, String>(); String sql =" insert into t_asset ("; String colSql =""; for (Map map : list) { colmap.put(map.get("COMMENTS").toString(), map.get("COLUMN_NAME").toString()); colSql+=","+map.get("COLUMN_NAME").toString(); } colSql =colSql.substring(1); colSql+=",id) values( "; //获取sheet数量 int sheetNum =workbook.getNumberOfSheets(); //循环读取sheet for (int i = 0; i < sheetNum; i++) { HSSFSheet sheet =workbook.getSheetAt(i); //获取有效行数 int rownum =sheet.getLastRowNum(); //中文字段名称集合 List cnColList = new ArrayList(); for (int j = 1; j <=rownum; j++) { HSSFRow row = sheet.getRow(j); String valueSql =""; if(j==1){ //解析字段信息 for (int j2 = 0; j2 < row.getLastCellNum(); j2++) { cnColList.add(row.getCell(j2).getStringCellValue()); } }else{ //解析数据行 for (int k = 0; k <cnColList.size(); k++) { //获取英文字段 String encol =colmap.get(cnColList.get(k)); //获取数据值 String cellVal =row.getCell(k).getStringCellValue(); if(changeMap.get(encol.toLowerCase())!=null){ cellVal =changeMap.get(encol.toLowerCase()).get(cellVal).toString(); } if(cnColList.get(k).equals("购买日期")){ cellVal="to_date('"+cellVal+"','yyyy-MM-dd')"; valueSql+=","+cellVal; }else{ valueSql+=",'"+cellVal+"'"; } } valueSql=valueSql.substring(1); valueSql+=",'"+WebUtils.getRandomId()+"')"; System.out.println(sql+colSql+valueSql); assetService.saveAA(sql+colSql+valueSql); } } } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; }