Ejemplo n.º 1
0
  //  traversal cell
  public void traversalCell(String filePath) {
    try {
      Workbook workBook = null;
      try {
        workBook = new XSSFWorkbook(filePath); // 支持2007
      } catch (Exception ex) {
        workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
      }

      // 获得Excel中工作表个数
      System.out.println("工作表个数 :" + workBook.getNumberOfSheets());

      // 循环每个工作表
      for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        // 创建工作表
        Sheet sheet = workBook.getSheetAt(i);

        int rows = sheet.getPhysicalNumberOfRows(); // 获得行数

        System.out.println(
            "工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());

        if (rows > 0) {
          sheet.getMargin(Sheet.TopMargin);
          for (int r = 0; r < rows; r++) { // 行循环
            Row row = sheet.getRow(r);
            if (row != null) {

              int cells = row.getLastCellNum(); // 获得列数
              for (short c = 0; c < cells; c++) { // 列循环
                Cell cell = row.getCell(c);

                if (cell != null) {
                  String value = getCellData(cell);
                  System.out.println("第" + r + "行 " + "第" + c + "列:" + value);
                }
              }
            }
          }
        }

        // 查询合并的单元格
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
          System.out.println("第" + i + "个合并单元格");
          CellRangeAddress region = sheet.getMergedRegion(i);
          int row = region.getLastRow() - region.getFirstRow() + 1;
          int col = region.getLastColumn() - region.getFirstColumn() + 1;
          System.out.println("起始行:" + region.getFirstRow());
          System.out.println("起始列:" + region.getFirstColumn());
          System.out.println("所占行:" + row);
          System.out.println("所占列:" + col);
        }
      }

    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
Ejemplo n.º 2
0
  /**
   * get the specified excel sheet and put its value string to list.
   *
   * @param sheetName excel sheet name
   * @param ignoreRows first several rows not to read.
   * @param ignoreCols first several cols not to read.
   * @param readRows specified row count to read.
   * @param readColumns specified column count to read.
   * @throws RuntimeException
   */
  public List<String> excelToList(
      String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
    FileInputStream fso = null;
    List<String> paraList = new ArrayList<String>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
      }
      readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
      for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
        xlRow = xlSheet.getRow(i);
        readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
        if (xlRow != null) {
          for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
            xlCell = xlRow.getCell(j);
            if (xlCell == null) {
              paraList.add("");
            } else {
              paraList.add(xlCell.toString());
            }
          }
        }
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
Ejemplo n.º 3
0
  @Test
  public void report_FacilityUsage_asOperator2() {
    final ReportParameters params = baseParams();
    params.interval = 3 * 60;
    registerMockFacilityUsages(facility1, apiUser);
    registerMockFacilityUsages(facility2, apiUser2);

    final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", operator2User);

    // If this succeeds, the response was a valid excel file
    final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
    assertThat(getSheetNames(workbook)).containsExactly("Käyttöasteraportti", "Selite");

    final Sheet usages = workbook.getSheetAt(0);
    // Header and one for each usage type
    assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(3);

    // Only operator2 visible
    assertThat(getDataFromColumn(usages, 3))
        .containsOnly("Operaattori", operator2.name.fi)
        .doesNotContain(operator1.name.fi);

    final List<String> headers = getDataFromRow(usages, 0);
    assertThat(headers.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, headers.size()))
        .containsExactly("00:00", "03:00", "06:00", "09:00", "12:00", "15:00", "18:00", "21:00");

    // Get the hourly utilizations for CAR
    // Results are not interpolated.
    final List<String> row = getDataFromRow(usages, 1);
    assertThat(row.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, row.size()))
        .containsExactly("24", "24", "24", "24", "0", "0", "0", "24");
  }
Ejemplo n.º 4
0
  private void processInnland(Sheet sheet) throws InvalidFormatException {
    if (sheet.getPhysicalNumberOfRows() > 0) {
      int lastRowNum = 0;
      Row row = null;
      lastRowNum = sheet.getLastRowNum();
      System.out.println("Innland har " + lastRowNum + " rader");
      row = sheet.getRow(0);
      int lastCellNum = row.getLastCellNum();
      System.out.println("Innland rad 0 har " + lastCellNum + " celler");
      System.out.println("Kjører tilregnelighetssjekk");

      String KO = text(row, INNLAND_KONTONR);
      String LE = text(row, INNLAND_LEVNR);
      String NA = text(row, INNLAND_NAVN);
      String A1 = text(row, INNLAND_ADDR1);
      String A2 = text(row, INNLAND_ADDR2);
      String NR = text(row, INNLAND_POSTNUMMER);
      String ST = text(row, INNLAND_POSTSTED);
      if (KO.equals("Kontonr")
          && LE.equals("Lev.nr")
          && NA.equals("Navn")
          && A1.equals("Adresse 1")
          && A2.equals("Adresse 2")
          && NR.equals("Postnr.")
          && ST.equals("Poststed")) {
        System.out.println("Første rad ser OK ut, fortsetter");
        for (int j = 1; j <= lastRowNum; j++) {
          System.out.println("Prosesserer rad " + j + " av " + lastRowNum);
          row = sheet.getRow(j);
          this.rowToXML(row, j);
        }
      } else {
        throw new InvalidFormatException(
            "Kjenner ikke igjen første rad\n"
                + "Skulle vært (1), fant (2)\n(1) 'Kontonr' 'Lev.nr' 'Navn' 'Adresse 1' 'Adresse 2' 'Postnr.' 'Poststed'\n'"
                + ""
                + KO
                + "' '"
                + LE
                + "' '"
                + NA
                + "' '"
                + A1
                + "' '"
                + A2
                + "' '"
                + NR
                + "' '"
                + ST
                + "'");
      }
    }
  }
Ejemplo n.º 5
0
  private void checkMaxUtilization_rows(Workbook workbook) {
    /*
    EXAMPLE:
    Hubi	Helsinki	X-Park	Liityntä	Henkilöauto	Toiminnassa	50	Arkipäivä	100 %
    Hubi	Helsinki	X-Park	Liityntä	Henkilöauto	Toiminnassa	50	Lauantai	100 %
    Hubi	Helsinki	X-Park	Liityntä	Henkilöauto	Toiminnassa	50	Sunnuntai	100 %
     */

    final Sheet utilization = workbook.getSheetAt(0);
    assertThat(utilization.getPhysicalNumberOfRows()).isEqualTo(4);
    final List<String> businessDay = getDataFromRow(utilization, 1);
    assertThat(businessDay)
        .containsExactly(
            hub.name.fi,
            "Helsinki", // The region name
            operator1.name.fi,
            translationService.translate(facility1.usages.first()),
            translationService.translate(CAR),
            translationService.translate(facility1.status),
            "" + facility1.builtCapacity.get(CAR),
            translationService.translate(DayType.BUSINESS_DAY),
            "100%");

    final List<String> saturday = getDataFromRow(utilization, 2);
    assertThat(saturday)
        .containsExactly(
            hub.name.fi,
            "Helsinki", // The region name
            operator1.name.fi,
            translationService.translate(facility1.usages.first()),
            translationService.translate(CAR),
            translationService.translate(facility1.status),
            "" + facility1.builtCapacity.get(CAR),
            translationService.translate(DayType.SATURDAY),
            "50%");

    final List<String> sunday = getDataFromRow(utilization, 3);
    assertThat(sunday)
        .containsExactly(
            hub.name.fi,
            "Helsinki", // The region name
            operator1.name.fi,
            translationService.translate(facility1.usages.first()),
            translationService.translate(CAR),
            translationService.translate(facility1.status),
            "" + facility1.builtCapacity.get(CAR),
            translationService.translate(DayType.SUNDAY),
            "20%");
  }
Ejemplo n.º 6
0
  /**
   * read excel Xls and add the result into arraylist.
   *
   * @param sheetName excel sheet name
   * @throws RuntimeException
   */
  public List<Map<String, String>> excelToList(String sheetName) {
    Row firstxlRow = null;
    FileInputStream fso = null;
    List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        return null;
      }
      firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
      int firstCell = firstxlRow.getFirstCellNum();
      int lastCell = firstxlRow.getLastCellNum();
      List<String> keyList = new ArrayList<String>();

      for (int cNum = firstCell; cNum < lastCell; cNum++) {
        if (firstxlRow.getCell(cNum).toString() == null) {
          break;
        }
        keyList.add(firstxlRow.getCell(cNum).toString());
      }

      for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
        xlRow = xlSheet.getRow(i);
        List<String> valueList = new ArrayList<String>();
        if (xlRow == null) {
          break;
        }
        for (int j = firstCell; j < lastCell; j++) {
          xlCell = xlRow.getCell(j);
          if (xlCell == null) {
            valueList.add(null);
            continue;
          } else {
            valueList.add(xlCell.toString());
          }
        }
        paraList.add(creatMap(keyList, valueList));
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
Ejemplo n.º 7
0
  public List<String> readExcel(String zh) throws IOException {
    List<String> values = new ArrayList<String>();
    FileInputStream fise = new FileInputStream(excelPath);
    HSSFWorkbook wb = new HSSFWorkbook(fise);
    Sheet sheet3 = wb.getSheetAt(1);
    Row r = null;
    int linenum = 0;
    Cell cell = null;
    for (int i = 8; i < sheet3.getPhysicalNumberOfRows(); i++) {
      r = sheet3.getRow(i);
      cell = r.getCell(3);
      if (cell.toString().contains(zh)) {
        linenum = i;
        break;
      }
    }

    r = sheet3.getRow(linenum);
    String value = null;
    for (int i = 15; i <= 21; i++) {
      cell = r.getCell(i);
      if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        HSSFDataFormatter df = new HSSFDataFormatter();
        String cellf = df.formatCellValue(cell);
        value = cellf;
      } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        value = cell.getStringCellValue();
      } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        value = "0";
      }
      values.add(value);
    }

    cell = r.getCell(24);
    if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
      HSSFDataFormatter df = new HSSFDataFormatter();
      String cellf = df.formatCellValue(cell);
      value = cellf;
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
      value = cell.getStringCellValue();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
      value = "0";
    }
    values.add(value);
    // System.out.println(values);
    return values;
  }
Ejemplo n.º 8
0
 public List<ExcelRow> readTemporalSentences() {
   List<ExcelRow> returnVal = new ArrayList<>();
   try {
     FileInputStream fileInputStream = new FileInputStream(FILE_LOC);
     Workbook workbook = new XSSFWorkbook(fileInputStream);
     Sheet worksheet = workbook.getSheetAt(sheet);
     Row row;
     ExcelRow dataRow;
     for (int i = 1; i < worksheet.getPhysicalNumberOfRows(); i++) {
       row = worksheet.getRow(i);
       dataRow = new ExcelRow(row);
       if (dataRow.getSen() == 1 && dataRow.getFinalMarked() == 1) returnVal.add(dataRow);
     }
     fileInputStream.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
   return returnVal;
 }
Ejemplo n.º 9
0
 private void checkHubsAndFacilities_facilityInfo(Workbook workbook) {
   final Sheet facilities = workbook.getSheetAt(1);
   assertThat(facilities.getPhysicalNumberOfRows()).isEqualTo(2);
   final List<String> facilityInfo = getDataFromRow(facilities, 1);
   assertThat(facilityInfo)
       .containsSequence(
           facility1.name.fi,
           String.join(", ", facility1.aliases),
           hub.name.fi,
           operator1.name.fi,
           translationService.translate(facility1.status),
           facility1.statusDescription.fi,
           String.format(Locale.ENGLISH, "%.4f", facility1.location.getCentroid().getX()),
           String.format(Locale.ENGLISH, "%.4f", facility1.location.getCentroid().getY()),
           "",
           "08:00 - 18:00",
           "08:00 - 18:00",
           facility1.openingHours.info.fi,
           ""
               + facility1
                   .builtCapacity
                   .entrySet()
                   .stream()
                   .filter(entry -> asList(motorCapacities).contains(entry.getKey()))
                   .mapToInt(entry -> entry.getValue())
                   .sum(),
           ""
               + facility1
                   .builtCapacity
                   .entrySet()
                   .stream()
                   .filter(entry -> asList(bicycleCapacities).contains(entry.getKey()))
                   .mapToInt(entry -> entry.getValue())
                   .sum(),
           "" + facility1.builtCapacity.getOrDefault(CAR, 0),
           "" + facility1.builtCapacity.getOrDefault(DISABLED, 0),
           "" + facility1.builtCapacity.getOrDefault(ELECTRIC_CAR, 0),
           "" + facility1.builtCapacity.getOrDefault(MOTORCYCLE, 0),
           "" + facility1.builtCapacity.getOrDefault(BICYCLE, 0),
           "" + facility1.builtCapacity.getOrDefault(BICYCLE_SECURE_SPACE, 0));
 }
Ejemplo n.º 10
0
  @Test
  public void report_FacilityUsage_asAdmin_oneFacility() {
    final ReportParameters params = baseParams();
    params.interval = 180;
    params.facilities = singleton(facility1.id);
    params.capacityTypes = singleton(CAR);
    params.usages = singleton(Usage.PARK_AND_RIDE);
    params.operators = singleton(facility1.operatorId);
    registerMockFacilityUsages(facility1, apiUser);
    registerMockFacilityUsages(facility2, apiUser2);

    final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", adminUser);

    // If this succeeds, the response was a valid excel file
    final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
    final Sheet usages = workbook.getSheetAt(0);
    // Header + one row
    assertThat(getDataFromColumn(usages, 0))
        .containsExactly("Pysäköintipaikan nimi", facility1.name.fi);
    assertThat(getDataFromColumn(usages, 4)).containsExactly("Käyttötapa", "Liityntä");
    assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(2);
  }
Ejemplo n.º 11
0
  private void checkHubsAndFacilities_hubInfo(Workbook workbook) {
    final Sheet hubs = workbook.getSheetAt(0);
    assertThat(hubs.getPhysicalNumberOfRows()).isEqualTo(2);

    final List<String> hubInfo = getDataFromRow(hubs, 1);
    assertThat(hubInfo)
        .containsExactly(
            hub.name.fi,
            String.join(
                ", ",
                toArray(hub.address.streetAddress.fi, hub.address.postalCode, hub.address.city.fi)),
            String.format(Locale.ENGLISH, "%.4f", hub.location.getX()),
            String.format(Locale.ENGLISH, "%.4f", hub.location.getY()),
            ""
                + facility1
                    .builtCapacity
                    .entrySet()
                    .stream()
                    .filter(entry -> asList(motorCapacities).contains(entry.getKey()))
                    .mapToInt(entry -> entry.getValue())
                    .sum(),
            ""
                + facility1
                    .builtCapacity
                    .entrySet()
                    .stream()
                    .filter(entry -> asList(bicycleCapacities).contains(entry.getKey()))
                    .mapToInt(entry -> entry.getValue())
                    .sum(),
            "" + facility1.builtCapacity.getOrDefault(CAR, 0),
            "" + facility1.builtCapacity.getOrDefault(DISABLED, 0),
            "" + facility1.builtCapacity.getOrDefault(ELECTRIC_CAR, 0),
            "" + facility1.builtCapacity.getOrDefault(MOTORCYCLE, 0),
            "" + facility1.builtCapacity.getOrDefault(BICYCLE, 0),
            "" + facility1.builtCapacity.getOrDefault(BICYCLE_SECURE_SPACE, 0),
            facility1.name.fi);
  }
Ejemplo n.º 12
0
 public static List<Doctor> importBuildInfo(File file, HttpServletRequest request) {
   try {
     // 创建需要批量插入数据集合
     List<Doctor> list = new ArrayList<Doctor>();
     // 创建一个FileInputStream 文件输入流
     FileInputStream inputStream = new FileInputStream(file);
     // 创建对Excel工作簿文件的引用
     Workbook wookbook = null;
     String name = file.getName();
     String fileType = name.substring(name.lastIndexOf(".") + 1, name.length());
     if (fileType.equals("xlsx")) {
       wookbook = new XSSFWorkbook(inputStream);
     } else if (fileType.equals("xls")) {
       wookbook = new HSSFWorkbook(inputStream);
     }
     // 在Excel文档中,第一张工作表的缺省索引是0
     Sheet sheet = wookbook.getSheetAt(0);
     // 获取到Excel文件中的所有行数
     int rows = sheet.getPhysicalNumberOfRows();
     // 遍历行 从第二行开始遍历
     for (int i = 1; i < rows; i++) {
       // 读取左上端单元格
       Row row = sheet.getRow(i);
       // 行不为空
       if (row != null) {
         // 将对象增加到集合中
         list.add(null);
       }
     }
     // 返回集合
     return list;
   } catch (IOException e) {
     logger.error("创建导入excel对象报错!", e);
   }
   return null;
 }
Ejemplo n.º 13
0
  @Test
  public void report_FacilityUsage_asAdmin() {
    final ReportParameters params = baseParams();
    params.interval = 180;
    registerMockFacilityUsages(facility1, apiUser);
    registerMockFacilityUsages(facility2, apiUser2);

    final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", adminUser);

    // If this succeeds, the response was a valid excel file
    final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);

    final Sheet usages = workbook.getSheetAt(0);
    // Header and one for each usage type for both facilities
    assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(5);

    assertThat(getDataFromColumn(usages, 3))
        .containsExactly(
            "Operaattori",
            operator1.name.fi,
            operator1.name.fi,
            operator2.name.fi,
            operator2.name.fi);
  }
Ejemplo n.º 14
0
  @Test
  // Read XL file have Executable content
  public void ReadTestCase() {

    boolean flag = false;
    PropertyConfigurator.configure("Configuration/log.properties");
    log.info("Reading Output XL file to Execute test Cases...");

    // Start Reading XL file
    try {
      FileInputStream FSRead = new FileInputStream(OutputfilePath);
      Workbook WB = new HSSFWorkbook(FSRead);
      Sheet sh = WB.getSheet(sheet_name);
      int rows = sh.getPhysicalNumberOfRows();
      // System.out.println("No. of rows in Input XL file for Test Scenarios Sheet = "
      // + rows);
      int cols = sh.getRow(0).getLastCellNum();
      // System.out.println("No. of columns in input file for Test Scenarios Sheet = "
      // + cols);
      int ExecuteTCCounter = -1;

      // Iterate Rows and Read complete input XL file
      for (int i = 0; i < rows; i++) {
        ExecuteTCCounter++;
        TestScenarios.add(new ArrayList());
        // Iterate Columns
        innerLoop:
        for (int j = 0; j < cols; j++) {
          Row current_row = sh.getRow(i);
          // Debug: System.out.println("row and col value =" + i + " , " + j);
          Cell blank_cell = current_row.getCell(j, current_row.CREATE_NULL_AS_BLANK);
          // Debug: System.out.println("Blank cell = " + blank_cell);
          int type = current_row.getCell(j).getCellType();
          // Debug: System.out.println("Value of Type = " + type);
          if (type == 3 && j == 0) {
            ExecuteTCCounter--;
            TestScenarios.remove(new ArrayList());
            break innerLoop;
          }

          if (type == 3) {
            // System.out.println("Empty Cell");
            ((ArrayList) TestScenarios.get(ExecuteTCCounter)).add("");
          }
          if (type == 1) {
            String data = current_row.getCell(j).getStringCellValue();
            // System.out.println("Cell Value" + " " + data + "\n");
            ((ArrayList) TestScenarios.get(ExecuteTCCounter)).add(data);
          }
          if (type == 0) {
            double value = current_row.getCell(j).getNumericCellValue();
            // System.out.println("Cell Value" + " " + value + "\n");
            ((ArrayList) TestScenarios.get(ExecuteTCCounter)).add(value);
          }
          type = -1;
        }
      }
      FSRead.close();
    } catch (IOException e) {
      log.debug("Error in ReadExcel.java while reading XL file", e);
    }

    // Reading Complete Array (Stored output XL file) to execute Test Case
    log.info("Start Reading Test Cases to Execute...");

    // Debug:
    // System.out.println("Test Scenario Size" + TestScenarios.size());
    // System.out.println("Test Scenario row size" + ((ArrayList) TestScenarios.get(0)).size());

    // for(int i=0; i<TestScenarios.size();i++) {
    for (int i = 0; i < 1; i++) {
      String newLine = System.getProperty("line.separator");
      System.out.println(newLine);

      log.info("Executing Test Case No." + i);
      next:
      for (int c = 0; c < ((ArrayList) TestScenarios.get(i)).size(); c++) {

        if (c == 0 || c == 1) {
          log.info("Executing " + (String) ((ArrayList) TestScenarios.get(i)).get(c));
        }
        if (((String) ((ArrayList) TestScenarios.get(i)).get(c)).equals("")) {
          // System.out.println("Bank");
          // Need to do some validation later: Check if user error and cell value is empty. (i.e.
          // check next cell value).
          break next;
        }

        if (c > 2) {
          String MethodName = (String) ((ArrayList) TestScenarios.get(i)).get(c);
          // System.out.println("Ash " + MethodName);
          String className = "selenium.dd.FunctionalCode.ExecuteTestCase";

          // Get input if type defined in XL column
          if (MethodName.contains("Type")) {
            // Increment to get the next cell value and set flag
            c++;
            flag = true;

            // Checking if last column have type. This is validation for a defect cause user error
            // in XL sheet
            if (c == ((ArrayList) TestScenarios.get(i)).size()) {
              driver.quit();
              log.info("Test Case Execution finished, checking for next test case");
              break next;
            }
          }

          try {
            Class cls = Class.forName(className);
            Object obj = cls.newInstance();

            // Defined to parameterized reflection method call
            Class[] paramTypes = new Class[1];
            paramTypes[0] = String.class;

            // If type was defined in XL column get the next 'Input' value from XL
            // And invoke method that required parameters. + Reset flag to flase.
            if (flag) {
              flag = false;
              String Input = (String) ((ArrayList) TestScenarios.get(i)).get(c);
              Method method = cls.getMethod(MethodName, paramTypes);
              method.invoke(obj, Input);
            } // else invoke simple reflection method (i.e. without parameter).
            else {
              Method method = cls.getMethod(MethodName);
              method.invoke(obj);
            }
          } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            log.debug(e);
            // e.printStackTrace();
          } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          } catch (NoSuchMethodException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          } catch (SecurityException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          } catch (IllegalArgumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          } catch (InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          }
        }

        // Checking for last column of row, so that we can close driver and close browser.
        // We will re initiate driver and re-open browser for next test case
        if (c == ((ArrayList) TestScenarios.get(i)).size() - 1) {
          log.info("Closing Driver for " + i + " Test Case");
          driver.quit();
        }
      } // Close inner For loop
    } // Close outer For loop
  }
  public void readProcessSpreadSheet() {
    processSpreadSheet = System.getProperty("processSpreadSheet");
    System.out.println("processSpreadSheet: " + processSpreadSheet);
    if (processCodesList.size() == 0) {
      if (processSpreadSheet != null && !processSpreadSheet.equals("")) {
        Workbook wb1 = null;
        try {
          wb1 = new XSSFWorkbook(processSpreadSheet);
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        Sheet sheet = wb1.getSheetAt(0);
        Row row;
        Cell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it
        // doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
          row = sheet.getRow(i);
          if (row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            // out.println("tmp value"+tmp);
            if (tmp > cols) {
              cols = tmp;
            }
          }
        }

        ProcessDefinition tempProcessCode;
        for (int r1 = 0; r1 < rows; r1++) {
          tempProcessCode = new ProcessDefinition();

          row = sheet.getRow(r1);
          if (row != null) {
            if (row.getCell(0) != null) {
              for (int counter = 0; counter < cols; counter++) {
                cell = row.getCell((short) counter);
                // cell = row.getCell(1);
                if (counter == 0) {
                  if (cell != null) {
                    tempProcessCode.setProcessName(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setProcessName("");
                  }
                } else if (counter == 1) {
                  if (cell != null) {
                    tempProcessCode.setProcessCode(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setProcessCode("");
                  }
                } else if (counter == 2) {
                  if (cell != null) {
                    tempProcessCode.setIaeaCode(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setIaeaCode("");
                  }
                } else if (counter == 3) {
                  if (cell != null) {
                    tempProcessCode.setProcessDescription(cell.getStringCellValue());
                    // System.out.println(tempProcessCode.getProcessDescription());
                  } else {
                    tempProcessCode.setProcessDescription("");
                    // System.out.println("Process Description EMpty");
                  }
                }
              }
            }

          } else {
            rows++;
          }
          processCodesList.add(tempProcessCode);
        }
      }
    }
    System.out.println(processCodesList.size());
  }
  /** Clone the sheet at the passed index and replace values as needed */
  public Sheet addSheet(
      Workbook wb,
      SheetToAdd sheetToAdd,
      Set<String> usedSheetNames,
      ReportData reportData,
      ReportDesign design,
      Map<String, String> repeatSections) {

    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    Sheet sheet = sheetToAdd.getSheet();
    sheet.setForceFormulaRecalculation(true);

    int sheetIndex = wb.getSheetIndex(sheet);

    // Configure the sheet name, replacing any values as needed, and ensuring it is unique for the
    // workbook
    String sheetName =
        EvaluationUtil.evaluateExpression(
                sheetToAdd.getOriginalSheetName(), sheetToAdd.getReplacementData(), prefix, suffix)
            .toString();
    sheetName = ExcelUtil.formatSheetTitle(sheetName, usedSheetNames);
    wb.setSheetName(sheetIndex, sheetName);
    usedSheetNames.add(sheetName);

    log.debug("Handling sheet: " + sheetName + " at index " + sheetIndex);

    // Iterate across all of the rows in the sheet, and configure all those that need to be
    // added/cloned
    List<RowToAdd> rowsToAdd = new ArrayList<RowToAdd>();

    int totalRows = sheet.getPhysicalNumberOfRows();
    int rowsFound = 0;
    for (int rowNum = 0;
        rowsFound < totalRows && rowNum < 50000;
        rowNum++) { // check for < 50000 is a hack to prevent infinite loops in edge cases
      Row currentRow = sheet.getRow(rowNum);
      log.debug("Handling row: " + ExcelUtil.formatRow(currentRow));
      if (currentRow != null) {
        rowsFound++;
      }
      // If we find that the row that we are on is a repeating row, then add the appropriate number
      // of rows to clone
      String repeatingRowProperty =
          getRepeatingRowProperty(sheetToAdd.getOriginalSheetNum(), rowNum, repeatSections);
      if (repeatingRowProperty != null) {
        String[] dataSetSpanSplit = repeatingRowProperty.split(",");
        String dataSetName = dataSetSpanSplit[0];
        DataSet dataSet = getDataSet(reportData, dataSetName, sheetToAdd.getReplacementData());

        int numRowsToRepeat = 1;
        if (dataSetSpanSplit.length == 2) {
          numRowsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
        }
        log.debug(
            "Repeating this row with dataset: " + dataSet + " and repeat of " + numRowsToRepeat);
        int repeatNum = 0;
        for (DataSetRow dataSetRow : dataSet) {
          repeatNum++;
          for (int i = 0; i < numRowsToRepeat; i++) {
            Row row = (i == 0 ? currentRow : sheet.getRow(rowNum + i));
            if (repeatNum == 1 && row != null && row != currentRow) {
              rowsFound++;
            }
            Map<String, Object> newReplacements =
                getReplacementData(
                    sheetToAdd.getReplacementData(),
                    reportData,
                    design,
                    dataSetName,
                    dataSetRow,
                    repeatNum);
            rowsToAdd.add(new RowToAdd(row, newReplacements));
            log.debug("Adding " + ExcelUtil.formatRow(row) + " with dataSetRow: " + dataSetRow);
          }
        }
        if (numRowsToRepeat > 1) {
          rowNum += numRowsToRepeat - 1;
        }
      } else {
        rowsToAdd.add(new RowToAdd(currentRow, sheetToAdd.getReplacementData()));
        log.debug("Adding row: " + ExcelUtil.formatRow(currentRow));
      }
    }

    // Now, go through all of the collected rows, and add them back in
    for (int i = 0; i < rowsToAdd.size(); i++) {
      RowToAdd rowToAdd = rowsToAdd.get(i);
      if (rowToAdd.getRowToClone() != null && rowToAdd.getRowToClone().cellIterator() != null) {
        Row addedRow = addRow(wb, sheetToAdd, rowToAdd, i, reportData, design, repeatSections);
        log.debug("Wrote row " + i + ": " + ExcelUtil.formatRow(addedRow));
      }
    }

    return sheet;
  }
Ejemplo n.º 17
0
  @Transactional
  public void saveWeeklyProfit(String fileTag, MultipartFile file) throws Exception {
    try {
      Workbook workbook;
      Sheet worksheet;
      if (file != null && file.getOriginalFilename().endsWith("xls")) {
        workbook = new HSSFWorkbook(file.getInputStream());
      } else if (file != null && file.getOriginalFilename().endsWith("xlsx")) {
        workbook = new XSSFWorkbook(file.getInputStream());
      } else {
        throw new IllegalArgumentException(
            "Received file does not have a standard excel extension.");
      }
      worksheet = workbook.getSheetAt(0);

      if (worksheet == null) {
        throw new Exception("Couldn't get the sheet.");
      }

      Integer noOfEntries = worksheet.getPhysicalNumberOfRows();
      logger.info(noOfEntries.toString());
      for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
        Row entry = worksheet.getRow(rowIndex);
        String name = entry.getCell(0).getStringCellValue();
        String nameOfBusiness = entry.getCell(1).getStringCellValue();
        Date weekEndingDt = entry.getCell(2).getDateCellValue();
        // Date date = new SimpleDateFormat("MM/dd/yyyy", Locale.ENGLISH).parse(openingDate);
        Double sale = entry.getCell(3).getNumericCellValue();
        Double approximateProfit = entry.getCell(4).getNumericCellValue();
        // Integer externalId=((Double)entry.getCell(0).getNumericCellValue()).intValue();
        String event = entry.getCell(5).getStringCellValue();
        logger.info(
            "Row Contents:"
                + name
                + " "
                + nameOfBusiness
                + " "
                + weekEndingDt
                + " "
                + sale
                + " "
                + approximateProfit
                + " "
                + event);

        WeeklyProfit wp = weeklyProfits.findProfits(name, nameOfBusiness, weekEndingDt);

        if (wp == null) {
          wp = new WeeklyProfit();
        }

        wp.setName(name);
        wp.setNameOfBusiness(nameOfBusiness);
        wp.setWeekEndingAt(weekEndingDt);
        wp.setSale(sale);
        wp.setApproximateProfit(approximateProfit);
        wp.setEvents(event);

        Long id = weeklyProfits.save(wp);
        logger.info("Saved : " + id);
        System.out.println("###### Saved : " + id);
      }

    } catch (Exception e) {
      logger.info(e.getMessage() + " " + e.getCause());
      // throw new MultipartException("Constraints Violated");
      throw e;
    }
  }
Ejemplo n.º 18
0
  /**
   * create a FeatureSource with the specified Query
   *
   * @param entry
   * @param query - a query containing a filter that will be applied to the data
   */
  public ExcelFeatureSource(ContentEntry entry, Query query) {
    super(entry, query);
    Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0);

    dataStore = (ExcelDataStore) entry.getDataStore();

    sheet = dataStore.getSheet();
    latCol = dataStore.getLatColumnIndex();
    lonCol = dataStore.getLonColumnIndex();
    int rows = sheet.getPhysicalNumberOfRows();
    int start = dataStore.getHeaderRowIndex() + 1;
    latCol = dataStore.getLatColumnIndex();
    lonCol = dataStore.getLonColumnIndex();
    features = new ArrayList<SimpleFeature>();
    filteredFeatures = new ArrayList<SimpleFeature>();
    evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator();
    if (schema == null) {
      schema = getSchema();
    }
    GeometryFactory geometryFactory = dataStore.getGeometryFactory();

    SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
    Row header = sheet.getRow(dataStore.getHeaderRowIndex());
    for (int i = start; i < rows; i++) {
      Row data = sheet.getRow(i);
      double x = 0.0;
      double y = 0.0;
      for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) {
        final Cell cell = data.getCell(col);
        CellValue value = evaluator.evaluate(cell);
        if (col == latCol) {

          if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            y = value.getNumberValue();
          }
        } else if (col == lonCol) {
          if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            x = value.getNumberValue();
          }
        } else {
          // cast and handle
          final String name = header.getCell(col).getStringCellValue().trim();
          switch (value.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
              AttributeType type = schema.getType(name);
              Class<?> clazz = type.getBinding();
              if (clazz == Double.class) {
                builder.set(name, value.getNumberValue());
              } else if (clazz == java.sql.Date.class) {
                final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                final Calendar cal = Calendar.getInstance();
                cal.clear();
                cal.setTime(javaDate);
                java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());
                builder.set(name, date);
              } else if (clazz == java.util.Date.class) {
                final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                builder.set(name, javaDate);
              } else if (clazz == Time.class) {
                final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                final Calendar cal = Calendar.getInstance();
                cal.clear();
                cal.setTime(javaDate);
                cal.set(0, 0, 0);
                Time time = new Time(cal.getTimeInMillis());
                builder.set(name, time);
              }
              break;
            case Cell.CELL_TYPE_STRING:
              builder.set(name, value.getStringValue().trim());
              break;
            case Cell.CELL_TYPE_BOOLEAN:
              builder.set(name, value.getBooleanValue());
              break;

            default:
              System.out.println(
                  "We don't handle "
                      + cell.getCellType()
                      + " type cells "
                      + cell.getStringCellValue());
          }
        }
      }
      Point p = geometryFactory.createPoint(new Coordinate(x, y));
      builder.set("the_geom", p);

      SimpleFeature feature = builder.buildFeature(null);
      features.add(feature);
    }
    filterFeatures(query);
  }
