中间使用map作为中间处理
将类处理为excel:
1.读取类转为map
//读取btl,转为map public static Map getBtlMap(String rule, BTLDAO binFile) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { int i; Object om; Map map = null; List<Map<String, Object>> tempMaps; Map<String, Object> rsMap = new HashMap(); Object s; JSONObject row; {//遍历btl Field[] fields = binFile.getClass().getDeclaredFields();//Object是已经被赋值的对象实例 for (Field field : fields) { if (!field.isAccessible()) { field.setAccessible(true); } //如果是list类 if (List.class.isAssignableFrom(field.getType())) { Type t = field.getGenericType(); if (t instanceof ParameterizedType) { ParameterizedType pt = (ParameterizedType) t; Class clz = (Class) pt.getActualTypeArguments()[0];//得到对象list中实例的类型 if (field.get(binFile) != null) { Class clazz = field.get(binFile).getClass();//获取到属性的值的Class对象 Method m = clazz.getDeclaredMethod("size"); int size = (Integer) m.invoke(field.get(binFile));//调用list的size方法，得到list的长度 tempMaps = new ArrayList<Map<String, Object>>(); for (int i2 = 0; i2 < size; i2++) {//遍历list，调用get方法，获取list中的对象实例 Method getM = clazz.getDeclaredMethod("get", int.class); if (!getM.isAccessible()) { getM.setAccessible(true); s = getM.invoke(field.get(binFile), i2); map = ComUtil.getKeyAndValue(s); tempMaps.add(map); } } rsMap.put(field.getName(), tempMaps); } } } else {//否则为普通类 field.setAccessible(true); Object value = field.get(binFile); map = ComUtil.getKeyAndValue(value); rsMap.put(field.getName(), map); } } } {//检查类数量 String[] cutStrs = new String[] { "bm0", "bm1", "bm2", "bm3", "bm4", "bm5", "bm6", "bm7", "bm8", "bm9", "bm10", "bm11", "bm12", "bm13", "bm14", "bm15", "bm16", "bm17", "bm18", "bm19", "bm20" }; for (i = 0; i < cutStrs.length; i++) { row = getInfoByRootName(rule, cutStrs[i]); if (row != null && row.getBoolean("ifCycle")) { if (!(row.getString("Count").equals("one") && row.getString("Count").equals("sumGride"))) { map.put(row.getString("Count"), ComUtil.getArrayListCapacity((ArrayList<?>) rsMap.get(cutStrs[i]))); // System.out.println(row.getString("Count")+":"+":"+ComUtil.getArrayListCapacity((ArrayList<?>) rsMap.get(cutStrs[i]))); } } } } return rsMap; }
2.将map转为excel
//rule 规则 //map 类 //folder 文件夹 //fileName public static boolean writeBTLExcel(String folder, String fileName, String rule, Map map) { if (ComUtil.isEmpty(folder) || ComUtil.isEmpty(fileName)) { return false; } // 判断路径是否正确 File file = new File(folder); if (!file.isDirectory()) { return false; } // 设置文件后缀 if (!fileName.endsWith(".xls")) { fileName = fileName + ".xls"; } String[] cutStrs = new String[] { "bm0", "bm1", "bm2", "bm3", "bm4", "bm5", "bm6", "bm7", "bm8", "bm9", "bm10", "bm11", "bm12", "bm13", "bm14", "bm15", "bm16", "bm17", "bm18", "bm19", "bm20" }; JSONObject jsonRow; List<DefRule> rs; List<Map> list; Map m; HSSFSheet sheet; HSSFRow row, row1; List<String> data; HSSFPatriarch p; HSSFComment comment; // 第一步，创建一个workbook对应一个excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); //1加载规则,开始循环 for (int w = 0; w < cutStrs.length; w++) { jsonRow = getInfoByRootName(rule, cutStrs[w]); if (jsonRow != null) { rs = getDefRuleInfosByRow(jsonRow); if (jsonRow.get("ifCycle").equals("true")) { list = (List<Map>) map.get(cutStrs[w]); // 第二步，在workbook中创建一个sheet对应excel中的sheet sheet = workbook.createSheet(cutStrs[w]); //绘制批注 p=sheet.createDrawingPatriarch(); // 第三步，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制 row = sheet.createRow(0); // 第四步，创建单元格，设置表头 for (int i = 0; i < rs.size(); i++) { //添加备注 if(!rs.get(i).getRemark().equals("?")) { comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6)); //输入批注信息 comment.setString(new HSSFRichTextString(rs.get(i).getRemark())); row.createCell(i).setCellComment(comment); } row.createCell(i).setCellValue(rs.get(i).getId()); } // 第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值 for (int i = 0; i < list.size(); i++) { row1 = sheet.createRow(i + 1); // 创建单元格设值 for (int j = 0; j < rs.size(); j++) { if (list.get(i).containsKey(rs.get(j).getId())) { row1.createCell(j).setCellValue(list.get(i).get(rs.get(j).getId()).toString()); } } } } else { if (map.containsKey(cutStrs[w])) { m = (Map) map.get(cutStrs[w]); // 第二步，在workbook中创建一个sheet对应excel中的sheet sheet = workbook.createSheet(cutStrs[w]); //绘制批注 p=sheet.createDrawingPatriarch(); // 第三步，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制 row = sheet.createRow(0); row1 = sheet.createRow(1); // 第四步，创建单元格，设置表头 for (int i = 0; i < rs.size(); i++) { //添加备注 if(!rs.get(i).getRemark().equals("?")) { comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6)); //输入批注信息 comment.setString(new HSSFRichTextString(rs.get(i).getRemark())); row.createCell(i).setCellComment(comment); } row.createCell(i).setCellValue(rs.get(i).getId()); row1.createCell(i).setCellValue(m.get(rs.get(i).getId()).toString()); } } } } } // 将文件保存到指定的位置 try { if (!file.exists()) { file.mkdirs(); } FileOutputStream fos = new FileOutputStream(folder + "\\" + fileName); workbook.write(fos); fos.close(); return true; } catch (IOException e) { e.printStackTrace(); } return false; }
将excel处理为类
1.读取excel,处理为map
//格式表头必须占一行! public static Map getExcelDataForMap(File file)throws FileNotFoundException, IOException { int rowSize = 0; BufferedInputStream in = new BufferedInputStream(new FileInputStream(file)); Map<String, Object> rsMap = new HashMap(); Map tempMap = null; List<Map<String, Object>> tempMaps = null; POIFSFileSystem fs = new POIFSFileSystem(in); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFCell cell = null;HSSFCell headCell = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { HSSFSheet st = wb.getSheetAt(sheetIndex); // 第一行为标题，不取 tempMaps = new ArrayList<Map<String, Object>>(); for (int rowIndex = 1; rowIndex <= st.getLastRowNum(); rowIndex++) { HSSFRow headR = st.getRow(0); HSSFRow row = st.getRow(rowIndex); if (row == null) { continue; } int tempRowSize = row.getLastCellNum() + 1; if (tempRowSize > rowSize) { rowSize = tempRowSize; } boolean hasValue = false; tempMap = new HashMap(); for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) { String value = ""; String headV = ""; cell = row.getCell(columnIndex); headCell=headR.getCell(columnIndex); if (cell != null) { // 注意：一定要设成这个，否则可能会出现乱码 //cell.setEncoding(HSSFCell.ENCODING_UTF_16); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { value = ""; } } else { value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = (cell.getBooleanCellValue() == true ? "Y": "N"); break; default: value = ""; } } if (headCell != null) { // 注意：一定要设成这个，否则可能会出现乱码 //cell.setEncoding(HSSFCell.ENCODING_UTF_16); switch (headCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: headV = headCell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(headCell)) { Date date = headCell.getDateCellValue(); if (date != null) { headV = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { headV = ""; } } else { value = new DecimalFormat("0").format(headCell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!headCell.getStringCellValue().equals("")) { headV = cell.getStringCellValue(); } else { headV = cell.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: headV = (cell.getBooleanCellValue() == true ? "Y": "N"); break; default: headV = ""; } } if (columnIndex == 0 && value.trim().equals("")) { break; } hasValue = true; tempMap.put(new String(ComUtil.rightTrim(headV)),new String(ComUtil.rightTrim(value))); } if (hasValue) { tempMaps.add(tempMap); } } rsMap.put(wb.getSheetName(sheetIndex), tempMaps); } in.close(); //把bm0的格式转为通用map的格式而非list rsMap.put("bm0", ((List)rsMap.get("bm0")).get(0)); return rsMap; }
2.读取map还原为类
public static BTLDAO getBtlByExcelMap(String rule,Map rsMaps) throws Exception { BTLDAO btl=new BTLDAO(); BtlModule0 bi; StringBuilder buf = new StringBuilder(); String cutStr = ""; int cutSumCt = 1;//总循环次数 int mapW = 0, mapH = 0, i,j; JSONObject row; List<DefRule> rs; Map biMap = null; Object rsO = null; List list; { //得到基础信息 cutStr = "bm0"; row = getInfoByRootName(rule, cutStr); rs = getDefRuleInfosByRow(row); bi = new BtlModule0(); biMap = (Map) (rsMaps.get(cutStr)); bi=(BtlModule0) ComUtil.mapToJBean(BtlModule0.class, biMap); btl.setBm0(bi); biMap = ComUtil.JBeanToMap(bi); } { //重复读取所有基本信息 Object[] objects = new Object[] { (new BtlModule1()), (new BtlModule2()), (new BtlModule3()), (new BtlModule4()), (new BtlModule5()), (new BtlModule6()), (new BtlModule7()), (new BtlModule8()), (new BtlModule9()), (new BtlModule10()), (new BtlModule11()), (new BtlModule12()), (new BtlModule13()), (new BtlModule14()), (new BtlModule15()), (new BtlModule16()), (new BtlModule17()), (new BtlModule18()), (new BtlModule19()), (new BtlModule20()) }; String[] cutStrs = new String[] { "bm1", "bm2", "bm3", "bm4", "bm5", "bm6", "bm7", "bm8", "bm9", "bm10", "bm11", "bm12", "bm13", "bm14", "bm15", "bm16", "bm17", "bm18", "bm19", "bm20" }; for (i = 0; i < cutStrs.length; i++) { row = getInfoByRootName(rule, cutStrs[i]); if (row != null) { //List<DefRule> rs, List list,Map map rs = getDefRuleInfosByRow(row); list=(List)rsMaps.get(cutStrs[i]); // getBtlMoudleList(List<DefRule> rs, List list,Map map) ComUtil.setVal(btl, "set" + ComUtil.UpperInitial(cutStrs[i]), getBtlMoudleList(rs,list,objects[i])); //ComUtil.setVal(btl, "set" + ComUtil.UpperInitial(cutStrs[i]), rsMap.get("T")); } } } return btl; }
