List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:com.creditcloud.carinsurance.CarInsuranceFeeServiceBean.java
/** * ??/* w w w.ja v a2 s . c o m*/ * * @param feeFileExcel */ public void bacthUpdateCarInsuranceFeeSatatus(File feeFileExcel) { try { FileInputStream file = new FileInputStream(feeFileExcel); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); String insuranceNum = ""; int currentPeriod = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: currentPeriod = (int) cell.getNumericCellValue(); System.out.print((int) cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: insuranceNum = cell.getStringCellValue().trim(); System.out.print(cell.getStringCellValue() + "\t"); break; } } CarInsuranceFee fee = carInsuranceFeeDAO.findByInSuranceNumAndCurrentPeriod(insuranceNum, currentPeriod); //? if (fee != null) { updateCarInsuranceFeeSatatus(fee.getId(), CarInsuranceStatus.CLEARED); } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.ddt.driver.EXCELDriver.java
private void readNewerVersionExcelFile(String path, String sheetName) { try {/*from w ww. j a v a 2 s . c om*/ try (final FileInputStream file = new FileInputStream(new File(path))) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = getSheetName(sheetName, workbook); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String line = ""; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: line += cell.getBooleanCellValue() + ";"; break; case Cell.CELL_TYPE_NUMERIC: line += String.valueOf(cell.getNumericCellValue()).split("\\.")[0] + ";"; break; case Cell.CELL_TYPE_STRING: line += cell.getStringCellValue() + ";"; break; } } linesOfDriver.add(line); } } } catch (FileNotFoundException ex) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.dfpray.formatter.CardModel.java
/** * Imports a .xlsx file and create a CardModel form it * @param path Path to file/*from ww w. j a va 2 s . co m*/ * @throws IOException */ public void importFromExcel(String path) throws IOException { String[] cardInfo = new String[32]; FileInputStream file = new FileInputStream(new File(path)); @SuppressWarnings("resource") XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Row row; Cell cell; Iterator<Cell> cellIterator; Iterator<Row> rowIterator; int i; //Iterate through each rows one by one rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); cellIterator = row.cellIterator(); i = 0; while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: cardInfo[i] = " "; //System.out.println("Nothing"); break; case Cell.CELL_TYPE_NUMERIC: cardInfo[i] = (Double.toString(cell.getNumericCellValue())); //System.out.println(Double.toString(cell.getNumericCellValue())); break; case Cell.CELL_TYPE_STRING: cardInfo[i] = cell.getStringCellValue(); //System.out.println(cell.getStringCellValue()); break; } i++; } //Create card and add it tho this cards.add(new BusinessCard(cardInfo)); } file.close(); }
From source file:com.dotosoft.dotoquiz.tools.OldApp.java
License:Apache License
private void processPicasa() { if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { log.info("process data from excel!"); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { log.info("process data from googlesheet!"); }//from w w w . ja v a 2 s. c om // variable for googlesheet GooglesheetClient googlesheetClient = null; WorksheetEntry fullSheet = null; // variable for excel FileInputStream file = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; // variable for DB Session session = null; Transaction trx = null; APPLICATION_TYPE type = APPLICATION_TYPE.valueOf(settings.getApplicationType()); try { if (APPLICATION_TYPE.DB.toString().equals(settings.getApplicationType())) { session = HibernateUtil.getSessionFactory().openSession(); trx = session.beginTransaction(); } if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { file = new FileInputStream(settings.getSyncDataFile()); workbook = new XSSFWorkbook(file); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { googlesheetClient = new GooglesheetClient(auth, settings.getSyncDataFile()); } int index = 0; // -------------------------------------------------------------------------- // Extract Achievement ------------------------------------------------------ // -------------------------------------------------------------------------- // parent folder if (!APPLICATION_TYPE.SHOW_COLUMN_HEADER.toString().equals(settings.getApplicationType())) { DataTopicsParser topicAchievement = new DataTopicsParser(QuizParserConstant.ACHIEVEMENT_NAME, QuizParserConstant.EMPTY_STRING, QuizParserConstant.EMPTY_STRING, QuizParserConstant.EMPTY_STRING, QuizParserConstant.ACHIEVEMENT_NAME, QuizParserConstant.ACHIEVEMENT_DESCRIPTION, QuizParserConstant.ACHIEVEMENT_IMAGE_URL, QuizConstant.NO, new java.util.Date(), QuizConstant.SYSTEM_USER, QuizConstant.NO, type); topicAchievement = syncTopicToPicasa(topicAchievement); topicMapByTopicId.put(topicAchievement.getId(), topicAchievement); } List listRow = null; for (String achievementTab : settings.getStructure().getTabAchievements().split(";")) { String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(achievementTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(achievementTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; ParameterAchievementParser achievement = DotoQuizStructure.convertDataToAchievement(row, settings); if (achievement != null) { if (type == APPLICATION_TYPE.DB) { session.saveOrUpdate(achievement.toParameterAchievements()); log.info("Save or update achievement: " + achievement); } else if (type == APPLICATION_TYPE.SYNC) { achievement = syncAchievementToPicasa(achievement); if (!QuizConstant.YES.equals(achievement.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_ACHIEVEMENT, row, achievement.getPicasaId(), achievement.getImagePicasaUrl(), QuizConstant.YES); } } } } } trx = CommitDB(trx, session, true); for (String dataTab : settings.getStructure().getTabTopics().split(";")) { // -------------------------------------------------------------------------- // Extract Topic // -------------------------------------------------------------------------- index = 0; String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(dataTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(dataTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; DataTopicsParser topic = DotoQuizStructure.convertDataToTopics(row, settings); if (topic != null) { if (type == APPLICATION_TYPE.DB) { if (StringUtils.hasValue(topic.getTopicParentId())) { topic.setDatTopics(topicMapByTopicId.get(topic.getTopicParentId()).toDataTopics()); } session.saveOrUpdate(topic.toDataTopics()); log.info("Save or update topic: " + topic); } else if (type == APPLICATION_TYPE.SYNC) { topic = syncTopicToPicasa(topic); if (!QuizConstant.YES.equals(topic.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_TOPIC, row, topic.getPicasaId(), topic.getImagePicasaUrl(), QuizConstant.YES); } } topicMapByTopicId.put(topic.getId(), topic); } } } trx = CommitDB(trx, session, true); for (String dataTab : settings.getStructure().getTabQuestions().split(";")) { // -------------------------------------------------------------------------- // Extract QuestionAnswers // -------------------------------------------------------------------------- index = 0; String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(dataTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(dataTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; DataQuestionsParser questionAnswer = DotoQuizStructure.convertDataToAnswerQuestion(row, settings); if (questionAnswer != null) { if (type == APPLICATION_TYPE.DB) { questionAnswer.setMtQuestionType(HibernateUtil.getQuestionTypeByName(session, questionAnswer.getQuestionTypeData())); session.saveOrUpdate(questionAnswer.toDataQuestion()); log.info("Save or update QuestionAnswers: " + questionAnswer); for (String topicId : questionAnswer.getTopics()) { DataTopicsParser datTopic = topicMapByTopicId.get(topicId); HibernateUtil.saveOrUpdateTopicQuestionData(session, datTopic.toDataTopics(), questionAnswer.toDataQuestion()); } } else if (type == APPLICATION_TYPE.SYNC) { questionAnswer = syncQuestionAnswersToPicasa(questionAnswer); if (!QuizConstant.YES.equals(questionAnswer.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_QUESTION_ANSWER, row, questionAnswer.getPicasaId(), questionAnswer.getImagePicasaUrl(), QuizConstant.YES); } } } } } trx = CommitDB(trx, session, false); if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { file.close(); log.info("Save data to file..."); FileOutputStream fos = new FileOutputStream(settings.getSyncDataFile()); workbook.write(fos); fos.close(); } log.info("Done"); } catch (Exception e) { trx.rollback(); session.close(); e.printStackTrace(); } System.exit(0); }
From source file:com.Excel.Excel2007.java
private void leerArchivo(int indiceHoja) { abrirArchivo();/*from w w w .ja va 2 s .c o m*/ this.datos = new HashMap<>(); XSSFSheet sheet = workbook.getSheetAt(indiceHoja); Iterator<Row> rowIterator = sheet.iterator(); int fila = 0; Row row; while (rowIterator.hasNext()) { List<Object> datosFila = new ArrayList<>(); Cell celda; row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Object dato = null; celda = cellIterator.next(); switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { System.out.print(celda.getDateCellValue()); dato = celda.getDateCellValue(); } else { System.out.print(celda.getNumericCellValue()); dato = celda.getNumericCellValue(); } break; case Cell.CELL_TYPE_STRING: System.out.print(celda.getStringCellValue()); dato = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(celda.getBooleanCellValue()); dato = celda.getBooleanCellValue(); break; } datosFila.add(dato); } datos.put(fila++, datosFila); System.out.println(""); } cerrarArchivo(); }
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 ww .j a v a 2 s .c om 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;/* ww w .j av a2 s . co 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. ja 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); // 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 w w.jav a2 s . co 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.fota.devMgt.controller.DevOpenMgtCTR.java
License:Open Source License
/** * ? , ? ? .//from w w w . j a va 2 s . c om * ? ?. * @param vo * @param model * @return */ @RequestMapping(value = "/openInfoUpload") public ModelAndView uploadTest(HttpServletRequest request, HttpServletResponse response, @ModelAttribute("devSearchVO") DevSearchVO vo, ModelMap model) throws Exception { if (!request.getServletPath().equals("/commonDevice/devMgt/openInfoUpload")) { response.setStatus(403); return null; } // ? ? String path = "/jb_log/excelUpload/devOpenInfoFiles_tmp/"; File pysicalFolder = new File(path); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8"); String upFile = multi.getFilesystemName("file"); if (upFile == null || upFile.equals("")) { model.addAttribute("msg", "?? ."); return new ModelAndView(ajaxMainView, model); } // poi ? ? ? File upfile = new File(path + upFile); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ?? List<DevSearchVO> rs = new ArrayList(); // ? rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); DevSearchVO tmp = new DevSearchVO(); rs.add(tmp); // Cell cell = row.getCell(0); tmp.setBizNm(cell.toString().trim()); if (tmp.getBizNm().length() > 50) { tmp.setBizNm(tmp.getBizNm().substring(0, 49)); // ? } // cell = row.getCell(1); tmp.setSvcNm(cell.toString().trim()); if (tmp.getSvcNm().length() > 50) { tmp.setSvcNm(tmp.getSvcNm().substring(0, 49)); // ? } // ?? cell = row.getCell(2); tmp.setDevModelNm(cell.toString().trim()); if (tmp.getDevModelNm().length() > 50) { tmp.setDevModelNm(tmp.getDevModelNm().substring(0, 49)); // ? } // CTN cell = row.getCell(3); tmp.setCtn(cell.toString().trim()); if (tmp.getCtn().length() > 20) { tmp.setCtn(tmp.getCtn().substring(0, 19)); // ? } // cell = row.getCell(4); tmp.setDealerNm(cell.toString().trim()); if (tmp.getDealerNm().length() > 50) { tmp.setDealerNm(tmp.getDealerNm().substring(0, 49)); // ? } // cell = row.getCell(5, row.CREATE_NULL_AS_BLANK); tmp.setCustomTag(cell.toString().trim()); if (tmp.getCustomTag().length() > 30) { tmp.setCustomTag(tmp.getCustomTag().substring(0, 29)); // ? } // ? ?? cell = row.getCell(6, row.CREATE_NULL_AS_BLANK); tmp.setApprovalYn(cell.toString().trim()); if (tmp.getApprovalYn().equals("")) tmp.setApprovalYn("Y"); if (!tmp.getApprovalYn().equals("Y")) tmp.setApprovalYn("N"); tmp.setMemo(""); } for (DevSearchVO tmp : rs) { mySVC.addDevInfo(tmp); } DevSearchVO footer = new DevSearchVO(); rs.add(footer); file.close(); upfile.delete(); DevSearchVO tempVo; // - ? java-Ctrl ? - DMS ? if (rs != null && rs.size() > 0) { for (int i = 0; i < rs.size(); i++) { tempVo = rs.get(i); if (tempVo.getCtn() != null && !tempVo.getCtn().isEmpty()) { tempVo.setCtn(Masking.convertCtn(tempVo.getCtn())); } } } model.addAttribute("gridData", rs); } catch (Exception e) { e.printStackTrace(); upfile.delete(); model.addAttribute("msg", "?? ? ."); } return new ModelAndView(ajaxMainView, model); }