Ejemplo n.º 19
0
  public void parse(Sheet sheet) {
    logger.debug("开始解析Sheet页:" + sheet.getSheetName());
    startSheet(sheet);

    int rowCount = sheet.getPhysicalNumberOfRows();
    logger.debug("行数:" + rowCount);

    // 遇到表头区域的时候记录开始列
    int headerStartCol = 0;

    for (Row r : sheet) {
      HSSFRow row = (HSSFRow) r;
      if (row == null) continue;

      this.rowIndex = row.getRowNum();
      if (rowIndex < startRow) {
        continue;
      }
      // sundl: row.getFirstCellNum()不是很稳定,第一列的空格有时候会是null,有时候又是空字符串,这样导致这个值不可信
      // 目前强制写成1, 后续可以考虑改成自己写找第一个非空列。
      int firstCellNum = row.getFirstCellNum();
      if (firstCellNum == -1) continue;
      else firstCellNum = 1;

      HSSFCell firstCell = row.getCell(firstCellNum);
      String firstcellString = getCellStringValue(firstCell, evaluator);

      if (!firstcellString.isEmpty()) {
        if (aresStart.contains(rowIndex)) {
          endArea();
          logger.debug("Area开始,行号: " + rowIndex);
          startArea(firstcellString);
        } else if (areaTags.indexOf(firstcellString) != -1) {
          endArea();
          logger.debug("Area开始,行号: " + rowIndex);
          startArea(firstcellString);
        }

        if (blockStart.keySet().contains(rowIndex)) {
          endBlock();
          startBlock(firstcellString, blockStart.get(rowIndex));
        } else if (blocks.containsKey(firstcellString)) {
          endBlock();
          startBlock(firstcellString, blocks.get(firstcellString));
          // Text区域第一行暂时不解析
          if (currentBlockType == BlockTypes.TEXT) {
            continue;
          }
        }
      }

      String[] strings = readRowStrings(row, 1, evaluator);
      switch (currentBlockType) {
        case BlockTypes.KEY_VALUE:
          parseKeyValue(row, 1);
          break;
        case BlockTypes.TABLE:
          if (!headerFound) {
            // header按理说不应该存在空字符串,开头应该不会,但结尾可能存在
            while (StringUtils.isEmpty(strings[strings.length - 1])) {
              strings = (String[]) ArrayUtils.remove(strings, strings.length - 1);
            }
            headerStartCol = firstCellNum;
            header(strings);
            headerFound = true;
          } else {
            strings = POIUtils.readRowStrings(row, headerStartCol, evaluator);
            row(strings);
          }
          break;
        case BlockTypes.TEXT:
          text.append(StringUtils.join(strings));
          text.append('\n');
          break;
      }
    }

    endBlock();
    endArea();
    endSheet();
  }
