@Override
 public Result downloadAndParse() {
   Result result = officeSheetPopulator.downloadAndParse();
   if (result.isSuccess()) result = clientSheetPopulator.downloadAndParse();
   if (result.isSuccess()) result = extrasSheetPopulator.downloadAndParse();
   if (result.isSuccess()) {
     try {
       restClient.createAuthToken();
       content = restClient.get("savingsaccounts?limit=-1");
       Gson gson = new Gson();
       JsonParser parser = new JsonParser();
       JsonObject obj = parser.parse(content).getAsJsonObject();
       JsonArray array = obj.getAsJsonArray("pageItems");
       Iterator<JsonElement> iterator = array.iterator();
       while (iterator.hasNext()) {
         JsonElement json = iterator.next();
         CompactSavingsAccount savingsAccount = gson.fromJson(json, CompactSavingsAccount.class);
         if (savingsAccount.isActive()) savings.add(savingsAccount);
       }
     } catch (Exception e) {
       result.addError(e.getMessage());
       logger.error(e.getMessage());
     }
   }
   return result;
 }
 private Result populateLoansTable(Sheet savingsTransactionSheet) {
   Result result = new Result();
   Workbook workbook = savingsTransactionSheet.getWorkbook();
   CellStyle dateCellStyle = workbook.createCellStyle();
   short df = workbook.createDataFormat().getFormat("dd/mm/yy");
   dateCellStyle.setDataFormat(df);
   int rowIndex = 1;
   Row row;
   Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
   try {
     for (CompactSavingsAccount savingsAccount : savings) {
       row = savingsTransactionSheet.createRow(rowIndex++);
       writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
       writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
       writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
       writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
       writeDate(
           LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
           row,
           savingsAccount.getTimeline().getActivatedOnDate().get(2)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(1)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(0),
           dateCellStyle);
     }
   } catch (Exception e) {
     result.addError(e.getMessage());
     logger.error(e.getMessage());
   }
   return result;
 }
 @Override
 public Result parse() {
   Result result = new Result();
   Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
   Integer noOfEntries = getNumberOfRows(savingsTransactionSheet, AMOUNT_COL);
   for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
     Row row;
     try {
       row = savingsTransactionSheet.getRow(rowIndex);
       if (isNotImported(row, STATUS_COL)) savingsTransactions.add(parseAsTransaction(row));
     } catch (Exception e) {
       logger.error("row = " + rowIndex, e);
       result.addError("Row = " + rowIndex + " , " + e.getMessage());
     }
   }
   return result;
 }
  @Override
  public Result upload() {
    Result result = new Result();
    Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");

    for (Transaction transaction : savingsTransactions) {
      try {
        Gson gson = new Gson();
        String payload = gson.toJson(transaction);
        restClient.post(
            "recurringdepositaccounts/"
                + transaction.getAccountId()
                + "/transactions?command="
                + transaction.getTransactionType(),
            payload);

        Cell statusCell =
            savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
        statusCell.setCellValue("Imported");
        statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.LIGHT_GREEN));
      } catch (Exception e) {
        Cell savingsAccountIdCell =
            savingsTransactionSheet
                .getRow(transaction.getRowIndex())
                .createCell(SAVINGS_ACCOUNT_NO_COL);
        savingsAccountIdCell.setCellValue(transaction.getAccountId());
        String message = parseStatus(e.getMessage());

        Cell statusCell =
            savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
        statusCell.setCellValue(message);
        statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.RED));
        result.addError("Row = " + transaction.getRowIndex() + " ," + message);
      }
    }
    savingsTransactionSheet.setColumnWidth(STATUS_COL, 15000);
    writeString(STATUS_COL, savingsTransactionSheet.getRow(0), "Status");
    return result;
  }
  private Result setRules(Sheet worksheet) {
    Result result = new Result();
    try {
      CellRangeAddressList officeNameRange =
          new CellRangeAddressList(
              1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), OFFICE_NAME_COL, OFFICE_NAME_COL);
      CellRangeAddressList clientNameRange =
          new CellRangeAddressList(
              1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CLIENT_NAME_COL, CLIENT_NAME_COL);
      CellRangeAddressList accountNumberRange =
          new CellRangeAddressList(
              1,
              SpreadsheetVersion.EXCEL97.getLastRowIndex(),
              SAVINGS_ACCOUNT_NO_COL,
              SAVINGS_ACCOUNT_NO_COL);
      CellRangeAddressList transactionTypeRange =
          new CellRangeAddressList(
              1,
              SpreadsheetVersion.EXCEL97.getLastRowIndex(),
              TRANSACTION_TYPE_COL,
              TRANSACTION_TYPE_COL);
      CellRangeAddressList paymentTypeRange =
          new CellRangeAddressList(
              1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), PAYMENT_TYPE_COL, PAYMENT_TYPE_COL);
      CellRangeAddressList transactionDateRange =
          new CellRangeAddressList(
              1,
              SpreadsheetVersion.EXCEL97.getLastRowIndex(),
              TRANSACTION_DATE_COL,
              TRANSACTION_DATE_COL);

      DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);

      setNames(worksheet);

      DataValidationConstraint officeNameConstraint =
          validationHelper.createFormulaListConstraint("Office");
      DataValidationConstraint clientNameConstraint =
          validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))");
      DataValidationConstraint accountNumberConstraint =
          validationHelper.createFormulaListConstraint(
              "INDIRECT(CONCATENATE(\"Account_\",SUBSTITUTE($B1,\" \",\"_\")))");
      DataValidationConstraint transactionTypeConstraint =
          validationHelper.createExplicitListConstraint(new String[] {"Withdrawal", "Deposit"});
      DataValidationConstraint paymentTypeConstraint =
          validationHelper.createFormulaListConstraint("PaymentTypes");
      DataValidationConstraint transactionDateConstraint =
          validationHelper.createDateConstraint(
              DataValidationConstraint.OperatorType.BETWEEN,
              "=VLOOKUP($C1,$Q$2:$T$" + (savings.size() + 1) + ",4,FALSE)",
              "=TODAY()",
              "dd/mm/yy");

      DataValidation officeValidation =
          validationHelper.createValidation(officeNameConstraint, officeNameRange);
      DataValidation clientValidation =
          validationHelper.createValidation(clientNameConstraint, clientNameRange);
      DataValidation accountNumberValidation =
          validationHelper.createValidation(accountNumberConstraint, accountNumberRange);
      DataValidation transactionTypeValidation =
          validationHelper.createValidation(transactionTypeConstraint, transactionTypeRange);
      DataValidation paymentTypeValidation =
          validationHelper.createValidation(paymentTypeConstraint, paymentTypeRange);
      DataValidation transactionDateValidation =
          validationHelper.createValidation(transactionDateConstraint, transactionDateRange);

      worksheet.addValidationData(officeValidation);
      worksheet.addValidationData(clientValidation);
      worksheet.addValidationData(accountNumberValidation);
      worksheet.addValidationData(transactionTypeValidation);
      worksheet.addValidationData(paymentTypeValidation);
      worksheet.addValidationData(transactionDateValidation);

    } catch (RuntimeException re) {
      result.addError(re.getMessage());
    }
    return result;
  }