List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:com.maxl.java.aips2sqlite.RealExpertInfo.java
License:Open Source License
/** * Extracts package info from Swissmedic package Excel file *//*from ww w .ja va2s .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 = ""; 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 va2 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 w w.j ava 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 = ""; // 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.metasoft.claim.service.impl.claim.ClaimImportServiceImpl.java
@Override @Transactional//w w w . j av a 2 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.mycompany.asyncreq.Main.java
@SuppressWarnings("empty-statement") public static ArrayList<String> GenData() { ArrayList<String> rootobject = new ArrayList<String>(); try {//from www .ja v a2 s.c o m 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.fyp_emr.ExcelReaders.java
public static Diagnostic_Algorithm ReadFullGraph(String file) throws IOException { Diagnostic_Algorithm DA = new Diagnostic_Algorithm(); FileInputStream fs = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row;//from www. j a va 2s .c o m int id, id1, id2; String label, type, color, available, label_e; Node n; Edge e; LinkedList<Edge> edges = new LinkedList<Edge>(); int rows = sheet.getPhysicalNumberOfRows(); for (int r = 2; r < rows; r++) { row = sheet.getRow(r); if (row != null) { if (row.getCell(0) != null) { row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC); id = (int) row.getCell(0).getNumericCellValue(); if (id != 0) { label = row.getCell(1).getStringCellValue(); type = row.getCell(2).getStringCellValue(); color = row.getCell(3).getStringCellValue(); available = row.getCell(4).getStringCellValue(); n = new Node(); n.setID(id); n.setLabel(label); n.setType(type); n.setAvailable(available); n.setColor(color); DA.addNode(n); } } if (row.getCell(6) != null) { row.getCell(6).setCellType(Cell.CELL_TYPE_NUMERIC); row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC); id1 = (int) row.getCell(6).getNumericCellValue(); id2 = (int) row.getCell(7).getNumericCellValue(); label_e = row.getCell(8).getStringCellValue(); e = new Edge(id1, id2); e.setLabel(label_e); edges.add(e); } } } for (int i = 0; i < edges.size(); i++) DA.addEdge(edges.get(i)); return DA; }
From source file:com.mycompany.javaapplicaton3.LerArquivo.java
public static void main(String args[]) { logger.info("Hello World!"); try {//from w w w .j av a 2 s. c o m //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.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileTransformed() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row;// ww w. j a va 2 s . c o m String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } XSSFWorkbook transformedWB = new XSSFWorkbook(); transformedWB.createSheet(); XSSFSheet transformedS = transformedWB.getSheetAt(0); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { transformedS.createRow(i); } /* elkezdnk vgigmenni az alap sheeten ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre */ ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum()).setCellValue(sum); } } for (int index : matchingIndexes) { for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); row.getCell(index).setCellValue(3.14159); } /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { if(i == index){ for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { row = sheet.getRow(j); transformedS.getRow(j).createCell(transformedColumnCount).setCellValue( row.getCell(i).getRawValue() ); } transformedColumnCount++; } }*/ } int columnsInTransformed = 0; for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { String cellValue = row.getCell(j).getRawValue(); if (!cellValue.equals("3.14159")) { transformedS.getRow(i).createCell(columnsInTransformed); transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue); columnsInTransformed++; } } columnsInTransformed = 0; } row = transformedS.getRow(0); row.createCell(row.getLastCellNum()).setCellValue(atcMask); File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); transformedWB.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileFiltered() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row;//from ww w. j av a 2 s . co m String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } row = sheet.getRow(0); row.createCell(row.getLastCellNum()).setCellValue("ATC mask:"); row.createCell(row.getLastCellNum()).setCellValue(atcMask); ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); System.out.println("SUMS"); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum() + 1).setCellValue(sum); } } File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); workbookToModify.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.niles.excel2json.objects.ExcelFile.java
License:Open Source License
public void process() throws Exception { File excel = new File(path); FileInputStream fis = SystemTools.getFileInputStream(excel); XSSFWorkbook wb = null; try {/*w w w .j a v a2 s . c o m*/ wb = new XSSFWorkbook(fis); } catch (IOException ex) { logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage()); return; } this.fileName = excel.getName().replace(StringConstants.XLSX, ""); this.folderPath = fileName + " Files"; folderPath = SystemTools.createFolder(folderPath); int sheetCount = wb.getNumberOfSheets(); int currentSheetNumber = 0; int rowCount = 0; int columnCount = 0; HashMap<Integer, String> headers = new HashMap<Integer, String>(); /* * Itterate through the Excel sheets here and convert them to JSON */ while (currentSheetNumber < sheetCount) { XSSFSheet current = wb.getSheetAt(currentSheetNumber); sheetName = current.getSheetName(); // System.out.println(sheetName); if (current.getRow(0) == null) { // logger.error("[{}] Sheet contains no data", sheetName); } else { //logger.info("[{}] Processing sheet", sheetName); rowCount = current.getLastRowNum() + 1; columnCount = current.getRow(0).getPhysicalNumberOfCells(); // System.out.println("Col Count: " + columnCount); // System.out.println("Row Count: " + rowCount); JSONArray myJSONArray = new JSONArray(); JSONObject currentJSONObject = null; for (int a = 0; a < rowCount; a++) { XSSFRow currentRow = current.getRow(a); if (a == 0) { logger.info("[{}] Loading header information", sheetName); for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) { XSSFCell currentCell = currentRow.getCell(currentCellNumber); String header = "Header" + currentCellNumber; if (currentCell != null) { if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { header = currentCell.getStringCellValue(); } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { header = currentCell.getNumericCellValue() + ""; } } headers.put(currentCellNumber, header.replaceAll(" ", "")); } } else { currentJSONObject = new JSONObject(); for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) { XSSFCell currentCell = currentRow.getCell(currentCellNumber); String value = ""; if (currentCell != null) { if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) { if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { value = currentCell.getStringCellValue(); } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { value = currentCell.getNumericCellValue() + ""; } if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { value = currentCell.getBooleanCellValue() + ""; } if (value == null) { value = ""; } } } currentJSONObject.put(headers.get(currentCellNumber), value); } myJSONArray.add(currentJSONObject); } } writeToJson(myJSONArray); } currentSheetNumber++; } }