Ejemplo n.º 20
0
 public int getSheetRows(Sheet sheet) {
   return sheet.getPhysicalNumberOfRows();
 }
Ejemplo n.º 21
0
  /**
   * 기념일정보 excel생성
   *
   * @param inputStream
   */
  public List<AnnvrsryManageVO> selectAnnvrsryManageBnde(InputStream inputStream) {

    String sTempId = null; // 사용자ID
    String sTempNm = null; // 사용자명
    String sTempAnnvrsryDe = null; // 기념일자
    String sTempCldrSe = null; // 양/음 구분
    String sTempAnnvrsrySe = null; // 기념일구분
    String sTempAnnvrsryNm = null; // 기념일명
    String sTempReptitSe = null; // 반복여부

    List<AnnvrsryManageVO> list = new ArrayList<AnnvrsryManageVO>();
    try {
      Workbook hssfWB = excelZipService.loadWorkbook(inputStream);
      // 엑셀 파일 시트 갯수 확인 sheet = 1
      if (hssfWB.getNumberOfSheets() == 1) {
        Sheet annvrsrySheet = hssfWB.getSheetAt(0); // 기념일관리 시트 가져오기
        //				HSSFRow annvrsryRow = annvrsrySheet.getRow(1); // 기념일 row 가져오기
        //				int annvrsrySheetRowCnt = annvrsryRow.getPhysicalNumberOfCells(); // 기념일 cell Cnt
        int rowsCnt = annvrsrySheet.getPhysicalNumberOfRows(); // 행 갯수 가져오기

        // 사용자ID 기념일자 양/음 구분 기념일구분 기념일명
        for (int j = 1; j < rowsCnt; j++) { // row 루프
          AnnvrsryManageVO annvrsryManageVO = new AnnvrsryManageVO();
          Row row = annvrsrySheet.getRow(j); // row 가져오기
          if (row != null) {
            //						int cells = row.getPhysicalNumberOfCells(); // cell 갯수 가져오기
            Cell cell = null;
            cell = row.getCell(0); // 사용자ID
            if (cell != null) {
              sTempId = cell.getRichStringCellValue().toString();
            }

            cell = row.getCell(1); // 사용자명
            if (cell != null) {
              sTempNm = cell.getRichStringCellValue().toString();
            }

            cell = row.getCell(2); // 기념일자
            if (cell != null) {
              sTempAnnvrsryDe = cell.getRichStringCellValue().toString();
            }

            cell = row.getCell(3); // 양/음구분
            if (cell != null) {
              sTempCldrSe = cell.getRichStringCellValue().toString();
            }

            cell = row.getCell(4); // 기념일구분
            if (cell != null) {
              sTempAnnvrsrySe = (String) cell.getStringCellValue();
            }
            cell = row.getCell(5); // 기념일명
            if (cell != null) {
              sTempAnnvrsryNm = cell.getRichStringCellValue().toString();
            }
            cell = row.getCell(6); // 반복여부
            if (cell != null) {
              sTempReptitSe = cell.getRichStringCellValue().toString();
            }
            annvrsryManageVO.setUsid(sTempId); // 당직자ID
            annvrsryManageVO.setUsNm(sTempNm); // 당직자명
            annvrsryManageVO.setAnnvrsrySe(sTempAnnvrsrySe);
            annvrsryManageMapper.selectAnnvrsryManageBnde(annvrsryManageVO);
            annvrsryManageVO.setAnnvrsryDe(DateUtil.formatDate(sTempAnnvrsryDe, "-"));
            annvrsryManageVO.setCldrSe(sTempCldrSe);
            annvrsryManageVO.setAnnvrsryNm(sTempAnnvrsryNm);
            annvrsryManageVO.setReptitSe(sTempReptitSe);
            list.add(annvrsryManageVO);
          }
        }
      }
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
    return list;
  }
Ejemplo n.º 22
0
  void excelCopy() {
    FileInputStream file1;
    int lastRowUpdated = 0, currentRow = 0;
    int i = 0;
    try {
      file1 =
          new FileInputStream(
              "C:\\Users\\SharvaP\\Desktop\\Excel Format\\LookUp for Transaction Name.xls");
      int columnIndex = 0;
      Workbook lookupWorkbook = new HSSFWorkbook(file1);
      Sheet lookupSheet = lookupWorkbook.getSheetAt(0);

      FileInputStream file2 =
          new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round1_temp.xls");
      Workbook round1Workbook = new HSSFWorkbook(file2);
      Sheet round1Sheet = round1Workbook.getSheetAt(0);

      FileInputStream file3 =
          new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\template.xls");
      Workbook templateWorkbook = new HSSFWorkbook(file3);
      Sheet templateSheet = templateWorkbook.getSheetAt(0);

      FileInputStream file4 =
          new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round2_temp.xls");
      Workbook round2Workbook = new HSSFWorkbook(file4);
      Sheet round2Sheet = round2Workbook.getSheetAt(0);

      int averageRow = round1Sheet.getPhysicalNumberOfRows() - 2;
      int percentileRow = round1Sheet.getPhysicalNumberOfRows() - 1;
      System.out.println("Average row1:" + averageRow);
      int averageRow2 = round2Sheet.getPhysicalNumberOfRows() - 2;
      int percentileRow2 = round2Sheet.getPhysicalNumberOfRows() - 1;

      /*Row round1Row = round1Sheet.getRow(averageRow);
      Row round2Row = round2Sheet.getRow(averageRow2);

      Cell tempRound1Cell=round1Row.getCell(columnIndex);
      Cell tempRound2Cell=round2Row.getCell(columnIndex);
      CellStyle cellStyle = templateWorkbook.createCellStyle();

      CreationHelper createHelper=templateWorkbook.getCreationHelper();
      cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
      */

      Iterator<Row> rowIterator1 = lookupSheet.iterator();
      while (rowIterator1.hasNext()) {

        Row lookupRow = rowIterator1.next();
        if (lookupRow.getRowNum() > 0) {
          Cell lookupCell = lookupRow.getCell(1);
          String transationNameTemplate = lookupCell.getStringCellValue();
          // System.out.println("NAme:"+transationNameTemplate);
          // System.out.println(templateSheet.getPhysicalNumberOfRows());
          for (int count = lastRowUpdated;
              count < templateSheet.getPhysicalNumberOfRows() - 1;
              count++) {
            Row templateRow = templateSheet.getRow(count);

            Cell templateCell = templateRow.getCell(0);
            Cell templateCellFormulae1 = templateRow.getCell(5);
            Cell templateCellFormulae2 = templateRow.getCell(6);

            String templateCellName = templateCell.getStringCellValue();
            // System.out.println("template cell name:"+templateCellName);
            currentRow = templateRow.getRowNum();
            if (templateCellName.equals(transationNameTemplate)) {

              // System.out.println("Transac Name:"+templateCellName+", row num:"+currentRow+", last
              // updated row:"+lastRowUpdated);
              Row round1RowAverage = round1Sheet.getRow(averageRow);
              Cell round1CellAverage = round1RowAverage.getCell(columnIndex);
              Row round2RowAverage = round2Sheet.getRow(averageRow2);
              Cell round2CellAverage = round2RowAverage.getCell(columnIndex);
              Cell templateCellRound1Average = templateRow.getCell(1);
              Cell templateCellRound2Average = templateRow.getCell(3);
              templateCellFormulae1.setCellFormula(
                  "IF(B"
                      + (currentRow + 1)
                      + ">D"
                      + (currentRow + 1)
                      + ",((B"
                      + (currentRow + 1)
                      + "-D"
                      + (currentRow + 1)
                      + ")/B"
                      + (currentRow + 1)
                      + ")*100,((B"
                      + (currentRow + 1)
                      + "-D"
                      + (currentRow + 1)
                      + ")/D"
                      + (currentRow + 1)
                      + ")*100)");
              System.out.println(round1CellAverage.getNumericCellValue());
              templateCellRound1Average.setCellValue(round1CellAverage.getNumericCellValue());
              templateCellRound2Average.setCellValue(round2CellAverage.getNumericCellValue());

              Row round1RowPercentile = round1Sheet.getRow(percentileRow);
              Cell round1CellPercentile = round1RowPercentile.getCell(columnIndex);
              Row round2RowPercentile = round2Sheet.getRow(percentileRow2);
              Cell round2CellPercentile = round2RowPercentile.getCell(columnIndex);
              Cell templateCellRound1Percentile = templateRow.getCell(2);
              Cell templateCellRound2Percentile = templateRow.getCell(4);
              templateCellFormulae2.setCellFormula(
                  "IF(C"
                      + (currentRow + 1)
                      + ">E"
                      + (currentRow + 1)
                      + ",((C"
                      + (currentRow + 1)
                      + "-E"
                      + (currentRow + 1)
                      + ")/C"
                      + (currentRow + 1)
                      + ")*100,((C"
                      + (currentRow + 1)
                      + "-E"
                      + (currentRow + 1)
                      + ")/E"
                      + (currentRow + 1)
                      + ")*100)");
              templateCellRound1Percentile.setCellValue(round1CellPercentile.getNumericCellValue());
              templateCellRound2Percentile.setCellValue(round2CellPercentile.getNumericCellValue());

              // System.out.println("round1 avg value:"+round1CellAverage.getNumericCellValue());
              // System.out.println("round2 avg value:"+round2CellAverage.getNumericCellValue());

              // System.out.println("Match Found"+templateRow.getCell(1).getNumericCellValue());
              columnIndex++;
              lastRowUpdated = currentRow;
              break;
            }
          }
        }
      }

      FileOutputStream out =
          new FileOutputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\Template.xls");
      templateWorkbook.write(out);
      out.close();
      System.out.println("Finished....");
      file1.close();
      file2.close();
      file3.close();
      file4.close();
    } catch (FileNotFoundException e) {
      System.out.println(e.getMessage());
      e.printStackTrace();
    } catch (IOException e) {
      System.out.println(e.getMessage());
      e.printStackTrace();
    }
  }