List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook(PackagePart part) throws IOException
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 www . ja v a 2 s . c om*/ 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 v a 2s . 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); }
From source file:com.fota.fota3g.deviceMgt.controller.TargetGroupMgtCTR.java
License:Open Source License
/** * targetGroup insert - Excel// w w w .j a v a 2 s . co m * @param param * @param response * @return */ @RequestMapping(value = "/addTargetGroupExcel") @ResponseBody public ModelAndView addTargetGroupExcel(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception { // logger.warn(" >>> TargetGroupMgtCTR - addTargetGroupExcel() start... "); String retMsg = ""; // ? ? String path = "/jb_log/excelUpload/targetGroup/"; File pysicalFolder = new File(path); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8"); String upFileName = multi.getFilesystemName("deviceIdF"); // logger.warn("path : " + path + " ### name : " + upFileName); // user HttpSession httpSession = request.getSession(false); UserLoginVO userVo = (UserLoginVO) httpSession.getAttribute("userInfo"); String userId = userVo.getUserId(); TargetGroupVo vo = new TargetGroupVo(); vo.setMoId(userId); vo.setListGroupName(multi.getParameter("listGroupName")); vo.setDescription(multi.getParameter("description")); vo.setDelYN(multi.getParameter("delYN")); vo.setDeviceType(multi.getParameter("deviceType")); vo.setInputType(multi.getParameter("inputType")); // logger.warn(" >>> TargetGroupMgtCTR - addTargetGroupExcel() vo info\r\n" // + "moId : " + vo.getMoId() + "\r\n" // + "listGroupName : " + vo.getListGroupName() + "\r\n" // + "description : " + vo.getDescription() + "\r\n" // + "delYN : " + vo.getDelYN() + "\r\n" // + "deviceType : " + vo.getDeviceType() + "\r\n" // + "inputType : " + vo.getInputType() + "\r\n" // ); // poi ? ? ? File upfile = new File(path + upFileName); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ? ? ctn list ? List<String> ctnList = new ArrayList<String>(); // ? // rowIterator.next(); String tempCtn = ""; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); tempCtn = ""; // ctn Cell cell = row.getCell(0); tempCtn = cell.toString().trim(); if (tempCtn != null && !tempCtn.isEmpty()) { ctnList.add(tempCtn); } } // logger.warn("-----------------------------------------------------------------"); // for(int i=0; i<ctnList.size(); i++) { // logger.warn("ctnList[" + i + "] : " + ctnList.get(i)); // } // logger.warn("-----------------------------------------------------------------"); // ? (console) ? // ? ? '?' ?? vo.setInputType("console"); StringBuffer sbTempDevIdC = new StringBuffer(); for (int i = 0; i < ctnList.size(); i++) { sbTempDevIdC.append(ctnList.get(i) + "\n"); } // logger.warn("ctn str >>>>>\r\n" + sbTempDevIdC.toString()); vo.setDeviceIdC(sbTempDevIdC.toString()); vo = targetSVC.insertTargetGroup(vo); } catch (Exception e) { logger.warn("?? ? ."); e.printStackTrace(); model.addAttribute("msg", "?? ? ."); } // db ? ? upfile.delete(); return new ModelAndView(ajaxMainView, model); }
From source file:com.fota.fota3g.selffotaMgt.controller.ListManagementCTR.java
License:Open Source License
/** * confirm //from w w w .j a v a2 s . c o m * @param param * @param response * @return */ @RequestMapping(value = "/registListMgt") @ResponseBody public ModelAndView registListMgt(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception { // logger.warn(" >>>>> Self Fota - ListManagementCTR - registListMgt() start..."); String retMsg = "-1"; // ? ? String path = "/jb_log/excelUpload/selfFotaList/"; File pysicalFolder = new File(path); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, path, 10 * 1024 * 1024, "utf-8"); String upFileName = multi.getFilesystemName("file"); // ? ? if (upFileName == null || upFileName.equals("")) { model.addAttribute("msg", "?? ."); return new ModelAndView(ajaxMainView, model); } // db Map Map<String, String> entity = new HashMap<String, String>(); // user HttpSession httpSession = request.getSession(false); UserLoginVO userVo = (UserLoginVO) httpSession.getAttribute("userInfo"); String userId = userVo.getUserId(); // int devCnt = 0; entity.put("reg_id", userId); entity.put("file_name", upFileName); entity.put("file_path", path); // 3G FOTA src ? path ? (path + name ) entity.put("company_name", VoV.replaceRoundBracket(multi.getParameter("maker"))); // entity.put("device_cnt", devCnt + ""); // device count excel file ? count ?. // poi ? ? ? File upfile = new File(path + upFileName); List<Map<String, String>> devList = new ArrayList<Map<String, String>>(); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ? ? Map<String, String> devInfo = null; // ? rowIterator.next(); while (rowIterator.hasNext()) { devInfo = new HashMap<String, String>(); Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); // model Cell cell = row.getCell(0); devInfo.put("device_model_name", cell.toString().trim()); // previous version cell = row.getCell(1); devInfo.put("device_pre_ver", cell.toString().trim()); // next version cell = row.getCell(2); devInfo.put("device_app_ver", cell.toString().trim()); // ctn cell = row.getCell(3); devInfo.put("ctn", cell.toString().trim()); // imei cell = row.getCell(4); devInfo.put("device_imei", cell.toString().trim()); devList.add(devInfo); } entity.put("device_cnt", devList.size() + ""); // logger.warn("insertXls data : " + entity.toString()); int insertRet = listManagementSVC.insertXls(entity); // logger.warn("insertXls ret : " + insertRet); String master_idx = ""; if (insertRet > 0) { master_idx = listManagementSVC.getInsertedMasterIdx(entity); // logger.warn("getInsertedMasterIdx ret (master_idx) : " + master_idx); } int count = 0; // ? db ? insert for (int i = 0; i < devList.size(); i++) { devList.get(i).put("reg_id", userId); devList.get(i).put("master_idx", master_idx); devList.get(i).put("company_name", entity.get("company_name").toString()); devList.get(i).put("ncn", sdpApi.getNcnInfo(devList.get(i).get("ctn").toString())); // devList.get(i).put("ncn", devList.get(i).get("ctn").toString() + "0001"); // test // logger.warn("devList[" + i + "] insertXlsFotaMng data : " + devList.get(i).toString()); insertRet = listManagementSVC.insertXlsFotaMng(devList.get(i)); // logger.warn("devList[" + i + "] insertXlsFotaMng ret : " + insertRet); count++; } String msg = "? [ " + devList.size() + " ] [ " + count + " ] ? ? ??."; //IMEI , NCN ? // count = devList.size() - count; // msg = msg + "(IMEI : " + count + " , NCN : " + ncnErrCnt + " )"; retMsg = "1"; model.addAttribute("msg", msg); } catch (Exception e) { logger.warn("?? ? ."); e.printStackTrace(); upfile.delete(); model.addAttribute("msg", "?? ? ."); } model.addAttribute("retMsg", retMsg); return new ModelAndView(ajaxMainView, model); }
From source file:com.fota.fota3g.selffotaMgt.controller.ListManagementCTR.java
License:Open Source License
/** * confirm // ww w . j a v a 2 s . c o m * @param param * @param response * @return */ @RequestMapping(value = "/setXmlData_back") @ResponseBody public ModelAndView setXmlDataRead_back(HttpServletRequest request, HttpServletResponse response, @ModelAttribute("ListManagementVo") ListManagementVo vo, ModelMap model) throws Exception { logger.warn("#############setXmlData################"); { Date now = new Date(); logger.info("File update start : " + now.toString()); String default_path = "F:\\servicefota\\jboss\\download\\"; // ? ? File temp_folder = new File(default_path); if (!temp_folder.exists() || !temp_folder.isDirectory()) { temp_folder.mkdirs(); } // ?? : 500M int maxfilesize = 500 * 1024 * 1024; MultipartRequest multi = null; File upfile = null; try { // ? logger.warn("#############multi start################"); multi = new MultipartRequest(request, default_path, maxfilesize, "utf-8"); upfile = new File(default_path + multi.getFilesystemName("file")); vo.setFileName(upfile.getName()); // logger.warn("#############multi end################" + upfile); // logger.warn("#############multi end################" + upfile); File upfiles = new File(default_path + upfile.getName()); FileInputStream file = new FileInputStream(upfiles); // logger.warn("#############getCell model################"); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // logger.warn("#############getCell model################"); // XSSFSheet sheet = workbook.getSheetAt(0); // logger.warn("#############getCell model################"); // Iterator<Row> rowIterator = sheet.iterator(); // logger.warn("#############getCell model################"); // ?? List<ListManagementVo> rs = new ArrayList(); // ? rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); logger.warn("#############getCell model################" + row.cellIterator()); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); ListManagementVo tmp = new ListManagementVo(); rs.add(tmp); // model Cell cell = row.getCell(0); logger.warn("#############getCell model################" + cell); tmp.setDeviceModelName(cell.toString().trim()); if (tmp.getDeviceModelName().length() > 50) { tmp.setDeviceModelName(tmp.getDeviceModelName().substring(0, 49)); // ? logger.warn("#############getCell model################" + tmp.getDeviceModelName()); } //before version cell = row.getCell(1); logger.warn("#############getCell before version################" + cell); tmp.setDevicePerVer(cell.toString().trim()); if (tmp.getDevicePerVer().length() > 50) { tmp.setDevicePerVer(tmp.getDevicePerVer().substring(0, 49)); // ? } //after version cell = row.getCell(2); logger.warn("#############getCell after version################" + cell); tmp.setDeviceAppVer(cell.toString().trim()); if (tmp.getDeviceAppVer().length() > 50) { tmp.setDeviceAppVer(tmp.getDeviceAppVer().substring(0, 49)); // ? } //ctn version cell = row.getCell(3); logger.warn("#############getCell ctn version################" + cell); tmp.setCtn(cell.toString().trim()); if (tmp.getCtn().length() > 50) { tmp.setCtn(tmp.getCtn().substring(0, 49)); // ? } //imei version cell = row.getCell(4); logger.warn("#############getCell imei version################" + cell); tmp.setDeviceImei(cell.toString().trim()); if (tmp.getDeviceImei().length() > 50) { tmp.setDeviceImei(tmp.getDeviceImei().substring(0, 49)); // ? } } for (ListManagementVo tmp : rs) { // mySVC.addDevInfo(tmp); } ListManagementVo footer = new ListManagementVo(); rs.add(footer); file.close(); upfile.delete(); model.addAttribute("gridData", rs); // vo.setBizTypeId(VoV.valid(multi.getParameter("bizTypeId"), 30)); // vo.setSvcId(VoV.valid(multi.getParameter("svcId"), 30, 4)); // vo.setMakerId(VoV.valid(multi.getParameter("makerId"), 30, 4)); // vo.setDevModelId(VoV.valid(multi.getParameter("devModelId"), 30, 4)); // vo.setFotaType(VoV.valid(multi.getParameter("fotaType"), 50)); // if (!vo.getFotaType().equals("") && !vo.getFotaType().equals("PUSH") && !vo.getFotaType().equals("POLLING") && !vo.getFotaType().equals("PUSHPOLLING")) // throw new Exception(); // // //vo.setFirmwareId(VoV.valid(multi.getParameter("firmwareId"), 30, 4)); // //vo.setFirmwareDesc(VoV.valid(multi.getParameter("firmwareDesc"), 1000, 9)); // vo.setFirmwareVer(VoV.valid(multi.getParameter("firmwareVer"), 100, 4)); // vo.setFirmwareMakerVer(VoV.valid(multi.getParameter("firmwareMakerVer"), 100, 8)); // vo.setVerMemo(VoV.valid(multi.getParameter("verMemo"), 1000, 9)); // // // ? ? // String path = default_path + vo.getDevModelId() + "/"; // File pysicalfolder = new File(path); // if(!pysicalfolder.exists() || !pysicalfolder.isDirectory()) { // pysicalfolder.mkdirs(); // } // vo.setFilePath(VoV.valid(path, 200)); // // // ??? // File destFile = new File(path + upfile.getName()); // if(destFile.exists()){ // destFile.delete(); // } // FileUtils.moveFile(upfile, destFile); // // //CRC, '0' // vo.setCrc("0"); // vo.setCretNm(((UserLoginVO)request.getSession().getAttribute("userInfo")).getUserNm()); // } catch (Exception e) { // ?? // ??? ? ? if (upfile != null && upfile.exists()) { upfile.delete(); } String errorMSG = e.getMessage(); if (errorMSG.indexOf("exceeds") != 0) { model.addAttribute("error", "?? 500MB ?."); } logger.info("File update error : " + errorMSG); response.setStatus(500); return new ModelAndView(ajaxMainView, model); } // mySVC.regi(vo); now = new Date(); logger.info("File update end : " + now.toString()); // return new ModelAndView (ajaxMainView, model); } // int retValue = 0; // vo.setModId("sooya118"); // vo.setConfirmState("2"); // // retValue = listManagementSVC.updataConfirm(vo); // retValue = listManagementSVC.updateHold(vo); // // model.addAttribute("retValue", retValue); // return new ModelAndView(ajaxMainView, model); }
From source file:com.fota.fotastatMgt.controller.FotastatCTR.java
License:Open Source License
/** * ? // w w w . j ava2 s . c om * ? ?. * @param vo * @param model * @return */ @RequestMapping(value = "/checkUploadExcelFile") public ModelAndView checkUploadExcelFile(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception { // logger.warn(" >>>>>>>>>> FotastatCTR - uploadExcelFil() start ... "); String retMsg = ""; // ? ? File pysicalFolder = new File(PATH); if (!pysicalFolder.exists() || !pysicalFolder.isDirectory()) { pysicalFolder.mkdirs(); } // ? MultipartRequest multi = new MultipartRequest(request, PATH, SIZE, "utf-8"); String upFileName = multi.getFilesystemName("file"); // logger.warn("upFile : " + upFile); FotastatSearchVO vo = new FotastatSearchVO(); vo.setBizTypeId(VoV.valid(multi.getParameter("bizTypeId"), 30, 4)); vo.setSvcId(VoV.valid(multi.getParameter("svcId"), 30, 4)); vo.setDevMakerId(VoV.valid(multi.getParameter("devMakerId"), 30, 4)); vo.setDevModelId(VoV.valid(multi.getParameter("devModelId"), 30, 4)); vo.setModemMakerId(VoV.valid(multi.getParameter("modemMakerId"), 30, 4)); vo.setModemModelId(VoV.valid(multi.getParameter("modemModelId"), 30, 4)); vo.setDealerNm(VoV.valid(multi.getParameter("dealerNm"), 50, 2)); // logger.warn("value check...\r\n" // + "bizTypeId : " + vo.getBizTypeId() + "\r\n" // + "svcId : " + vo.getSvcId() + "\r\n" // + "DevMakerId : " + vo.getDevMakerId() + "\r\n" // + "DevModelId : " + vo.getDevModelId() + "\r\n" // + "ModemMakerId : " + vo.getModemMakerId() + "\r\n" // + "ModemModelId : " + vo.getModemModelId() + "\r\n" // + "DealerNm : " + vo.getDealerNm() + "\r\n" // ); // vo.setPage("1"); vo.setRowsPerPage("1000"); if (upFileName == null || upFileName.equals("")) { model.addAttribute("msg", "?? ."); return new ModelAndView(ajaxMainView, model); } // poi ? ? ? File upfile = new File(PATH + upFileName); // poi try { FileInputStream file = new FileInputStream(upfile); //.xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // XSSFSheet sheet = workbook.getSheetAt(0); // Iterator<Row> rowIterator = sheet.iterator(); // ? ? ctn list ? List<String> ctnList = new ArrayList<String>(); // ? rowIterator.next(); String tempCtn = ""; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); tempCtn = ""; // ctn Cell cell = row.getCell(0); tempCtn = cell.toString().trim(); ctnList.add(tempCtn); } // ? ctn ? // db ? ctn vo = mySVC.search(vo); List<String> checkedCtnList = new ArrayList<String>(); int checkCount = 0; int fotaPossibilityCount = 0; for (int i = 0; i < ctnList.size(); i++) { for (int j = 0; j < vo.getResults().size(); j++) { if (ctnList.get(i).equals(vo.getResults().get(j).getCtn())) { checkedCtnList.add(ctnList.get(i)); checkCount++; // P(), S(), V(?) FOTA // W(), E(?), F() if (!((vo.getResults().get(j).getProcessStatCd().equals("") || vo.getResults().get(j).getProcessStatCd().equals("P")) || (vo.getResults().get(j).getProcessStatCd().equals("") || vo.getResults().get(j).getProcessStatCd().equals("S")) || (vo.getResults().get(j).getProcessStatCd().equals("?") || vo.getResults().get(j).getProcessStatCd().equals("V")))) { fotaPossibilityCount++; } } } } retMsg = " : " + ctnList.size() + "\r\n" + "? : " + checkCount + "\r\n" + " ? : " + (ctnList.size() - checkCount) + "\r\n" + " (? : " + fotaPossibilityCount + ")"; file.close(); upfile.delete(); model.addAttribute("retMsg", retMsg); model.addAttribute("checkedCtnList", checkedCtnList); model.addAttribute("fotaPossibilityCount", fotaPossibilityCount); } catch (Exception e) { logger.warn("?? ? ."); e.printStackTrace(); upfile.delete(); model.addAttribute("msg", "?? ? ."); } return new ModelAndView(ajaxMainView, model); }
From source file:com.frameworkset.platform.cms.searchmanager.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * ?excel2007 //from w w w .j ava 2 s. c o m * @param path * @return * @throws IOException */ public String readExcel2007(InputStream in) throws IOException { // StringBuffer content = new StringBuffer(); // XSSFWorkbook strPath String content = null; XSSFWorkbook xwb = new XSSFWorkbook(in); XSSFExcelExtractor extractor = new XSSFExcelExtractor(xwb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); content = extractor.getText(); this.cp = extractor.getCoreProperties(); return content; // // Sheet // for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) { // XSSFSheet xSheet = xwb.getSheetAt(numSheet); // if (xSheet == null) { // continue; // } // // Row // for (int rowNum = 0; rowNum <= xSheet.getLastRowNum(); rowNum++) { // XSSFRow xRow = xSheet.getRow(rowNum); // if (xRow == null) { // continue; // } // // Cell // for (int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++) { // XSSFCell xCell = xRow.getCell(cellNum); // if (xCell == null) { // continue; // } // if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { // content.append(xCell.getBooleanCellValue()); // } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // content.append(xCell.getNumericCellValue()); // } else { // content.append(xCell.getStringCellValue()); // } // } // } // } // // return content.toString(); }
From source file:com.frameworkset.platform.cms.votemanager.VoteMobileController.java
License:Open Source License
/** ????/*ww w . j a v a2 s .c o m*/ */ public void exportExcel(String titleId, ModelMap model, HttpServletRequest request, HttpServletResponse response) { InputStream in = null; Workbook workbook = null; try { List<ExcelBean> list = voteMobileService.queryAnswerContent(titleId); in = VoteMobileController.class.getResourceAsStream("MobileVoteAnswerExcelModel.xlsx"); workbook = new XSSFWorkbook(in); voteMobileService.setExcelData(workbook, list); String voteName = voteMobileService.queryVoteNameByTitleId(titleId); voteName = null == voteName || "".equals(voteName) ? "?" : voteName; sendFile(request, response, voteName + ".xlsx", workbook, 0); } catch (Exception e) { log.error("???,?id=" + titleId, e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { } } } }
From source file:com.frameworkset.platform.cms.votemanager.VoteMobileController.java
License:Open Source License
public void newExportExcel(String titleId, ModelMap model, HttpServletRequest request, HttpServletResponse response) {/* w w w . ja v a 2 s. com*/ InputStream in = null; Workbook workbook = null; try { // ?? List<Map<String, String>> titleList = voteMobileService.getQuestionTitleById(titleId); // ?? List<ExcelBean> answerList = voteMobileService.queryAnswerContent(titleId); in = VoteMobileController.class.getResourceAsStream("MobileVoteAnswerExcelModel.xlsx"); workbook = new XSSFWorkbook(in); voteMobileService.setExcelData(workbook, titleList, answerList); String voteName = voteMobileService.queryVoteNameByTitleId(titleId); voteName = null == voteName || "".equals(voteName) ? "?" : voteName; sendFile(request, response, voteName + ".xlsx", workbook, 0); } catch (Exception e) { log.error("???,?id=" + titleId, e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { } } } }