public void parseData(
      final Sheet sheet,
      final int startRow,
      final int endColumn,
      final List<String> propertyNames,
      final Class clazzOfTestCase) {

    int rowCounter = startRow;

    while (!isBlank(sheet, rowCounter, endColumn)) {
      Row row = sheet.getRow(rowCounter - 1);
      for (int i = 0; i < endColumn; i++) {
        Cell cell = row.getCell(i);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
          try {
            Method method = clazzOfTestCase.getMethod("set" + propertyNames.get(i), Object.class);
          } catch (NoSuchMethodException e) {
            e
                .printStackTrace(); // To change body of catch statement use File | Settings | File
                                    // Templates.
          }
        }
      }
    }
  }
Example #2
0
 private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
   XSSFWorkbook wb = null;
   try {
     if (isXssf) {
       XSSFWorkbook w = new XSSFWorkbook();
     } else {
       HSSFWorkbook w = new HSSFWorkbook();
     }
     wb = new XSSFWorkbook();
     XSSFDataFormat format = wb.createDataFormat();
     XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
     XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
     XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
     XSSFDataFormat passwordFormat = wb.createDataFormat();
     passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
     List<ExcelHeader> headers = getHeaderList(clz);
     Collections.sort(headers);
     sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
     Row r0 = sheet.createRow(0);
     Cell cell = r0.createCell(0);
     r0.setHeightInPoints(28);
     cell.setCellValue(message + "备份记录");
     Row r = sheet.createRow(1);
     r.setHeightInPoints(25);
     cell.setCellStyle(cellStyle);
     // 输出标题
     for (int i = 0; i < headers.size(); i++) {
       Cell cell1 = r.createCell(i);
       if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
       else cell1.setCellStyle(cellStyle);
       cell1.setCellValue(headers.get(i).getTitle());
     }
     Object obj = null;
     // 输出用户资料信息
     if (message.indexOf("用户资料 ") > 0) {
       sheet.setColumnWidth(3, 32 * 150);
       sheet.setColumnWidth(4, 32 * 110);
       sheet.setColumnWidth(7, 32 * 120);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           if (getMethodName(headers.get(j)).equals("nabled"))
             cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
           else if (getMethodName(headers.get(j)).equals("password")) {
             cell2.setCellStyle(passwordCellStyle);
             cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
           } else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 输出房间使用信息数据
     else {
       sheet.setColumnWidth(0, 32 * 80);
       sheet.setColumnWidth(2, 32 * 100);
       sheet.setColumnWidth(3, 32 * 190);
       sheet.setColumnWidth(4, 32 * 190);
       sheet.setColumnWidth(5, 32 * 190);
       sheet.setColumnWidth(10, 32 * 130);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           if (j == 3 || j == 4 || j == 5) {
             XSSFCellStyle cs3 = wb.createCellStyle();
             cell2.setCellValue(new Date());
             copyDefaultCellStyle(format, cell2, cs3, 1);
           }
           if (j == 10) {
             XSSFCellStyle cs2 = wb.createCellStyle();
             copyDefaultCellStyle(format, cell2, cs2, 2);
           }
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 设置行列的默认宽度和高度
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NoSuchMethodException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return wb;
 }