public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) { Sheet sheet = wb.getSheetAt(0); // 取第一张表 List<Object> objs = null; try { Row row = sheet.getRow(readLine); // 开始行,主题栏 objs = new ArrayList<Object>(); Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名 if (maps == null || maps.size() <= 0) throw new RuntimeException("要读取的Excel的格式不正确,检查是否设定了合适的行"); // 与order顺序不符 for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据 row = sheet.getRow(i); Object obj = clz.newInstance(); // 调用无参结构 for (Cell c : row) { int ci = c.getColumnIndex(); String mn = maps.get(ci).substring(3); // 消除get mn = mn.substring(0, 1).toLowerCase() + mn.substring(1); Map<String, Object> params = new HashMap<String, Object>(); if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式 else c.setCellType(Cell.CELL_TYPE_NUMERIC); if (this.getCellValue(c).trim().equals("是")) { BeanUtils.copyProperty(obj, mn, 1); } else if (this.getCellValue(c).trim().equals("否")) { BeanUtils.copyProperty(obj, mn, 0); } else BeanUtils.copyProperty(obj, mn, this.getCellValue(c)); } objs.add(obj); } } catch (InstantiationException e) { e.printStackTrace(); logger.error(e); } catch (IllegalAccessException e) { e.printStackTrace(); logger.error(e); } catch (InvocationTargetException e) { e.printStackTrace(); logger.error(e); } catch (NumberFormatException e) { e.printStackTrace(); logger.error(e); } return objs; }
/** * 将excel内的内容读取到xml文件中,并添加dtd验证 * * @param xmlFile * @param sheetNum * @return 1代表成功,0失败,-1超过最大sheet,2跳过当前失败的xml */ public int excelToXml(String xmlFile, int sheetNum) { if (sheetNum >= workBook.getNumberOfSheets()) return -1; else sheet = workBook.getSheetAt(sheetNum); xmlFile = xmlFile + ".xml"; try { Document document = DocumentHelper.createDocument(); // 使用sheet名称命名跟节点 String rootName = sheet.getSheetName().replaceAll(" ", ""); Element root = document.addElement(rootName); // 添加dtd文件说明 DocumentType documentType = new DOMDocumentType(); documentType.setElementName(rootName); List<ElementDecl> declList = new ArrayList<>(); declList.add(new ElementDecl(rootName, "(row*)")); // 判断sheet是否为空,为空则不执行任何操作 if (sheet.getRow(0) == null) return 1; // 遍历sheet第一行,获取元素名称 row = sheet.getRow(0); String rowString = null; List<String> pcdataList = new ArrayList<>(); for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) { Object object = this.getCellValueObject(0, y); // 判断是否有合并单元格,有的话跳过 if (object == null) return 2; // 去除表头字符串中的空格 String objectStr = object.toString().replaceAll(" ", ""); if (rowString != null) rowString += "|" + objectStr; else rowString = objectStr; pcdataList.add(objectStr); } // 设置行节点 declList.add(new ElementDecl("row", "(" + rowString + ")*")); // 遍历list设置行的下级节点 for (String tmp : pcdataList) { declList.add(new ElementDecl(tmp, "(#PCDATA)")); } documentType.setInternalDeclarations(declList); // 遍历读写excel数据到xml中 for (int x = 1; x < sheet.getLastRowNum(); x++) { row = sheet.getRow(x); Element rowElement = root.addElement("row"); for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) { // cell = row.getCell(y); Object object = this.getCellValueObject(x, y); if (object != null) { // 将sheet第一行的行首元素当作元素名称 String pcdataString = pcdataList.get(y); Element element = rowElement.addElement(pcdataString); // Element element = rowElement.addElement("name"); element.setText(object.toString()); } } } // 写入文件和dtd document.setDocType(documentType); this.docToXmlFile(document, xmlFile); } catch (Exception e) { e.printStackTrace(); } return 1; }