List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:com.maxl.java.aips2sqlite.RealExpertInfo.java
License:Open Source License
/** * Extracts package info from Swissmedic package Excel file *//*from w w w . ja v a 2 s. c o 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 *//*from w w w . ja v a2 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(); } }
From source file:com.maxl.java.aips2sqlite.RealPatientInfo.java
License:Open Source License
/** * Extracts package info from Swissmedic package Excel file *//*from w ww.jav a 2s . c om*/ 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 = ""; // 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 = row.getCell(2).getStringCellValue(); // Sequence name if (row.getCell(4) != null) heilmittel_code = row.getCell(4).getStringCellValue(); // Heilmittelcode if (row.getCell(11) != null) package_size = row.getCell(11).getStringCellValue(); // Packungsgrsse if (row.getCell(12) != null) package_unit = row.getCell(12).getStringCellValue(); // Einheit if (row.getCell(13) != null) swissmedic_cat = row.getCell(13).getStringCellValue(); // Abgabekategorie if (row.getCell(18) != null) application_area = row.getCell(18).getStringCellValue(); // Anwendungsgebiet 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."; // else if (CmlOptions.DB_LANGUAGE.equals("it")) withdrawn_str = "f.c."; // fuori commercio 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 ean_code_str = "7680" + swissmedic_no8; pack.add(ean_code_str); // 14 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); // 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 multilinguagl ATC classes txt file 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(); m_atc_map.put(atc_code, atc_class_french); } scanner.close(); } else if (CmlOptions.DB_LANGUAGE.equals("it")) { // Load multilinguagl ATC classes txt file 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(); 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.print("- 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(); 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, ""); // 22.03.2014: EAN-13 barcodes - replace with refdata if package exists pi_row.set(14, ean_code); } else { if (CmlOptions.SHOW_ERRORS) { 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.print("- 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 (CmlOptions.DB_LANGUAGE.equals("it")) 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 if (CmlOptions.DB_LANGUAGE.equals("it")) 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("it")) therapeutic_code = code.getDescriptionIt(); } else { if (CmlOptions.DB_LANGUAGE.equals("de")) bag_application = code.getDescriptionDe(); else if (CmlOptions.DB_LANGUAGE.equals("fr")) bag_application = code.getDescriptionFr(); else if (CmlOptions.DB_LANGUAGE.equals("it")) bag_application = code.getDescriptionIt(); } 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"); else if (CmlOptions.DB_LANGUAGE.equals("it")) 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.mycompany.asyncreq.Main.java
@SuppressWarnings("empty-statement") public static ArrayList<String> GenData() { ArrayList<String> rootobject = new ArrayList<String>(); try {//from www . j a v a2 s.com FileInputStream file = new FileInputStream(new File("src/main/resources/Eu3.xlsx")); FileOutputStream ffout = new FileOutputStream(new File("trade.json")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows from first sheet Iterator rowIterator = sheet.iterator(); Root r; String addr; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); //For each row, iterate through each columns Iterator cellIterator = row.cellIterator(); Cell cell = (Cell) row.getCell(0); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: addr = "http://comtrade.un.org/api/get?max=50000&type=C&freq=M&px=HS&ps=2014&r=804&p=" + (int) cell.getNumericCellValue() + "&rg=All&cc=All&fmt=json"; rootobject.add(addr); break; } } } catch (IOException e) { e.printStackTrace(); } return rootobject; }
From source file:com.mycompany.chartproject.ExcelReader.java
public Map<String, Double> getPieChartData(String repo) { Map<String, Double> map = new HashMap<>(); try {//from w w w . j a v a2 s. co m String fileName = "src/main/resources/Stabilityfinal.xlsx"; String test = fileName; //String fileName2 = "src/main/resources/Series.xlsx"; //String test2 = fileName2; FileInputStream file = new FileInputStream(new File(test)); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheet(repo); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int total = -1; int success = 0; int failure = 0; int unstable = 0; int aborted = 0; while (rowIterator.hasNext()) { ++total; Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue() + "\t\t"); } else { System.out.print(cell.getNumericCellValue() + "\t\t"); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equalsIgnoreCase("SUCCESS")) { ++success; } else if (cell.getStringCellValue().equalsIgnoreCase("FAILURE")) { ++failure; } else if (cell.getStringCellValue().equalsIgnoreCase("UNSTABLE")) { ++unstable; } else if (cell.getStringCellValue().equalsIgnoreCase("ABORTED")) { ++aborted; } System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); file.close(); FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); } System.out.println("Total " + total); System.out.println("no. Successful " + success); System.out.println("no. Failures " + failure); System.out.println("no. Unstable " + unstable); int green = ((success * 100 / total)); double passedPercentage = (double) green / 100; System.out.println("Passed: " + passedPercentage); int red = ((failure * 100 / total)); double failedPercentage = (double) red / 100; System.out.println("Failed: " + failedPercentage); int orange = ((unstable * 100 / total)); double unstablePercentage = (double) orange / 100; System.out.println("Unstable: " + unstablePercentage); int abort = ((aborted * 100 / total)); double abortedPercentage = (double) abort / 100; System.out.println("Aborted: " + abortedPercentage); map.put("Failed", failedPercentage); map.put("Unstable", unstablePercentage); map.put("Passed", passedPercentage); map.put("Aborted", abortedPercentage); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:com.mycompany.chartproject.ExcelReader.java
public List<ChartSeries> getSeriesChartData(String repo) { List<ChartSeries> cs = new ArrayList<>(); try {//ww w . j a va2 s.c o m String fileName = "src/main/resources/Series.xlsx"; String test = fileName; //String fileName2 = "src/main/resources/Series.xlsx"; //String test2 = fileName2; FileInputStream file = new FileInputStream(new File(test)); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheet(repo); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); ChartSeries chartSeries = null; while (rowIterator.hasNext()) { chartSeries = new ChartSeries(); Row row = rowIterator.next(); if (row.getRowNum() == 0) { row = rowIterator.next(); } //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //System.out.println("numeric"); switch (cell.getColumnIndex()) { case 1: chartSeries.setTotal((int) cell.getNumericCellValue()); break; case 2: chartSeries.setPassed((int) cell.getNumericCellValue()); break; case 3: chartSeries.setFailed((int) cell.getNumericCellValue()); break; case 4: chartSeries.setSkipped((int) cell.getNumericCellValue()); break; } System.out.println(cell.getDateCellValue() + "\t\t"); System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: chartSeries.setDate(cell.getStringCellValue()); System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); cs.add(chartSeries); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return cs; }
From source file:com.mycompany.javaapplicaton3.LerArquivo.java
public static void main(String args[]) { logger.info("Hello World!"); try {/* www. j a v a2 s .com*/ //File excel = new File ("C:/Users/lprates/Documents/arquivo2013.xlsx"); //FileInputStream fis = new FileInputStream(excel); OPCPackage pkg = OPCPackage.open("C:/Users/lprates/Documents/arquivo2013.xlsx"); XSSFWorkbook myWorkBook = new XSSFWorkbook(pkg); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t"); break; default: System.out.print("Nada"); } } System.out.println(""); } } catch (Exception ex) { logger.error(ex.toString()); } }
From source file:com.read.main.LeerPDF.java
/** * @param args the command line arguments *//*from www .j a v a 2s .c om*/ public static void main(String[] args) throws IOException { try { FileInputStream file = new FileInputStream(new File("/home/aaron/Escritorio/Example.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file); XSSFSheet sheet = workbook2.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); System.out.println("Numero de Columnas: " + row.getLastCellNum()); System.out.println(row.getRowNum()); if (row.getRowNum() == 0) { System.out.println("Fila Cero"); } else { int numColumna = 0; while (numColumna < row.getLastCellNum()) { Cell cell = row.getCell(numColumna); try { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.print(numColumna + ".- BOOLEAN: "); System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(numColumna + ".- NUMERIC: "); System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: System.out.print(numColumna + ".- STRING: "); System.out.print(cell.getStringCellValue() + "\t\t"); break; } } catch (Exception e) { System.err.println(e); } ; numColumna++; } } System.out.println(""); } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.safeway.app.appcert.util.smoketester.TestCaseReader.java
public List<TestScriptTemplate> readExcel() throws Exception { FileInputStream file = new FileInputStream( new File("C:\\Users\\nbret00\\Documents\\SeleniumSmokeTest\\TestCases.xlsx")); List<TestScriptTemplate> tstList = new ArrayList<TestScriptTemplate>(); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); List<TestScriptTemplate> TestScriptTemplateList = new ArrayList(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from ww w .jav a2 s . c o m //skip until row 5 if (row.getRowNum() > 4) { TestScriptTemplate tscripttemp = new TestScriptTemplate(); //Cell appcode = row.getCell(0); //this should be the item # on the list //System.out.println("application name #: "+itemnum.getStringCellValue()); tscripttemp.setAppCode(getCellValueStr(row.getCell(1))); tscripttemp.setAppURL(getCellValueStr(row.getCell(2))); tscripttemp.setAppUserID(getCellValueStr(row.getCell(3))); tscripttemp.setAppPassword(getCellValueStr(row.getCell(4))); tscripttemp.setHomePageTitle(getCellValueStr(row.getCell(5))); tscripttemp.setHomePageElementType(getCellValueStr(row.getCell(6))); tscripttemp.setHomePageElement(getCellValueStr(row.getCell(7))); tscripttemp.setLevel1URL(getCellValueStr(row.getCell(8))); tscripttemp.setLevel1PageTitle(getCellValueStr(row.getCell(9))); tscripttemp.setLevel1ElementType(getCellValueStr(row.getCell(10))); tscripttemp.setLevel1Element(getCellValueStr(row.getCell(11))); TestScriptTemplateList.add(tscripttemp); System.out.println("this to string: " + tscripttemp.toString()); } } file.close(); return TestScriptTemplateList; }
From source file:com.siacra.beans.GrupoBean.java
public void archivoXlsx(String path, FileUploadEvent archivo) { excelResponse = new ArrayList<>(); List<Horario> horas = new ArrayList<>(); try {//ww w . j av a 2s .c o m FileInputStream file = new FileInputStream(new File(path + "\\" + archivo.getFile().getFileName())); // Crear el objeto que tendra el libro de Excel XSSFWorkbook workbook = new XSSFWorkbook(file); /* * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda int cantidad = 0; int cantidad2 = 0; while (rowIterator.hasNext()) { row = rowIterator.next(); if (cantidad2 != 0) { Horario h = new Horario(); UpploadGrupos grupo = new UpploadGrupos(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; cantidad = 1; while (cellIterator.hasNext()) { celda = cellIterator.next(); // if((cantidad%10)==0) // { // System.out.print(grupo.toString()); // excelResponse.add(grupo); // grupo = new UpploadGrupos(); // cantidad=1; // } // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { SimpleDateFormat f = new SimpleDateFormat("HH:mm:ss"); String fecha = f.format(celda.getDateCellValue()); System.out.print(":::::::: " + fecha); Date dos = f.parse(fecha); if (cantidad == 4) { h.setHinicio1(dos); grupo.setInicio1(dos); cantidad++; } else if (cantidad == 5) { h.setHfin1(dos); grupo.setFin1(dos); cantidad++; } else if (cantidad == 7) { h.setHinicio2(dos); grupo.setInicio2(dos); cantidad++; } else if (cantidad == 8) { h.setHfin2(dos); grupo.setFin2(dos); cantidad++; } System.out.println(dos); } // else // { // double numero = celda.getNumericCellValue(); // System.out.println(celda.getNumericCellValue()); // } break; case Cell.CELL_TYPE_STRING: if (cantidad == 1) { grupo.setAsignatura(celda.getStringCellValue()); cantidad++; } else if (cantidad == 2) { grupo.setTipoGrupo(celda.getStringCellValue()); cantidad++; } else if (cantidad == 3) { h.setDia1(celda.getStringCellValue()); grupo.setDia1(celda.getStringCellValue()); cantidad++; } else if (cantidad == 6) { h.setDia2(celda.getStringCellValue()); grupo.setDia2(celda.getStringCellValue()); cantidad++; } else if (cantidad == 9) { grupo.setNumeroGrupo(celda.getStringCellValue()); cantidad++; } else if (cantidad == 10) { grupo.setCupos(celda.getStringCellValue()); cantidad++; } String texto = celda.getStringCellValue(); System.out.println(celda.getStringCellValue()); break; // case Cell.CELL_TYPE_BOOLEAN: // System.out.println(celda.getBooleanCellValue()); // break; }//fin if que obtiene valor de celda } //fin while que recorre celdas System.out.print("objeto:::" + grupo.toString()); System.out.print("objeto:::" + h.toString()); horas.add(h); excelResponse.add(grupo); } // fin if primera iteracion cantidad2++; } // fin while que recorre filas // cerramos el libro excel workbook.close(); } catch (Exception e) { e.printStackTrace(); } procesarListaCargada(); }