List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum);
From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java
private List<Lng> populateLngData(Sheet sheet, Tab tab) { logger.info("Class - ReadExcelFile - populateLngData()"); int columnNo = 0; List<Lng> lngList = new ArrayList<Lng>(); int rowCount = sheet.getLastRowNum(); int totalRecords = 0; StringBuffer recordsList = new StringBuffer(BLANK); Set<String> columnNamesSet = new HashSet<String>(); int linebreak = 1; for (int i = 1; i <= rowCount; i++) { if (i == 191) System.out.println(i); Row row = sheet.getRow(i); Lng l = new Lng(); try {/*from ww w. j a v a 2 s.c om*/ columnNo = 0; l.setName(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0)) || (BLANK).equals(df.formatCellValue(row.getCell(0))) ? BLANK : df.formatCellValue(row.getCell(0))); columnNo = 1; l.setCountry(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1)) || (BLANK).equals(df.formatCellValue(row.getCell(1))) ? BLANK : df.formatCellValue(row.getCell(1))); columnNo = 2; l.setArea(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2)) || (BLANK).equals(df.formatCellValue(row.getCell(2))) ? BLANK : df.formatCellValue(row.getCell(2))); columnNo = 3; l.setRegion(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3)) || (BLANK).equals(df.formatCellValue(row.getCell(3))) ? BLANK : df.formatCellValue(row.getCell(3))); columnNo = 4; l.setType(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4)) || (BLANK).equals(df.formatCellValue(row.getCell(4))) ? BLANK : df.formatCellValue(row.getCell(4))); columnNo = 5; l.setStatus(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5)) || (BLANK).equals(df.formatCellValue(row.getCell(5))) ? BLANK : df.formatCellValue(row.getCell(5))); columnNo = 6; l.setFeedDetails(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6)) || (BLANK).equals(df.formatCellValue(row.getCell(6))) ? BLANK : df.formatCellValue(row.getCell(6))); columnNo = 7; l.setFidDetails(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7)) || (BLANK).equals(df.formatCellValue(row.getCell(7))) ? BLANK : df.formatCellValue(row.getCell(7))); columnNo = 8; l.setConstructionStatusDetails(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8)) || (BLANK).equals(df.formatCellValue(row.getCell(8))) ? BLANK : df.formatCellValue(row.getCell(8))); columnNo = 9; l.setSources(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9)) || (BLANK).equals(df.formatCellValue(row.getCell(9))) ? BLANK : df.formatCellValue(row.getCell(9))); columnNo = 10; l.setOtherStatusDetails(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10)) || (BLANK).equals(df.formatCellValue(row.getCell(10))) ? BLANK : df.formatCellValue(row.getCell(10))); // System.out.println("LNG first"); columnNo = 11; l.setStatusDate(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11)) || (BLANK).equals(df.formatCellValue(row.getCell(11))) ? null : new Date(df.formatCellValue(row.getCell(11)))); columnNo = 12; l.setStatusSource(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12)) || (BLANK).equals(df.formatCellValue(row.getCell(12))) ? BLANK : df.formatCellValue(row.getCell(12))); columnNo = 13; l.setOnshoreOrOffshore(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13)) || (BLANK).equals(df.formatCellValue(row.getCell(13))) ? BLANK : df.formatCellValue(row.getCell(13))); columnNo = 14; l.setExpectedStartYear(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14)) || (BLANK).equals(df.formatCellValue(row.getCell(14))) ? null : new Date(df.formatCellValue(row.getCell(14)))); columnNo = 15; l.setScheduledStartYear(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15)) || (BLANK).equals(df.formatCellValue(row.getCell(15))) ? null : new Date(df.formatCellValue(row.getCell(15)))); columnNo = 16; l.setDelayOrInitialStartYear(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16)) || (BLANK).equals(df.formatCellValue(row.getCell(16))) ? null : new Date(df.formatCellValue(row.getCell(16)))); columnNo = 17; l.setDelayDetails(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17)) || (BLANK).equals(df.formatCellValue(row.getCell(17))) ? BLANK : df.formatCellValue(row.getCell(17))); columnNo = 18; l.setTechnologyDetails(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18)) || (BLANK).equals(df.formatCellValue(row.getCell(18))) ? BLANK : df.formatCellValue(row.getCell(18))); columnNo = 19; l.setNumberOfTrainsOrNumberOfVaporizers( null == row.getCell(19) || null == df.formatCellValue(row.getCell(19)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(19)))); columnNo = 20; l.setCapacity(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(20)))); // System.out.println("LNG second"); columnNo = 21; l.setCapacityYear(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(21)))); columnNo = 22; l.setExpansionDetail(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22)) || (BLANK).equals(df.formatCellValue(row.getCell(22))) ? BLANK : df.formatCellValue(row.getCell(22))); columnNo = 23; l.setSource(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23)) || (BLANK).equals(df.formatCellValue(row.getCell(23))) ? BLANK : df.formatCellValue(row.getCell(23))); columnNo = 24; l.setAdditionalProducts(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24)) || (BLANK).equals(df.formatCellValue(row.getCell(24))) ? BLANK : df.formatCellValue(row.getCell(24))); columnNo = 25; l.setAdditionalProductsProduction( null == row.getCell(25) || null == df.formatCellValue(row.getCell(25)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(25)))); columnNo = 26; l.setAdditionalProductsProductionUnit( null == row.getCell(26) || null == df.formatCellValue(row.getCell(26)) || (BLANK).equals(df.formatCellValue(row.getCell(26))) ? BLANK : df.formatCellValue(row.getCell(26))); columnNo = 27; l.setNumberOfStorageTanks(null == row.getCell(27) || null == df.formatCellValue(row.getCell(27)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(27))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(27)))); columnNo = 28; l.setTotalLngStorageCapacity(null == row.getCell(28) || null == df.formatCellValue(row.getCell(28)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(28))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(28)))); columnNo = 29; l.setSourcesOther(null == row.getCell(29) || null == df.formatCellValue(row.getCell(29)) || (BLANK).equals(df.formatCellValue(row.getCell(29))) ? BLANK : df.formatCellValue(row.getCell(29))); columnNo = 30; l.setOperator(null == row.getCell(30) || null == df.formatCellValue(row.getCell(30)) || (BLANK).equals(df.formatCellValue(row.getCell(30))) ? BLANK : df.formatCellValue(row.getCell(30))); // //// System.out.println("LNG third"); // columnNo = 31; l.setEquityPartners(null == row.getCell(31) || null == df.formatCellValue(row.getCell(31)) || (BLANK).equals(df.formatCellValue(row.getCell(31))) ? BLANK : df.formatCellValue(row.getCell(31))); columnNo = 32; l.setEquityStakes(null == row.getCell(32) || null == df.formatCellValue(row.getCell(32)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(32))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(32)))); columnNo = 33; l.setEquityNotes(null == row.getCell(33) || null == df.formatCellValue(row.getCell(33)) || (BLANK).equals(df.formatCellValue(row.getCell(33))) ? BLANK : df.formatCellValue(row.getCell(33))); columnNo = 34; l.setHistoricOperator(null == row.getCell(34) || null == df.formatCellValue(row.getCell(34)) || (BLANK).equals(df.formatCellValue(row.getCell(34))) ? BLANK : df.formatCellValue(row.getCell(34))); columnNo = 35; l.setHistoricEquityPartner(null == row.getCell(35) || null == df.formatCellValue(row.getCell(35)) || (BLANK).equals(df.formatCellValue(row.getCell(35))) ? BLANK : df.formatCellValue(row.getCell(35))); columnNo = 36; l.setHistoricEquityStake(null == row.getCell(36) || null == df.formatCellValue(row.getCell(36)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(36))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(36)))); columnNo = 37; l.setHistoricEquityYear(null == row.getCell(37) || null == df.formatCellValue(row.getCell(37)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(37))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(37)))); columnNo = 38; l.setEquitySources(null == row.getCell(38) || null == df.formatCellValue(row.getCell(38)) || (BLANK).equals(df.formatCellValue(row.getCell(38))) ? BLANK : df.formatCellValue(row.getCell(38))); columnNo = 39; l.setFeedOrInputType(null == row.getCell(39) || null == df.formatCellValue(row.getCell(39)) || (BLANK).equals(df.formatCellValue(row.getCell(39))) ? BLANK : df.formatCellValue(row.getCell(39))); columnNo = 40; l.setFeedOrInputName(null == row.getCell(40) || null == df.formatCellValue(row.getCell(40)) || (BLANK).equals(df.formatCellValue(row.getCell(40))) ? BLANK : df.formatCellValue(row.getCell(40))); // //// System.out.println("LNG fourth"); // columnNo = 41; l.setFeedOrInputDetails(null == row.getCell(41) || null == df.formatCellValue(row.getCell(41)) || (BLANK).equals(df.formatCellValue(row.getCell(41))) ? BLANK : df.formatCellValue(row.getCell(41))); columnNo = 42; l.setDisttributionOrOutputType( null == row.getCell(42) || null == df.formatCellValue(row.getCell(42)) || (BLANK).equals(df.formatCellValue(row.getCell(42))) ? BLANK : df.formatCellValue(row.getCell(42))); columnNo = 43; l.setDisttributionOrOutputName( null == row.getCell(43) || null == df.formatCellValue(row.getCell(43)) || (BLANK).equals(df.formatCellValue(row.getCell(43))) ? BLANK : df.formatCellValue(row.getCell(43))); columnNo = 44; l.setDisttributionOrOutputDetails( null == row.getCell(44) || null == df.formatCellValue(row.getCell(44)) || (BLANK).equals(df.formatCellValue(row.getCell(44))) ? BLANK : df.formatCellValue(row.getCell(44))); columnNo = 45; l.setJettyInfo_m3(null == row.getCell(45) || null == df.formatCellValue(row.getCell(45)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(45))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(45)))); columnNo = 46; l.setCapex(null == row.getCell(46) || null == df.formatCellValue(row.getCell(46)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(46))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(46)))); columnNo = 47; l.setCapexYear(null == row.getCell(47) || null == df.formatCellValue(row.getCell(47)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(47))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(47)))); columnNo = 48; l.setCapexDetails(null == row.getCell(48) || null == df.formatCellValue(row.getCell(48)) || (BLANK).equals(df.formatCellValue(row.getCell(48))) ? BLANK : df.formatCellValue(row.getCell(48))); columnNo = 49; l.setSources1(null == row.getCell(49) || null == df.formatCellValue(row.getCell(49)) || (BLANK).equals(df.formatCellValue(row.getCell(49))) ? BLANK : df.formatCellValue(row.getCell(49))); columnNo = 50; l.setConstructionStart(null == row.getCell(50) || null == df.formatCellValue(row.getCell(50)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(50))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(50)))); // //// System.out.println("LNG five"); // columnNo = 51; l.setConstructionEnd(null == row.getCell(51) || null == df.formatCellValue(row.getCell(51)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(51))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(51)))); columnNo = 52; l.setConstructionCompanyName(null == row.getCell(52) || null == df.formatCellValue(row.getCell(52)) || (BLANK).equals(df.formatCellValue(row.getCell(52))) ? BLANK : df.formatCellValue(row.getCell(52))); columnNo = 53; l.setConstructionContractDetails( null == row.getCell(53) || null == df.formatCellValue(row.getCell(53)) || (BLANK).equals(df.formatCellValue(row.getCell(53))) ? BLANK : df.formatCellValue(row.getCell(53))); columnNo = 54; l.setOtherContractsDetails(null == row.getCell(54) || null == df.formatCellValue(row.getCell(54)) || (BLANK).equals(df.formatCellValue(row.getCell(54))) ? BLANK : df.formatCellValue(row.getCell(54))); columnNo = 55; l.setLngTerminalOtherNames(null == row.getCell(55) || null == df.formatCellValue(row.getCell(55)) || (BLANK).equals(df.formatCellValue(row.getCell(55))) ? BLANK : df.formatCellValue(row.getCell(55))); columnNo = 56; l.setRelatedCompanies(null == row.getCell(56) || null == df.formatCellValue(row.getCell(56)) || (BLANK).equals(df.formatCellValue(row.getCell(56))) ? BLANK : df.formatCellValue(row.getCell(56))); columnNo = 57; l.setRelatedCompanyDetails(null == row.getCell(57) || null == df.formatCellValue(row.getCell(57)) || (BLANK).equals(df.formatCellValue(row.getCell(57))) ? BLANK : df.formatCellValue(row.getCell(57))); columnNo = 58; l.setSources2(null == row.getCell(58) || null == df.formatCellValue(row.getCell(58)) || (BLANK).equals(df.formatCellValue(row.getCell(58))) ? BLANK : df.formatCellValue(row.getCell(58))); columnNo = 59; l.setMoreInfo(null == row.getCell(59) || null == df.formatCellValue(row.getCell(59)) || (BLANK).equals(df.formatCellValue(row.getCell(59))) ? BLANK : df.formatCellValue(row.getCell(59))); columnNo = 60; l.setUnits(null == row.getCell(60) || null == df.formatCellValue(row.getCell(60)) || (BLANK).equals(df.formatCellValue(row.getCell(60))) ? BLANK : df.formatCellValue(row.getCell(60))); lngList.add(l); } catch (Exception e) { logger.error("Exception in ReadExcelFile - populateLngData():" + e); totalRecords++; String columnName = getHeaderValues(sheet, columnNo); recordsList.append((row.getRowNum() + 1) + COMMA); if (totalRecords == (12 * linebreak)) { recordsList.append(SPACE);// Adding space for rendering in front end; linebreak++; } columnNamesSet.add(columnName); } } if (totalRecords > 0) { createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet); logger.info("Lng totalRecords:" + totalRecords + "recordsList:" + recordsList); } logger.info("Total objects set for Lng:" + lngList.size()); return lngList; }
From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java
private List<Storage> populateStorageData(Sheet sheet, Tab tab) { logger.info("Class - ReadExcelFile - populateStorageData()"); int columnNo = 0; List<Storage> storageList = new ArrayList<Storage>(); int rowCount = sheet.getLastRowNum(); int totalRecords = 0; StringBuffer recordsList = new StringBuffer(BLANK); Set<String> columnNamesSet = new HashSet<String>(); int linebreak = 1; for (int i = 1; i <= rowCount; i++) { Row row = sheet.getRow(i); Storage s = new Storage(); try {//from w w w .j a v a2s .c o m columnNo = 0; s.setTankFarm(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK : df.formatCellValue(row.getCell(0))); columnNo = 1; s.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK : df.formatCellValue(row.getCell(1))); columnNo = 2; s.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK : df.formatCellValue(row.getCell(2))); columnNo = 3; s.setLocation(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK : df.formatCellValue(row.getCell(3))); columnNo = 4; s.setStatus(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK : df.formatCellValue(row.getCell(4))); columnNo = 5; s.setCommencementDate(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? null : new Date(df.formatCellValue(row.getCell(5)))); columnNo = 6; s.setCommencementSource(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? BLANK : df.formatCellValue(row.getCell(6))); columnNo = 7; s.setCurrentOperator(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? BLANK : df.formatCellValue(row.getCell(7))); columnNo = 8; s.setCurrentOwners(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK : df.formatCellValue(row.getCell(8))); columnNo = 9; s.setCurrentOwnership(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(9)))); columnNo = 10; s.setOwnershipNotes(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK : df.formatCellValue(row.getCell(10))); // System.out.println("Storage First"); columnNo = 11; s.setHistoricOperator(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? BLANK : df.formatCellValue(row.getCell(11))); columnNo = 12; s.setHistoricOwners(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? BLANK : df.formatCellValue(row.getCell(12))); columnNo = 13; s.setHistoricOwnership(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(13)))); columnNo = 14; s.setHistoricalEquityYear(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(14)))); columnNo = 15; s.setEquitySource(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK : df.formatCellValue(row.getCell(15))); columnNo = 16; s.setProductsStored(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? BLANK : df.formatCellValue(row.getCell(16))); columnNo = 17; s.setModeOfAccess(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? BLANK : df.formatCellValue(row.getCell(17))); columnNo = 18; s.setCapacityM3(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(18)))); columnNo = 19; s.setCapacityYear(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(19)))); columnNo = 20; s.setTanks(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(20)))); // System.out.println("Storage second"); columnNo = 21; s.setTankSizeRange_min_m3(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(21)))); columnNo = 22; s.setTankSizeRange_max_m3(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(22)))); columnNo = 23; s.setCapacitySource(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? BLANK : df.formatCellValue(row.getCell(23))); columnNo = 24; s.setCapitalInvestment(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(24)))); columnNo = 25; s.setCapexSource(null == row.getCell(25) || null == df.formatCellValue(row.getCell(25)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? BLANK : df.formatCellValue(row.getCell(25))); columnNo = 26; s.setNotes(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? BLANK : df.formatCellValue(row.getCell(26))); storageList.add(s); } catch (Exception e) { logger.error("Exception in ReadExcelFile - populateStorageData():" + e); totalRecords++; String columnName = getHeaderValues(sheet, columnNo); recordsList.append((row.getRowNum() + 1) + COMMA); if (totalRecords == (12 * linebreak)) { recordsList.append(SPACE);// Adding space for rendering in front end; linebreak++; } columnNamesSet.add(columnName); } } if (totalRecords > 0) { createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet); logger.info("Storage totalRecords:" + totalRecords + "recordsList:" + recordsList); } logger.info("Total objects set for Storage:" + storageList.size()); return storageList; }
From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java
private List<Refinery> populateRefineryData(Sheet sheet, Tab tab) { logger.info("Class - ReadExcelFile - populateRefineryData()"); int columnNo = 0; List<Refinery> refineryList = new ArrayList<Refinery>(); int rowCount = sheet.getLastRowNum(); int totalRecords = 0; StringBuffer recordsList = new StringBuffer(BLANK); Set<String> columnNamesSet = new HashSet<String>(); int linebreak = 1; for (int i = 1; i <= rowCount; i++) { Row row = sheet.getRow(i); Refinery r = new Refinery(); try {/* w w w .j a v a 2 s .c o m*/ // System.out.println(row.getRowNum()); columnNo = 0; r.setName(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK : df.formatCellValue(row.getCell(0))); columnNo = 1; r.setLocation(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK : df.formatCellValue(row.getCell(1))); columnNo = 2; r.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK : df.formatCellValue(row.getCell(2))); columnNo = 3; r.setRegion(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK : df.formatCellValue(row.getCell(3))); columnNo = 4; r.setStatus(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK : df.formatCellValue(row.getCell(4))); columnNo = 5; r.setCapacityYear(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(5)))); columnNo = 6; r.setStatusDetails(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? BLANK : df.formatCellValue(row.getCell(6))); columnNo = 7; r.setStatusDate(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? null : new Date(df.formatCellValue(row.getCell(7)))); // Later this one needs to change to String columnNo = 8; r.setStatusSource(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK : df.formatCellValue(row.getCell(8))); columnNo = 9; r.setMoreInfo(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? BLANK : df.formatCellValue(row.getCell(9))); columnNo = 10; r.setType(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK : df.formatCellValue(row.getCell(10))); // System.out.println(" Refinery First"); columnNo = 11; r.setStartYear(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? null : new Date(df.formatCellValue(row.getCell(11)))); columnNo = 12; r.setDecomissionedYear(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? null : new Date(df.formatCellValue(row.getCell(12)))); columnNo = 13; r.setAssetOrStartSource(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? BLANK : df.formatCellValue(row.getCell(13))); columnNo = 14; r.setCurrentOperator(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? BLANK : df.formatCellValue(row.getCell(14))); columnNo = 15; r.setCurrentEquityPartners(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? BLANK : df.formatCellValue(row.getCell(15))); columnNo = 16; r.setCurrentEquityStakes(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(16)))); columnNo = 17; r.setEquityDetails(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? BLANK : df.formatCellValue(row.getCell(17))); columnNo = 18; r.setHistoricOperator(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? BLANK : df.formatCellValue(row.getCell(18))); columnNo = 19; r.setHistoricEquityPartners(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? BLANK : df.formatCellValue(row.getCell(19))); columnNo = 20; r.setHistoricEquityStakes(null == row.getCell(20) || null == df.formatCellValue(row.getCell(20)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(20))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(20)))); // System.out.println("Refinery second"); columnNo = 21; r.setHistoricEquityYear(null == row.getCell(21) || null == df.formatCellValue(row.getCell(21)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(21))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(21)))); columnNo = 22; r.setEquitySource(null == row.getCell(22) || null == df.formatCellValue(row.getCell(22)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(22))) ? BLANK : df.formatCellValue(row.getCell(22))); columnNo = 23; r.setRefiningCapacity(null == row.getCell(23) || null == df.formatCellValue(row.getCell(23)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(23))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(23)))); columnNo = 24; r.setVduCapacity(null == row.getCell(24) || null == df.formatCellValue(row.getCell(24)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(24))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(24)))); columnNo = 25; r.setCokingCapacity(null == row.getCell(25) || null == df.formatCellValue(row.getCell(25)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(25))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(25)))); columnNo = 26; r.setFcc(null == row.getCell(26) || null == df.formatCellValue(row.getCell(26)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(26))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(26)))); columnNo = 27; r.setHydroCrackingCapacity(null == row.getCell(27) || null == df.formatCellValue(row.getCell(27)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(27))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(27)))); columnNo = 28; r.setSourceCapacities(null == row.getCell(28) || null == df.formatCellValue(row.getCell(28)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(28))) ? BLANK : df.formatCellValue(row.getCell(28))); columnNo = 29; r.setCrudeStorageUnitOrTanksNo( null == row.getCell(29) || null == df.formatCellValue(row.getCell(29)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(29))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(29)))); columnNo = 30; r.setCrudeStorageCapacity(null == row.getCell(30) || null == df.formatCellValue(row.getCell(30)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(30))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(30)))); // System.out.println("Refinery Three"); columnNo = 31; r.setNelsonComplexityIndex(null == row.getCell(31) || null == df.formatCellValue(row.getCell(31)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(31))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(31)))); columnNo = 32; r.setCrudeThroughput(null == row.getCell(32) || null == df.formatCellValue(row.getCell(32)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(32))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(32)))); columnNo = 33; r.setCrudeType(null == row.getCell(33) || null == df.formatCellValue(row.getCell(33)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(33))) ? BLANK : df.formatCellValue(row.getCell(33))); columnNo = 34; r.setApi(null == row.getCell(34) || null == df.formatCellValue(row.getCell(34)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(34))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(34)))); columnNo = 35; r.setSulphur(null == row.getCell(35) || null == df.formatCellValue(row.getCell(35)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(35))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(35)))); columnNo = 36; r.setSourceInput(null == row.getCell(36) || null == df.formatCellValue(row.getCell(36)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(36))) ? BLANK : df.formatCellValue(row.getCell(36))); columnNo = 37; r.setVisbreakingCapacity(null == row.getCell(37) || null == df.formatCellValue(row.getCell(37)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(37))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(37)))); columnNo = 38; r.setReformerCapacity(null == row.getCell(38) || null == df.formatCellValue(row.getCell(38)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(38))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(38)))); columnNo = 39; r.setHydrotreatingCapacity(null == row.getCell(39) || null == df.formatCellValue(row.getCell(39)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(39))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(39)))); columnNo = 40; r.setAlkylationCapacity(null == row.getCell(40) || null == df.formatCellValue(row.getCell(40)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(40))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(40)))); // System.out.println("Refinery Four"); columnNo = 41; r.setAlkylationCapacityUnit(null == row.getCell(41) || null == df.formatCellValue(row.getCell(41)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(41))) ? BLANK : df.formatCellValue(row.getCell(41))); columnNo = 42; r.setAromaticsCapacity(null == row.getCell(42) || null == df.formatCellValue(row.getCell(42)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(42))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(42)))); columnNo = 43; r.setAromaticsCapacityUnit(null == row.getCell(43) || null == df.formatCellValue(row.getCell(43)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(43))) ? BLANK : df.formatCellValue(row.getCell(43))); columnNo = 44; r.setIsomerizationCapacity(null == row.getCell(44) || null == df.formatCellValue(row.getCell(44)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(44))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(44)))); columnNo = 45; r.setPolymerizationCapacity(null == row.getCell(45) || null == df.formatCellValue(row.getCell(45)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(45))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(45)))); columnNo = 46; r.setPolymerizationCapacityUnit( null == row.getCell(46) || null == df.formatCellValue(row.getCell(46)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(46))) ? BLANK : df.formatCellValue(row.getCell(46))); columnNo = 47; r.setLubesCapacity(null == row.getCell(47) || null == df.formatCellValue(row.getCell(47)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(47))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(47)))); columnNo = 48; r.setLubesCapacityUnit(null == row.getCell(48) || null == df.formatCellValue(row.getCell(48)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(48))) ? BLANK : df.formatCellValue(row.getCell(48))); columnNo = 49; r.setOxygenatesCapacity(null == row.getCell(49) || null == df.formatCellValue(row.getCell(49)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(49))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(49)))); columnNo = 50; r.setOxygenatesCapacityUnit(null == row.getCell(50) || null == df.formatCellValue(row.getCell(50)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(50))) ? BLANK : df.formatCellValue(row.getCell(50))); // System.out.println("Refinery five"); columnNo = 51; r.setCokeCapacity(null == row.getCell(51) || null == df.formatCellValue(row.getCell(51)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(51))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(51)))); columnNo = 52; r.setCokeCapacityUnit(null == row.getCell(52) || null == df.formatCellValue(row.getCell(52)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(52))) ? BLANK : df.formatCellValue(row.getCell(52))); columnNo = 53; r.setSulphurCapacity(null == row.getCell(53) || null == df.formatCellValue(row.getCell(53)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(53))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(53)))); columnNo = 54; r.setSulphurCapacityUnit(null == row.getCell(54) || null == df.formatCellValue(row.getCell(54)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(54))) ? BLANK : df.formatCellValue(row.getCell(54))); columnNo = 55; r.setHydrogenCapacity(null == row.getCell(55) || null == df.formatCellValue(row.getCell(55)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(55))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(55)))); columnNo = 56; r.setHydrogenCapacityUnit(null == row.getCell(56) || null == df.formatCellValue(row.getCell(56)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(56))) ? BLANK : df.formatCellValue(row.getCell(56))); columnNo = 57; r.setAsphaltCapacity(null == row.getCell(57) || null == df.formatCellValue(row.getCell(57)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(57))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(57)))); columnNo = 58; r.setAsphaltCapacityUnit(null == row.getCell(58) || null == df.formatCellValue(row.getCell(58)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(58))) ? BLANK : df.formatCellValue(row.getCell(58))); columnNo = 59; r.setOthersCapacity(null == row.getCell(59) || null == df.formatCellValue(row.getCell(59)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(59))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(59)))); columnNo = 60; r.setRefineryProducts(null == row.getCell(60) || null == df.formatCellValue(row.getCell(60)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(60))) ? BLANK : df.formatCellValue(row.getCell(60))); // System.out.println("Refinery six"); columnNo = 61; r.setSourceProducts(null == row.getCell(61) || null == df.formatCellValue(row.getCell(61)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(61))) ? BLANK : df.formatCellValue(row.getCell(61))); columnNo = 62; r.setRefineryUtilization(null == row.getCell(62) || null == df.formatCellValue(row.getCell(62)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(62))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(62)))); columnNo = 63; r.setGrm(null == row.getCell(63) || null == df.formatCellValue(row.getCell(63)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(63))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(63)))); columnNo = 64; r.setCapex(null == row.getCell(64) || null == df.formatCellValue(row.getCell(64)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(64))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(64)))); columnNo = 65; r.setCapexYear(null == row.getCell(65) || null == df.formatCellValue(row.getCell(65)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(65))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(65))));// This is also an int or text columnNo = 66; r.setCapexDetails(null == row.getCell(66) || null == df.formatCellValue(row.getCell(66)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(66))) ? BLANK : df.formatCellValue(row.getCell(66))); columnNo = 67; r.setSourceCapex(null == row.getCell(67) || null == df.formatCellValue(row.getCell(67)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(67))) ? BLANK : df.formatCellValue(row.getCell(67))); columnNo = 68; r.setConstructionCompanyName(null == row.getCell(68) || null == df.formatCellValue(row.getCell(68)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(68))) ? BLANK : df.formatCellValue(row.getCell(68))); columnNo = 69; r.setConstructionContractDetails( null == row.getCell(69) || null == df.formatCellValue(row.getCell(69)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(69))) ? BLANK : df.formatCellValue(row.getCell(69))); columnNo = 70; r.setRefineryOtherNames(null == row.getCell(70) || null == df.formatCellValue(row.getCell(70)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(70))) ? BLANK : df.formatCellValue(row.getCell(70))); // System.out.println("Refinery seven"); columnNo = 71; r.setOtherSources(null == row.getCell(71) || null == df.formatCellValue(row.getCell(71)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(71))) ? BLANK : df.formatCellValue(row.getCell(71))); columnNo = 72; r.setContact(null == row.getCell(72) || null == df.formatCellValue(row.getCell(72)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(72))) ? BLANK : df.formatCellValue(row.getCell(72))); columnNo = 73; r.setGasolinePetrol(null == row.getCell(73) || null == df.formatCellValue(row.getCell(73)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(73))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(73)))); columnNo = 74; r.setLpg(null == row.getCell(74) || null == df.formatCellValue(row.getCell(74)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(74))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(74)))); columnNo = 75; r.setKerosine(null == row.getCell(75) || null == df.formatCellValue(row.getCell(75)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(75))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(75)))); columnNo = 76; r.setJetFuel(null == row.getCell(76) || null == df.formatCellValue(row.getCell(76)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(76))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(76)))); columnNo = 77; r.setDiesel(null == row.getCell(77) || null == df.formatCellValue(row.getCell(77)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(77))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(77)))); columnNo = 78; r.setPropylene(null == row.getCell(78) || null == df.formatCellValue(row.getCell(78)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(78))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(78)))); columnNo = 79; r.setLightNaphtha(null == row.getCell(79) || null == df.formatCellValue(row.getCell(79)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(79))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(79)))); columnNo = 80; r.setHeavyNaphtha(null == row.getCell(80) || null == df.formatCellValue(row.getCell(80)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(80))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(80)))); // System.out.println("Refinery eight"); columnNo = 81; r.setKerojet(null == row.getCell(81) || null == df.formatCellValue(row.getCell(81)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(81))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(81)))); columnNo = 82; r.setBenzeneSaturationUnit(null == row.getCell(82) || null == df.formatCellValue(row.getCell(82)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(82))) ? BLANK : df.formatCellValue(row.getCell(82))); columnNo = 83; r.setProductsSources(null == row.getCell(83) || null == df.formatCellValue(row.getCell(83)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(83))) ? BLANK : df.formatCellValue(row.getCell(83))); columnNo = 84; r.setLicensors(null == row.getCell(84) || null == df.formatCellValue(row.getCell(84)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(84))) ? BLANK : df.formatCellValue(row.getCell(84))); columnNo = 85; r.setMaintananceStartDate(null == row.getCell(85) || null == df.formatCellValue(row.getCell(85)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(85))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(85)))); columnNo = 86; r.setMaintananceEndDate(null == row.getCell(86) || null == df.formatCellValue(row.getCell(86)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(86))) ? 0 : Integer.parseInt(df.formatCellValue(row.getCell(86)))); columnNo = 87; r.setMaintananceNote(null == row.getCell(87) || null == df.formatCellValue(row.getCell(87)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(87))) ? BLANK : df.formatCellValue(row.getCell(87))); columnNo = 88; r.setCduProducingCapacity(null == row.getCell(88) || null == df.formatCellValue(row.getCell(88)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(88))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(88)))); refineryList.add(r); } catch (Exception e) { logger.error("Exception in ReadExcelFile - populateRefineryData():" + e); totalRecords++; String columnName = getHeaderValues(sheet, columnNo); recordsList.append((row.getRowNum() + 1) + COMMA); if (totalRecords == (12 * linebreak)) { recordsList.append(SPACE);// Adding space for rendering in front end; linebreak++; } columnNamesSet.add(columnName); } } if (totalRecords > 0) { createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet); logger.info("Refinery totalRecords:" + totalRecords + "recordsList:" + recordsList); } logger.info("Total objects set for refinery:" + refineryList.size()); return refineryList; }
From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java
private List<Exploration> populateExplorationData(Sheet sheet, Tab tab) { logger.info("Class - ReadExcelFile - populateExplorationData()"); int columnNo = 0; List<Exploration> explorationDataList = new ArrayList<Exploration>(); int rowCount = sheet.getLastRowNum(); int totalRecords = 0; StringBuffer recordsList = new StringBuffer(BLANK); // StringBuffer descriptionList=new StringBuffer(BLANK); Set<String> columnNamesSet = new HashSet<String>(); int linebreak = 1; for (int i = 1; i <= rowCount; i++) { Row row = sheet.getRow(i); // System.out.println(row.getRowNum()); Exploration e = new Exploration(); try {/*from ww w. j ava 2 s.c o m*/ columnNo = 0; e.setBlockNo(null == row.getCell(0) || null == df.formatCellValue(row.getCell(0)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(0))) ? BLANK : df.formatCellValue(row.getCell(0))); columnNo = 1; e.setRegion(null == row.getCell(1) || null == df.formatCellValue(row.getCell(1)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(1))) ? BLANK : df.formatCellValue(row.getCell(1))); columnNo = 2; e.setCountry(null == row.getCell(2) || null == df.formatCellValue(row.getCell(2)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(2))) ? BLANK : df.formatCellValue(row.getCell(2))); columnNo = 3; e.setOnShoreOrOffShore(null == row.getCell(3) || null == df.formatCellValue(row.getCell(3)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(3))) ? BLANK : df.formatCellValue(row.getCell(3))); columnNo = 4; e.setBasin(null == row.getCell(4) || null == df.formatCellValue(row.getCell(4)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(4))) ? BLANK : df.formatCellValue(row.getCell(4))); columnNo = 5; e.setStatus(null == row.getCell(5) || null == df.formatCellValue(row.getCell(5)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(5))) ? BLANK : df.formatCellValue(row.getCell(5))); // System.out.println("Exploration first"); columnNo = 6; e.setStartDate(null == row.getCell(6) || null == df.formatCellValue(row.getCell(6)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(6))) ? null : new Date(df.formatCellValue(row.getCell(6)))); columnNo = 7; e.setOperator(null == row.getCell(7) || null == df.formatCellValue(row.getCell(7)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(7))) ? BLANK : df.formatCellValue(row.getCell(7))); columnNo = 8; e.setEquityPartners(null == row.getCell(8) || null == df.formatCellValue(row.getCell(8)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(8))) ? BLANK : df.formatCellValue(row.getCell(8))); columnNo = 9; e.setEquityStakes(null == row.getCell(9) || null == df.formatCellValue(row.getCell(9)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(9))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(9)))); columnNo = 10; e.setSourceEquity(null == row.getCell(10) || null == df.formatCellValue(row.getCell(10)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(10))) ? BLANK : df.formatCellValue(row.getCell(10))); columnNo = 11; e.setArea(null == row.getCell(11) || null == df.formatCellValue(row.getCell(11)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(11))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(11)))); columnNo = 12; e.setLicenseEnddate(null == row.getCell(12) || null == df.formatCellValue(row.getCell(12)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(12))) ? null : new Date(df.formatCellValue(row.getCell(12)))); // System.out.println("Exploration second"); columnNo = 13; e.setWellsDrilled(null == row.getCell(13) || null == df.formatCellValue(row.getCell(13)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(13))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(13)))); columnNo = 14; e.setTwoDSeismicCompleted(null == row.getCell(14) || null == df.formatCellValue(row.getCell(14)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(14))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(14)))); columnNo = 15; e.setThreeDSeismic(null == row.getCell(15) || null == df.formatCellValue(row.getCell(15)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(15))) ? 0 : Double.valueOf(df.formatCellValue(row.getCell(15)))); columnNo = 16; e.setMoreInfo(null == row.getCell(16) || null == df.formatCellValue(row.getCell(16)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(16))) ? BLANK : df.formatCellValue(row.getCell(16))); columnNo = 17; e.setNotes(null == row.getCell(17) || null == df.formatCellValue(row.getCell(17)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(17))) ? BLANK : df.formatCellValue(row.getCell(17))); columnNo = 18; e.setSource(null == row.getCell(18) || null == df.formatCellValue(row.getCell(18)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(18))) ? BLANK : df.formatCellValue(row.getCell(18))); // System.out.println(row.getCell(18)); // System.out.println(row.getCell(18).getStringCellValue()); columnNo = 19; e.setLicenseNo(null == row.getCell(19) || null == df.formatCellValue(row.getCell(19)) || (BLANK).equalsIgnoreCase(df.formatCellValue(row.getCell(19))) ? BLANK : df.formatCellValue(row.getCell(19))); // columnNo=19;e.setRecordId(null==row.getCell(19)?0:Double.valueOf(row.getCell(19).getNumericCellValue()).intValue()); explorationDataList.add(e); } catch (Exception ex) { logger.error("Exception in ReadExcelFile - populateExplorationData():" + ex); totalRecords++; String columnName = getHeaderValues(sheet, columnNo); recordsList.append((row.getRowNum() + 1) + COMMA); if (totalRecords == (12 * linebreak)) { recordsList.append(SPACE);// Adding space for rendering in front end; linebreak++; } columnNamesSet.add(columnName); } } if (totalRecords > 0) { createTabData(tab, sheet.getSheetName().toUpperCase(), totalRecords, recordsList, columnNamesSet); logger.info("Exploration totalRecords:" + totalRecords + "recordsList:" + recordsList); } logger.info("Total objects set for Exploration:" + explorationDataList.size()); return explorationDataList; }
From source file:com.lnganalysis.fileupload.util.ReadExcelFile.java
private String getHeaderValues(Sheet sheet, int columnNo) { logger.info("Class - ReadExcelFile - getHeaderValues()"); Row headerRow = sheet.getRow(0); int cellCount = headerRow.getLastCellNum(); String[] headers = new String[cellCount]; for (int i = 0; i < cellCount; i++) headers[i] = headerRow.getCell(i).toString(); String columName = headers[columnNo]; return columName; }
From source file:com.lulu.ofarm.test.ImportControllerTest.java
@Test public void getBeanFromExcel() throws FileNotFoundException, IOException { //1.Excel //from www . ja v a 2 s . c o m // POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls")); //2.Excel Workbook wb = new HSSFWorkbook(new FileInputStream("F:/reposibility_new.xls")); //3.Excel Sheet sheet = wb.getSheetAt(0); // int trLength = sheet.getLastRowNum(); //4.Excel Row row = sheet.getRow(0); // int tdLength = row.getLastCellNum(); //5.Excel? Cell cell = row.getCell((short) 1); //6.?? //CellStyle cellStyle = cell.getCellStyle(); for (int i = 2; i < trLength; i++) { //Excel Row row1 = sheet.getRow(i); for (int j = 0; j < tdLength; j++) { Cell cell1 = row1.getCell(j); /** * ?ExcelCannot get a text value from a numeric cell * ?String? */ if (cell1 != null) { cell1.setCellType(Cell.CELL_TYPE_STRING); } System.out.println(cell1.getStringCellValue()); } //Excel? //? // OutfallPolluateResourceBean resource = new OutfallPolluateResourceBean(); // resource.setRivername(row1.getCell(1).getStringCellValue()); // resource.setArea(row1.getCell(2).getStringCellValue()); // resource.setLeftorrightbank(row1.getCell(3).getStringCellValue()); // resource.setOutfalltype(row1.getCell(4).getStringCellValue()); // resource.setOutfallcode(row1.getCell(5).getStringCellValue()); // resource.setSecondaryunit(row1.getCell(6).getStringCellValue()); // resource.setStreetname(row1.getCell(7).getStringCellValue()); // resource.setStreetmanager(row1.getCell(8).getStringCellValue()); // resource.setVillage(row1.getCell(9).getStringCellValue()); // resource.setVillagemanager(row1.getCell(10).getStringCellValue()); // resource.setPosition(row1.getCell(11).getStringCellValue()); // resource.setCoordinate(row1.getCell(12).getStringCellValue()); // resource.setOutfallsize(row1.getCell(13).getStringCellValue()); // resource.setOutfallshape(row1.getCell(14).getStringCellValue()); // resource.setPolldescription(row1.getCell(15).getStringCellValue()); // // resource.setRectificationmeasures(row1.getCell(16).getStringCellValue()); // resource.setDrainageTo(row1.getCell(17).getStringCellValue()); // resource.setTherectificationresponsibilityunit(row1.getCell(18).getStringCellValue()); // resource.setTimeofcompletion(row1.getCell(19).getStringCellValue()); // resource.setRemark(row1.getCell(20).getStringCellValue()); // System.err.println(resource); // service.save(resource); // PollutantSourceBean source = new PollutantSourceBean(); // source.setRivername(row1.getCell(1).getStringCellValue()); // source.setArea(row1.getCell(2).getStringCellValue()); // source.setPollsourcename(row1.getCell(3).getStringCellValue()); // source.setStreetname(row1.getCell(4).getStringCellValue()); // source.setStreetmanager(row1.getCell(5).getStringCellValue()); // source.setVillage(row1.getCell(6).getStringCellValue()); // source.setVillagemanager(row1.getCell(7).getStringCellValue()); // source.setPollsourcetype(row1.getCell(8).getStringCellValue()); // source.setOutfalltype(row1.getCell(9).getStringCellValue()); // source.setOutfallcode(row1.getCell(10).getStringCellValue()); // source.setPosition(row1.getCell(11).getStringCellValue()); // source.setCoordinate(row1.getCell(12).getStringCellValue()); // source.setPolldescription(row1.getCell(13).getStringCellValue()); // source.setDrainageto(row1.getCell(14).getStringCellValue()); // source.setPolldischarginglicense(row1.getCell(15).getStringCellValue()); // source.setDrainaglicense(row1.getCell(16).getStringCellValue()); // source.setHasmeasures(row1.getCell(17).getStringCellValue()); // source.setRectificationmeasures(row1.getCell(18).getStringCellValue()); // source.setTherectificationresponsibilityunit(row1.getCell(19).getStringCellValue()); // source.setTimeofcompletion(row1.getCell(20).getStringCellValue()); // source.setRemark(row1.getCell(21).getStringCellValue()); // System.out.println(source); // service.save(source); } }
From source file:com.lw.common.utils.ExcelUtil.java
private <T> void parseRowAndFillData(Row row, List<T> modelList, List<Method> methods, Class<T> modelClass) throws Exception { // ?// w ww.j ava2 s .c o m if (row.getCell(0) == null) { return; } List<String> rst = parseDataRow(row, methods.size()); T obj = (T) modelClass.newInstance(); // ??Excel?????? if (methods.size() != rst.size()) { throw new Exception("Excel?"); } else { for (int i = 0; i < methods.size(); i++) { Method m = methods.get(i); String str = rst.get(i); if (Utils.judgeStr(str)) { // null""?? Class<?>[] paramTypes = m.getParameterTypes(); String typeName = paramTypes[0].getName(); // ??rst? switch (typeName) { case "java.math.BigDecimal": m.invoke(obj, new BigDecimal(str)); break; case "java.lang.Integer": m.invoke(obj, new Integer(str)); break; case "java.lang.Byte": m.invoke(obj, new Byte(str)); break; default: m.invoke(obj, str); break; } } } modelList.add(obj); } }
From source file:com.maxl.java.aips2sqlite.DispoParse.java
License:Open Source License
public void processXlsx() { /* Sheet format//from ww w .j a v a 2s .c o m * 0: Pharmacode * 1: Artikelname * 2: Strichcode * 3: Ausstand bis * 4: Fehlt auf unbestimmte Zeit * 5: Packungsinhalt * 6: Therapeutischer Code * 7: ATC-Key * 8: Lagerbestand * 9: Lieferant gem. Rose * 10: Galen. Form * 11: Dosierung * 12: Rose Basispreis (rbp) */ List<Article> list_of_articles = new ArrayList<Article>(); Iterator<Row> rowIterator = m_dispo_articles_sheet.iterator(); int num_rows = 0; while (rowIterator.hasNext() && num_rows < 100) { Row row = rowIterator.next(); if (num_rows > 0) { Article article = new Article(); if (row.getCell(0) != null) article.pharma_code = intToString(ExcelOps.getCellValue(row.getCell(0))); if (row.getCell(1) != null) article.pack_title = ExcelOps.getCellValue(row.getCell(1)); if (row.getCell(2) != null) article.ean_code = intToString(ExcelOps.getCellValue(row.getCell(2))); if (row.getCell(3) != null) article.availability = ExcelOps.getCellValue(row.getCell(3)); if (row.getCell(5) != null) article.pack_size = doubleToInt(ExcelOps.getCellValue(row.getCell(5))); if (row.getCell(6) != null) article.therapy_code = ExcelOps.getCellValue(row.getCell(6)); if (row.getCell(7) != null) article.atc_code = ExcelOps.getCellValue(row.getCell(7)).toUpperCase(); if (row.getCell(8) != null) article.stock = doubleToInt(ExcelOps.getCellValue(row.getCell(8))); if (row.getCell(9) != null) article.rose_supplier = ExcelOps.getCellValue(row.getCell(9)); if (row.getCell(10) != null) article.galen_form = ExcelOps.getCellValue(row.getCell(10)); if (row.getCell(11) != null) article.pack_unit = ExcelOps.getCellValue(row.getCell(11)); if (row.getCell(12) != null) article.rose_base_price = ExcelOps.getCellValue(row.getCell(12)); list_of_articles.add(article); addArticleDB(article); System.out.println(article.rose_base_price); } num_rows++; } complete(); System.out.println("Number of articles = " + list_of_articles.size()); }
From source file:com.maxl.java.aips2sqlite.RealExpertInfo.java
License:Open Source License
/** * Extracts package info from Swissmedic package Excel file *///from w ww . ja v a 2 s . co m private void extractPackageInfo() { try { long startTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.print("- Processing packages xlsx... "); // Load Swissmedic xls file FileInputStream packages_file = new FileInputStream(Constants.FILE_PACKAGES_XLSX); // Get workbook instance for XLSX file (XSSF = Horrible SpreadSheet Format) XSSFWorkbook packages_workbook = new XSSFWorkbook(packages_file); // Get first sheet from workbook XSSFSheet packages_sheet = packages_workbook.getSheetAt(0); /* if (SHOW_LOGS) System.out.print("- Processing packages xls... "); // Load Swissmedic xls file FileInputStream packages_file = new FileInputStream(FILE_PACKAGES_XLS); // Get workbook instance for XLS file (HSSF = Horrible SpreadSheet Format) HSSFWorkbook packages_workbook = new HSSFWorkbook(packages_file); // Get first sheet from workbook HSSFSheet packages_sheet = packages_workbook.getSheetAt(0); */ // Iterate through all rows of first sheet Iterator<Row> rowIterator = packages_sheet.iterator(); int num_rows = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (num_rows > 5) { String swissmedic_no5 = ""; // SwissmedicNo5 registration number (5 digits) String sequence_name = ""; String package_id = ""; String swissmedic_no8 = ""; // SwissmedicNo8 = SwissmedicNo5 + Package id (8 digits) String heilmittel_code = ""; String package_size = ""; String package_unit = ""; String swissmedic_cat = ""; String application_area = ""; String public_price = ""; String exfactory_price = ""; String therapeutic_index = ""; String withdrawn_str = ""; String speciality_str = ""; String plimitation_str = ""; String add_info_str = ""; // Contains additional information separated by ; String ean_code_str = ""; String pharma_code_str = ""; // 0: Zulassungsnummer, 1: Dosisstrkenummer, 2: Prparatebezeichnung, 3: Zulassunginhaberin, 4: Heilmittelcode, 5: IT-Nummer, 6: ATC-Code // 7: Erstzulassung Prparat, 8: Zulassungsdatum Sequenz, 9: Gltigkeitsdatum, 10: Packungscode, 11: Packungsgrsse // 12: Einheit, 13: Abgabekategorie Packung, 14: Abgabekategorie Dosisstrke, 15: Abgabekategorie Prparat, // 16: Wirkstoff, 17: Zusammensetzung, 18: Anwendungsgebiet Prparat, 19: Anwendungsgebiet Dosisstrke, 20: Gentechnisch hergestellte Wirkstoffe // 21: Kategorie bei Insulinen, 22: Betubungsmittelhaltigen Prparaten // @cybermax: 15.10.2013 - work around for Excel cells of type "Special" (cell0 and cell10) if (row.getCell(0) != null) swissmedic_no5 = String.format("%05d", (int) (row.getCell(0).getNumericCellValue())); // Swissmedic registration number (5 digits) if (row.getCell(2) != null) sequence_name = ExcelOps.getCellValue(row.getCell(2)); // Sequence name if (row.getCell(4) != null) heilmittel_code = ExcelOps.getCellValue(row.getCell(4)); // Heilmittelcode if (row.getCell(11) != null) package_size = ExcelOps.getCellValue(row.getCell(11)); // Packungsgrsse if (row.getCell(12) != null) package_unit = ExcelOps.getCellValue(row.getCell(12)); // Einheit if (row.getCell(13) != null) swissmedic_cat = ExcelOps.getCellValue(row.getCell(13)); // Abgabekategorie Packung if (row.getCell(18) != null) application_area = ExcelOps.getCellValue(row.getCell(18)); // Anwendungsgebiet Prparat if (row.getCell(10) != null) { package_id = String.format("%03d", (int) (row.getCell(10).getNumericCellValue())); // Verpackungs ID swissmedic_no8 = swissmedic_no5 + package_id; // Fill in row ArrayList<String> pack = new ArrayList<String>(); pack.add(swissmedic_no5); // 0 pack.add(sequence_name); // 1 pack.add(heilmittel_code); // 2 pack.add(package_size); // 3 pack.add(package_unit); // 4 pack.add(swissmedic_cat); // 5 if (!application_area.isEmpty()) pack.add(application_area + " (Swissmedic);"); // 6 = swissmedic + bag else pack.add(""); pack.add(public_price); // 7 pack.add(exfactory_price); // 8 pack.add(therapeutic_index);// 9 // By default the meds are "ausser Handel" if (CmlOptions.DB_LANGUAGE.equals("de")) withdrawn_str = "a.H."; // ausser Handel else if (CmlOptions.DB_LANGUAGE.equals("fr")) withdrawn_str = "p.c."; // pack.add(withdrawn_str); // 10 pack.add(speciality_str); // 11 pack.add(plimitation_str); // 12 pack.add(add_info_str); // 13 // 22.03.2014: EAN-13 barcodes - initialization - check digit is missing! ean_code_str = "7680" + swissmedic_no8; pack.add(ean_code_str); // 14 pack.add(pharma_code_str); // 15 m_package_info.put(swissmedic_no8, pack); } } num_rows++; } long stopTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) { System.out.println( (m_package_info.size() + 1) + " packages in " + (stopTime - startTime) / 1000.0f + " sec"); } startTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.print("- Processing atc classes xls... "); if (CmlOptions.DB_LANGUAGE.equals("de")) { /* // Load ATC classes xls file FileInputStream atc_classes_file = new FileInputStream(Constants.FILE_ATC_CLASSES_XLS); // Get workbook instance for XLS file (HSSF = Horrible SpreadSheet Format) HSSFWorkbook atc_classes_workbook = new HSSFWorkbook(atc_classes_file); // Get first sheet from workbook // HSSFSheet atc_classes_sheet = atc_classes_workbook.getSheetAt(1); // --> 2013 file HSSFSheet atc_classes_sheet = atc_classes_workbook.getSheetAt(0); // --> 2014 file // Iterate through all rows of first sheet rowIterator = atc_classes_sheet.iterator(); num_rows = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (num_rows>2) { String atc_code = ""; String atc_class = ""; if (row.getCell(0)!=null) { atc_code = row.getCell(0).getStringCellValue().replaceAll("\\s", ""); } if (row.getCell(2)!=null) { atc_class = row.getCell(2).getStringCellValue(); } // Build a full map atc code to atc class if (atc_code.length()>0) { m_atc_map.put(atc_code, atc_class); } } num_rows++; } */ CSVReader reader = new CSVReader( new InputStreamReader(new FileInputStream(Constants.FILE_EPHA_ATC_CODES_CSV), "UTF-8")); List<String[]> myEntries = reader.readAll(); num_rows = myEntries.size(); for (String[] s : myEntries) { if (s.length > 2) { String atc_code = s[0]; String atc_class = s[1]; m_atc_map.put(atc_code, atc_class); } } reader.close(); } else if (CmlOptions.DB_LANGUAGE.equals("fr")) { // Load ATC classes xls file FileInputStream atc_classes_file = new FileInputStream(Constants.FILE_WHO_ATC_CLASSES_XLS); // Get workbook instance for XLS file (HSSF = Horrible SpreadSheet Format) HSSFWorkbook atc_classes_workbook = new HSSFWorkbook(atc_classes_file); // Get first sheet from workbook HSSFSheet atc_classes_sheet = atc_classes_workbook.getSheetAt(0); // --> 2014 file // Iterate through all rows of first sheet rowIterator = atc_classes_sheet.iterator(); num_rows = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (num_rows > 0) { String atc_code = ""; String atc_class = ""; if (row.getCell(1) != null) { atc_code = row.getCell(1).getStringCellValue(); if (atc_code.length() > 0) { // Extract L5 and below if (atc_code.length() < 6 && row.getCell(2) != null) { atc_class = row.getCell(2).getStringCellValue(); // Build a full map atc code to atc class m_atc_map.put(atc_code, atc_class); // Extract L7 } else if (atc_code.length() == 7 && row.getCell(4) != null) { atc_class = row.getCell(4).getStringCellValue(); m_atc_map.put(atc_code, atc_class); } } } } num_rows++; } // Load multilingual ATC classes txt file, replace English with French String atc_classes_multi = FileOps.readFromFile(Constants.FILE_ATC_MULTI_LINGUAL_TXT); // Loop through all lines Scanner scanner = new Scanner(atc_classes_multi); while (scanner.hasNextLine()) { String line = scanner.nextLine(); List<String> atc_class = Arrays.asList(line.split(": ")); String atc_code = atc_class.get(0); String[] atc_classes_str = atc_class.get(1).split(";"); String atc_class_french = atc_classes_str[1].trim(); // Replaces atc code... m_atc_map.put(atc_code, atc_class_french); } scanner.close(); } stopTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.println( (m_atc_map.size() + 1) + " classes in " + (stopTime - startTime) / 1000.0f + " sec"); // Load Refdata xml file File refdata_xml_file = new File(Constants.FILE_REFDATA_PHARMA_XML); FileInputStream refdata_fis = new FileInputStream(refdata_xml_file); startTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.println("- Unmarshalling Refdatabase for " + CmlOptions.DB_LANGUAGE + "... "); JAXBContext context = JAXBContext.newInstance(Refdata.class); Unmarshaller um = context.createUnmarshaller(); Refdata refdataPharma = (Refdata) um.unmarshal(refdata_fis); List<Refdata.ITEM> pharma_list = refdataPharma.getItem(); String smno8; for (Refdata.ITEM pharma : pharma_list) { String ean_code = pharma.getGtin(); String pharma_code = pharma.getPhar(); if (ean_code.length() == 13) { smno8 = ean_code.substring(4, 12); // Extract pharma corresponding to swissmedicno8 (source: swissmedic package file) ArrayList<String> pi_row = m_package_info.get(smno8); // Replace sequence_name if (pi_row != null) { // Prparatname + galenische Form if (CmlOptions.DB_LANGUAGE.equals("de")) pi_row.set(1, pharma.getNameDE()); else if (CmlOptions.DB_LANGUAGE.equals("fr")) pi_row.set(1, pharma.getNameFR()); // If med is in refdata file, then it is "in Handel!!" ;) pi_row.set(10, ""); // By default this is set to a.H. or p.C. // 22.03.2014: EAN-13 barcodes - replace with refdata if package exists pi_row.set(14, ean_code); // Pharma code pi_row.set(15, pharma_code); } else { if (CmlOptions.SHOW_ERRORS) { if (pharma.getATYPE().equals("PHARMA")) System.err.println( ">> Does not exist in BAG xls: " + smno8 + " (" + pharma.getNameDE() + ")"); } } } else if (ean_code.length() < 13) { if (CmlOptions.SHOW_ERRORS) System.err.println(">> EAN code too short: " + ean_code + ": " + pharma.getNameDE()); } else if (ean_code.length() > 13) { if (CmlOptions.SHOW_ERRORS) System.err.println(">> EAN code too long: " + ean_code + ": " + pharma.getNameDE()); } } stopTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.println(pharma_list.size() + " medis in " + (stopTime - startTime) / 1000.0f + " sec"); // Load BAG xml file File bag_xml_file = new File(Constants.FILE_PREPARATIONS_XML); FileInputStream fis_bag = new FileInputStream(bag_xml_file); startTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.println("- Processing preparations xml... "); context = JAXBContext.newInstance(Preparations.class); um = context.createUnmarshaller(); Preparations prepInfos = (Preparations) um.unmarshal(fis_bag); List<Preparations.Preparation> prep_list = prepInfos.getPreparations(); int num_preparations = 0; for (Preparations.Preparation prep : prep_list) { String swissmedicno5_str = prep.getSwissmedicNo5(); if (swissmedicno5_str != null) { String orggencode_str = ""; // "O", "G" or empty -> "" String flagSB20_str = ""; // "Y" -> 20% or "N" -> 10% if (prep.getOrgGenCode() != null) orggencode_str = prep.getOrgGenCode(); if (prep.getFlagSB20() != null) { flagSB20_str = prep.getFlagSB20(); if (flagSB20_str.equals("Y")) { if (CmlOptions.DB_LANGUAGE.equals("de")) flagSB20_str = "SB 20%"; else if (CmlOptions.DB_LANGUAGE.equals("fr")) flagSB20_str = "QP 20%"; } else if (flagSB20_str.equals("N")) { if (CmlOptions.DB_LANGUAGE.equals("de")) flagSB20_str = "SB 10%"; else if (CmlOptions.DB_LANGUAGE.equals("fr")) flagSB20_str = "QP 10%"; } else flagSB20_str = ""; } m_add_info_map.put(swissmedicno5_str, orggencode_str + ";" + flagSB20_str); } List<Preparation.Packs> packs_list = prep.getPacks(); for (Preparation.Packs packs : packs_list) { // Extract codes for therapeutic index / classification String bag_application = ""; String therapeutic_code = ""; List<Preparations.Preparation.ItCodes> itcode_list = prep.getItCodes(); for (Preparations.Preparation.ItCodes itc : itcode_list) { List<Preparations.Preparation.ItCodes.ItCode> code_list = itc.getItCode(); int index = 0; for (Preparations.Preparation.ItCodes.ItCode code : code_list) { if (index == 0) { if (CmlOptions.DB_LANGUAGE.equals("de")) therapeutic_code = code.getDescriptionDe(); else if (CmlOptions.DB_LANGUAGE.equals("fr")) therapeutic_code = code.getDescriptionFr(); } else { if (CmlOptions.DB_LANGUAGE.equals("de")) bag_application = code.getDescriptionDe(); else if (CmlOptions.DB_LANGUAGE.equals("fr")) bag_application = code.getDescriptionFr(); } index++; } } // Generate new package info List<Preparation.Packs.Pack> pack_list = packs.getPack(); for (Preparation.Packs.Pack pack : pack_list) { // Get SwissmedicNo8 and used it as a key to extract all the relevant package info String swissMedicNo8 = pack.getSwissmedicNo8(); ArrayList<String> pi_row = null; if (swissMedicNo8 != null) pi_row = m_package_info.get(swissMedicNo8); // Preparation also in BAG xml file (we have a price) if (pi_row != null) { // Update Swissmedic catory if necessary ("N->A", Y->"A+") if (pack.getFlagNarcosis().equals("Y")) pi_row.set(5, pi_row.get(5) + "+"); // Extract point limitations List<Preparations.Preparation.Packs.Pack.PointLimitations> point_limits = pack .getPointLimitations(); for (Preparations.Preparation.Packs.Pack.PointLimitations limits : point_limits) { List<Preparations.Preparation.Packs.Pack.PointLimitations.PointLimitation> plimits_list = limits .getPointLimitation(); if (plimits_list.size() > 0) if (plimits_list.get(0) != null) pi_row.set(12, ", LIM" + plimits_list.get(0).getPoints() + ""); } // Extract exfactory and public prices List<Preparations.Preparation.Packs.Pack.Prices> price_list = pack.getPrices(); for (Preparations.Preparation.Packs.Pack.Prices price : price_list) { List<Preparations.Preparation.Packs.Pack.Prices.PublicPrice> public_price = price .getPublicPrice(); List<Preparations.Preparation.Packs.Pack.Prices.ExFactoryPrice> exfactory_price = price .getExFactoryPrice(); if (exfactory_price.size() > 0) { try { float f = Float.valueOf(exfactory_price.get(0).getPrice()); String ep = String.format("%.2f", f); pi_row.set(8, "CHF " + ep); } catch (NumberFormatException e) { if (CmlOptions.SHOW_ERRORS) System.err.println("Number format exception (exfactory price): " + swissMedicNo8 + " (" + public_price.size() + ")"); } } if (public_price.size() > 0) { try { float f = Float.valueOf(public_price.get(0).getPrice()); String pp = String.format("%.2f", f); pi_row.set(7, "CHF " + pp); if (CmlOptions.DB_LANGUAGE.equals("de")) pi_row.set(11, ", SL"); else if (CmlOptions.DB_LANGUAGE.equals("fr")) pi_row.set(11, ", LS"); } catch (NullPointerException e) { if (CmlOptions.SHOW_ERRORS) System.err.println("Null pointer exception (public price): " + swissMedicNo8 + " (" + public_price.size() + ")"); } catch (NumberFormatException e) { if (CmlOptions.SHOW_ERRORS) System.err.println("Number format exception (public price): " + swissMedicNo8 + " (" + public_price.size() + ")"); } } // Add application area and therapeutic code if (!bag_application.isEmpty()) pi_row.set(6, pi_row.get(6) + bag_application + " (BAG)"); pi_row.set(9, therapeutic_code); } } } } num_preparations++; } stopTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.println( num_preparations + " preparations in " + (stopTime - startTime) / 1000.0f + " sec"); // Loop through all SwissmedicNo8 numbers /* for (Map.Entry<String, ArrayList<String>> entry : package_info.entrySet()) { String swissmedicno8 = entry.getKey(); ArrayList<String> pi_row = entry.getValue(); } */ } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (JAXBException e) { e.printStackTrace(); } }
From source file:com.maxl.java.aips2sqlite.RealExpertInfo.java
License:Open Source License
/** * Extracts Swiss DRG info from Swiss DRG Excel file *///w w w .ja v a 2 s . c o m private void extractSwissDRGInfo() { try { long startTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) System.out.print("- Processing Swiss DRG xlsx... "); // Load Swiss DRG file FileInputStream swiss_drg_file = null; if (CmlOptions.DB_LANGUAGE.equals("de")) swiss_drg_file = new FileInputStream(Constants.FILE_SWISS_DRG_DE_XLSX); else if (CmlOptions.DB_LANGUAGE.equals("fr")) swiss_drg_file = new FileInputStream(Constants.FILE_SWISS_DRG_FR_XLSX); else swiss_drg_file = new FileInputStream(Constants.FILE_SWISS_DRG_DE_XLSX); // Get workbook instance for XLSX file (XSSF = Horrible SpreadSheet Format) XSSFWorkbook swiss_drg_workbook = new XSSFWorkbook(swiss_drg_file); // Get "Anlage 2 und Anlage 3" String zusatz_entgelt = ""; String atc_code = ""; String dosage_class = ""; String price = ""; // TODO: Add code for Anlage 3 (a==5) for (int a = 4; a <= 4; a++) { int num_rows = 0; String current_footnote = ""; XSSFSheet swiss_drg_sheet = swiss_drg_workbook.getSheetAt(a); // Iterate through all rows of first sheet Iterator<Row> rowIterator = swiss_drg_sheet.iterator(); while (rowIterator.hasNext()) { if (num_rows > 7) { Row row = rowIterator.next(); if (row.getCell(0) != null) // Zusatzentgelt zusatz_entgelt = ExcelOps.getCellValue(row.getCell(0)); if (row.getCell(2) != null) // ATC Code atc_code = ExcelOps.getCellValue(row.getCell(2)).replaceAll("[^A-Za-z0-9.]", ""); if (row.getCell(3) != null) // Dosage class dosage_class = ExcelOps.getCellValue(row.getCell(3)); if (row.getCell(4) != null) // Price price = ExcelOps.getCellValue(row.getCell(4)); if (!zusatz_entgelt.isEmpty() && !dosage_class.isEmpty() && !price.isEmpty() && !atc_code.contains(".") && !dosage_class.equals("BLANK") && !price.equals("BLANK")) { String swiss_drg_str = ""; if (a == 4) { if (CmlOptions.DB_LANGUAGE.equals("de")) swiss_drg_str = zusatz_entgelt + ", Dosierung " + dosage_class + ", CHF " + price; else if (CmlOptions.DB_LANGUAGE.equals("fr")) swiss_drg_str = zusatz_entgelt + ", dosage " + dosage_class + ", CHF " + price; } else if (a == 5) swiss_drg_str = zusatz_entgelt + ", " + price; // Get list of dosages for a particular atc code ArrayList<String> dosages = m_swiss_drg_info.get(atc_code); // If there is no list, create a new one if (dosages == null) dosages = new ArrayList<String>(); dosages.add(swiss_drg_str); // Update global swiss drg list m_swiss_drg_info.put(atc_code, dosages); // Update footnote map m_swiss_drg_footnote.put(atc_code, current_footnote); } else if (!zusatz_entgelt.isEmpty() && dosage_class.equals("BLANK") && price.equals("BLANK")) { if (zusatz_entgelt.contains(" ")) { String[] sub_script = zusatz_entgelt.split(" "); if (sub_script.length > 1 && sub_script[0].contains("ZE")) { // Update atc code to footnote map current_footnote = sub_script[1]; } } } } num_rows++; } } long stopTime = System.currentTimeMillis(); if (CmlOptions.SHOW_LOGS) { System.out.println( "processed all Swiss DRG packages in " + (stopTime - startTime) / 1000.0f + " sec"); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }