Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getRowNum.

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.elecnor.ecosystem.helper.LocalLicenseHelper.java

public HashMap<String, Object> validateRowDataAndFetchBean(Row row, UserDetail userDetail) {
    int rowNumber = row.getRowNum();
    ArrayList<ExcelErrorDetails> rowErrorList = new ArrayList<ExcelErrorDetails>();
    String stringToCheck = "";
    UploadFileUtility upUtil = new UploadFileUtility();
    int colNum;//from  w w  w.  j av  a2 s  . c om
    boolean isRowHavingErrors = false;
    LicenseDirectory licenseDirectory = new LicenseDirectory();
    Map<String, Object> rowValidationReturn = new HashMap<String, Object>();

    colNum = ConstantUtil.LOCAL_LICENSE_DATA_LOCAL_JURISDICTION;
    try {
        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
        licenseDirectory.setLocalJurisdiction(stringToCheck);
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(
                upUtil.getExcelErrorDetails(rowNumber, colNum, ConstantUtil.ERROR_STRING_VALIDATION_ERROR));
        isRowHavingErrors = true;

    }
    stringToCheck = "";
    colNum = ConstantUtil.LOCAL_LICENSE_DATA_LICENSE_NO;
    try {
        stringToCheck = String
                .valueOf((int) row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getNumericCellValue());
        licenseDirectory.setLicenseNumber(stringToCheck);

    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(
                upUtil.getExcelErrorDetails(rowNumber, colNum, ConstantUtil.ERROR_INTEGER_VALIDATION_ERROR));
        isRowHavingErrors = true;

    }

    //validate Expiry date
    stringToCheck = "";
    colNum = ConstantUtil.LOCAL_LICENSE_DATA_EXPIRY_DATE;
    try {
        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
        DateFormat format = new SimpleDateFormat("dd-mm-yyyy", Locale.ENGLISH);
        Date date = format.parse(stringToCheck);
        licenseDirectory.setExpiryDate(date);
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(
                upUtil.getExcelErrorDetails(rowNumber, colNum, ConstantUtil.ERROR_STRING_VALIDATION_ERROR));
        isRowHavingErrors = true;
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    licenseDirectory.setStatus("ACTIVE");
    licenseDirectory.setType("LOCAL");
    licenseDirectory.setLicenseSubmittedBy(userDetail);
    licenseDirectory.setDomainDetail(userDetail.getDomainDetail());
    //validate Primary Person

    stringToCheck = "";
    colNum = ConstantUtil.LOCAL_LICENSE_DATA_PRIMARY_PERSON;
    try {

        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
        licenseDirectory.setPrimaryPerson(stringToCheck);

    } catch (Exception e) {

        licenseDirectory.setPrimaryPerson("");

    }
    ExcelErrorDetails excelErrorDetailsBasedOnBeanValidations = upUtil
            .getExcelErrorDetailsBasedOnBeanValidations(row.getRowNum(), licenseDirectory);
    if (excelErrorDetailsBasedOnBeanValidations != null) {
        isRowHavingErrors = true;
        rowErrorList.add(excelErrorDetailsBasedOnBeanValidations);
    }

    //}
    if (isRowHavingErrors) {
        rowValidationReturn.put("errorList", rowErrorList);
        rowValidationReturn.put("licenseDirectoryBean", null);

    } else {
        rowValidationReturn.put("errorList", null);
        rowValidationReturn.put("licenseDirectoryBean", licenseDirectory);
    }
    return (HashMap<String, Object>) rowValidationReturn;

}

From source file:com.elecnor.ecosystem.helper.StateLicenseHelper.java

public HashMap<String, Object> validateRowDataAndFetchBean(Row row, UserDetail userDetail) {
    int rowNumber = row.getRowNum();
    ArrayList<String> states = new ArrayList<String>();
    String states_temp[] = { "Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado",
            "Connecticut", "Delaware", "Florida", "Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa",
            "Kansas", "Kentucky", "Louisiana", "Maine", "Maryland", "Massachusetts", "Michigan", "Minnesota",
            "Mississippi", "Missouri", "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey",
            "New Mexico", "New York", "North Carolina", "North Dakota", "Ohio", "Oklahoma", "Oregon",
            "Pennsylvania", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah",
            "Vermont", "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming" };

    for (int i = 0; i < states_temp.length; i++) {
        states.add(states_temp[i]);//from  w ww  .ja  v a 2  s.c  o  m
    }
    ArrayList<ExcelErrorDetails> rowErrorList = new ArrayList<ExcelErrorDetails>();
    String stringToCheck = "";
    UploadFileUtility upUtil = new UploadFileUtility();
    int colNum;
    boolean isRowHavingErrors = false;
    LicenseDirectory licenseDirectory = new LicenseDirectory();
    Map<String, Object> rowValidationReturn = new HashMap<String, Object>();
    colNum = ConstantUtil.STATE_LICENSE_DATA_LICENSE_NO;
    try {

        stringToCheck = String
                .valueOf((int) row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getNumericCellValue());
        licenseDirectory.setLicenseNumber(stringToCheck);

    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(
                upUtil.getExcelErrorDetails(rowNumber, colNum, ConstantUtil.ERROR_STRING_VALIDATION_ERROR));
        isRowHavingErrors = true;
        illegalStateException.printStackTrace();
    }
    //validate state
    stringToCheck = "";
    colNum = ConstantUtil.STATE_LICENSE_DATA_STATE;
    try {
        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue();

        licenseDirectory.setState(states.indexOf(stringToCheck));
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(
                upUtil.getExcelErrorDetails(rowNumber, colNum, ConstantUtil.ERROR_STRING_VALIDATION_ERROR));
        isRowHavingErrors = true;
        illegalStateException.printStackTrace();
    }

    //validate Expiry date
    stringToCheck = "";
    colNum = ConstantUtil.STATE_LICENSE_DATA_EXPIRY_DATE;
    try {
        stringToCheck = String
                .valueOf((row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
        DateFormat format = new SimpleDateFormat("dd-mm-yyyy", Locale.ENGLISH);
        Date date = format.parse(stringToCheck);
        licenseDirectory.setExpiryDate(date);
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(
                upUtil.getExcelErrorDetails(rowNumber, colNum, ConstantUtil.ERROR_STRING_VALIDATION_ERROR));
        isRowHavingErrors = true;
        illegalStateException.printStackTrace();
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    licenseDirectory.setStatus("ACTIVE");
    licenseDirectory.setType("STATE");
    licenseDirectory.setLicenseSubmittedBy(userDetail);
    licenseDirectory.setDomainDetail(userDetail.getDomainDetail());
    //validate Primary Person

    stringToCheck = "";
    colNum = ConstantUtil.STATE_LICENSE_DATA_PRIMARY_PERSON;
    try {

        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
        licenseDirectory.setLicenseDescription(stringToCheck);

    } catch (Exception e) {

        licenseDirectory.setLicenseDescription("");
        e.printStackTrace();
    }
    ExcelErrorDetails excelErrorDetailsBasedOnBeanValidations = upUtil
            .getExcelErrorDetailsBasedOnBeanValidations(row.getRowNum(), licenseDirectory);
    if (excelErrorDetailsBasedOnBeanValidations != null) {
        isRowHavingErrors = true;
        rowErrorList.add(excelErrorDetailsBasedOnBeanValidations);
    }

    //}
    if (isRowHavingErrors) {
        rowValidationReturn.put("errorList", rowErrorList);
        rowValidationReturn.put("licenseDirectoryBean", null);

    } else {
        rowValidationReturn.put("errorList", null);
        rowValidationReturn.put("licenseDirectoryBean", licenseDirectory);
    }
    return (HashMap<String, Object>) rowValidationReturn;

}

From source file:com.elecnor.ecosystem.serviceimpl.LicenseDirectoryServiceImpl.java

License:Open Source License

public ArrayList<ExcelErrorDetails> validateRowData(Row row) {
    int rowNumber = row.getRowNum();
    ArrayList<ExcelErrorDetails> rowErrorList = new ArrayList<ExcelErrorDetails>();
    String stringToCheck = "";
    int colNum;/*from w  w w .j  ava  2  s .  c  om*/
    String fieldToCheck = "";
    boolean valueNotValid = false;

    // validation for License Number
    fieldToCheck = "STATE_LICENSE_DATA_LICENSE_NO";
    colNum = readFromHeader(null, fieldToCheck);
    try {
        stringToCheck = String
                .valueOf((long) (row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getNumericCellValue()));
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "License No. should not have characters"));
        valueNotValid = true;
    }
    if (!valueNotValid) {
        if (stringToCheck.equals("0")) {

            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "License Number cannot be null"));
        }
    }
    valueNotValid = false;

    // validation for State
    fieldToCheck = "STATE_LICENSE_DATA_STATE";
    colNum = readFromHeader(null, fieldToCheck);
    try {
        stringToCheck = String
                .valueOf((int) (row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getNumericCellValue()));
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Choose state from the given options"));
        valueNotValid = true;
    }
    valueNotValid = false;

    // validation for Expiry Date
    fieldToCheck = "STATE_LICENSE_DATA_EXPIRY_DATE";
    colNum = readFromHeader(null, fieldToCheck);
    try {
        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(getExcelErrorDetails(rowNumber, colNum,
                "Expiry date should be in proper format and with '-' or '.' as separators"));
        valueNotValid = true;
    }
    if (!valueNotValid) {
        if (stringToCheck == null) {
            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Expiry Date cannot be null"));
        }
        if (!stringToCheck.matches("^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}$")) {
            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Wrong Date Format"));
        }
    }
    valueNotValid = false;

    if (!rowErrorList.isEmpty()) {
        return rowErrorList;
    } else
        return null;
}

