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

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

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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.faizod.aem.component.core.servlets.datasources.impl.ExcelDatasourceParser.java

License:Apache License

@Override
public Map<Object, List<Object>> parseMultiColumn(InputStream inputStream) {
    Map<Object, List<Object>> map = new LinkedHashMap<Object, List<Object>>();

    // read in the Excel file
    try {//from  w  ww.ja  va2 s .c o  m
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rows = sheet.iterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            List<Cell> cells = new ArrayList<Cell>();
            short lineMin = row.getFirstCellNum();
            short lineMax = row.getLastCellNum();

            for (short index = lineMin; index < lineMax; index++)
                cells.add(row.getCell(index));

            Object label = "";
            switch (cells.get(0).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                label = cells.get(0).getNumericCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                label = "" + (cells.get(0).getStringCellValue());
                break;
            default:
                break;
            }

            List<Object> values = new ArrayList<Object>();

            for (short index = 1; index < (lineMax - lineMin); index++) {
                Object value;

                switch (cells.get(index).getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cells.get(index).getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value = cells.get(index).getNumericCellValue();
                    break;
                default:
                    value = new Object();
                    break;
                }
                values.add(value);
            }
            map.put(label, values);
        }
    } catch (IOException e) {
        LOG.error("Unable to read datasource.", e);
        throw new DatasourceException("Unable to read datasource.", e);
    } catch (InvalidFormatException e) {
        LOG.error("File Format not supported.", e);
        throw new DatasourceException("File Format not supported.", e);
    }
    return map;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

/**
 * Mtodo encargado de la lectura y anlisis de una hoja del archivo excel
 * cargado en la interfaz grfica correspondiente a Rolling
 *
 *
 * @param rowIterator lista de renglones contenidos en la hoja de excel
 * @param usuario usuario que realiza el anlisis
 * @param sheetName nombre de la hoja de excel
 * @return Regresa una lista con los registros a ser almacenados en base de
 * datos//from w ww  .j  av  a 2  s .c o  m
 */
private List<Xtmpinddl> analizeSheetIndi(Iterator<Row> rowIterator, DcsUsuario usuario, String sheetName,
        List<DcsCatPais> paises, List<DcsCatIndicadores> indicadores) throws DCSException {
    int numRow = 0;
    List<Xtmpinddl> cargas = new ArrayList<Xtmpinddl>();
    Xtmpinddl indi;
    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    Calendar calendarioActual = Calendar.getInstance();
    Calendar calendario = Calendar.getInstance();
    end: while (rowIterator != null && rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell cell;
        if (numRow == 0) {

        } else {
            indi = new Xtmpinddl();
            cell = row.getCell(0);
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                calendario.setTime(cell.getDateCellValue());
                if ("User".equalsIgnoreCase(usuario.getFkIdRol().getRol())
                        && (calendarioActual.get(Calendar.YEAR) != calendario.get(Calendar.YEAR)
                                || calendarioActual.get(Calendar.MONTH) != calendario.get(Calendar.MONTH))) {
                    throw new DCSException(
                            "Error: You can not load information of a different month of the current");
                }
                indi.setFecha(sdf.format(cell.getDateCellValue()));
            } else if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                try {
                    calendario.setTime(sdf.parse(cell.getStringCellValue().trim()));
                    if ("User".equalsIgnoreCase(usuario.getFkIdRol().getRol()) && (calendarioActual
                            .get(Calendar.YEAR) != calendario.get(Calendar.YEAR)
                            || calendarioActual.get(Calendar.MONTH) != calendario.get(Calendar.MONTH))) {
                        throw new DCSException(
                                "Error: You can not load information of a different month of the current");
                    }
                    indi.setFecha(cell.getStringCellValue().trim());
                } catch (ParseException ex) {
                    Logger.getLogger(XlsAnalizer.class.getName()).log(Level.SEVERE, null, ex);
                    errors.add("Approximately " + Character.toString((char) (65 + 0)) + "" + (numRow + 1)
                            + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                    cargas.clear();
                    break;
                }
            } else {
                numRow++;
                continue;
            }
            cell = row.getCell(1);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                indi.setGrupoInd(cell != null ? cell.getStringCellValue().trim() : null);
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 1)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(2);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (indicadores.contains(
                        new DcsCatIndicadores(cell != null ? cell.getStringCellValue().trim() : null))) {
                    indi.setIndicador(cell != null ? cell.getStringCellValue().trim() : null);
                } else {
                    errors.add("Approximately " + Character.toString((char) (65 + 2)) + "" + (numRow + 1)
                            + " cell in " + sheetName + " sheet have a invalid value [" + cell
                            + "], indicator not found.");
                    cargas.clear();
                    break;
                }
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 2)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(3);
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if ("KOF".equalsIgnoreCase(cell.getStringCellValue().trim())
                        || paises.contains(new DcsCatPais(cell.getStringCellValue().trim()))) {
                    indi.setPais(cell.getStringCellValue().trim());
                } else {
                    errors.add("Approximately " + Character.toString((char) (65 + 3)) + "" + (numRow + 1)
                            + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                    cargas.clear();
                    break;
                }
                if ("User".equalsIgnoreCase(usuario.getFkIdRol().getRol())
                        && (!usuario.getPais().equalsIgnoreCase(indi.getPais()))) {
                    throw new DCSException("Error: you can not load information from other country");
                }
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 3)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(4);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                indi.setCentro(cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK
                        ? cell.getStringCellValue().trim()
                        : null);
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 4)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(5);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                indi.setRuta(cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK
                        ? cell.getStringCellValue().trim()
                        : null);
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 5)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(6);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                indi.setValorMensual(cell != null ? (float) cell.getNumericCellValue() : 0);
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 6)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(7);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                indi.setValorAcumulado(cell != null ? (float) cell.getNumericCellValue() : 0);
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 7)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cargas.add(indi);
        }
        numRow++;
    }
    return cargas;
}

From source file:com.femsa.kof.csi.util.XlsAnalizer.java

/**
 * Mtodo encargado de la lectura y anlisis de una hoja del archivo excel
 * cargado en la interfaz grfica correspondiente a flota
 *
 *
 * @param rowIterator lista de renglones contenidos en la hoja de excel
 * @param usuario usuario que realiza el anlisis
 * @param sheetName nombre de la hoja de excel
 * @return Regresa una lista con los registros a ser almacenados en base de
 * datos//from w ww . j  a  v a2  s  . com
 */
private List<XtmpinddlFlota> analizeSheetFlota(Iterator<Row> rowIterator, DcsUsuario usuario, String sheetName,
        List<DcsCatPais> paises) throws DCSException {
    int numRow = 0;
    List<XtmpinddlFlota> cargas = new ArrayList<XtmpinddlFlota>();
    XtmpinddlFlota flota;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
    Calendar calendario = Calendar.getInstance();
    end: while (rowIterator != null && rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell cell;
        if (numRow == 0) {

        } else {
            flota = new XtmpinddlFlota();
            cell = row.getCell(0);
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                try {
                    calendario.setTime(sdf.parse(((int) cell.getNumericCellValue()) + ""));
                } catch (ParseException ex) {
                    Logger.getLogger(XlsAnalizer.class.getName()).log(Level.SEVERE, null, ex);
                    errors.add("Approximately " + Character.toString((char) (65 + 0)) + "" + (numRow + 1)
                            + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                    cargas.clear();
                    break;
                }
                flota.setAnio(calendario.get(Calendar.YEAR));
            } else if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) {
                try {
                    calendario.setTime(sdf.parse(cell.getStringCellValue().trim()));
                } catch (ParseException ex) {
                    Logger.getLogger(XlsAnalizer.class.getName()).log(Level.SEVERE, null, ex);
                    errors.add("Approximately " + Character.toString((char) (65 + 0)) + "" + (numRow + 1)
                            + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                    cargas.clear();
                    break;
                }
                flota.setAnio(calendario.get(Calendar.YEAR));
            } else {
                numRow++;
                continue;
            }
            cell = row.getCell(1);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if ("KOF".equalsIgnoreCase(cell != null ? cell.getStringCellValue().trim() : "") || paises
                        .contains(new DcsCatPais(cell != null ? cell.getStringCellValue().trim() : ""))) {
                    flota.setPais(cell != null ? cell.getStringCellValue().trim() : null);
                } else {
                    errors.add("Approximately " + Character.toString((char) (65 + 1)) + "" + (numRow + 1)
                            + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                    cargas.clear();
                    break;
                }
                if ("User".equalsIgnoreCase(usuario.getFkIdRol().getRol())
                        && (!usuario.getPais().equalsIgnoreCase(flota.getPais()))) {
                    throw new DCSException("Error: you can not load information from other country");
                }
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 1)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(2);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                flota.setTipo(cell != null ? cell.getStringCellValue().trim() : null);
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 2)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(3);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                flota.setEdad(cell != null ? cell.getStringCellValue().trim() : null);
            } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                flota.setEdad((int) cell.getNumericCellValue() + "");
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 3)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cell = row.getCell(4);
            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                flota.setCantidad(cell != null ? Integer.parseInt(cell.getStringCellValue().trim()) : null);
            } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                flota.setCantidad((int) cell.getNumericCellValue());
            } else {
                errors.add("Approximately " + Character.toString((char) (65 + 4)) + "" + (numRow + 1)
                        + " cell in " + sheetName + " sheet have a invalid value [" + cell + "].");
                cargas.clear();
                break;
            }
            cargas.add(flota);
        }
        numRow++;
    }
    return cargas;
}

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 w w  w .  j a  v  a  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());

        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  v  a  2s.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 w  w . j  a v a 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);

    // 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);/*from  w  ww .  jav  a2s  .  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);           
    }
}

From source file:com.firstonesoft.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/* w  w  w .jav a 2s .  c o  m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Resumen de Horas");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "D://timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * sheetrowIndexcolIndex?/*  ww w.  j ava 2s.c  o m*/
 *
 * @param sheet
 * @param rowIndex
 * @param colIndex
 * @param value
 */
public static void setValue(Sheet sheet, int rowIndex, int colIndex, Object value) {
    Row row = sheet.getRow(rowIndex);
    Cell cell = row.getCell(colIndex);
    setCellValue(cell, value);
}

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ??/*from ww w. j av  a 2  s.c  om*/
 *
 * @param sheet
 * @param columnIndex
 * @param style
 * @return
 */
public static boolean setColumnStyle(Sheet sheet, short columnIndex, int rowFirstIndex, int rowLastIndex,
        CellStyle style) {
    if (sheet == null)
        return false;

    int rowNum = sheet.getLastRowNum();
    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    // ??
    if (rowFirstIndex < rowLastIndex) {
        int temp = rowFirstIndex;
        rowFirstIndex = rowLastIndex;
        rowLastIndex = temp;
    }
    // TODO 
    if (rowNum < rowFirstIndex) {// ?
        return false;
    }
    // 
    for (int i = rowFirstIndex; i <= rowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            return false;
        Cell cell = row.getCell(columnIndex);
        if (cell == null)
            return false;
        newCellStyle.cloneStyleFrom(cell.getCellStyle());// ??
        newCellStyle.cloneStyleFrom(style); // ??
        cell.setCellStyle(newCellStyle);

    }

    return true;
}