List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell
@Override public XSSFCell getCell(int cellnum)
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);/*from w w w .j av a2s .c o m*/ int rows = sheet.getPhysicalNumberOfRows(); XSSFRow glrow = sheet.getRow(1); //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);/*from w w w.ja v a 2 s . c o m*/ int rows = sheet.getPhysicalNumberOfRows(); XSSFRow glrow = sheet.getRow(1); //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 w ww . j a va 2 s .c o m 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 ww .ja v a 2 s . co 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);/*from w w w. j a v a 2 s . com*/ int rows = sheet.getPhysicalNumberOfRows(); XSSFRow glrow = sheet.getRow(1); // 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:uk.ac.manchester.cs.owl.semspreadsheets.model.xssf.impl.WorkbookXSSFImpl.java
License:Open Source License
public void visit(SetCellValue setCellValue) { XSSFSheet xssfSheet = workbook.getSheet(setCellValue.getSheet().getName()); XSSFRow xssfRow = xssfSheet.getRow(setCellValue.getRow()); if (xssfRow == null && setCellValue.getNewValue() != null) { xssfRow = xssfSheet.createRow(setCellValue.getRow()); }/* w w w. j av a 2 s . c o m*/ XSSFCell xssfCell = xssfRow.getCell(setCellValue.getCol()); if (xssfCell == null && setCellValue.getNewValue() != null) { xssfCell = xssfRow.createCell(setCellValue.getCol()); } if (xssfCell != null) { if (setCellValue.getNewValue() != null) { xssfCell.setCellValue(new XSSFRichTextString(setCellValue.getNewValue().toString())); } else { xssfRow.removeCell(xssfCell); } } }
From source file:uscraper.UScraper.java
public static void CareerBuilderScraper(String path, int totalPage, String[] states) { setLoggerOff();/* w w w. j ava 2s . c o m*/ try { outstream = new FileOutputStream(new File(path)); workbook = new XSSFWorkbook(); sheet = workbook.createSheet("CareerBuilder"); webClient = new WebClient(); webClient.getOptions().setJavaScriptEnabled(false); webClient.getOptions().setTimeout(86400000);// 1 day page = webClient.getPage("http://www.careerbuilder.com/"); HtmlForm form = page.getFirstByXPath("//form[@action='http://www.careerbuilder.com/jobs']"); HtmlElement button = (HtmlElement) page.createElement("button"); button.setAttribute("type", "submit"); form.appendChild(button); HtmlTextInput what = (HtmlTextInput) page.getElementByName("keywords"); HtmlTextInput where = (HtmlTextInput) page.getElementByName("location"); what.setValueAttribute(""); for (String state : states) { where.setValueAttribute(state); //where.setValueAttribute("Connecticut"); //System.out.println(page.asXml());System.exit(0); page = button.click(); webClient.waitForBackgroundJavaScript(1000); int pageNum = 1; int rowNum = 0; while (pageNum <= totalPage) { List<?> aTitle = page.getByXPath("//div[@class='job-row']/div/div/h2/a"); for (int i = 0; i < aTitle.size(); i++) { HtmlAnchor a = (HtmlAnchor) aTitle.get(i); XSSFRow row = (XSSFRow) sheet.createRow(rowNum); XSSFCell cellSource = row.createCell(0); cellSource.setCellValue("CareerBuilder"); XSSFCell cellTitle = row.createCell(1); cellTitle.setCellValue(a.asText()); HtmlPage descriptionPage = a.click(); webClient.waitForBackgroundJavaScript(1000); HtmlDivision div = (HtmlDivision) descriptionPage.getByXPath("//div[@class='description']") .get(0); XSSFCell cellDesc = row.createCell(8); cellDesc.setCellValue(div.asText()); rowNum++; } webClient.waitForBackgroundJavaScript(1000); List<?> hCompany = page.getByXPath( "//div[@class='job-row']/div/div[@class='columns large-2 medium-3 small-12']/h4"); rowNum = 0; for (int i = 0; i < hCompany.size(); i++) { HtmlElement e = (HtmlElement) hCompany.get(i); XSSFRow row = (XSSFRow) sheet.getRow(rowNum); XSSFCell cellCompany = row.createCell(2); cellCompany.setCellValue(e.asText()); rowNum++; } webClient.waitForBackgroundJavaScript(1000); List<?> hLocation = page.getByXPath( "//div[@class='job-row']/div[@class='row job-information']/div[@class='columns end large-2 medium-3 small-12']/h4[@class='job-text']"); rowNum = 0; for (int i = 0; i < hLocation.size(); i++) { HtmlElement e = (HtmlElement) hLocation.get(i); XSSFRow row = (XSSFRow) sheet.getRow(rowNum); XSSFCell cellLocation = row.createCell(3); cellLocation.setCellValue(e.asText()); System.out.println(row.getCell(3)); rowNum++; } webClient.waitForBackgroundJavaScript(1000); List<?> hInfo = page .getByXPath("//div[@class='job-row']/div/div[@class='columns medium-6 large-8']/h4"); rowNum = 0; for (int i = 0; i < hInfo.size(); i++) { HtmlElement e = (HtmlElement) hInfo.get(i); XSSFRow row = (XSSFRow) sheet.getRow(rowNum); XSSFCell cellInfo = row.createCell(4); cellInfo.setCellValue(e.asText()); rowNum++; } webClient.waitForBackgroundJavaScript(1000); List<?> emElapsedTime = page .getByXPath("//div[@class='job-row']/div/div/div[@class='show-for-medium-up']/em"); rowNum = 0; for (int i = 0; i < emElapsedTime.size(); i++) { HtmlElement e = (HtmlElement) emElapsedTime.get(i); XSSFRow row = (XSSFRow) sheet.getRow(rowNum); XSSFCell cellElapsedTime = row.createCell(5); cellElapsedTime.setCellValue(e.asText()); String timeStamp = new SimpleDateFormat("yyyyMMdd_HHmmss") .format(Calendar.getInstance().getTime()); XSSFCell cellDateTime = row.createCell(6); cellDateTime.setCellValue(timeStamp); XSSFCell cellState = row.createCell(7); cellState.setCellValue(state); rowNum++; } HtmlAnchor next = (HtmlAnchor) page.getElementById("next-button"); next.click(); pageNum++; } } workbook.write(outstream); } catch (Exception e) { e.printStackTrace(); } finally { try { if (workbook != null) workbook.close(); if (outstream != null) outstream.close(); } catch (Exception e) { e.printStackTrace(); } } return; }
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 {// www . j a v a 2 s.co 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(); } }