From source file:com.elecnor.ecosystem.serviceimpl.LicenseDirectoryServiceImpl.java

License:Open Source License

public ArrayList<ExcelErrorDetails> validateLocalRowData(Row row) {
    int rowNumber = row.getRowNum();
    ArrayList<ExcelErrorDetails> rowErrorList = new ArrayList<ExcelErrorDetails>();
    String stringToCheck = "";
    int colNum;//from w  ww .j  a v a 2  s.  c o  m
    String fieldToCheck = "";
    boolean valueNotValid = false;

    // validation for License Number
    fieldToCheck = "LOCAL_LICENSE_DATA_LICENSE_NO";
    colNum = readFromLocalHeader(null, fieldToCheck);
    try {
        stringToCheck = String
                .valueOf((int) (row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getNumericCellValue()));
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "License No. should not have characters"));
        valueNotValid = true;
    }
    if (!valueNotValid) {
        if (stringToCheck.equals("0")) {

            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "License Number cannot be null"));
        }
    }

    // validation for Local Jurisdiction
    fieldToCheck = "LOCAL_LICENSE_DATA_LOCAL_JURISDICTION";
    colNum = readFromLocalHeader(null, fieldToCheck);
    try {
        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue().trim();
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Local Jurisdiction should have characters"));
        valueNotValid = true;
    }
    if (!valueNotValid) {
        if (stringToCheck.equals("")) {
            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Local Jurisdiction cannot be null"));
        }
    }

    // validation for Expiry Date
    fieldToCheck = "LOCAL_LICENSE_DATA_EXPIRY_DATE";
    colNum = readFromHeader(null, fieldToCheck);
    try {
        stringToCheck = row.getCell(colNum, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
    } catch (IllegalStateException illegalStateException) {
        rowErrorList.add(getExcelErrorDetails(rowNumber, colNum,
                "Expiry date should be in proper format and with '-' or '.' as separators"));
        valueNotValid = true;
    }
    if (!valueNotValid) {
        if (stringToCheck == null) {
            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Expiry Date cannot be null"));
        }
        if (!stringToCheck.matches("^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}$")) {
            rowErrorList.add(getExcelErrorDetails(rowNumber, colNum, "Wrong Date Format"));
        }
    }

    if (!rowErrorList.isEmpty()) {
        return rowErrorList;
    } else
        return null;
}

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * purpose of this method to create ValueList along with types and column
 * name. Simple design followed : Just have ColumnMetaData object which
 * contains everything. For a Cell we will have one columnMetaData object
 * and it will have values across the/* w ww . java 2 s . c  o m*/
 * 
 * @param row
 * @throws Exception
 */
private void readARow(Row row, int nbrColumnsInARow) throws ExilityException {
    Value[] columnValues = new Value[nbrColumnsInARow];
    Value aColumnValue = null;
    String rawValue = null;

    for (int c = 0; c < nbrColumnsInARow; c++) {
        Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);

        ColumnMetaData columnInfo = this.columnsData.get(new Integer(c));
        int xlsColumnDataType = columnInfo.getXlsDataType();
        DataValueType exilDataType = null;

        int cellType = cell.getCellType();
        if (xlsColumnDataType != XLSReader.UNKNOWN_TYPE) {
            cellType = xlsColumnDataType;
        }

        try {

            switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:

                if (DateUtil.isCellDateFormatted(cell)) {
                    rawValue = DateUtility.formatDate(cell.getDateCellValue());
                    /*
                     * returns yyyy-mm-dd hh:mm:ss.sss full date with time.
                     */

                    exilDataType = DataValueType.DATE;
                } else {
                    double decimalNumber = cell.getNumericCellValue();
                    rawValue = NumberToTextConverter.toText(decimalNumber);

                    boolean isDecimal = rawValue.contains(".");
                    if (isDecimal) {
                        exilDataType = DataValueType.DECIMAL;
                    } else {
                        exilDataType = DataValueType.INTEGRAL;
                    }
                }

                break;

            case Cell.CELL_TYPE_STRING:

                rawValue = cell.getStringCellValue().trim();
                exilDataType = DataValueType.TEXT;
                break;

            case Cell.CELL_TYPE_FORMULA:

                rawValue = cell.getStringCellValue().trim();
                exilDataType = DataValueType.TEXT;
                break;

            case Cell.CELL_TYPE_BLANK:

                rawValue = cell.getStringCellValue();
                exilDataType = DataValueType.NULL;
                columnInfo.setExilDataType(exilDataType);
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                rawValue = cell.getBooleanCellValue() ? BooleanValue.TRUE : BooleanValue.FALSE;
                exilDataType = DataValueType.BOOLEAN;
                break;
            default:
                String msg = columnInfo.getColumnName() + XLSReader.INVALID_COLUMN_TYPE + row.getRowNum();
                Spit.out(msg);

            }

        } catch (Exception e) {
            // Trying to set valueType value and expected valueType value
            // for column in row
            String[] params = { this.getXlsTypeAsText(cell.getCellType()), this.getXlsTypeAsText(cellType),
                    columnInfo.getColumnName(), "" + row.getRowNum() };

            String message = this.replaceMessageParams(XLSReader.DATATYPE_MISMATCH, params);
            throw new ExilityException(message);
        }

        if (xlsColumnDataType == XLSReader.UNKNOWN_TYPE && cellType != Cell.CELL_TYPE_BLANK) {
            columnInfo.setXlsDataType(cellType);
            columnInfo.setExilDataType(exilDataType);
        }

        exilDataType = columnInfo.getExilDataType();

        aColumnValue = Value.newValue(rawValue, exilDataType);

        columnValues[c] = aColumnValue;
        this.columnsData.put(new Integer(c), columnInfo);
    }

    this.rows.add(columnValues);
}

From source file:com.fengduo.spark.commons.file.excel.ExportExcel.java

License:Open Source License

/**
 * ?annotation.ExportField?//ww w .ja v a  2  s .  c  o m
 * 
 * @return list ?
 */
public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = Reflections.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }
                }
                // If is dict, get dict label
                // if (StringUtils.isNotBlank(ef.dictType())) {
                // val = DictUtils.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
                // }
            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

From source file:com.fingence.slayer.service.impl.AssetLocalServiceImpl.java

License:Open Source License

public void loadPricingData(long userId, File excelFile, ServiceContext serviceContext, int type) {

    System.out.println("inside Load Pricing Data....");
    if (Validator.isNull(excelFile))
        return;/*from   ww w .  j  a va2s .  c  o  m*/

    InputStream is = null;
    try {
        is = new FileInputStream(excelFile);
    } catch (FileNotFoundException e) {
        //e.printStackTrace();
    }

    if (Validator.isNull(is))
        return;

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

    if (Validator.isNull(workbook))
        return;

    // Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();
    Map<Integer, Long> columnNames = new HashMap<Integer, Long>();
    int columnCount = 0;

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        columnCount = row.getPhysicalNumberOfCells();

        _log.debug("processing row ==> " + row.getRowNum());
        System.out.println("processing row ==> " + row.getRowNum());

        int rowNum = row.getRowNum();

        if (rowNum == 0)
            continue;

        if (rowNum == 1) {
            for (int i = 0; i < columnCount; i++) {
                Cell cell = row.getCell(i);
                if (Validator.isNull(cell))
                    continue;

                String id_isin = CellUtil.getString(cell);

                Asset asset = null;
                try {
                    asset = assetPersistence.fetchByIdISIN(id_isin);
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                if (Validator.isNull(asset))
                    continue;

                columnNames.put(i, asset.getAssetId());
            }
            continue;
        }

        if (rowNum > 1 && rowNum < 14)
            continue;

        System.out.println("going to process data...");

        Iterator<Integer> itr = columnNames.keySet().iterator();

        //for (int i=3; i < columnCount; i++){

        while (itr.hasNext()) {

            int i = itr.next();
            Date date = CellUtil.getDate(row.getCell(i));

            if (Validator.isNull(date))
                continue;

            long assetId = 0l;
            try {
                assetId = columnNames.get(i);
            } catch (Exception e) {
                _log.debug(e.getMessage() + ": There is an exception...");
                continue;
            }

            double value = CellUtil.getDouble(row.getCell(++i));

            History history = null;
            try {
                history = historyPersistence.fetchByAssetId_Date_Type(assetId, date, type);
                _log.debug("history record already present...");
            } catch (SystemException e) {
                e.printStackTrace();
            }

            if (Validator.isNull(history)) {
                long recId = 0l;
                try {
                    recId = counterLocalService.increment(History.class.getName());
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                history = historyLocalService.createHistory(recId);
                history.setAssetId(assetId);
                history.setType(type);
                history.setValue(value);
                history.setLogDate(date);

                if (type == IConstants.HISTORY_TYPE_BOND_CASHFLOW) {
                    double principal = CellUtil.getDouble(row.getCell(++i));
                    history.setPrincipal(principal);
                }

                try {
                    history = historyLocalService.addHistory(history);
                } catch (SystemException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

From source file:com.fingence.slayer.service.impl.AssetLocalServiceImpl.java

License:Open Source License

public void loadDividends(long userId, File excelFile, ServiceContext serviceContext) {

    System.out.println("inside Load Dividends Data....");
    if (Validator.isNull(excelFile))
        return;//w w w.j a  va 2  s  .c  o m

    InputStream is = null;
    try {
        is = new FileInputStream(excelFile);
    } catch (FileNotFoundException e) {
        //e.printStackTrace();
    }

    if (Validator.isNull(is))
        return;

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

    if (Validator.isNull(workbook))
        return;

    // Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();
    Map<Integer, Long> columnNames = new HashMap<Integer, Long>();
    int columnCount = 0;

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        columnCount = row.getPhysicalNumberOfCells();

        _log.debug("processing row ==> " + row.getRowNum());
        System.out.println("processing row ==> " + row.getRowNum());

        if (row.getRowNum() == 0)
            continue;

        if (row.getRowNum() == 1) {
            for (int i = 0; i < columnCount; i++) {
                Cell cell = row.getCell(i);
                if (Validator.isNull(cell))
                    continue;

                String id_isin = CellUtil.getString(cell);

                Asset asset = null;
                try {
                    asset = assetPersistence.fetchByIdISIN(id_isin);
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                if (Validator.isNull(asset))
                    continue;

                columnNames.put(i, asset.getAssetId());
            }
            continue;
        }

        for (int i = 0; i < columnCount; i++) {
            Date declaredDate = CellUtil.getDate(row.getCell(i));

            if (Validator.isNull(declaredDate))
                continue;

            long assetId = 0l;
            try {
                assetId = columnNames.get(i);
            } catch (Exception e) {
                _log.debug(e.getMessage() + ": There is an exception...");
                continue;
            }

            Date exDate = CellUtil.getDate(row.getCell(++i));

            Date recordDate = CellUtil.getDate(row.getCell(++i));

            Date payableDate = CellUtil.getDate(row.getCell(++i));

            double amount = CellUtil.getDouble(row.getCell(++i));
            String frequency = CellUtil.getString(row.getCell(++i));
            String type = CellUtil.getString(row.getCell(++i));

            Dividend dividend = null;
            try {
                dividend = dividendPersistence.fetchByAssetId_DeclaredDate(assetId, declaredDate);
                _log.debug("dividend record already present...");
            } catch (SystemException e) {
                e.printStackTrace();
            }

            if (Validator.isNull(dividend)) {

                long recId = 0l;
                try {
                    recId = counterLocalService.increment(Dividend.class.getName());
                } catch (SystemException e) {
                    e.printStackTrace();
                }

                dividend = dividendPersistence.create(recId);
            }

            // update the record
            dividend.setDeclaredDate(declaredDate);
            dividend.setExDate(exDate);
            dividend.setAssetId(assetId);
            dividend.setRecordDate(recordDate);
            dividend.setPayableDate(payableDate);
            dividend.setAmount(amount);
            dividend.setFrequency(frequency);
            dividend.setType(type);

            try {
                dividend = dividendLocalService.updateDividend(dividend);
                System.out.println("dividend new history records..." + dividend);
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:com.fingence.slayer.service.impl.AssetLocalServiceImpl.java

License:Open Source License

public void importFromExcel(long userId, File excelFile, ServiceContext serviceContext) {

    if (Validator.isNull(excelFile))
        return;//from  w  ww .j a  v a2 s  .c o  m

    InputStream is = null;
    try {
        is = new FileInputStream(excelFile);
    } catch (FileNotFoundException e) {
        //e.printStackTrace();
    }

    if (Validator.isNull(is))
        return;

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

    if (Validator.isNull(workbook))
        return;

    // Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    // Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();
    Map<String, Integer> columnNames = new HashMap<String, Integer>();
    int columnCount = 0;

    long bbSecurityVocabularyId = AssetHelper.getVocabularyId(userId, "BB_Security", serviceContext);
    long bbIndustryVocabularyId = AssetHelper.getVocabularyId(userId, "BB_Industry", serviceContext);
    long bbAssetClassVocabularyId = AssetHelper.getVocabularyId(userId, "BB_Asset_Class", serviceContext);

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        columnCount = row.getPhysicalNumberOfCells();

        if (row.getRowNum() == 0) {
            for (int i = 0; i < columnCount; i++) {
                Cell cell = row.getCell(i);
                if (Validator.isNotNull(cell)) {
                    columnNames.put(CellUtil.getStringCaps(cell), i);
                }
            }
            continue;
        }

        String id_isin = CellUtil.getString(row.getCell(columnNames.get("ID_ISIN")));

        if (Validator.isNull(id_isin)) {
            System.out.println("id_isin is null or empty.. continuing...the name is..."
                    + CellUtil.getString(row.getCell(columnNames.get("NAME"))));
            continue;
        }

        Asset asset = getAsset(userId, id_isin);

        asset.setSecurity_ticker(CellUtil.getString(row.getCell(columnNames.get("SECURITY_TICKER"))));
        asset.setId_cusip(CellUtil.getString(row.getCell(columnNames.get("ID_CUSIP"))));
        asset.setId_bb_global(CellUtil.getString(row.getCell(columnNames.get("ID_BB_GLOBAL"))));
        asset.setId_bb_sec_num_src(CellUtil.getLong(row.getCell(columnNames.get("ID_BB_SEC_NUM_SRC"))));
        asset.setName(CellUtil.getString(row.getCell(columnNames.get("NAME"))));
        asset.setChg_pct_mtd(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_MTD"))));
        asset.setChg_pct_5d(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_5D"))));
        asset.setChg_pct_1m(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_1M"))));
        asset.setChg_pct_3m(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_3M"))));
        asset.setChg_pct_6m(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_6M"))));
        asset.setChg_pct_ytd(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_YTD"))));
        asset.setBid_price(CellUtil.getDouble(row.getCell(columnNames.get("PX_BID"))));
        asset.setAsk_price(CellUtil.getDouble(row.getCell(columnNames.get("PX_ASK"))));
        asset.setLast_price(CellUtil.getDouble(row.getCell(columnNames.get("PX_LAST"))));
        asset.setChg_pct_high_52week(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_HIGH_52WEEK"))));
        asset.setChg_pct_low_52week(CellUtil.getDouble(row.getCell(columnNames.get("CHG_PCT_LOW_52WEEK"))));
        asset.setSecurity_des(CellUtil.getString(row.getCell(columnNames.get("SECURITY_DES"))));
        asset.setParent_comp_name(CellUtil.getString(row.getCell(columnNames.get("PARENT_COMP_NAME"))));

        String securityClass = CellUtil
                .getString(row.getCell(columnNames.get("BPIPE_REFERENCE_SECURITY_CLASS")));

        if (securityClass.equalsIgnoreCase("FixedIncome")) {
            securityClass = "Fixed Income";
        }

        asset.setVolatility_30d(CellUtil.getDouble(row.getCell(columnNames.get("VOLATILITY_30D"))));
        asset.setVolatility_90d(CellUtil.getDouble(row.getCell(columnNames.get("VOLATILITY_90D"))));
        asset.setVolatility_180d(CellUtil.getDouble(row.getCell(columnNames.get("VOLATILITY_180D"))));
        asset.setVolatility_360d(CellUtil.getDouble(row.getCell(columnNames.get("VOLATILITY_360D"))));

        asset.setCurrency(CellUtil.getString(row.getCell(columnNames.get("CRNCY"))).toUpperCase());

        Country country = null;
        try {
            String countryCode = CellUtil.getString(row.getCell(columnNames.get("CNTRY_OF_DOMICILE")));

            if (countryCode.equalsIgnoreCase("SP")) {
                countryCode = "ES";
            } else if (countryCode.equalsIgnoreCase("EN")) {
                countryCode = "GB";
            }
            country = CountryServiceUtil.fetchCountryByA2(countryCode);
        } catch (SystemException e) {
            e.printStackTrace();
        }
        if (Validator.isNotNull(country)) {
            asset.setCountry(country.getCountryId());
        }

        country = null;
        try {
            //CNTRY_OF_RISK
            String countryCode = CellUtil.getString(row.getCell(columnNames.get("CNTRY_OF_RISK")));

            if (countryCode.equalsIgnoreCase("SP")) {
                countryCode = "ES";
            } else if (countryCode.equalsIgnoreCase("EN")) {
                countryCode = "GB";
            }

            country = CountryServiceUtil.fetchCountryByA2(countryCode);
        } catch (SystemException e) {
            e.printStackTrace();
        }

        if (Validator.isNotNull(country)) {
            asset.setCountry_of_risk(country.getCountryId());
        } else {
            asset.setCountry_of_risk(asset.getCountry());
        }

        if (securityClass.equalsIgnoreCase("Fixed Income")) {
            asset.setSecurity_class(IConstants.SECURITY_CLASS_FIXED_INCOME);
            asset.setCurrent_price(asset.getBid_price() / 100);
        } else if (securityClass.equalsIgnoreCase("Fund")) {
            asset.setSecurity_class(IConstants.SECURITY_CLASS_FUND);
            asset.setCurrent_price(CellUtil.getDouble(row.getCell(columnNames.get("FUND_NET_ASSET_VAL"))));
        } else {
            asset.setSecurity_class(IConstants.SECURITY_CLASS_EQUITY);
            asset.setCurrent_price(asset.getLast_price());
        }

        try {
            updateAsset(asset);
        } catch (SystemException e) {
            e.printStackTrace();
        }

        long assetId = asset.getAssetId();

        // Saving to AssetEntry table
        long entryId = AssetHelper.updateAssetEntry(assetId);

        AssetHelper.assignCategories(asset, entryId, userId, row, columnNames, serviceContext,
                bbSecurityVocabularyId, bbIndustryVocabularyId, bbAssetClassVocabularyId);

        if (securityClass.equalsIgnoreCase("Fixed Income")) {
            Bond bond = getBond(assetId);
            bond.setIssuer_bulk(CellUtil.getString(row.getCell(columnNames.get("ISSUER_BULK"))));
            bond.setCpn(CellUtil.getDouble(row.getCell(columnNames.get("CPN"))));
            bond.setCpn_typ(CellUtil.getString(row.getCell(columnNames.get("CPN_TYP"))));
            bond.setMty_typ(CellUtil.getString(row.getCell(columnNames.get("MTY_TYP"))));
            bond.setMty_years_tdy(CellUtil.getDouble(row.getCell(columnNames.get("MTY_YEARS_TDY"))));
            bond.setYld_ytm_ask(CellUtil.getDouble(row.getCell(columnNames.get("YLD_YTM_ASK"))));
            bond.setYld_ytm_bid(CellUtil.getDouble(row.getCell(columnNames.get("YLD_YTM_BID"))));
            bond.setYld_cur_mid(CellUtil.getDouble(row.getCell(columnNames.get("YLD_CUR_MID"))));
            bond.setBb_composite(CellUtil.getString(row.getCell(columnNames.get("BB_COMPOSITE"))));
            bond.setRtg_sp(CellUtil.getString(row.getCell(columnNames.get("RTG_SP"))));
            bond.setRtg_moody(CellUtil.getString(row.getCell(columnNames.get("RTG_MOODY"))));
            bond.setRtg_fitch(CellUtil.getString(row.getCell(columnNames.get("RTG_FITCH"))));
            bond.setCpn_freq(CellUtil.getDouble(row.getCell(columnNames.get("CPN_FREQ"))));
            bond.setFive_y_bid_cds_spread(
                    CellUtil.getDouble(row.getCell(columnNames.get("5Y_BID_CDS_SPREAD"))));
            bond.setDur_mid(CellUtil.getDouble(row.getCell(columnNames.get("DUR_MID"))));
            bond.setPrice_to_cash_flow(CellUtil.getDouble(row.getCell(columnNames.get("PX_TO_CASH_FLOW"))));
            bond.setMaturity_dt(CellUtil.getDate(row.getCell(columnNames.get("MATURITY"))));
            bond.setCollat_typ(CellUtil.getString(row.getCell(columnNames.get("PAYMENT_RANK"))));
            bond.setCalc_typ(CellUtil.getDouble(row.getCell(columnNames.get("CALC_TYP"))));
            bond.setIs_bond_no_calctyp(
                    Validator.isNull(CellUtil.getString(row.getCell(columnNames.get("IS_BOND_NO_CALCTYP")))));
            bond.setIssue_dt(CellUtil.getDate(row.getCell(columnNames.get("ISSUE_DT"))));
            bond.setAmount_issued(CellUtil.getDouble(row.getCell(columnNames.get("AMT_ISSUED"))));
            bond.setAmount_outstanding(CellUtil.getDouble(row.getCell(columnNames.get("AMT_OUTSTANDING"))));

            try {
                bondLocalService.updateBond(bond);
            } catch (SystemException e) {
                e.printStackTrace();
            }

        } else if (securityClass.equalsIgnoreCase("Fund")) {
            MutualFund mutualFund = getMutualFund(assetId);
            mutualFund.setFund_total_assets(
                    CellUtil.getDouble(row.getCell(columnNames.get("FUND_TOTAL_ASSETS"))));
            mutualFund.setFund_asset_class_focus(
                    CellUtil.getString(row.getCell(columnNames.get("FUND_ASSET_CLASS_FOCUS"))));
            mutualFund.setFund_geo_focus(CellUtil.getString(row.getCell(columnNames.get("FUND_GEO_FOCUS"))));

            try {
                mutualFundLocalService.updateMutualFund(mutualFund);
            } catch (SystemException e) {
                e.printStackTrace();
            }

        } else if (securityClass.equalsIgnoreCase("Equity")) {
            Equity equity = getEquity(assetId);
            equity.setEqy_alpha(CellUtil.getDouble(row.getCell(columnNames.get("EQY_ALPHA"))));
            equity.setDividend_yield(CellUtil.getDouble(row.getCell(columnNames.get("DIVIDEND_YIELD"))));
            equity.setEqy_dvd_yld_12m(CellUtil.getDouble(row.getCell(columnNames.get("EQY_DVD_YLD_12M"))));
            equity.setEqy_dvd_yld_es(CellUtil.getDouble(row.getCell(columnNames.get("EQY_DVD_YLD_EST"))));
            equity.setDvd_payout_ratio(CellUtil.getDouble(row.getCell(columnNames.get("DVD_PAYOUT_RATIO"))));
            equity.setPe_ratio(CellUtil.getDouble(row.getCell(columnNames.get("PE_RATIO"))));
            equity.setTot_debt_to_com_eqy(
                    CellUtil.getDouble(row.getCell(columnNames.get("TOT_DEBT_TO_COM_EQY"))));
            equity.setEbitda_to_revenue(CellUtil.getDouble(row.getCell(columnNames.get("EBITDA_TO_REVENUE"))));
            equity.setTrail_12m_prof_margin(
                    CellUtil.getDouble(row.getCell(columnNames.get("TRAIL_12M_PROF_MARGIN"))));
            equity.setBest_current_ev_best_opp(
                    CellUtil.getDouble(row.getCell(columnNames.get("BEST_CURRENT_EV_BEST_OPP"))));
            equity.setEqy_beta(CellUtil.getDouble(row.getCell(columnNames.get("EQY_ALPHA"))));
            equity.setReturn_sharpe_ratio(
                    CellUtil.getDouble(row.getCell(columnNames.get("RETURN_SHARPE_RATIO"))));
            equity.setEqy_sharpe_ratio_1yr(
                    CellUtil.getDouble(row.getCell(columnNames.get("EQY_SHARPE_RATIO_1YR"))));
            equity.setEqy_sharpe_ratio_3yr(
                    CellUtil.getDouble(row.getCell(columnNames.get("EQY_SHARPE_RATIO_3YR"))));
            equity.setEqy_sharpe_ratio_5yr(
                    CellUtil.getDouble(row.getCell(columnNames.get("EQY_SHARPE_RATIO_5YR"))));

            try {
                equityLocalService.updateEquity(equity);
            } catch (SystemException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:com.fingence.slayer.service.impl.PortfolioLocalServiceImpl.java

License:Open Source License

public void updatePortfolio(long portfolioId, long userId, String portfolioName, long investorId,
        long institutionId, long wealthAdvisorId, boolean trial, long relationshipManagerId, boolean social,
        String baseCurrency, File excelFile) {

    Portfolio portfolio = getPortfolioObj(portfolioId, userId);

    portfolioId = portfolio.getPortfolioId();
    portfolio.setPortfolioName(portfolioName);
    portfolio.setInvestorId(investorId);
    portfolio.setWealthAdvisorId(wealthAdvisorId);
    portfolio.setRelationshipManagerId(relationshipManagerId);
    portfolio.setInstitutionId(institutionId);
    portfolio.setTrial(trial);//  w ww .  ja v  a 2 s.  c  o  m
    portfolio.setPrimary(isFirstPortfolio(investorId));
    portfolio.setSocial(social);
    portfolio.setBaseCurrency(baseCurrency);

    try {
        portfolio = updatePortfolio(portfolio);
    } catch (SystemException e) {
        e.printStackTrace();
    }

    if (Validator.isNull(excelFile))
        return;

    InputStream is = null;
    try {
        is = new FileInputStream(excelFile);
    } catch (FileNotFoundException e) {
        //e.printStackTrace();
    }

    if (Validator.isNull(is))
        return;

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(is);
    } catch (IOException e) {
        e.printStackTrace();
    }

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    while (rowIterator.hasNext()) {
        // get the individual columns. 

        Row row = rowIterator.next();
        if (row.getRowNum() == 0)
            continue;

        String id_isin = CellUtil.getString(row.getCell(0));

        Asset asset = null;
        try {
            asset = assetPersistence.fetchByIdISIN(id_isin);
        } catch (SystemException e) {
            e.printStackTrace();
        }

        if (Validator.isNull(asset))
            continue;

        long assetId = asset.getAssetId();

        PortfolioItem portfolioItem = null;
        try {
            portfolioItem = portfolioItemPersistence.fetchByAssetId_PortfolioId(assetId, portfolioId);
        } catch (SystemException e) {
            e.printStackTrace();
        }

        if (Validator.isNull(portfolioItem)) {
            long itemId = 0l;
            try {
                itemId = counterLocalService.increment(PortfolioItem.class.getName());
            } catch (SystemException e) {
                e.printStackTrace();
            }
            portfolioItem = portfolioItemLocalService.createPortfolioItem(itemId);
            portfolioItem.setCreateDate(new java.util.Date());
            portfolioItem.setPortfolioId(portfolioId);
            portfolioItem.setAssetId(assetId);

            try {
                portfolioItemLocalService.addPortfolioItem(portfolioItem);
            } catch (SystemException e) {
                e.printStackTrace();
            }
        } else {
            portfolioItem.setModifiedDate(new java.util.Date());
        }

        portfolioItem.setPurchaseDate(CellUtil.getDate(row.getCell(2)));
        portfolioItem.setPurchasePrice(CellUtil.getDouble(row.getCell(3)));
        portfolioItem.setPurchaseQty(CellUtil.getDouble(row.getCell(4)));

        double purchasedFx = asset.getCurrency().equalsIgnoreCase(IConstants.CURRENCY_USD) ? 1.0d
                : CellUtil.getDouble(row.getCell(5));

        if (purchasedFx == 0.0d) {
            purchasedFx = ConversionUtil.getConversion(asset.getCurrency(), portfolioItem.getPurchaseDate());
        }

        portfolioItem.setPurchasedFx(purchasedFx);

        try {
            portfolioItemLocalService.updatePortfolioItem(portfolioItem);
        } catch (SystemException e) {
            e.printStackTrace();
        }
    }

    if (Validator.isNotNull(excelFile)) {
        // invoke JMS
        Message message = new Message();
        message.put("MESSAGE_NAME", "setConvertionRate");
        message.put("portfolioId", portfolioId);

        // Temporarily commenting this out
        //MessageBusUtil.sendMessage("fingence/destination", message);           
    }
}