Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow.

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:tubessc.Dataset.java

public void addDataSetTrainingExcel(String InputFile, int numOfInput) throws IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i <= rowEnd; i++) {
        double price[] = new double[numOfInput];
        double target = 0;
        if ((i + numOfInput) <= rowEnd) {
            for (int j = 0; j <= numOfInput; j++) {
                Row row = sheet.getRow(i + j);
                if (j != numOfInput) {
                    Cell cell = row.getCell(0);
                    price[j] = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                } else {
                    Cell cell = row.getCell(0);
                    target = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                }/* ww w .j a v  a  2 s. c  om*/
            }
            GoldPrice gp = new GoldPrice(price, target);
            dataSetTraining.add(gp);
        }
    }
    file.close();

}

From source file:tubessc.Dataset.java

public void addDataSetTestingExcel(String InputFile, int numOfInput) throws IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i < rowEnd; i++) {
        double price[] = new double[numOfInput];
        double target = 0;
        if ((i + numOfInput) <= rowEnd) {
            for (int j = 0; j <= numOfInput; j++) {
                Row row = sheet.getRow(i + j);
                if (j != numOfInput) {
                    Cell cell = row.getCell(0);
                    price[j] = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                } else {
                    Cell cell = row.getCell(0);
                    target = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                }/*from w w w.j  a  va2s .c  om*/
            }
            GoldPrice gp = new GoldPrice(price, target);
            dataSetTesting.add(gp);
        }
    }
    file.close();

}

From source file:tubessc.Dataset.java

public void calculateFluctuation(String InputFile, String OutputFile)
        throws FileNotFoundException, IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFWorkbook output = new XSSFWorkbook();
    XSSFSheet sheetOutput = output.createSheet("new sheet");
    FileOutputStream fileOut = new FileOutputStream(OutputFile);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i <= rowEnd - 1; i++) {
        Row rowIn1 = sheet.getRow(i);
        Cell cellIn1 = rowIn1.getCell(0);
        Row rowIn2 = sheet.getRow(i + 1);
        Cell cellIn2 = rowIn2.getCell(0);
        double value1 = Double.parseDouble(String.valueOf(cellIn1.getNumericCellValue()));
        double value2 = Double.parseDouble(String.valueOf(cellIn2.getNumericCellValue()));
        Row rowOut = sheetOutput.createRow(i);
        Cell cellOut = rowOut.createCell(0);
        cellOut.setCellValue(value2 - value1);
    }/*from  w ww. ja  v  a 2s.  co  m*/
    output.write(fileOut);
    fileOut.close();
}

From source file:tubessc.Dataset.java

public void normalization(String InputFile, String outputFile, double minValue, double maxValue)
        throws FileNotFoundException, IOException {
    this.minValue = minValue;
    this.maxValue = maxValue;
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFWorkbook output = new XSSFWorkbook();
    XSSFSheet sheetOutput = output.createSheet("new sheet");
    FileOutputStream fileOut = new FileOutputStream(outputFile);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    Row row = sheet.getRow(rowStart);
    Cell cell = row.getCell(0);//from w ww  .j  av  a 2 s  .  c om
    max = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
    min = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
    for (int i = rowStart + 1; i <= rowEnd; i++) {
        row = sheet.getRow(i);
        cell = row.getCell(0);
        double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
        if (value > max) {
            max = value;
        }
        if (value < min) {
            min = value;
        }
    }
    for (int i = rowStart; i <= rowEnd; i++) {
        row = sheet.getRow(i);
        cell = row.getCell(0);
        double value = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
        double newValue = minValue + ((value - min) * (maxValue - minValue) / (max - min));
        Row rowOut = sheetOutput.createRow(i);
        Cell cellOut = rowOut.createCell(0);
        cellOut.setCellValue(newValue);
    }
    output.write(fileOut);
    fileOut.close();
}

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);

    //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;//  w  w w . ja v a 2 s  . c  om
    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);

    //process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired;
    if (getCellValueAsString(pairedCell) != null) {
        paired = pairedCell.getBooleanCellValue();
        log.info("Got paired: " + paired);
    } else {/*from   w ww  .j av a 2s  .  c  o m*/
        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 void createLibraryPoolExportFormFromWeb(File outpath, JSONArray jsonArray, String indexFamily)
        throws Exception {
    InputStream in = null;//  w w w.  jav  a  2  s  . c  om
    in = FormUtils.class.getResourceAsStream("/forms/ods/export_libraries_pools.xlsx");
    if (in != null) {
        XSSFWorkbook oDoc = new XSSFWorkbook(in);

        XSSFSheet sheet = oDoc.getSheet("library_pool_export");
        FileOutputStream fileOut = new FileOutputStream(outpath);
        XSSFRow row2 = sheet.getRow(1);

        int i = 6;
        for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) {
            if ("paired".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellA = row2.createCell(0);
                row2cellA.setCellValue(jsonObject.getString("value"));
            } else if ("platform".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellB = row2.createCell(1);
                row2cellB.setCellValue(jsonObject.getString("value"));
            } else if ("type".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellC = row2.createCell(2);
                row2cellC.setCellValue(jsonObject.getString("value"));
            } else if ("selection".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellD = row2.createCell(3);
                row2cellD.setCellValue(jsonObject.getString("value"));
            } else if ("strategy".equals(jsonObject.getString("name"))) {
                XSSFCell row2cellE = row2.createCell(4);
                row2cellE.setCellValue(jsonObject.getString("value"));
            }
            if ("sampleinwell".equals(jsonObject.getString("name"))) {
                String sampleinwell = jsonObject.getString("value");
                // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA"
                String sampleId = sampleinwell.split(":")[0];
                String wellId = sampleinwell.split(":")[1];
                String sampleAlias = sampleinwell.split(":")[2];
                String projectName = sampleinwell.split(":")[3];
                String projectAlias = sampleinwell.split(":")[4];
                XSSFRow row = sheet.createRow(i);
                XSSFCell cellA = row.createCell(0);
                cellA.setCellValue(projectName);
                XSSFCell cellB = row.createCell(1);
                cellB.setCellValue(projectAlias);
                XSSFCell cellC = row.createCell(2);
                cellC.setCellValue(sampleId);
                XSSFCell cellD = row.createCell(3);
                cellD.setCellValue(sampleAlias);
                XSSFCell cellE = row.createCell(4);
                cellE.setCellValue(wellId);
                if (indexFamily != null) {
                    XSSFCell cellJ = row.createCell(9);
                    cellJ.setCellValue(indexFamily);
                }
                i++;
            }
        }
        oDoc.write(fileOut);
        fileOut.close();
    } else {
        throw new IOException("Could not read from resource.");
    }

}

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;//ww  w.j  a  v a  2  s  .  c om
            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 ww w  .j a  v  a 2s  .  com*/
            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);

    // process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired;
    if (getCellValueAsString(pairedCell) != null) {
        paired = pairedCell.getBooleanCellValue();
        log.info("Got paired: " + paired);
    } else {/*  w  w  w  .  ja v a 2  s  . c om*/
        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;
}