List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum);
From source file:com.maxl.java.aips2sqlite.RealPatientInfo.java
License:Open Source License
/** * Extracts package info from Swissmedic package Excel file *//*from ww w . 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 = ""; // 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.maxl.java.aips2xml.Aips2Xml.java
License:Open Source License
static void extractPackageInfo() { try {//from w w w .j ava2 s.c o m long startTime = System.currentTimeMillis(); 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 > 3) { 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 ; // 0: Zulassungsnnr, 1: Sequenz, 2: Sequenzname, 3: Zulassunginhaberin, 4: T-Nummer, 5: ATC-Code, 6: Heilmittelcode // 7: Erstzulassung Prparat, 8: Zulassungsdatum Sequenz, 9: Gltigkeitsdatum, 10: Verpackung, 11: Packungsgrsse // 12: Einheit, 13: Abgabekategorie, 14: Wirkstoff, 15: Zusammensetzung, 16: Anwendungsgebiet Prparat, 17: Anwendungsgebiet Sequenz swissmedic_no5 = getAnyValue(row.getCell(0)); // Swissmedic registration number (5 digits) sequence_name = getAnyValue(row.getCell(2)); // Sequence name heilmittel_code = getAnyValue(row.getCell(6)); package_size = getAnyValue(row.getCell(11)); package_unit = getAnyValue(row.getCell(12)); swissmedic_cat = getAnyValue(row.getCell(13)); application_area = getAnyValue(row.getCell(16)); if (row.getCell(10) != null) { package_id = getAnyValue(row.getCell(10)); 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)\n"); // 6 = swissmedic + bag else pack.add(""); pack.add(public_price); // 7 pack.add(exfactory_price); // 8 pack.add(therapeutic_index); // 9 pack.add(withdrawn_str); // 10 pack.add(speciality_str); // 11 pack.add(plimitation_str); // 12 pack.add(add_info_str); // 13 package_info.put(swissmedic_no8, pack); } } num_rows++; } long stopTime = System.currentTimeMillis(); if (SHOW_LOGS) { System.out.println( (package_info.size() + 1) + " packages in " + (stopTime - startTime) / 1000.0f + " sec"); } startTime = System.currentTimeMillis(); if (SHOW_LOGS) System.out.print("- Processing atc classes xls ... "); if (DB_LANGUAGE.equals("de")) { // Load ATC classes xls file FileInputStream atc_classes_file = new FileInputStream(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) { atc_map.put(atc_code, atc_class); } } num_rows++; } } else if (DB_LANGUAGE.equals("fr")) { // Load multilinguagl ATC classes txt file String atc_classes_multi = readFromFile(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(); atc_map.put(atc_code, atc_class_french); } scanner.close(); } stopTime = System.currentTimeMillis(); if (SHOW_LOGS) System.out .println((atc_map.size() + 1) + " classes in " + (stopTime - startTime) / 1000.0f + " sec"); // Load Refdata xml file File refdata_xml_file = null; if (DB_LANGUAGE.equals("de")) refdata_xml_file = new File(FILE_REFDATA_PHARMA_DE_XML); else if (DB_LANGUAGE.equals("fr")) refdata_xml_file = new File(FILE_REFDATA_PHARMA_FR_XML); else { System.err.println("ERROR: DB_LANGUAGE undefined"); System.exit(1); } FileInputStream refdata_fis = new FileInputStream(refdata_xml_file); startTime = System.currentTimeMillis(); if (SHOW_LOGS) System.out.print("- Unmarshalling Refdata Pharma " + DB_LANGUAGE + " ... "); JAXBContext context = JAXBContext.newInstance(Pharma.class); Unmarshaller um = context.createUnmarshaller(); Pharma refdataPharma = (Pharma) um.unmarshal(refdata_fis); List<Pharma.ITEM> pharma_list = refdataPharma.getItem(); String smno8; for (Pharma.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 ArrayList<String> pi_row = package_info.get(smno8); // Replace sequence_name if (pi_row != null) { if (pharma.getAddscr().length() > 0) pi_row.set(1, pharma.getDscr() + ", " + pharma.getAddscr()); else pi_row.set(1, pharma.getDscr()); if (pharma.getStatus().equals("I")) { if (DB_LANGUAGE.equals("de")) pi_row.set(10, "a.H."); else if (DB_LANGUAGE.equals("fr")) pi_row.set(10, "p.c."); } } else { if (SHOW_ERRORS) System.err.println(">> Does not exist in BAG xls: " + smno8 + " (" + pharma.getDscr() + ", " + pharma.getAddscr() + ")"); } } else if (ean_code.length() < 13) { if (SHOW_ERRORS) System.err.println(">> EAN code too short: " + ean_code + ": " + pharma.getDscr()); } else if (ean_code.length() > 13) { if (SHOW_ERRORS) System.err.println(">> EAN code too long: " + ean_code + ": " + pharma.getDscr()); } } stopTime = System.currentTimeMillis(); if (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(FILE_PREPARATIONS_XML); FileInputStream fis_bag = new FileInputStream(bag_xml_file); startTime = System.currentTimeMillis(); if (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 (DB_LANGUAGE.equals("de")) flagSB20_str = "SB 20%"; else if (DB_LANGUAGE.equals("fr")) flagSB20_str = "QP 20%"; } else if (flagSB20_str.equals("N")) { if (DB_LANGUAGE.equals("de")) flagSB20_str = "SB 10%"; else if (DB_LANGUAGE.equals("fr")) flagSB20_str = "QP 10%"; } else flagSB20_str = ""; } 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 (DB_LANGUAGE.equals("de")) therapeutic_code = code.getDescriptionDe(); else if (DB_LANGUAGE.equals("fr")) therapeutic_code = code.getDescriptionFr(); } else { if (DB_LANGUAGE.equals("de")) bag_application = code.getDescriptionDe(); else if (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 = 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 (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 (DB_LANGUAGE.equals("de")) pi_row.set(11, ", SL"); else if (DB_LANGUAGE.equals("fr")) pi_row.set(11, ", LS"); } catch (NumberFormatException e) { if (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 (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.mec.DAO.Superior.SuperiorDAO.java
public Map<String, List<String>> getAll() throws IOException { Map<String, List<String>> s = new HashMap<>(); ClassLoader classloader = Thread.currentThread().getContextClassLoader(); InputStream is = classloader.getResourceAsStream("superior.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet datatypeSheet = workbook.getSheetAt(0); for (Row row : datatypeSheet) { Cell cue = row.getCell(0); Cell carrera = row.getCell(1);//from w w w . j ava 2s . com if (cue != null && carrera != null && !cue.toString().isEmpty()) { cue.setCellType(Cell.CELL_TYPE_STRING); if (s.containsKey(carrera.toString())) { s.get(carrera.toString()).add(cue.toString()); } else { List<String> aux = new ArrayList<>(); aux.add(cue.toString()); s.put(carrera.toString(), aux); } //list.add(new Superior(cue.toString(), carrera.toString())); } } return s; }
From source file:com.mec.DAO.Superior.SuperiorDAO.java
public Map<String, List<String>> getByFiltro(String filtro) throws IOException { Map<String, List<String>> s = new HashMap<>(); ClassLoader classloader = Thread.currentThread().getContextClassLoader(); InputStream is = classloader.getResourceAsStream("superior.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet datatypeSheet = workbook.getSheetAt(0); for (Row row : datatypeSheet) { Cell cue = row.getCell(0); Cell carrera = row.getCell(1);/*w w w. j a va 2 s. co m*/ if (cue != null && carrera != null && !cue.toString().isEmpty() && clean((carrera.toString())).contains(clean(filtro))) { cue.setCellType(Cell.CELL_TYPE_STRING); if (s.containsKey(carrera.toString())) { s.get(carrera.toString()).add(cue.toString()); } else { List<String> aux = new ArrayList<>(); aux.add(cue.toString()); s.put(carrera.toString(), aux); } } } return s; }
From source file:com.mec.Services.VoteroService.java
private Map<String, List<Establecimiento>> getFromExcel() throws IOException { Map<String, List<Establecimiento>> s = new HashMap<>(); ClassLoader classloader = Thread.currentThread().getContextClassLoader(); InputStream is = classloader.getResourceAsStream("votero.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet datatypeSheet = workbook.getSheetAt(0); for (Row row : datatypeSheet) { Cell cue = row.getCell(0); Cell anexo = row.getCell(1);//from ww w. ja v a 2 s.c o m Cell circuito = row.getCell(3); Cell desde = row.getCell(4); Cell hasta = row.getCell(5); Cell total = row.getCell(6); if (cue != null && anexo != null && !cue.toString().isEmpty()) { cue.setCellType(Cell.CELL_TYPE_STRING); anexo.setCellType(Cell.CELL_TYPE_STRING); circuito.setCellType(Cell.CELL_TYPE_STRING); desde.setCellType(Cell.CELL_TYPE_STRING); hasta.setCellType(Cell.CELL_TYPE_STRING); total.setCellType(Cell.CELL_TYPE_STRING); if (s.containsKey(circuito.toString())) { EstablecimientoPost est = establecimientosDAO.getByCueAnexo(Integer.parseInt(cue.toString()), Integer.parseInt(anexo.toString())); setGeo(est); s.get(circuito.toString()).add(new Establecimiento(est, Integer.parseInt(desde.toString()), Integer.parseInt(hasta.toString()), Integer.parseInt(total.toString()))); } else { List<Establecimiento> aux = new ArrayList<>(); EstablecimientoPost est = establecimientosDAO.getByCueAnexo(Integer.parseInt(cue.toString()), Integer.parseInt(anexo.toString())); setGeo(est); aux.add(new Establecimiento(est, Integer.parseInt(desde.toString()), Integer.parseInt(hasta.toString()), Integer.parseInt(total.toString()))); s.put(circuito.toString(), aux); } } } return s; }
From source file:com.medicaid.mmis.util.CodeMappingLoader.java
License:Apache License
private static void importSheet(EntityManager em, SequenceGeneratorBean sequence, Workbook workbook, String sheetName) {/* ww w . j a va 2 s.c om*/ logger.info("Importing legacy mapping from worksheet: " + sheetName); Sheet sheet = workbook.getSheet(sheetName); Iterator<Row> rowIterator = sheet.rowIterator(); int total = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() < 1) { continue; } Cell systemIdCell = row.getCell(0); Cell codeTypeCell = row.getCell(1); Cell internalCodeCell = row.getCell(2); Cell externalCodeCell = row.getCell(4); if (systemIdCell == null || StringUtils.isBlank(systemIdCell.getStringCellValue())) { continue; } if (codeTypeCell == null || StringUtils.isBlank(codeTypeCell.getStringCellValue())) { continue; } if (internalCodeCell == null || StringUtils.isBlank(internalCodeCell.getStringCellValue())) { continue; } if (externalCodeCell == null || StringUtils.isBlank(externalCodeCell.getStringCellValue())) { continue; } LegacySystemMapping mapping = new LegacySystemMapping(); mapping.setId(sequence.getNextValue("LEGACY_MAPPING")); mapping.setSystemName(systemIdCell.getStringCellValue()); mapping.setExternalCode(externalCodeCell.getStringCellValue()); mapping.setInternalCode(internalCodeCell.getStringCellValue()); mapping.setCodeType(codeTypeCell.getStringCellValue()); logger.debug("Inserting mapping: " + mapping); em.persist(mapping); total++; } logger.info("Total records imported from sheet: " + total); System.out.println("Total records imported from sheet[" + sheetName + "] : " + total); }
From source file:com.metasoft.claim.service.impl.claim.ClaimImportServiceImpl.java
@Override @Transactional/*from ww w . j a v a2 s . c o m*/ public List<ImportError> saveFromFile(UploadedFile uploadedFile, SecUser user) throws Exception { List<ImportError> errorClaimNumbers = new ArrayList<ImportError>(); XSSFWorkbook workbook = null; try { InputStream input = new ByteArrayInputStream(uploadedFile.bytes); Date today = new Date(); // Using XSSF for xlsx format, for xls use HSSF workbook = new XSSFWorkbook(input); Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row = rowIterator.next(); // iterating over each row int i = 1; int rowAt = 1; while (rowIterator.hasNext()) { i = 1; TblClaimRecovery claim = new TblClaimRecovery(); row = rowIterator.next(); try { // ? ? ? ? ? claim.setClaimNumber(StringUtils.trimToNull(row.getCell(i++).getStringCellValue())); claim.setPolicyNo(StringUtils.trimToNull(row.getCell(i++).getStringCellValue())); claim.setLicenseNumber(StringUtils.trimToNull(row.getCell(i++).getStringCellValue())); claim.setPartyLicenseNumber(StringUtils.trimToNull(row.getCell(i++).getStringCellValue())); claim.setAccidentDate(row.getCell(i++).getDateCellValue()); claim.setMaturityDate(row.getCell(i++).getDateCellValue()); } catch (Exception e) { ImportError importError = new ImportError(); importError.setReason("? " + rowAt + " " + i + " ?"); importError.setClaimNumber(StringUtils.isEmpty(claim.getClaimNumber()) ? "" : claim.getClaimNumber()); errorClaimNumbers.add(importError); continue; } String partyInsuranceName = StringUtils.trimToNull(row.getCell(i++).getStringCellValue()); if (partyInsuranceName != null) { claim.setPartyInsurance(insuranceDao.findByName(partyInsuranceName)); } claim.setClaimInsuranceAmount((float) row.getCell(i++).getNumericCellValue()); if (claim.getClaimNumber() != null && claim.getPartyInsurance() != null) { if (!claimDao.checkDupClaimNumber(claim.getClaimNumber())) { claim.setCreateDate(today); claim.setCreateBy(user); claim.setJobDate(today); claim.setJobStatus(JobStatus.RECEIVED); List<TblUserInsurance> tblUserInsurances = userInsuranceDao .searchByInsuranceId(claim.getPartyInsurance().getId()); if (tblUserInsurances != null && !tblUserInsurances.isEmpty()) { claim.setAgent(userDao.findById(tblUserInsurances.get(0).getId().getUserId())); } claimDao.save(claim); claim.setJobNo(DateToolsUtil.convertToString(today, DateToolsUtil.DATE_PATTERN_VIEW_YYYYMMDD, DateToolsUtil.LOCALE_TH) + claim.getId()); claimDao.save(claim); } else { ImportError importError = new ImportError(); importError.setReason(""); importError.setClaimNumber(claim.getClaimNumber()); errorClaimNumbers.add(importError); } } else if (claim.getClaimNumber() != null && claim.getPartyInsurance() == null) { ImportError importError = new ImportError(); importError.setReason("?"); importError.setClaimNumber(claim.getClaimNumber()); errorClaimNumbers.add(importError); } rowAt++; } input.close(); } catch (Exception e) { e.printStackTrace(); throw e; } finally { if (workbook != null) { try { workbook.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return errorClaimNumbers; }
From source file:com.microej.tool.excel2nls.SheetReader.java
License:Open Source License
/** * Read the sheet and extract the NLS language it contains. * /* w w w . j av a2 s .c om*/ * @return an NLS language */ public Language read() { Language language = new Language(); Row row; // Extract language row = sheet.getRow(DISPLAY_NAME_ROW_INDEX); String displayName = row.getCell(DISPLAY_NAME_COLUMN_INDEX).getStringCellValue(); language.setDisplayName(displayName); // Extract local row = sheet.getRow(LOCALE_ROW_INDEX); String locale = row.getCell(LOCALE_COLUMN_INDEX).getStringCellValue(); language.setLocale(locale); // Extract messages int i = FIRST_MESSAGE_ROW_INDEX; while ((row = sheet.getRow(i++)) != null) { String id = row.getCell(ID_COLUMN_INDEX).getStringCellValue(); String text = row.getCell(MESSAGE_COLUMN_INDEX).getStringCellValue(); Message message = new Message(id, text); language.add(message); } return language; }
From source file:com.mimp.controllers.reporte.java
private static void copyRowStyle(Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Coge la fila antigua y nueva Row newRow = worksheet.getRow(destinationRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {/* w w w . ja v a2s . c om*/ newRow = worksheet.createRow(destinationRowNum); } // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Copia la antigua y nueva celda Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // Si la anterior celda es null, evalua la siguiente celda defrente if (oldCell == null) { newCell = null; continue; } // Usa el estilo de la celda antigua newCell.setCellStyle(oldCell.getCellStyle()); // Establece el tipo de valor de la celda newCell.setCellType(oldCell.getCellType()); // Establece el valor de la celda // switch (oldCell.getCellType()) { // case Cell.CELL_TYPE_BLANK: // break; // case Cell.CELL_TYPE_BOOLEAN: // newCell.setCellValue(oldCell.getBooleanCellValue()); // break; // case Cell.CELL_TYPE_ERROR: // newCell.setCellErrorValue(oldCell.getErrorCellValue()); // break; // case Cell.CELL_TYPE_FORMULA: // newCell.setCellFormula(oldCell.getCellFormula()); // break; // case Cell.CELL_TYPE_NUMERIC: // newCell.setCellValue(oldCell.getNumericCellValue()); // break; // case Cell.CELL_TYPE_STRING: // newCell.setCellValue(oldCell.getRichStringCellValue()); // break; // } } }
From source file:com.mimp.controllers.reporte.java
private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Coge la fila antigua y nueva Row newRow = worksheet.getRow(destinationRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from ww w . j a va 2 s . com newRow = worksheet.createRow(destinationRowNum); } // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Copia la antigua y nueva celda Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // Si la anterior celda es null, evalua la siguiente celda defrente if (oldCell == null) { newCell = null; continue; } // Usa el estilo de la celda antigua newCell.setCellStyle(oldCell.getCellStyle()); // Establece el tipo de valor de la celda newCell.setCellType(oldCell.getCellType()); // Establece el valor de la celda switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }