List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importVendorSpecificFuelLog(InputStream is, LinkedHashMap<String, String> vendorSpecificColumns, Long vendor, HashMap<String, Object> additionalVendorData) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {/*from www.ja va2s .co m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, vendorSpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", vendor); FuelVendor fuelVendor = genericDAO.findByCriteria(FuelVendor.class, criterias, "name", false).get(0); boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; !stopParsing && i <= sheet.getPhysicalNumberOfRows() - 1; i++) { LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(fuelVendor.getName()); rowObjects.add(fuelVendor.getCompany().getName()); Row row = sheet.getRow(i); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); // corresponding column not found in actual column list, find in additionalVendorData if (entry.getValue() == -1) { System.out.println("Additional vendor data = " + additionalVendorData); System.out.println("Column " + entry.getKey() + " not found in Vendor Excel, checking in additionalVendorData"); Object cellValueObj = additionalVendorData.get(entry.getKey()); if (cellValueObj != null) { rowObjects.add(cellValueObj); } else { rowObjects.add(StringUtils.EMPTY); } continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null && cellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); stopParsing = true; rowObjects.clear(); break; } rowObjects.add(cellValueObj); } if (!stopParsing) { data.add(rowObjects); } } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importTollCompanySpecificTollTag(InputStream is, LinkedHashMap<String, String> tollCompanySpecificColumns, Long tollCompanyId) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {/*from ww w. ja v a2s . co m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, tollCompanySpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", tollCompanyId); TollCompany tollCompany = genericDAO.findByCriteria(TollCompany.class, criterias, "name", false).get(0); //boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; i <= sheet.getPhysicalNumberOfRows() - 1; i++) { Row row = sheet.getRow(i); Object firstCellValueObj = getCellValue((HSSFCell) row.getCell(0), true); if (firstCellValueObj != null && firstCellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); break; } LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(tollCompany.getName()); /*// TODO: For now, need to get logic String company = StringUtils.substringAfterLast(tollCompany.getName(), " "); company = StringUtils.defaultIfEmpty(company, "LU"); rowObjects.add(company);*/ rowObjects.add(tollCompany.getCompany().getName()); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); if (entry.getValue() == -1) { // corresponding column not found rowObjects.add(StringUtils.EMPTY); continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null) { System.out.println("Adding " + cellValueObj.toString()); } else { System.out.println("Adding NULL"); } rowObjects.add(cellValueObj); } data.add(rowObjects); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }
From source file:com.proem.exm.service.wholesaleGroupPurchase.customer.impl.CustomerInfoServiceImpl.java
@Override public Map<String, Object> getAllByExcel(String path) { Map returnMap = new HashMap(); String returnAnwer = ""; List<CustomerInfo> list = new ArrayList<CustomerInfo>(); try {//from ww w.j a v a2 s . com HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(path)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null;// excel HSSFCell cell = null;// excel int totalRow = sheet.getLastRowNum(); for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); if (row != null) { CustomerInfo obj = new CustomerInfo(); HSSFCell obj_id = row.getCell(0); if (getValue(obj_id) != null) { obj.setId(getValue(obj_id)); } HSSFCell obj_code = row.getCell(1); obj.setCode(getValue(obj_code) == null ? "" : getValue(obj_code)); HSSFCell obj_name = row.getCell(2); obj.setName(getValue(obj_name) == null ? "" : getValue(obj_name)); HSSFCell obj_memory_code = row.getCell(3); obj.setMnemonicCode(getValue(obj_memory_code) == null ? "" : getValue(obj_memory_code)); HSSFCell defaultPrice = row.getCell(4); //obj.setDefaultPrice(getValue(defaultPrice) == null ? "" : getValue(defaultPrice)); obj.setDefaultPrice("WHOLESALE_PRICE"); HSSFCell discount = row.getCell(5); obj.setDiscount(getValue(discount) == null ? "" : getValue(discount)); HSSFCell ownedStores = row.getCell(6); obj.setOwnedStores(getValue(ownedStores) == null ? "" : getValue(ownedStores)); HSSFCell preferentialWay = row.getCell(7); obj.setPreferentialWay(getValue(preferentialWay) == null ? "" : getValue(preferentialWay)); HSSFCell settlementcycle = row.getCell(8); obj.setSettlementcycle(getValue(settlementcycle) == null ? "" : getValue(settlementcycle)); HSSFCell settlementdate = row.getCell(9); obj.setSettlementdate(getValue(settlementdate) == null ? "" : getValue(settlementdate)); HSSFCell settlementway = row.getCell(10); //obj.setSettlementway(getValue(settlementway)== null ? "" : getValue(settlementway)); obj.setSettlementway("?"); HSSFCell creditLimit = row.getCell(11); obj.setCreditLimit(getValue(creditLimit) == null ? "" : getValue(creditLimit)); HSSFCell saleman = row.getCell(12); obj.setSaleman(getValue(saleman) == null ? "" : getValue(saleman)); HSSFCell area = row.getCell(13); //obj.setArea(getValue(area) == null ? "" : getValue(area)); obj.setArea(""); HSSFCell type = row.getCell(14); obj.setType(getValue(type) == null ? "" : getValue(type)); HSSFCell linkman = row.getCell(15); obj.setLinkman(getValue(linkman) == null ? "" : getValue(linkman)); HSSFCell address = row.getCell(16); obj.setAddress(getValue(address) == null ? "" : getValue(address)); HSSFCell mail = row.getCell(17); obj.setMail(getValue(mail) == null ? "" : getValue(mail)); HSSFCell telephone = row.getCell(18); obj.setTelephone(getValue(telephone) == null ? "" : getValue(telephone)); HSSFCell bank = row.getCell(19); obj.setBank(getValue(bank) == null ? "" : getValue(bank)); HSSFCell mobilephone = row.getCell(20); obj.setMobilephone(getValue(mobilephone) == null ? "" : getValue(mobilephone)); HSSFCell taxregistration = row.getCell(21); obj.setTaxregistration(getValue(taxregistration) == null ? "" : getValue(taxregistration)); HSSFCell fax = row.getCell(22); obj.setFax(getValue(fax) == null ? "" : getValue(fax)); HSSFCell license = row.getCell(23); obj.setLicense(getValue(license) == null ? "" : getValue(license)); HSSFCell postcode = row.getCell(24); obj.setPostcode(getValue(postcode) == null ? "" : getValue(postcode)); HSSFCell account = row.getCell(25); obj.setAccount(getValue(account) == null ? "" : getValue(account)); HSSFCell frozen = row.getCell(26); obj.setFrozen(getValue(frozen) == null ? "" : getValue(frozen)); HSSFCell remark = row.getCell(27); obj.setRemark(getValue(remark) == null ? "" : getValue(remark)); list.add(obj); } } returnMap.put("returnAnwer", returnAnwer); returnMap.put("listSupply", list); return returnMap; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:com.pureinfo.studio.db.txt2SRM.impl.SchoolSCITxtImportRunner.java
License:Open Source License
/** * @param _sString//from w w w . j av a 2s .c o m * @throws PureException * @throws IOException * @throws FileNotFoundException */ public List check(String _sFileName) throws PureException, FileNotFoundException, IOException { POIFSFileSystem fs; HSSFSheet m_sheet = null; FileInputStream fileInputStream = new FileInputStream(_sFileName); String[] m_heads; fs = new POIFSFileSystem(fileInputStream); HSSFWorkbook wb = new HSSFWorkbook(fs); m_sheet = wb.getSheetAt(0); HSSFRow row = m_sheet.getRow(0); HSSFCell cell; // TITLE // BY lily List list = new ArrayList(row.getLastCellNum()); for (int i = 0; i < row.getLastCellNum(); i++) { cell = row.getCell((short) i); if (cell == null) { break; } list.add(cell.getStringCellValue().trim()); } m_heads = new String[list.size()]; list.toArray(m_heads); list.clear(); fileInputStream.close(); return checkExcelHead(m_heads); }
From source file:com.pureinfo.studio.db.xls2srm.impl.ProjectBatchImportRunner.java
License:Open Source License
/** * @param _sString//from w w w.j av a 2s.c om * @throws PureException * @throws IOException * @throws FileNotFoundException */ public List check(String _sFileName) throws PureException, FileNotFoundException, IOException { POIFSFileSystem fs; HSSFSheet m_sheet = null; FileInputStream fileInputStream = new FileInputStream(_sFileName); String[] m_heads; fs = new POIFSFileSystem(fileInputStream); HSSFWorkbook wb = new HSSFWorkbook(fs); m_sheet = wb.getSheetAt(0); HSSFRow row = m_sheet.getRow(0); HSSFCell cell; // TITLE // BY lily List list = new ArrayList(row.getLastCellNum()); for (int i = 0; i < row.getLastCellNum(); i++) { cell = row.getCell((short) i); if (cell == null) { break; } list.add(cell.getStringCellValue().trim()); } m_heads = new String[list.size()]; list.toArray(m_heads); list.clear(); fileInputStream.close(); return checkExcelHead(m_heads); }
From source file:com.pureinfo.studio.db.xls2srm.impl.XlsObjectsImpl.java
License:Open Source License
public XlsObjectsImpl(String _sFileName) throws PureException { POIFSFileSystem fs;/*from www.j ava 2 s . c om*/ try { fs = new POIFSFileSystem(new FileInputStream(_sFileName)); HSSFWorkbook wb = new HSSFWorkbook(fs); m_sheet = wb.getSheetAt(0); HSSFRow row = m_sheet.getRow(0); List list = new ArrayList(row.getLastCellNum()); HSSFCell cell; for (int i = 0; i < row.getLastCellNum(); i++) { cell = row.getCell((short) i); if (cell == null) { break; } list.add(cell.getStringCellValue().trim().toUpperCase()); } m_heads = new String[list.size()]; list.toArray(m_heads); list.clear(); } catch (Exception ex) { throw new PureException(PureException.UNKNOWN, "", ex); } }
From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java
License:Open Source License
public String processExcel(String fileName, ESupplyGlobalParameters loginBean) throws EJBException { long startTime = System.currentTimeMillis(); String s1 = ""; PreparedStatement pstmtMainHeader = null; PreparedStatement pstmtDetails = null; PreparedStatement pstmtDetailsData = null; PreparedStatement MainHeader = null; PreparedStatement Details = null; PreparedStatement DetailsData = null; Connection connection = null; OperationsImpl operationsImpl = null; String dateFormat = null;// w w w . ja v a 2 s . c o m String deleteDtldata = "DELETE FROM QMS_STG_BUYRATES_DETAILS_DATA"; String deleteDtl = "DELETE FROM QMS_STG_BUYRATES_DETAILS"; String deleteHeader = "DELETE FROM QMS_STG_BUYRATES_MAIN_HEADER"; String selectMainHeader = "SELECT WEIGHT_BREAK FROM QMS_STG_BUYRATES_MAIN_HEADER"; ResultSet rs = null; PreparedStatement pstmtHeader = null; //@@Added by Kameswari on 15/04/09 String wtbreak = null; String returnstr = null; CallableStatement cstmt = null; double time = 0.0; String data = null; try { String s = ""; String mainHeaderData[] = new String[8]; int rows = 0; int cols = 0; int notesIndex = 0; int batchCount = 0; HSSFRow row = null; HSSFCell cell = null; InputStream myxls = new FileInputStream(fileName); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); rows = sheet.getPhysicalNumberOfRows(); dateFormat = loginBean.getUserPreferences().getDateFormat(); String queryMainHeader = "INSERT INTO QMS_STG_BUYRATES_MAIN_HEADER (SHIPMENT_MODE,CURRENCY,WEIGHT_BREAK,WEIGHT_CLASS,RATE_TYPE,UOM,CONSOLETYPE,DENSITY_RATIO,TERMINALID) VALUES (?,?,?,?,?,?,?,?,?)"; // String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,?,?,?)"; String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,TO_DATE(?),TO_DATE(?),?)"; String queryDetailsData = "INSERT INTO QMS_STG_BUYRATES_DETAILS_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINERS_VALUE,LOWER_BOUND,UPPER_BOUND,RATE_DESCRIPTION) VALUES (?,?,?,?,?,?,?)"; String msgquery = "SELECT ERROR_MSG FROM QMS_STG_BUYRATES_DETAILS"; // connection=ConnectionUtil.getConnection(); operationsImpl = new OperationsImpl(); connection = operationsImpl.getConnection(); DetailsData = connection.prepareStatement(deleteDtldata); Details = connection.prepareStatement(deleteDtl); MainHeader = connection.prepareStatement(deleteHeader); DetailsData.execute(); Details.execute(); MainHeader.execute(); // long startTime1=System.currentTimeMillis(); //For MainHeader row = sheet.getRow(1); for (int k = 0; k < 8; k++) { cell = row.getCell((short) k); mainHeaderData[k] = getCellData(cell, 0); } pstmtMainHeader = connection.prepareStatement(queryMainHeader); insertMainHeaderData(mainHeaderData, pstmtMainHeader, loginBean); //End pstmtHeader = connection.prepareStatement(selectMainHeader); rs = pstmtHeader.executeQuery(); if (rs.next()) { wtbreak = rs.getString("WEIGHT_BREAK"); } //To get columns count , column index for NOTES and containers ids row = sheet.getRow(3); cols = row.getPhysicalNumberOfCells(); int l = cols - 1; for (; l >= 0; l--) { cell = row.getCell((short) l); if ("NOTES".equalsIgnoreCase(getCellData(cell, l))) break; } notesIndex = l + 1; String detailsData[] = new String[notesIndex]; String containersData[] = new String[notesIndex - 9]; for (l = 8; l < notesIndex - 1; l++) { cell = row.getCell((short) l); containersData[l - 8] = getCellData(cell, l); } //End //For Details pstmtDetails = connection.prepareStatement(queryDetails); ; pstmtDetailsData = connection.prepareStatement(queryDetailsData); for (int i = 4; i <= rows; i++) { row = sheet.getRow(i); //cols=row.getPhysicalNumberOfCells(); //s=row.getRowNum() + " : "; for (int j = 0; j < notesIndex; j++) { if (row != null) { cell = row.getCell((short) j); detailsData[j] = getCellData(cell, j); } // s = s+ " -- " + detailsData[j]; if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (Double.parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) { time = 1 / Double.parseDouble(detailsData[j]); Double t = new Double(time); if (t.toString().indexOf(".") != -1) { data = t.toString().substring(0, t.toString().indexOf(".")); } if (data != null) { detailsData[j] = data + ":" + "00"; } } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (!(Double .parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) { if (detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } if (j == 5 && detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } //long EndTime1=System.currentTimeMillis(); //System.out.println("\n------->>>> M.SEC : "+(EndTime1-startTime1) +" SEC : " + (EndTime1-startTime1)/(1000) + " MIN : "+(EndTime1-startTime1)/(1000*60)); batchCount++; insertDetails(detailsData, containersData, pstmtDetails, pstmtDetailsData, notesIndex, i, dateFormat, wtbreak); if (batchCount == 150) { pstmtDetails.executeBatch(); pstmtDetailsData.executeBatch(); pstmtDetails.clearBatch(); pstmtDetailsData.clearBatch(); batchCount = 0; } // s1=s1+s + "\n"; } if (batchCount > 0) { pstmtDetails.executeBatch(); pstmtDetailsData.executeBatch(); pstmtDetails.clearBatch(); pstmtDetailsData.clearBatch(); batchCount = 0; } cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_PROC(?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, loginBean.getTerminalId()); cstmt.execute(); returnstr = (String) cstmt.getString(1); //End return returnstr; } catch (Exception e) { logger.error("Exception"); e.printStackTrace(); } finally { try { if (pstmtMainHeader != null) pstmtMainHeader.close(); if (pstmtDetails != null) pstmtDetails.close(); if (pstmtDetailsData != null) pstmtDetailsData.close(); if (cstmt != null) cstmt.close(); if (connection != null) connection.close(); if (rs != null)//Added by govind on 16-02-2010 for Connection Leakages rs.close(); if (pstmtHeader != null)//Added by govind on 16-02-2010 for Connection Leakages pstmtHeader.close(); if (MainHeader != null)//Added by govind on 16-02-2010 for Connection Leakages MainHeader.close(); if (DetailsData != null) //Added by govind on 16-02-2010 for Connection Leakages DetailsData.close(); if (Details != null)//Added by govind on 16-02-2010 for Connection Leakages Details.close(); } catch (Exception e) { logger.error("Error Occured while closing Resources" + e); } } long EndTime = System.currentTimeMillis(); s1 = " M.SEC : " + (EndTime - startTime) + " SEC : " + (EndTime - startTime) / (1000) + " MIN : " + (EndTime - startTime) / (1000 * 60); return s1; }
From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java
License:Open Source License
public String processExcelDelete(String fileName) throws EJBException { String s1 = ""; PreparedStatement pstmtDetails = null; // PreparedStatement pstmtDetails1 = null;Commented by govind on 16-02-2010 for connectionLeakages PreparedStatement Details = null; // PreparedStatement Details1 = null;Commented by govind on 16-02-2010 for connectionLeakages Connection connection = null; OperationsImpl operationsImpl = null; String deleteDtl = "TRUNCATE TABLE QMS_BUYRATES_DELETE_DETAILS"; // String deleteDtl1 = "DELETE FROM QMS_BUYRATES_DELETE_DATA"; String queryDetails = "INSERT INTO QMS_BUYRATES_DELETE_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICE_LEVEL,FREQUENCY,TRANSIT_TIME,CURRENCY,TERMINAL_ID,DENSITY_CODE) VALUES (?,?,?,?,?,?,?,?,?,?)"; // String queryDetails1 = "INSERT INTO QMS_BUYRATES_DELETE_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINER_VALUE) VALUES (?,?,?,?)"; String msgquery = "SELECT ERROR_MSG FROM QMS_BUYRATES_DELETE_DETAILS"; // ResultSet rs = null;Commeneted by Govind on 16-02-2010 for Connection Leakages String returnstr = null;/*from w w w. j a va2 s .c o m*/ CallableStatement cstmt = null; try { String s = ""; String mainHeaderData[] = new String[8]; int rows = 0; int cols = 0; int notesIndex = 0; int batchCount = 0; HSSFRow row = null; HSSFCell cell = null; InputStream myxls = new FileInputStream(fileName); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); rows = sheet.getPhysicalNumberOfRows(); // connection=ConnectionUtil.getConnection(); operationsImpl = new OperationsImpl(); connection = operationsImpl.getConnection(); Details = connection.prepareStatement(deleteDtl); Details.execute(); // Details1 = connection.prepareStatement(deleteDtl1); // Details1.execute(); row = sheet.getRow(0); cols = row.getPhysicalNumberOfCells(); int l = cols - 1; for (; l >= 0; l--) { cell = row.getCell((short) l); if ("TERMINALID:".equalsIgnoreCase(getCellData(cell, 0))) break; } notesIndex = l + 1; // notesIndex=cols; String detailsData[] = new String[notesIndex]; /*String containersData[] =new String[notesIndex-7]; for(l=7;l<notesIndex-2;l++) { cell=row.getCell((short)l); containersData[l-7]=getCellData(cell); }*/ pstmtDetails = connection.prepareStatement(queryDetails); // pstmtDetails1= connection.prepareStatement(queryDetails1); for (int i = 1; i < rows; i++) { row = sheet.getRow(i); double time = 0.0; String data = null; for (int j = 0; j < notesIndex; j++) { if (row != null) { cell = row.getCell((short) j); /* { cell.setCellType(cell.CELL_TYPE_STRING); }*/ detailsData[j] = getCellData(cell, 0); if (j == 4) // Added by Gowtham. { if (detailsData[j].toString().indexOf(".") != -1) detailsData[j] = detailsData[j].substring(0, detailsData[j].toString().indexOf(".")); } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (Double.parseDouble( detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) { time = 1 / Double.parseDouble(detailsData[j]); Double t = new Double(time); if (t.toString().indexOf(".") != -1) { data = t.toString().substring(0, t.toString().indexOf(".")); } if (data != null) { detailsData[j] = data + ":" + "00"; } } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (!(Double.parseDouble( detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) { if (detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } if (j == 5 && detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } /*if(j==8&&detailsData[j].indexOf(".")!=-1) { detailsData[j] =detailsData[j].substring(0, detailsData[j].indexOf(".")); }*/ } } batchCount++; insertDeleteDetails(detailsData, pstmtDetails, notesIndex, i); if (batchCount == 150) { pstmtDetails.executeBatch(); pstmtDetails.clearBatch(); batchCount = 0; } } if (batchCount > 0) { pstmtDetails.executeBatch(); pstmtDetails.clearBatch(); batchCount = 0; } cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_DELETE_PROC}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.execute(); returnstr = (String) cstmt.getString(1); //End return returnstr; } catch (Exception e) { logger.error("Exception"); e.printStackTrace(); } finally { try { /* if(rs!=null) rs.close();-*/ //Commented by govind on 16-02-2010 fro connection leakages. if (pstmtDetails != null) pstmtDetails.close(); if (Details != null) Details.close(); if (cstmt != null) cstmt.close(); if (connection != null) connection.close(); } catch (Exception e) { logger.error("Error Occured while closing Resources" + e); } } return s1; }
From source file:com.quix.aia.cn.imo.mapper.EopMaintenance.java
License:Open Source License
private boolean checkSpecialGroupAgentId(String eopPath, String tempDir, String fileName, HttpServletRequest requestParameters, String byteSession) { // TODO Auto-generated method stub log.log(Level.INFO, "---EopMaintenance checkSpecialGroupAgentId---"); String path = ""; FileOutputStream stream = null; String rendomStr = LMSUtil.getRendomToken(); boolean flag = false; try {/*from ww w . ja v a 2 s . com*/ String serverFilename = eopPath + "/" + "EOP_" + rendomStr; File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } byte[] bytearray = (byte[]) requestParameters.getSession().getAttribute(byteSession); stream = new FileOutputStream(serverFilename + "/" + fileName); stream.write(bytearray); path = "EOP_" + rendomStr + "/" + fileName; log.log(Level.INFO, "---EopMaintenance deleting temp folder---"); FileInputStream fileInputStream = null; BufferedReader bufferedReader = null; String records = null; SpecialGroup sg = null; fileInputStream = new FileInputStream(new File(eopPath + "/" + path)); bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream)); if (fileName.contains("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell.toString()); if (cell.toString().trim().length() != 0 && cell.toString() != null) { flag = checkAgentCodeInAamData(cell.toString(), requestParameters); } else { flag = true; } if (flag == false) { break; } } if (flag == false) { break; } } } else { while ((records = bufferedReader.readLine()) != null) { flag = checkAgentCodeInAamData(records, requestParameters); } } } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); LogsMaintenance logsMain = new LogsMaintenance(); e.printStackTrace(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("EopMaintenance", Level.SEVERE + "", errors.toString()); } finally { if (stream != null) try { stream.close(); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); } } return flag; }
From source file:com.quix.aia.cn.imo.mapper.EopMaintenance.java
License:Open Source License
/** * <p>Method takes care for inserting special users agency unit.</p> * @param session hibernate session object * @param eventCode event code of current Event * @param filePath file path//from w w w. j av a2s . com * @param op represents from where this method is called(during insert or update) * @param fileExtension */ public void insertSpecialGroup(Session session, int eventCode, String filePath, String op, String fileExtension) { // Retrieve datas from CSV FileInputStream fileInputStream = null; BufferedReader bufferedReader = null; try { String records = null; SpecialGroup sg = null; fileInputStream = new FileInputStream(new File(filePath)); bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream)); if (op.equals("UPDATE")) { Query deleteQ = session.createQuery("delete SpecialGroup where eventCode = " + eventCode + ""); deleteQ.executeUpdate(); } if (fileExtension.equals("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell.toString()); sg = new SpecialGroup(); sg.setEventCode(eventCode); sg.setAgencyUnit(cell.toString()); session.save(sg); } } } else { while ((records = bufferedReader.readLine()) != null) { sg = new SpecialGroup(); sg.setEventCode(eventCode); sg.setAgencyUnit(records); session.save(sg); } } log.log(Level.INFO, "---EopMaintenance special user inserted---"); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); LogsMaintenance logsMain = new LogsMaintenance(); e.printStackTrace(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("EopMaintenance", Level.SEVERE + "", errors.toString()); } finally { try { if (fileInputStream != null) fileInputStream.close(); if (bufferedReader != null) bufferedReader.close(); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } } }