List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:testpoi.ReadExcelRowsRandomly.java
License:Open Source License
public static void main(String args[]) { try {//from w w w .ja v a 2 s. c om FileInputStream file = new FileInputStream(new File( "C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\1.xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); double random = Math.random(); int rowNum = (int) (random * sheet.getPhysicalNumberOfRows()); Row row = sheet.getRow(rowNum); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double cellValue = cell.getNumericCellValue(); System.out.print(cellValue + "\t"); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue(); System.out.print(cellValue + "\t"); } } System.out.println(); } catch (Exception e) { e.printStackTrace(); } }
From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java
License:Open Source License
private static Map<String, PlatePool> process384PlateInputXLSX(XSSFWorkbook wb, User u, RequestManager manager, MisoNamingScheme<Library> libraryNamingScheme) throws Exception { ((RequestManagerAwareNamingScheme) libraryNamingScheme).setRequestManager(manager); List<Sample> samples = new ArrayList<Sample>(); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); XSSFRow glrow = sheet.getRow(1);// ww w . j a va2s . c o m //process global headers XSSFCell pairedCell = glrow.getCell(0); boolean paired = pairedCell.getBooleanCellValue(); log.info("Got paired: " + paired); XSSFCell platformCell = glrow.getCell(1); PlatformType pt = null; if (getCellValueAsString(platformCell) != null) { pt = PlatformType.get(getCellValueAsString(platformCell)); } if (pt == null) { throw new InputFormException( "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'"); } else { log.info("Got platform type: " + pt.getKey()); } XSSFCell typeCell = glrow.getCell(2); LibraryType lt = null; if (getCellValueAsString(typeCell) != null) { String[] split = getCellValueAsString(typeCell).split("-"); String plat = split[0]; String type = split[1]; if (getCellValueAsString(platformCell).equals(plat)) { lt = manager.getLibraryTypeByDescriptionAndPlatform(type, pt); } else { throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell) + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'"); } } if (lt == null) { throw new InputFormException( "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'"); } else { log.info("Got library type: " + lt.getDescription()); } XSSFCell selectionCell = glrow.getCell(3); LibrarySelectionType ls = null; if (getCellValueAsString(selectionCell) != null) { ls = manager.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell)); } if (ls == null) { throw new InputFormException( "Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'"); } else { log.info("Got library selection type: " + ls.getName()); } XSSFCell strategyCell = glrow.getCell(4); LibraryStrategyType lst = null; if (getCellValueAsString(strategyCell) != null) { lst = manager.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell)); } if (lst == null) { throw new InputFormException( "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'"); } else { log.info("Got library strategy type: " + lst.getName()); } XSSFCell plateBarcodeCell = glrow.getCell(5); String plateBarcode = null; if (getCellValueAsString(plateBarcodeCell) != null) { plateBarcode = getCellValueAsString(plateBarcodeCell); } if (plateBarcode == null) { throw new InputFormException( "Cannot resolve plate barcode from: '" + getCellValueAsString(plateBarcodeCell) + "'"); } else { log.info("Got plate barcode: " + plateBarcode); } //process entries Simple384WellPlate libraryPlate = null; //Map<String, Pool<Plate<LinkedList<Library>, Library>>> pools = new HashMap<String, Pool<Plate<LinkedList<Library>, Library>>>(); Map<String, PlatePool> pools = new HashMap<String, PlatePool>(); for (int ri = 4; ri < rows; ri++) { XSSFRow row = sheet.getRow(ri); // Ax - plate position XSSFCell platePosCell = row.getCell(0); String platePos = getCellValueAsString(platePosCell); if (platePos != null && libraryPlate == null) { //plated libraries - process as plate libraryPlate = new Simple384WellPlate(); libraryPlate.setIdentificationBarcode(plateBarcode); libraryPlate.setCreationDate(new Date()); } //cell defs XSSFCell sampleAliasCell = row.getCell(2); Sample s = null; if (getCellValueAsString(sampleAliasCell) != null) { String salias = getCellValueAsString(sampleAliasCell); Collection<Sample> ss = manager.listSamplesByAlias(salias); if (!ss.isEmpty()) { if (ss.size() == 1) { s = ss.iterator().next(); log.info("Got sample: " + s.getAlias()); } else { throw new InputFormException( "Multiple samples retrieved with this alias: '" + salias + "'. Cannot process."); } } else { throw new InputFormException("No such sample '" + salias + "'in database. Samples need to be created before using the form input functionality"); } } else { log.info("Blank sample row found. Ending import."); break; } //sample OK - good to go if (s != null) { XSSFCell entityIDCell = row.getCell(2); XSSFCell poolNumberCell = row.getCell(3); XSSFCell sampleQcCell = row.getCell(4); //XSSFCell sampleAmountCell = row.getCell(5); //XSSFCell sampleWaterAmountCell = row.getCell(6); XSSFCell libraryDescriptionCell = row.getCell(7); XSSFCell barcodeKitCell = row.getCell(8); XSSFCell barcodeTagsCell = row.getCell(9); XSSFCell libraryQcCell = row.getCell(10); XSSFCell libraryQcInsertSizeCell = row.getCell(11); XSSFCell libraryQcMolarityCell = row.getCell(12); XSSFCell libraryQcPassFailCell = row.getCell(13); //XSSFCell libraryAmountCell = row.getCell(14); //XSSFCell libraryWaterAmountCell = row.getCell(15); //XSSFCell dilutionQcCell = row.getCell(16); XSSFCell dilutionMolarityCell = row.getCell(17); //XSSFCell dilutionAmountCell = row.getCell(18); //XSSFCell dilutionWaterAmountCell = row.getCell(19); XSSFCell poolQcCell = row.getCell(20); //XSSFCell poolAverageInsertSizeCell = row.getCell(21); XSSFCell poolConvertedMolarityCell = row.getCell(22); //add pool, if any if (getCellValueAsString(poolNumberCell) != null) { String poolNum = getCellValueAsString(poolNumberCell); if (!pools.containsKey(poolNum)) { PlatePool pool = new PlatePool(); pool.setAlias(poolNum); pool.setPlatformType(pt); pool.setReadyToRun(true); pool.setCreationDate(new Date()); pools.put(poolNum, pool); log.info("Added pool: " + poolNum); manager.savePool(pool); } } //process sample QC if (getCellValueAsString(sampleQcCell) != null) { try { SampleQC sqc = new SampleQCImpl(); sqc.setSample(s); sqc.setResults(Double.valueOf(getCellValueAsString(sampleQcCell))); sqc.setQcCreator(u.getLoginName()); sqc.setQcDate(new Date()); if (manager.getSampleQcTypeByName("Picogreen") != null) { sqc.setQcType(manager.getSampleQcTypeByName("Picogreen")); } else { sqc.setQcType(manager.getSampleQcTypeByName("QuBit")); } if (!s.getSampleQCs().contains(sqc)) { s.addQc(sqc); manager.saveSampleQC(sqc); manager.saveSample(s); log.info("Added sample QC: " + sqc.toString()); } } catch (NumberFormatException nfe) { throw new InputFormException("Supplied Sample QC concentration for sample '" + getCellValueAsString(sampleAliasCell) + "' is invalid", nfe); } } //if (getCellValueAsString(libraryQcCell) != null) { if (getCellValueAsString(barcodeKitCell) != null && getCellValueAsString(barcodeTagsCell) != null) { //create library Library library = new LibraryImpl(); library.setSample(s); Matcher mat = samplePattern.matcher(s.getAlias()); if (mat.matches()) { String libAlias = plateBarcode + "_" + "L" + mat.group(2) + "-" + platePos + "_" + getCellValueAsString(entityIDCell); //String libAlias = libraryNamingScheme.generateNameFor("alias", library); //library.setAlias(libAlias); library.setAlias(libAlias); library.setSecurityProfile(s.getSecurityProfile()); library.setDescription(s.getDescription()); library.setCreationDate(new Date()); library.setPlatformName(pt.name()); library.setLibraryType(lt); library.setLibrarySelectionType(ls); library.setLibraryStrategyType(lst); library.setPaired(paired); if (getCellValueAsString(libraryQcMolarityCell) != null) { int insertSize = 0; try { String bp = getCellValueAsString(libraryQcInsertSizeCell); Matcher m = digitPattern.matcher(bp); if (m.matches()) { insertSize = Integer.valueOf(m.group(1)); } else { throw new InputFormException("Supplied Library insert size for library '" + libAlias + "' (" + s.getAlias() + ") is invalid"); } } catch (NumberFormatException nfe) { throw new InputFormException("Supplied Library insert size for library '" + libAlias + "' (" + s.getAlias() + ") is invalid", nfe); } try { LibraryQC lqc = new LibraryQCImpl(); lqc.setLibrary(library); lqc.setInsertSize(insertSize); lqc.setResults(Double.valueOf(getCellValueAsString(libraryQcMolarityCell))); lqc.setQcCreator(u.getLoginName()); lqc.setQcDate(new Date()); lqc.setQcType(manager.getLibraryQcTypeByName("Bioanalyzer")); if (!library.getLibraryQCs().contains(lqc)) { library.addQc(lqc); manager.saveLibraryQC(lqc); log.info("Added library QC: " + lqc.toString()); } if (insertSize == 0 && lqc.getResults() == 0) { library.setQcPassed(false); } else { //TODO check libraryQcPassFailCell? library.setQcPassed(true); } } catch (NumberFormatException nfe) { throw new InputFormException("Supplied Library QC concentration for library '" + libAlias + "' (" + s.getAlias() + ") is invalid", nfe); } } if (getCellValueAsString(barcodeKitCell) != null) { Collection<TagBarcode> bcs = manager .listAllTagBarcodesByStrategyName(getCellValueAsString(barcodeKitCell)); if (!bcs.isEmpty()) { String tags = getCellValueAsString(barcodeTagsCell); if (!"".equals(tags)) { HashMap<Integer, TagBarcode> tbs = new HashMap<Integer, TagBarcode>(); if (tags.contains("-")) { String[] splits = tags.split("-"); int count = 1; for (String tag : splits) { for (TagBarcode tb : bcs) { if (tb.getName().equals(tag)) { //set tag barcodes tbs.put(count, tb); count++; } } } } else { for (TagBarcode tb : bcs) { if (tb.getName().equals(tags) || tb.getSequence().equals(tags)) { //set tag barcode tbs.put(1, tb); log.info("Got tag barcode: " + tb.getName()); break; } } } library.setTagBarcodes(tbs); } else { throw new InputFormException( "Barcode Kit specified but no tag barcodes entered for: '" + s.getAlias() + "'."); } } else { throw new InputFormException("No tag barcodes associated with the kit definition '" + getCellValueAsString(barcodeKitCell) + "' for sample: '" + s.getAlias() + "'."); } } /* if (getCellValueAsString(dilutionMolarityCell) != null) { try { LibraryDilution ldi = new LibraryDilution(); ldi.setLibrary(library); ldi.setSecurityProfile(library.getSecurityProfile()); ldi.setConcentration(Double.valueOf(getCellValueAsString(dilutionMolarityCell))); ldi.setCreationDate(new Date()); ldi.setDilutionCreator(u.getLoginName()); if (!library.getLibraryDilutions().contains(ldi)) { library.addDilution(ldi); log.info("Added library dilution: " + ldi.toString()); } if (getCellValueAsString(poolNumberCell) != null) { String poolNum = String.valueOf(new Double(getCellValueAsString(poolNumberCell)).intValue()); Pool<Plate<LinkedList<Library>, Library>> p = pools.get(poolNum); if (p != null) { p.addPoolableElement(ldi); log.info("Added library dilution to pool: " + p.toString()); } } } catch (NumberFormatException nfe) { throw new InputFormException("Supplied LibraryDilution concentration for library '"+libAlias+"' ("+s.getAlias()+") is invalid", nfe); } } */ if (getCellValueAsString(poolConvertedMolarityCell) != null) { String poolNum = getCellValueAsString(poolNumberCell); Pool<Plate<LinkedList<Library>, Library>> p = pools.get(poolNum); if (p != null) { log.debug("Retrieved pool " + poolNum); try { p.setConcentration( Double.valueOf(getCellValueAsString(poolConvertedMolarityCell))); } catch (NumberFormatException nfe) { throw new InputFormException( "Supplied pool concentration for pool '" + poolNum + "' is invalid", nfe); } } } log.info("Added library: " + library.toString()); manager.saveLibrary(library); if (getCellValueAsString(platePosCell) != null && libraryPlate != null) { //libraryPlate.setElement(getCellValueAsString(platePosCell), library); libraryPlate.addElement(library); log.info("Added library " + library.getAlias() + " to " + getCellValueAsString(platePosCell)); } samples.add(s); Pool<Plate<LinkedList<Library>, Library>> p = pools .get(getCellValueAsString(poolNumberCell)); if (p != null && !p.getPoolableElements().contains(libraryPlate)) { p.addPoolableElement(libraryPlate); log.info("Added plate to pool: " + p.toString()); } } else { log.error( "Cannot generate library alias from specified parent sample alias. Does it match the required schema?"); } } } } log.info("Done"); return pools; }
From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java
License:Open Source License
private static List<Sample> processSampleInputXLSX(XSSFWorkbook wb, User u, RequestManager manager, MisoNamingScheme<Library> libraryNamingScheme) throws Exception { ((RequestManagerAwareNamingScheme) libraryNamingScheme).setRequestManager(manager); List<Sample> samples = new ArrayList<Sample>(); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); XSSFRow glrow = sheet.getRow(1);//from w w w. j a v a 2 s. c o m //process global headers XSSFCell pairedCell = glrow.getCell(0); boolean paired; if (getCellValueAsString(pairedCell) != null) { paired = pairedCell.getBooleanCellValue(); log.info("Got paired: " + paired); } else { throw new InputFormException("'Paired' cell is empty. Please specify TRUE or FALSE."); } XSSFCell platformCell = glrow.getCell(1); PlatformType pt = null; if (getCellValueAsString(platformCell) != null) { pt = PlatformType.get(getCellValueAsString(platformCell)); } if (pt == null) { throw new InputFormException( "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'"); } else { log.info("Got platform type: " + pt.getKey()); } XSSFCell typeCell = glrow.getCell(2); LibraryType lt = null; if (getCellValueAsString(typeCell) != null) { String[] split = getCellValueAsString(typeCell).split("-"); String plat = split[0]; String type = split[1]; if (getCellValueAsString(platformCell).equals(plat)) { lt = manager.getLibraryTypeByDescriptionAndPlatform(type, pt); } else { throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell) + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'"); } } if (lt == null) { throw new InputFormException( "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'"); } else { log.info("Got library type: " + lt.getDescription()); } XSSFCell selectionCell = glrow.getCell(3); LibrarySelectionType ls = null; if (getCellValueAsString(selectionCell) != null) { ls = manager.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell)); } if (ls == null) { throw new InputFormException( "Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'"); } else { log.info("Got library selection type: " + ls.getName()); } XSSFCell strategyCell = glrow.getCell(4); LibraryStrategyType lst = null; if (getCellValueAsString(strategyCell) != null) { lst = manager.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell)); } if (lst == null) { throw new InputFormException( "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'"); } else { log.info("Got library strategy type: " + lst.getName()); } //process entries Plate<LinkedList<Sample>, Sample> samplePlate = null; Map<String, Pool<Dilution>> pools = new HashMap<String, Pool<Dilution>>(); for (int ri = 4; ri < rows; ri++) { XSSFRow row = sheet.getRow(ri); // Ax - plate position XSSFCell platePosCell = row.getCell(0); if (getCellValueAsString(platePosCell) != null && samplePlate == null) { //plated samples - process as plate samplePlate = new PlateImpl<Sample>(); } //cell defs XSSFCell sampleAliasCell = row.getCell(2); Sample s = null; if (getCellValueAsString(sampleAliasCell) != null) { String salias = getCellValueAsString(sampleAliasCell); Collection<Sample> ss = manager.listSamplesByAlias(salias); if (!ss.isEmpty()) { if (ss.size() == 1) { s = ss.iterator().next(); log.info("Got sample: " + s.getAlias()); } else { throw new InputFormException( "Multiple samples retrieved with this alias: '" + salias + "'. Cannot process."); } } else { throw new InputFormException("No such sample '" + salias + "'in database. Samples need to be created before using the form input functionality"); } } else { log.info("Blank sample row found. Ending import."); break; } //sample OK - good to go if (s != null) { String projectAliasCell = getCellValueAsString(row.getCell(1)); String poolNumberCell = getCellValueAsString(row.getCell(3)); String sampleQcCell = getCellValueAsString(row.getCell(4)); //String sampleAmountCell = getCellValueAsString(row.getCell(5)); //String sampleWaterAmountCell = getCellValueAsString(row.getCell(6)); String libraryDescriptionCell = getCellValueAsString(row.getCell(7)); String barcodeKitCell = getCellValueAsString(row.getCell(8)); String barcodeTagsCell = getCellValueAsString(row.getCell(9)); String libraryQcCell = getCellValueAsString(row.getCell(10)); String libraryQcInsertSizeCell = getCellValueAsString(row.getCell(11)); String libraryQcMolarityCell = getCellValueAsString(row.getCell(12)); String libraryQcPassFailCell = getCellValueAsString(row.getCell(13)); //String libraryAmountCell = getCellValueAsString(row.getCell(14)); //String libraryWaterAmountCell = getCellValueAsString(row.getCell(15)); //String dilutionQcCell = getCellValueAsString(row.getCell(16)); String dilutionMolarityCell = getCellValueAsString(row.getCell(17)); //String dilutionAmountCell = getCellValueAsString(row.getCell(18)); //String dilutionWaterAmountCell = getCellValueAsString(row.getCell(19)); String poolQcCell = getCellValueAsString(row.getCell(20)); //String poolAverageInsertSizeCell = getCellValueAsString(row.getCell(21)); String poolConvertedMolarityCell = getCellValueAsString(row.getCell(22)); //add pool, if any processPool(poolNumberCell, poolConvertedMolarityCell, pools); processSampleQC(sampleQcCell, s, u, manager); Library library = processLibrary(libraryQcCell, libraryDescriptionCell, libraryQcPassFailCell, s, pt, lt, ls, lst, paired, libraryNamingScheme); if (library != null) { processLibraryQC(libraryQcCell, libraryQcMolarityCell, libraryQcInsertSizeCell, library, u, manager); processBarcodes(barcodeKitCell, barcodeTagsCell, library, manager); processDilutions(dilutionMolarityCell, library, pools.get(poolNumberCell), u); log.info("Added library: " + library.toString()); s.addLibrary(library); } samples.add(s); } } return samples; }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
public static JSONArray preProcessSampleSheetImport(File inPath, User u, SampleService sampleService) throws Exception { if (inPath.getName().endsWith(".xlsx")) { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inPath)); JSONArray jsonArray = new JSONArray(); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); for (int ri = 5; ri < rows; ri++) { XSSFRow row = sheet.getRow(ri); XSSFCell sampleAliasCell = row.getCell(3); Sample s = null;/*from www. j a v a 2 s. com*/ if (getCellValueAsString(sampleAliasCell) != null) { String salias = getCellValueAsString(sampleAliasCell); Collection<Sample> ss = sampleService.getByAlias(salias); if (!ss.isEmpty()) { if (ss.size() == 1) { s = ss.iterator().next(); log.info("Got sample: " + s.getAlias()); } else { throw new InputFormException("Multiple samples retrieved with this alias: '" + salias + "'. Cannot process."); } } else { throw new InputFormException("No such sample '" + salias + "'in database. Samples need to be created before using the form input functionality"); } } else { log.info("Blank sample row found. Ending import."); break; } // sample OK - good to go if (s != null) { JSONArray sampleArray = new JSONArray(); XSSFCell projectNameCell = row.getCell(0); XSSFCell projectAliasCell = row.getCell(1); XSSFCell sampleNameCell = row.getCell(2); XSSFCell wellCell = row.getCell(4); XSSFCell adaptorCell = row.getCell(5); XSSFCell qcPassedCell = row.getCell(13); sampleArray.add(getCellValueAsString(projectNameCell)); sampleArray.add(getCellValueAsString(projectAliasCell)); sampleArray.add(getCellValueAsString(sampleNameCell)); sampleArray.add(getCellValueAsString(sampleAliasCell)); sampleArray.add(getCellValueAsString(wellCell)); if ((getCellValueAsString(adaptorCell)) != null) { sampleArray.add(getCellValueAsString(adaptorCell)); } else { sampleArray.add(""); } XSSFCell qcResultCell = null; if ("GENOMIC".equals(s.getSampleType()) || "METAGENOMIC".equals(s.getSampleType())) { qcResultCell = row.getCell(6); } else if ("NON GENOMIC".equals(s.getSampleType()) || "VIRAL RNA".equals(s.getSampleType()) || "TRANSCRIPTOMIC".equals(s.getSampleType()) || "METATRANSCRIPTOMIC".equals(s.getSampleType())) { qcResultCell = row.getCell(7); } else { if (!"NA".equals(getCellValueAsString(row.getCell(6)))) { qcResultCell = row.getCell(6); } else if (!"NA".equals(getCellValueAsString(row.getCell(7)))) { qcResultCell = row.getCell(7); } } XSSFCell rinCell = row.getCell(8); XSSFCell sample260280Cell = row.getCell(9); XSSFCell sample260230Cell = row.getCell(10); try { if (getCellValueAsString(qcResultCell) != null && !"NA".equals(getCellValueAsString(qcResultCell))) { sampleArray.add(Double.valueOf(getCellValueAsString(qcResultCell))); if (getCellValueAsString(qcPassedCell) != null) { if ("Y".equals(getCellValueAsString(qcPassedCell)) || "y".equals(getCellValueAsString(qcPassedCell))) { sampleArray.add("true"); } else if ("N".equals(getCellValueAsString(qcPassedCell)) || "n".equals(getCellValueAsString(qcPassedCell))) { sampleArray.add("false"); } } } else { sampleArray.add(""); sampleArray.add(""); } StringBuilder noteSB = new StringBuilder(); if (!isStringEmptyOrNull(getCellValueAsString(rinCell)) && !"NA".equals(getCellValueAsString(rinCell))) { noteSB.append("RIN:" + getCellValueAsString(rinCell) + ";"); } if (!isStringEmptyOrNull(getCellValueAsString(sample260280Cell))) { noteSB.append("260/280:" + getCellValueAsString(sample260280Cell) + ";"); } if (!isStringEmptyOrNull(getCellValueAsString(sample260230Cell))) { noteSB.append("260/230:" + getCellValueAsString(sample260230Cell) + ";"); } sampleArray.add(noteSB.toString()); } catch (NumberFormatException nfe) { throw new InputFormException("Supplied Sample QC concentration for sample '" + getCellValueAsString(sampleAliasCell) + "' is invalid", nfe); } jsonArray.add(sampleArray); } } return jsonArray; } else { throw new UnsupportedOperationException( "Cannot process bulk input files other than xls, xlsx, and ods."); } }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
public static JSONObject preProcessLibraryPoolSheetImport(File inPath, User u, SampleService sampleService) throws Exception { if (inPath.getName().endsWith(".xlsx")) { JSONObject jsonObject = new JSONObject(); JSONArray sampleArray = new JSONArray(); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inPath)); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow glrow = sheet.getRow(1); // process global headers XSSFCell pairedCell = glrow.getCell(0); jsonObject.put("paired", getCellValueAsString(pairedCell)); XSSFCell platformCell = glrow.getCell(1); if (getCellValueAsString(platformCell) != null) { jsonObject.put("platform", getCellValueAsString(platformCell)); } else {/*from w w w . j a v a 2s .c o m*/ throw new InputFormException( "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'"); } XSSFCell typeCell = glrow.getCell(2); if (getCellValueAsString(typeCell) != null) { String[] split = getCellValueAsString(typeCell).split("-"); String plat = split[0]; String type = split[1]; if (getCellValueAsString(platformCell).equals(plat)) { jsonObject.put("type", type); } else { throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell) + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'"); } } else { throw new InputFormException( "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'"); } XSSFCell selectionCell = glrow.getCell(3); if (getCellValueAsString(selectionCell) != null) { jsonObject.put("selection", getCellValueAsString(selectionCell)); } else { throw new InputFormException("Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'"); } XSSFCell strategyCell = glrow.getCell(4); if (getCellValueAsString(strategyCell) != null) { jsonObject.put("strategy", getCellValueAsString(strategyCell)); } else { throw new InputFormException( "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'"); } int rows = sheet.getPhysicalNumberOfRows(); for (int ri = 6; ri < rows; ri++) { JSONArray rowsJSONArray = new JSONArray(); XSSFRow row = sheet.getRow(ri); XSSFCell sampleNameCell = row.getCell(2); XSSFCell sampleAliasCell = row.getCell(3); Sample s = null; if (getCellValueAsString(sampleAliasCell) != null) { String salias = getCellValueAsString(sampleAliasCell); Collection<Sample> ss = sampleService.getByAlias(salias); if (!ss.isEmpty()) { if (ss.size() == 1) { s = ss.iterator().next(); log.info("Got sample: " + s.getAlias()); } else { throw new InputFormException("Multiple samples retrieved with this alias: '" + salias + "'. Cannot process."); } } else { throw new InputFormException("No such sample '" + salias + "'in database. Samples need to be created before using the form input functionality"); } } else { log.info("Blank sample row found. Ending import."); break; } // sample OK - good to go if (s != null) { XSSFCell indexFamilyCell = row.getCell(9); XSSFCell indicesCell = row.getCell(10); XSSFCell libraryQubitCell = row.getCell(6); XSSFCell libraryQcInsertSizeCell = row.getCell(7); XSSFCell libraryQcMolarityCell = row.getCell(8); XSSFCell qcPassedCell = row.getCell(11); XSSFCell libraryDescriptionCell = row.getCell(12); XSSFCell wellCell = row.getCell(4); XSSFCell dilutionMolarityCell = row.getCell(16); XSSFCell poolNameCell = row.getCell(21); XSSFCell poolConvertedMolarityCell = row.getCell(20); rowsJSONArray.add(getCellValueAsString(sampleNameCell)); rowsJSONArray.add(getCellValueAsString(sampleAliasCell)); rowsJSONArray.add(getCellValueAsString(wellCell).replaceAll("\\s", "")); XSSFCell proceedKeyCell = row.getCell(22); String proceedKey = "A"; if (getCellValueAsString(proceedKeyCell) != null) { String proceedKeyString = getCellValueAsString(proceedKeyCell).toUpperCase() .replaceAll("\\s", ""); if ("L".equals(proceedKeyString)) { proceedKey = "L"; } else if ("U".equals(proceedKeyString)) { proceedKey = "U"; } else if ("P".equals(proceedKeyString)) { proceedKey = "P"; } } String libAlias = ""; Matcher mat = samplePattern.matcher(getCellValueAsString(sampleAliasCell)); if (mat.matches()) { String platePos = getCellValueAsString(wellCell); libAlias = mat.group(1) + "_" + "L" + mat.group(2) + "-" + platePos.toUpperCase() + "_" + mat.group(3); } rowsJSONArray.add(libAlias); if ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey)) { String libDesc = s.getDescription(); if (!isStringEmptyOrNull(getCellValueAsString(libraryDescriptionCell))) { libDesc = getCellValueAsString(libraryDescriptionCell); } rowsJSONArray.add(libDesc); } else { rowsJSONArray.add(""); } if (getCellValueAsString(libraryQubitCell) != null && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(libraryQubitCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(libraryQcInsertSizeCell) != null && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(libraryQcInsertSizeCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(libraryQcMolarityCell) != null && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(libraryQcMolarityCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(qcPassedCell) != null && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) { if ("Y".equals(getCellValueAsString(qcPassedCell)) || "y".equals(getCellValueAsString(qcPassedCell))) { rowsJSONArray.add("true"); } else if ("N".equals(getCellValueAsString(qcPassedCell)) || "n".equals(getCellValueAsString(qcPassedCell))) { rowsJSONArray.add("false"); } } else { rowsJSONArray.add(""); } if (getCellValueAsString(indexFamilyCell) != null && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(indexFamilyCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(indicesCell) != null && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(indicesCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(dilutionMolarityCell) != null && ("A".equals(proceedKey) || "P".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(dilutionMolarityCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(poolNameCell) != null && ("A".equals(proceedKey) || "P".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(poolNameCell)); } else { rowsJSONArray.add(""); } if (getCellValueAsString(poolConvertedMolarityCell) != null && ("A".equals(proceedKey) || "P".equals(proceedKey))) { rowsJSONArray.add(getCellValueAsString(poolConvertedMolarityCell)); } else { rowsJSONArray.add(""); } rowsJSONArray.add(proceedKey); if ("A".equals(proceedKey)) { rowsJSONArray.add("A: Import everything"); } else if ("L".equals(proceedKey)) { rowsJSONArray.add("L: Import and create library only"); } else if ("U".equals(proceedKey)) { rowsJSONArray.add("U: Updated the library info only"); } else if ("P".equals(proceedKey)) { rowsJSONArray.add("P: import the library dilution and pool based on the library info"); } } sampleArray.add(rowsJSONArray); } jsonObject.put("rows", sampleArray); return jsonObject; } else { throw new UnsupportedOperationException( "Cannot process bulk input files other than xls, xlsx, and ods."); } }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
private static List<Sample> processSampleInputXLSX(XSSFWorkbook wb, User u, SampleService sampleService, LibraryService libraryService, QualityControlService qcService, NamingScheme namingScheme, IndexService indexService) throws Exception { List<Sample> samples = new ArrayList<>(); XSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); XSSFRow glrow = sheet.getRow(1);//from www . jav a 2s . com // process global headers XSSFCell pairedCell = glrow.getCell(0); boolean paired; if (getCellValueAsString(pairedCell) != null) { paired = pairedCell.getBooleanCellValue(); log.info("Got paired: " + paired); } else { throw new InputFormException("'Paired' cell is empty. Please specify TRUE or FALSE."); } XSSFCell platformCell = glrow.getCell(1); PlatformType pt = null; if (getCellValueAsString(platformCell) != null) { pt = PlatformType.get(getCellValueAsString(platformCell)); } if (pt == null) { throw new InputFormException( "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'"); } else { log.info("Got platform type: " + pt.getKey()); } XSSFCell typeCell = glrow.getCell(2); LibraryType lt = null; if (getCellValueAsString(typeCell) != null) { String[] split = getCellValueAsString(typeCell).split("-"); String plat = split[0]; String type = split[1]; if (getCellValueAsString(platformCell).equals(plat)) { lt = libraryService.getLibraryTypeByDescriptionAndPlatform(type, pt); } else { throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell) + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'"); } } if (lt == null) { throw new InputFormException( "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'"); } else { log.info("Got library type: " + lt.getDescription()); } XSSFCell selectionCell = glrow.getCell(3); LibrarySelectionType ls = null; if (getCellValueAsString(selectionCell) != null) { ls = libraryService.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell)); } if (ls == null) { throw new InputFormException( "Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'"); } else { log.info("Got library selection type: " + ls.getName()); } XSSFCell strategyCell = glrow.getCell(4); LibraryStrategyType lst = null; if (getCellValueAsString(strategyCell) != null) { lst = libraryService.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell)); } if (lst == null) { throw new InputFormException( "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'"); } else { log.info("Got library strategy type: " + lst.getName()); } // process entries Map<String, Pool> pools = new HashMap<>(); for (int ri = 4; ri < rows; ri++) { XSSFRow row = sheet.getRow(ri); // cell defs XSSFCell sampleAliasCell = row.getCell(2); Sample s = null; if (getCellValueAsString(sampleAliasCell) != null) { String salias = getCellValueAsString(sampleAliasCell); Collection<Sample> ss = sampleService.getByAlias(salias); if (!ss.isEmpty()) { if (ss.size() == 1) { s = ss.iterator().next(); log.info("Got sample: " + s.getAlias()); } else { throw new InputFormException( "Multiple samples retrieved with this alias: '" + salias + "'. Cannot process."); } } else { throw new InputFormException("No such sample '" + salias + "'in database. Samples need to be created before using the form input functionality"); } } else { log.info("Blank sample row found. Ending import."); break; } // sample OK - good to go if (s != null) { String poolNumberCell = getCellValueAsString(row.getCell(3)); String sampleQcCell = getCellValueAsString(row.getCell(4)); String libraryDescriptionCell = getCellValueAsString(row.getCell(7)); String indexKitCell = getCellValueAsString(row.getCell(8)); String indexTagsCell = getCellValueAsString(row.getCell(9)); String libraryQcCell = getCellValueAsString(row.getCell(10)); String libraryQcInsertSizeCell = getCellValueAsString(row.getCell(11)); String libraryQcMolarityCell = getCellValueAsString(row.getCell(12)); String libraryQcPassFailCell = getCellValueAsString(row.getCell(13)); String dilutionMolarityCell = getCellValueAsString(row.getCell(17)); String poolConvertedMolarityCell = getCellValueAsString(row.getCell(22)); // add pool, if any processPool(poolNumberCell, poolConvertedMolarityCell, pools); processSampleQC(sampleQcCell, s, u, qcService); Library library = processLibrary(libraryQcCell, libraryDescriptionCell, libraryQcPassFailCell, s, pt, lt, ls, lst, paired, namingScheme); if (library != null) { processLibraryQC(libraryQcCell, libraryQcMolarityCell, libraryQcInsertSizeCell, library, u, qcService); processIndices(indexKitCell, indexTagsCell, library, indexService); processDilutions(dilutionMolarityCell, library, pools.get(poolNumberCell), u); log.info("Added library: " + library.toString()); s.addLibrary(library); } samples.add(s); } } return samples; }
From source file:Utilities.BatchInDJMSHelper.java
private void legendBuilder(XSSFWorkbook myWorkBook) { XSSFSheet mySheet = myWorkBook.getSheetAt(0); Row row1;/*from w w w . j ava2 s . c o m*/ Row row2; Row row3; Row row4; System.out.println("BatchInDJMSHelper.java: Line number in xlsx" + mySheet.getPhysicalNumberOfRows()); // Create a row and put some cells in it. if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y) { row1 = mySheet.getRow(GlobalVar.LEGEND_Y); } else { row1 = mySheet.createRow(GlobalVar.LEGEND_Y); } if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 1) { row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1); } else { row2 = mySheet.createRow(GlobalVar.LEGEND_Y + 1); } if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 2) { row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2); } else { row3 = mySheet.createRow(GlobalVar.LEGEND_Y + 2); } if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 3) { row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3); } else { row4 = mySheet.createRow(GlobalVar.LEGEND_Y + 3); } // Row row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1); // Row row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2); // Row row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3); int col1 = GlobalVar.LEGEND_X; int col2 = GlobalVar.LEGEND_X + 1; int col3 = GlobalVar.LEGEND_X + 2; int col4 = GlobalVar.LEGEND_X + 3; //// row 1 CellStyle style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.ETS_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Cell cell = row1.createCell(col1); cell.setCellValue("ETS"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.BAD_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col2); cell.setCellValue("Bad"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.OVERLAP_LV_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col3); cell.setCellValue("Overlap Lv"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.DUPLICATE_LV_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col4); cell.setCellValue("Duplicate Lv"); cell.setCellStyle(style); // case INPROCESSING_ERR: return INPROCESSING_COLOR; // case AFTER_PCS_ERR: return AFTER_PCS_COLOR; //// row 2 style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.WRONG_SSN_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col1); cell.setCellValue("Wrong SSN"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.DUPLICATE_CTRL_NUM_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col2); cell.setCellValue("Duplicate CtrlNum"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.INPROCESSING_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col3); cell.setCellValue("Inprocessing"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.AFTER_PCS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col4); cell.setCellValue("After PCS"); cell.setCellStyle(style); // row3 style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.IN_CYCLE_DUPLICATE_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col1); cell.setCellValue("In-cycle duplicates/Invalid first five"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.AUDITOR_DELETED_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col2); cell.setCellValue("Auditor deleted"); cell.setCellStyle(style); }
From source file:Wael.UI.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed //start if the button //aliases variables try {//from w w w . j a va 2 s . c o m if (jRadioButton1.isSelected()) { //Brocade Configuration variables int a; int b; int c; int d; int e; int f; File file = new File(jTextField2.getText() + "\\" + jTextField3.getText()); file.createNewFile(); FileWriter brocadefile = new FileWriter(file); FileInputStream fileinputstream = new FileInputStream(jTextField1.getText()); XSSFWorkbook workbook = new XSSFWorkbook(fileinputstream); XSSFSheet alisheet = workbook.getSheet("Aliases"); XSSFSheet zonesheet = workbook.getSheet("Zones"); XSSFSheet cfgsheet = workbook.getSheet("CFG"); b = alisheet.getPhysicalNumberOfRows(); d = zonesheet.getPhysicalNumberOfRows(); f = cfgsheet.getPhysicalNumberOfRows(); for (a = 0; a < b; a++) { XSSFRow rowa = alisheet.getRow(a); XSSFCell cellza = rowa.getCell((int) 0); XSSFCell cellzb = rowa.getCell((int) 1); if (cellza.getStringCellValue().equals("") || cellza.getStringCellValue() == null) { break; } brocadefile.write("alicreate" + " " + "\"" + cellza.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellzb.getStringCellValue().trim() + "\""); brocadefile.write("\n"); } brocadefile.write("\n"); brocadefile.write("\n"); //Start of zone creation for (c = 0; c < d; c++) { XSSFRow rowc = zonesheet.getRow(c); XSSFCell cellca = rowc.getCell((int) 0); XSSFCell cellcb = rowc.getCell((int) 1); XSSFCell cellcc = rowc.getCell((int) 2); if (cellca.getStringCellValue().equals("") || cellca.getStringCellValue() == null) { break; } //zonecreate "zonemame", "member1; member2" brocadefile.write("zonecreate" + " " + "\"" + cellca.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellcb.getStringCellValue().trim() + ";" + " " + cellcc.getStringCellValue().trim() + "\""); brocadefile.write("\n"); } brocadefile.write("\n"); brocadefile.write("\n"); XSSFRow rowc = cfgsheet.getRow(0); XSSFCell cellcfgcreateA = rowc.getCell((int) 0); XSSFCell cellcfgcreateB = rowc.getCell((int) 1); brocadefile.write("cfgcreate" + " " + "\"" + cellcfgcreateB.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellcfgcreateA.getStringCellValue().trim() + "\""); brocadefile.write("\n"); brocadefile.write("\n"); for (e = 1; e < f; e++) { XSSFRow rowe = cfgsheet.getRow(e); XSSFCell cellea = rowe.getCell((int) 0); XSSFCell celleb = rowe.getCell((int) 1); if (cellea.getStringCellValue().equals("") || cellea.getStringCellValue() == null) { break; } brocadefile.write("cfgadd" + " " + "\"" + celleb.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellea.getStringCellValue().trim() + "\""); brocadefile.write("\n"); } brocadefile.write("\n"); brocadefile.write("\n"); brocadefile.close(); } //Start of Cisco Configuration else if (jRadioButton2.isSelected()) { //Cisco Configuration variables int g; int h; int i; int j; int k; int l; File file = new File(jTextField2.getText() + "\\" + jTextField3.getText()); file.createNewFile(); FileWriter ciscofile = new FileWriter(file); FileInputStream fileinputstream = new FileInputStream(jTextField1.getText()); XSSFWorkbook ciscoworkbook = new XSSFWorkbook(fileinputstream); XSSFSheet alisheet = ciscoworkbook.getSheet("Aliases"); XSSFSheet zonesheet = ciscoworkbook.getSheet("Zones"); XSSFSheet cfgsheet = ciscoworkbook.getSheet("CFG"); h = alisheet.getPhysicalNumberOfRows(); j = zonesheet.getPhysicalNumberOfRows(); l = cfgsheet.getPhysicalNumberOfRows(); ciscofile.write("config t"); ciscofile.write("\n"); ciscofile.write("\n"); //create aliases for Cisco switch for (g = 0; g < h; g++) { XSSFRow rowg = alisheet.getRow(g); XSSFCell cellga = rowg.getCell((int) 0); XSSFCell cellgb = rowg.getCell((int) 1); XSSFCell cellgc = rowg.getCell((int) 2); //config t //fcalias name CX4240_21_SPA vsan 3 //member pwwn 50:06:01:60:46:e0:0f:ba //exit if (cellga.getStringCellValue().equals("") || cellga.getStringCellValue() == null) { break; } ciscofile.write("fcalias name" + " " + cellga.getStringCellValue().trim() + " " + cellgc.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("member pwwn" + " " + cellgb.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("exit"); ciscofile.write("\n"); ciscofile.write("\n"); } ciscofile.write("\n"); //create zones for cisco switch for (i = 0; i < j; i++) { XSSFRow rowi = zonesheet.getRow(i); XSSFCell cellia = rowi.getCell((int) 0); XSSFCell cellib = rowi.getCell((int) 1); XSSFCell cellic = rowi.getCell((int) 2); XSSFCell cellid = rowi.getCell((int) 3); //zone name U52P1_13 vsan 3 //member fcalias CX4240_21_SPA //member fcalias Unix1_52_P1 //exit if (cellia.getStringCellValue().equals("") || cellia.getStringCellValue() == null) { break; } ciscofile.write("zone name" + " " + cellia.getStringCellValue().trim() + " " + cellid.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("member fcalias" + " " + cellib.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("member fcalias" + " " + cellic.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("exit"); ciscofile.write("\n"); ciscofile.write("\n"); } ciscofile.write("\n"); ciscofile.write("\n"); //create zoneset and add it to a VSAN // XSSFRow rowmain = cfgsheet.getRow(1); XSSFCell cellcfgname = rowmain.getCell((int) 1); XSSFCell cellvsanname = rowmain.getCell((int) 2); //zoneset name main_config vsan 3 ciscofile.write("zoneset name" + " " + cellcfgname.getStringCellValue().trim() + " " + cellvsanname.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("\n"); for (k = 0; k < l; k++) { XSSFRow rowk = cfgsheet.getRow(k); XSSFCell cellka = rowk.getCell((int) 0); if (cellka.getStringCellValue().equals("") || cellka.getStringCellValue() == null) { break; } //member L51P2_14 ciscofile.write("member" + " " + cellka.getStringCellValue().trim()); ciscofile.write("\n"); } ciscofile.write("\n"); XSSFRow rowend = cfgsheet.getRow(1); XSSFCell cellendb = rowend.getCell((int) 1); XSSFCell cellendc = rowend.getCell((int) 2); //zoneset activate name main_config vsan 3 ciscofile.write("zoneset activate name" + " " + cellendb.getStringCellValue().trim() + " " + cellendc.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("\n"); ciscofile.close(); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:WeeklyOPD.ReadWeeklyTarget.java
public static void main(String args[]) { /******************************* TO UPDATE ON EACH RUN ***************************/ targetFile = "May 2014.xlsx"; weekNumber = 1;/*from www . j av a 2 s .c o m*/ femaleRowNum = 1212; childRowNum = 749; allRowNum = 7414; crNo = 187394;//crNo to begin with /*********************************************************************************/ rowCnt = 1; GenerateDailyNewOldExcelPickingRowsSequentially.mainCreateExcelAndInitialize(); try { FileInputStream targetFileIn = new FileInputStream(path + targetFile); XSSFWorkbook workbookTarget = new XSSFWorkbook(targetFileIn); XSSFSheet sheetTarget = workbookTarget.getSheetAt(0); out = new FileOutputStream(new File(path + "Week" + weekNumber + targetFile)); int startAtRow = weekNumber == 1 ? 4 : weekNumber == 2 ? 11 : weekNumber == 3 ? 18 : weekNumber == 4 ? 25 : 32; int rowsToRead = 7; System.out.println("Physical No. Of Rows: " + sheetTarget.getPhysicalNumberOfRows()); for (int rowNum = startAtRow; rowNum <= startAtRow + rowsToRead - 1 && rowNum < sheetTarget.getPhysicalNumberOfRows(); rowNum++) { System.out.println("RowNum: " + rowNum); Row row = sheetTarget.getRow(rowNum); // int date = (int) row.getCell(0).getNumericCellValue(); //// String[] splitDate = date.split("/"); // System.out.println (date); //// System.out.println (splitDate.length); // int day = Integer.parseInt(splitDate[0]); // int month = Integer.parseInt(splitDate[1]); // int year = Integer.parseInt(splitDate[2]); int medicineNew = (int) row.getCell(1).getNumericCellValue(); int surgeryNew = (int) row.getCell(4).getNumericCellValue(); int ophthalmologyNew = (int) row.getCell(7).getNumericCellValue(); int entNew = (int) row.getCell(10).getNumericCellValue(); int paediatricsNew = (int) row.getCell(13).getNumericCellValue(); int ogNew = (int) row.getCell(16).getNumericCellValue(); int orthopaedicsNew = (int) row.getCell(19).getNumericCellValue(); int dentalNew = (int) row.getCell(22).getNumericCellValue(); int casualtyNew = (int) row.getCell(25).getNumericCellValue(); int medicineOld = (int) row.getCell(2).getNumericCellValue(); int surgeryOld = (int) row.getCell(5).getNumericCellValue(); int ophthalmologyOld = (int) row.getCell(8).getNumericCellValue(); int entOld = (int) row.getCell(11).getNumericCellValue(); int paediatricsOld = (int) row.getCell(14).getNumericCellValue(); int ogOld = (int) row.getCell(17).getNumericCellValue(); int orthopaedicsOld = (int) row.getCell(20).getNumericCellValue(); int dentalOld = (int) row.getCell(23).getNumericCellValue(); System.out.println(medicineNew + "\t" + medicineOld + "\t" + surgeryOld + "\t" + surgeryNew + "\t" + surgeryOld + "\t" + surgeryNew + "\t" + ophthalmologyOld + "\t" + ophthalmologyNew + "\t" + entOld + "\t" + entNew + "\t" + paediatricsOld + "\t" + paediatricsNew + "\t" + ogOld + "\t" + ogNew + "\t" + orthopaedicsOld + "\t" + orthopaedicsNew + "\t" + dentalOld + "\t" + dentalNew + "\t" + casualtyNew); GenerateDailyNewOldExcelPickingRowsSequentially.mainGenerateExcel(medicineNew, medicineOld, surgeryNew, surgeryOld, ophthalmologyNew, ophthalmologyOld, entNew, entOld, paediatricsNew, paediatricsOld, ogNew, ogOld, orthopaedicsNew, orthopaedicsOld, dentalNew, dentalOld, casualtyNew); } workbook.write(out); GenerateDailyNewOldExcelPickingRowsSequentially.writeExcelAndcloseFiles(); System.out.println("Week " + weekNumber + " Excel generated successfully!"); System.out.println("New Row Numbers to start with:"); System.out.println("All: " + allRowNum + "\tFemale: " + femaleRowNum + "\tChild: " + childRowNum); System.out.println("New CrNo. to start with: " + crNo); } catch (Exception e) { System.err.println("Error reading target!"); e.printStackTrace(); } }
From source file:xqt.adapters.csv.test.ExcelTest.java
public void read() { try {//from www . j a v a 2s . c o m FileInputStream file = new FileInputStream(new File("C:\\Users\\standard\\Downloads\\javaTest.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheet("Sheet1"); //workbook.getSheet("test"); // Stream stream = StreamSupport.stream(sheet.spliterator(), false); // maybe it is better to have a limited size stream by passing the sheet.getPhysicalNumberOfRows() Stream<Row> stream = StreamSupport.stream(Spliterators.spliterator(sheet.iterator(), sheet.getPhysicalNumberOfRows(), Spliterator.ORDERED), false); //long cnt = stream.count(); stream = stream.skip(1); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Iterable<Row> iterable = () -> sheet.iterator(); List<Entity> result = stream.filter(row -> (!row.getZeroHeight())) .map(row -> RowBuilder.createRowArray(row, evaluator)).map(rowArray -> new Entity(rowArray)) .peek(p -> { System.out.println(""); }).collect(Collectors.toList()); long count = result.stream().count(); System.out.println("Total records: " + count); file.close(); } catch (Exception e) { e.printStackTrace(); } }