Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:tools.parsing.ExcelReader.java

public void readExcel() {
    try {/*w ww .j ava2 s . c  o m*/
        FileInputStream file = new FileInputStream(new File(this.filePath));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            Comment comment = new Comment();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (cell.getColumnIndex() == 0)
                        comment.setTopic(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 1)
                        comment.setCode(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 2)
                        comment.setAuthor(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 3)
                        comment.setDate(cell.getNumericCellValue() + "");
                    else
                        comment.setComment(cell.getNumericCellValue() + "");
                    break;
                case Cell.CELL_TYPE_STRING:
                    if (cell.getColumnIndex() == 0)
                        comment.setTopic(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 1)
                        comment.setCode(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 2)
                        comment.setAuthor(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 3)
                        comment.setDate(cell.getStringCellValue());
                    else
                        comment.setComment(cell.getStringCellValue());
                    break;
                }
            }
            this.comments.add(comment);
        }
        file.close();
    } catch (IOException e) {
        Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, e);
    }
}

From source file:Tools.PostProcessing.java

private Map<String, Integer> generateXLSXforS1(String XLSXfileName) throws FileNotFoundException, IOException {
    Map<String, Integer> statusMap = null;
    File myFile = new File(XLSXfileName);

    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    //Return first sheet from the XLSX workbook
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    List<String> errorList = new ArrayList<>();
    Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
    //Get iterator to all the rows in current sheet
    Iterator<Row> rowIterator = mySheet.iterator();
    int lineCount = 1;
    // Traversing over each row of XLSX file
    if (rowIterator.hasNext()) {

        Row headerRow = rowIterator.next(); //skip the header row
        Iterator<Cell> it = headerRow.cellIterator();
        int numCell = 0;
        List<String> keyList = new ArrayList<String>(); //keep track info of each column

        while (it.hasNext()) {
            keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
            numCell++;/*from  w  w  w. j a  va  2s .c  o  m*/
        }
        if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
            // System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
            //                int globalCount = 1;
            DataFormatter df = new DataFormatter();
            statusMap = new TreeMap<>();
            while (rowIterator.hasNext()) {
                lineCount++;
                Row row = rowIterator.next();
                Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2);
                String fullSSN = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell));
                colorDeletedLeaves(fullSSN, ctrlNumCell, myWorkBook, statusMap);
            }
            // output to a new xlsx file
            fis.close();
            FileOutputStream output;
            String targetFile = null;
            if (XLSXfileName.contains(".xlsx")) {
                targetFile = XLSXfileName.replace(".xlsx", "_forS1.xlsx");
            } else {
                targetFile = XLSXfileName + "_forS1.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();

        } else {
            JOptionPane.showMessageDialog(null, "XLSX file format is incorrect! Must be full SSN format");
        }
    }
    return statusMap;
}

From source file:Tools.PreProcessing.java

private List<String> readXlsxFile(String xlsxFileName) throws IOException {
    File myFile = new File(xlsxFileName);
    List<String> list = new ArrayList<>(); //store ctrlNum + GlobalVar.PRE_PROC_KEY_SYMBOL + fullSSN that printed on the pdf file
    try {// w  w w  . j  a  v a 2s. co  m
        FileInputStream fis = new FileInputStream(myFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        List<String> errorList = new ArrayList<>();
        Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        int lineCount = 1;
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {

            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;
            List<String> keyList = new ArrayList<String>(); //keep track info of each column

            while (it.hasNext()) {
                keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
                System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
                int globalCount = 1;
                while (rowIterator.hasNext()) {
                    //int cellCount = 0;             
                    lineCount++;

                    Row row = rowIterator.next();
                    Cell cell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                    String ctrlNumString = cell.getStringCellValue();
                    if (!ctrlNumString.equalsIgnoreCase("")) { //skip empty lines
                        Iterator<Cell> cellIterator = row.cellIterator();
                        Map<String, String> rowContainer = new HashMap<>(); //store info of each row                                       
                        rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer

                        //    public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", 
                        //            "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area",
                        //        "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"};
                        //      make sure the key mataches the name in the header                        
                        String ctrlNum = GlobalVar
                                .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2)));
                        //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); 
                        if (ctrlNum == null) {
                            JOptionPane.showMessageDialog(null,
                                    "Line " + lineCount + ": Invalid ctrl number received.");
                        }
                        // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2));
                        String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn                                
                        list.add(ctrlNum + GlobalVar.PRE_PROC_KEY_SYMBOL + fullSSN);

                    }
                }
                fis.close();

            } else {

                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        // finds the work book in stance for XLSX file

    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!");
        Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    return list;
}

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()));
                }/*from   w  w  w.  java  2 s .  c  o m*/
            }
            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()));
                }/*w  w w. j  a  va2 s.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);/*from ww w .  j a v a2  s  .  c om*/
        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);
    }
    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);//  w w w. ja v  a2  s.  c  om
    Cell cell = row.getCell(0);
    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);/*from  w w w . j  a v  a  2s .  co m*/

    //process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired = pairedCell.getBooleanCellValue();
    log.info("Got paired: " + paired);

    XSSFCell platformCell = glrow.getCell(1);
    PlatformType pt = null;
    if (getCellValueAsString(platformCell) != null) {
        pt = PlatformType.get(getCellValueAsString(platformCell));
    }
    if (pt == null) {
        throw new InputFormException(
                "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'");
    } else {
        log.info("Got platform type: " + pt.getKey());
    }

    XSSFCell typeCell = glrow.getCell(2);
    LibraryType lt = null;
    if (getCellValueAsString(typeCell) != null) {
        String[] split = getCellValueAsString(typeCell).split("-");
        String plat = split[0];
        String type = split[1];
        if (getCellValueAsString(platformCell).equals(plat)) {
            lt = manager.getLibraryTypeByDescriptionAndPlatform(type, pt);
        } else {
            throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell)
                    + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'");
        }
    }
    if (lt == null) {
        throw new InputFormException(
                "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'");
    } else {
        log.info("Got library type: " + lt.getDescription());
    }

    XSSFCell selectionCell = glrow.getCell(3);
    LibrarySelectionType ls = null;
    if (getCellValueAsString(selectionCell) != null) {
        ls = manager.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell));
    }
    if (ls == null) {
        throw new InputFormException(
                "Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'");
    } else {
        log.info("Got library selection type: " + ls.getName());
    }

    XSSFCell strategyCell = glrow.getCell(4);
    LibraryStrategyType lst = null;
    if (getCellValueAsString(strategyCell) != null) {
        lst = manager.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell));
    }
    if (lst == null) {
        throw new InputFormException(
                "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'");
    } else {
        log.info("Got library strategy type: " + lst.getName());
    }

    XSSFCell plateBarcodeCell = glrow.getCell(5);
    String plateBarcode = null;
    if (getCellValueAsString(plateBarcodeCell) != null) {
        plateBarcode = getCellValueAsString(plateBarcodeCell);
    }
    if (plateBarcode == null) {
        throw new InputFormException(
                "Cannot resolve plate barcode from: '" + getCellValueAsString(plateBarcodeCell) + "'");
    } else {
        log.info("Got plate barcode: " + plateBarcode);
    }

    //process entries
    Simple384WellPlate libraryPlate = null;
    //Map<String, Pool<Plate<LinkedList<Library>, Library>>> pools = new HashMap<String, Pool<Plate<LinkedList<Library>, Library>>>();
    Map<String, PlatePool> pools = new HashMap<String, PlatePool>();
    for (int ri = 4; ri < rows; ri++) {
        XSSFRow row = sheet.getRow(ri);

        // Ax - plate position
        XSSFCell platePosCell = row.getCell(0);
        String platePos = getCellValueAsString(platePosCell);
        if (platePos != null && libraryPlate == null) {
            //plated libraries - process as plate
            libraryPlate = new Simple384WellPlate();
            libraryPlate.setIdentificationBarcode(plateBarcode);
            libraryPlate.setCreationDate(new Date());
        }

        //cell defs
        XSSFCell sampleAliasCell = row.getCell(2);

        Sample s = null;
        if (getCellValueAsString(sampleAliasCell) != null) {
            String salias = getCellValueAsString(sampleAliasCell);
            Collection<Sample> ss = manager.listSamplesByAlias(salias);
            if (!ss.isEmpty()) {
                if (ss.size() == 1) {
                    s = ss.iterator().next();
                    log.info("Got sample: " + s.getAlias());
                } else {
                    throw new InputFormException(
                            "Multiple samples retrieved with this alias: '" + salias + "'. Cannot process.");
                }
            } else {
                throw new InputFormException("No such sample '" + salias
                        + "'in database. Samples need to be created before using the form input functionality");
            }
        } else {
            log.info("Blank sample row found. Ending import.");
            break;
        }

        //sample OK - good to go
        if (s != null) {
            XSSFCell entityIDCell = row.getCell(2);
            XSSFCell poolNumberCell = row.getCell(3);
            XSSFCell sampleQcCell = row.getCell(4);
            //XSSFCell sampleAmountCell = row.getCell(5);
            //XSSFCell sampleWaterAmountCell = row.getCell(6);
            XSSFCell libraryDescriptionCell = row.getCell(7);
            XSSFCell barcodeKitCell = row.getCell(8);
            XSSFCell barcodeTagsCell = row.getCell(9);
            XSSFCell libraryQcCell = row.getCell(10);
            XSSFCell libraryQcInsertSizeCell = row.getCell(11);
            XSSFCell libraryQcMolarityCell = row.getCell(12);
            XSSFCell libraryQcPassFailCell = row.getCell(13);
            //XSSFCell libraryAmountCell = row.getCell(14);
            //XSSFCell libraryWaterAmountCell = row.getCell(15);
            //XSSFCell dilutionQcCell = row.getCell(16);
            XSSFCell dilutionMolarityCell = row.getCell(17);
            //XSSFCell dilutionAmountCell = row.getCell(18);
            //XSSFCell dilutionWaterAmountCell = row.getCell(19);
            XSSFCell poolQcCell = row.getCell(20);
            //XSSFCell poolAverageInsertSizeCell = row.getCell(21);
            XSSFCell poolConvertedMolarityCell = row.getCell(22);

            //add pool, if any
            if (getCellValueAsString(poolNumberCell) != null) {
                String poolNum = getCellValueAsString(poolNumberCell);
                if (!pools.containsKey(poolNum)) {
                    PlatePool pool = new PlatePool();
                    pool.setAlias(poolNum);
                    pool.setPlatformType(pt);
                    pool.setReadyToRun(true);
                    pool.setCreationDate(new Date());
                    pools.put(poolNum, pool);
                    log.info("Added pool: " + poolNum);
                    manager.savePool(pool);
                }
            }

            //process sample QC
            if (getCellValueAsString(sampleQcCell) != null) {
                try {
                    SampleQC sqc = new SampleQCImpl();
                    sqc.setSample(s);
                    sqc.setResults(Double.valueOf(getCellValueAsString(sampleQcCell)));
                    sqc.setQcCreator(u.getLoginName());
                    sqc.setQcDate(new Date());
                    if (manager.getSampleQcTypeByName("Picogreen") != null) {
                        sqc.setQcType(manager.getSampleQcTypeByName("Picogreen"));
                    } else {
                        sqc.setQcType(manager.getSampleQcTypeByName("QuBit"));
                    }
                    if (!s.getSampleQCs().contains(sqc)) {
                        s.addQc(sqc);
                        manager.saveSampleQC(sqc);
                        manager.saveSample(s);
                        log.info("Added sample QC: " + sqc.toString());
                    }
                } catch (NumberFormatException nfe) {
                    throw new InputFormException("Supplied Sample QC concentration for sample '"
                            + getCellValueAsString(sampleAliasCell) + "' is invalid", nfe);
                }
            }

            //if (getCellValueAsString(libraryQcCell) != null) {

            if (getCellValueAsString(barcodeKitCell) != null && getCellValueAsString(barcodeTagsCell) != null) {
                //create library
                Library library = new LibraryImpl();
                library.setSample(s);

                Matcher mat = samplePattern.matcher(s.getAlias());
                if (mat.matches()) {
                    String libAlias = plateBarcode + "_" + "L" + mat.group(2) + "-" + platePos + "_"
                            + getCellValueAsString(entityIDCell);
                    //String libAlias = libraryNamingScheme.generateNameFor("alias", library);
                    //library.setAlias(libAlias);

                    library.setAlias(libAlias);
                    library.setSecurityProfile(s.getSecurityProfile());
                    library.setDescription(s.getDescription());
                    library.setCreationDate(new Date());
                    library.setPlatformName(pt.name());
                    library.setLibraryType(lt);
                    library.setLibrarySelectionType(ls);
                    library.setLibraryStrategyType(lst);
                    library.setPaired(paired);

                    if (getCellValueAsString(libraryQcMolarityCell) != null) {
                        int insertSize = 0;
                        try {
                            String bp = getCellValueAsString(libraryQcInsertSizeCell);
                            Matcher m = digitPattern.matcher(bp);
                            if (m.matches()) {
                                insertSize = Integer.valueOf(m.group(1));
                            } else {
                                throw new InputFormException("Supplied Library insert size for library '"
                                        + libAlias + "' (" + s.getAlias() + ") is invalid");
                            }
                        } catch (NumberFormatException nfe) {
                            throw new InputFormException("Supplied Library insert size for library '" + libAlias
                                    + "' (" + s.getAlias() + ") is invalid", nfe);
                        }

                        try {
                            LibraryQC lqc = new LibraryQCImpl();
                            lqc.setLibrary(library);
                            lqc.setInsertSize(insertSize);
                            lqc.setResults(Double.valueOf(getCellValueAsString(libraryQcMolarityCell)));
                            lqc.setQcCreator(u.getLoginName());
                            lqc.setQcDate(new Date());
                            lqc.setQcType(manager.getLibraryQcTypeByName("Bioanalyzer"));
                            if (!library.getLibraryQCs().contains(lqc)) {
                                library.addQc(lqc);
                                manager.saveLibraryQC(lqc);
                                log.info("Added library QC: " + lqc.toString());
                            }

                            if (insertSize == 0 && lqc.getResults() == 0) {
                                library.setQcPassed(false);
                            } else {
                                //TODO check libraryQcPassFailCell?
                                library.setQcPassed(true);
                            }
                        } catch (NumberFormatException nfe) {
                            throw new InputFormException("Supplied Library QC concentration for library '"
                                    + libAlias + "' (" + s.getAlias() + ") is invalid", nfe);
                        }
                    }

                    if (getCellValueAsString(barcodeKitCell) != null) {
                        Collection<TagBarcode> bcs = manager
                                .listAllTagBarcodesByStrategyName(getCellValueAsString(barcodeKitCell));
                        if (!bcs.isEmpty()) {
                            String tags = getCellValueAsString(barcodeTagsCell);
                            if (!"".equals(tags)) {
                                HashMap<Integer, TagBarcode> tbs = new HashMap<Integer, TagBarcode>();
                                if (tags.contains("-")) {
                                    String[] splits = tags.split("-");
                                    int count = 1;
                                    for (String tag : splits) {
                                        for (TagBarcode tb : bcs) {
                                            if (tb.getName().equals(tag)) {
                                                //set tag barcodes
                                                tbs.put(count, tb);
                                                count++;
                                            }
                                        }
                                    }
                                } else {
                                    for (TagBarcode tb : bcs) {
                                        if (tb.getName().equals(tags) || tb.getSequence().equals(tags)) {
                                            //set tag barcode
                                            tbs.put(1, tb);
                                            log.info("Got tag barcode: " + tb.getName());
                                            break;
                                        }
                                    }
                                }

                                library.setTagBarcodes(tbs);
                            } else {
                                throw new InputFormException(
                                        "Barcode Kit specified but no tag barcodes entered for: '"
                                                + s.getAlias() + "'.");
                            }
                        } else {
                            throw new InputFormException("No tag barcodes associated with the kit definition '"
                                    + getCellValueAsString(barcodeKitCell) + "' for sample: '" + s.getAlias()
                                    + "'.");
                        }
                    }

                    /*
                    if (getCellValueAsString(dilutionMolarityCell) != null) {
                      try {
                        LibraryDilution ldi = new LibraryDilution();
                        ldi.setLibrary(library);
                        ldi.setSecurityProfile(library.getSecurityProfile());
                        ldi.setConcentration(Double.valueOf(getCellValueAsString(dilutionMolarityCell)));
                        ldi.setCreationDate(new Date());
                        ldi.setDilutionCreator(u.getLoginName());
                        if (!library.getLibraryDilutions().contains(ldi)) {
                          library.addDilution(ldi);
                          log.info("Added library dilution: " + ldi.toString());
                        }
                            
                        if (getCellValueAsString(poolNumberCell) != null) {
                          String poolNum = String.valueOf(new Double(getCellValueAsString(poolNumberCell)).intValue());
                          Pool<Plate<LinkedList<Library>, Library>> p = pools.get(poolNum);
                          if (p != null) {
                            p.addPoolableElement(ldi);
                            log.info("Added library dilution to pool: " + p.toString());
                          }
                        }
                      }
                      catch (NumberFormatException nfe) {
                        throw new InputFormException("Supplied LibraryDilution concentration for library '"+libAlias+"' ("+s.getAlias()+") is invalid", nfe);
                      }
                    }
                    */

                    if (getCellValueAsString(poolConvertedMolarityCell) != null) {
                        String poolNum = getCellValueAsString(poolNumberCell);
                        Pool<Plate<LinkedList<Library>, Library>> p = pools.get(poolNum);
                        if (p != null) {
                            log.debug("Retrieved pool " + poolNum);
                            try {
                                p.setConcentration(
                                        Double.valueOf(getCellValueAsString(poolConvertedMolarityCell)));
                            } catch (NumberFormatException nfe) {
                                throw new InputFormException(
                                        "Supplied pool concentration for pool '" + poolNum + "' is invalid",
                                        nfe);
                            }
                        }
                    }

                    log.info("Added library: " + library.toString());
                    manager.saveLibrary(library);

                    if (getCellValueAsString(platePosCell) != null && libraryPlate != null) {
                        //libraryPlate.setElement(getCellValueAsString(platePosCell), library);
                        libraryPlate.addElement(library);
                        log.info("Added library " + library.getAlias() + " to "
                                + getCellValueAsString(platePosCell));
                    }

                    samples.add(s);

                    Pool<Plate<LinkedList<Library>, Library>> p = pools
                            .get(getCellValueAsString(poolNumberCell));
                    if (p != null && !p.getPoolableElements().contains(libraryPlate)) {
                        p.addPoolableElement(libraryPlate);
                        log.info("Added plate to pool: " + p.toString());
                    }
                } else {
                    log.error(
                            "Cannot generate library alias from specified parent sample alias. Does it match the required schema?");
                }
            }
        }
    }
    log.info("Done");
    return pools;
}

From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java

License:Open Source License

private static List<Sample> processSampleInputXLSX(XSSFWorkbook wb, User u, RequestManager manager,
        MisoNamingScheme<Library> libraryNamingScheme) throws Exception {
    ((RequestManagerAwareNamingScheme) libraryNamingScheme).setRequestManager(manager);

    List<Sample> samples = new ArrayList<Sample>();
    XSSFSheet sheet = wb.getSheetAt(0);
    int rows = sheet.getPhysicalNumberOfRows();

    XSSFRow glrow = sheet.getRow(1);/*from   w ww. ja v  a 2 s .  c  o m*/

    //process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired;
    if (getCellValueAsString(pairedCell) != null) {
        paired = pairedCell.getBooleanCellValue();
        log.info("Got paired: " + paired);
    } else {
        throw new InputFormException("'Paired' cell is empty. Please specify TRUE or FALSE.");
    }

    XSSFCell platformCell = glrow.getCell(1);
    PlatformType pt = null;
    if (getCellValueAsString(platformCell) != null) {
        pt = PlatformType.get(getCellValueAsString(platformCell));
    }
    if (pt == null) {
        throw new InputFormException(
                "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'");
    } else {
        log.info("Got platform type: " + pt.getKey());
    }

    XSSFCell typeCell = glrow.getCell(2);
    LibraryType lt = null;
    if (getCellValueAsString(typeCell) != null) {
        String[] split = getCellValueAsString(typeCell).split("-");
        String plat = split[0];
        String type = split[1];
        if (getCellValueAsString(platformCell).equals(plat)) {
            lt = manager.getLibraryTypeByDescriptionAndPlatform(type, pt);
        } else {
            throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell)
                    + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'");
        }
    }
    if (lt == null) {
        throw new InputFormException(
                "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'");
    } else {
        log.info("Got library type: " + lt.getDescription());
    }

    XSSFCell selectionCell = glrow.getCell(3);
    LibrarySelectionType ls = null;
    if (getCellValueAsString(selectionCell) != null) {
        ls = manager.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell));
    }
    if (ls == null) {
        throw new InputFormException(
                "Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'");
    } else {
        log.info("Got library selection type: " + ls.getName());
    }

    XSSFCell strategyCell = glrow.getCell(4);
    LibraryStrategyType lst = null;
    if (getCellValueAsString(strategyCell) != null) {
        lst = manager.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell));
    }
    if (lst == null) {
        throw new InputFormException(
                "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'");
    } else {
        log.info("Got library strategy type: " + lst.getName());
    }

    //process entries
    Plate<LinkedList<Sample>, Sample> samplePlate = null;
    Map<String, Pool<Dilution>> pools = new HashMap<String, Pool<Dilution>>();

    for (int ri = 4; ri < rows; ri++) {
        XSSFRow row = sheet.getRow(ri);

        // Ax - plate position
        XSSFCell platePosCell = row.getCell(0);
        if (getCellValueAsString(platePosCell) != null && samplePlate == null) {
            //plated samples - process as plate
            samplePlate = new PlateImpl<Sample>();
        }

        //cell defs
        XSSFCell sampleAliasCell = row.getCell(2);

        Sample s = null;
        if (getCellValueAsString(sampleAliasCell) != null) {
            String salias = getCellValueAsString(sampleAliasCell);
            Collection<Sample> ss = manager.listSamplesByAlias(salias);
            if (!ss.isEmpty()) {
                if (ss.size() == 1) {
                    s = ss.iterator().next();
                    log.info("Got sample: " + s.getAlias());
                } else {
                    throw new InputFormException(
                            "Multiple samples retrieved with this alias: '" + salias + "'. Cannot process.");
                }
            } else {
                throw new InputFormException("No such sample '" + salias
                        + "'in database. Samples need to be created before using the form input functionality");
            }
        } else {
            log.info("Blank sample row found. Ending import.");
            break;
        }

        //sample OK - good to go
        if (s != null) {
            String projectAliasCell = getCellValueAsString(row.getCell(1));
            String poolNumberCell = getCellValueAsString(row.getCell(3));
            String sampleQcCell = getCellValueAsString(row.getCell(4));
            //String sampleAmountCell = getCellValueAsString(row.getCell(5));
            //String sampleWaterAmountCell = getCellValueAsString(row.getCell(6));
            String libraryDescriptionCell = getCellValueAsString(row.getCell(7));
            String barcodeKitCell = getCellValueAsString(row.getCell(8));
            String barcodeTagsCell = getCellValueAsString(row.getCell(9));
            String libraryQcCell = getCellValueAsString(row.getCell(10));
            String libraryQcInsertSizeCell = getCellValueAsString(row.getCell(11));
            String libraryQcMolarityCell = getCellValueAsString(row.getCell(12));
            String libraryQcPassFailCell = getCellValueAsString(row.getCell(13));
            //String libraryAmountCell = getCellValueAsString(row.getCell(14));
            //String libraryWaterAmountCell = getCellValueAsString(row.getCell(15));
            //String dilutionQcCell = getCellValueAsString(row.getCell(16));
            String dilutionMolarityCell = getCellValueAsString(row.getCell(17));
            //String dilutionAmountCell = getCellValueAsString(row.getCell(18));
            //String dilutionWaterAmountCell = getCellValueAsString(row.getCell(19));
            String poolQcCell = getCellValueAsString(row.getCell(20));
            //String poolAverageInsertSizeCell = getCellValueAsString(row.getCell(21));
            String poolConvertedMolarityCell = getCellValueAsString(row.getCell(22));

            //add pool, if any
            processPool(poolNumberCell, poolConvertedMolarityCell, pools);
            processSampleQC(sampleQcCell, s, u, manager);

            Library library = processLibrary(libraryQcCell, libraryDescriptionCell, libraryQcPassFailCell, s,
                    pt, lt, ls, lst, paired, libraryNamingScheme);
            if (library != null) {
                processLibraryQC(libraryQcCell, libraryQcMolarityCell, libraryQcInsertSizeCell, library, u,
                        manager);
                processBarcodes(barcodeKitCell, barcodeTagsCell, library, manager);
                processDilutions(dilutionMolarityCell, library, pools.get(poolNumberCell), u);
                log.info("Added library: " + library.toString());
                s.addLibrary(library);
            }
            samples.add(s);
        }
    }
    return samples;
}

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static JSONArray preProcessSampleSheetImport(File inPath, User u, SampleService sampleService)
        throws Exception {
    if (inPath.getName().endsWith(".xlsx")) {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inPath));
        JSONArray jsonArray = new JSONArray();
        XSSFSheet sheet = wb.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        for (int ri = 5; ri < rows; ri++) {
            XSSFRow row = sheet.getRow(ri);
            XSSFCell sampleAliasCell = row.getCell(3);
            Sample s = null;//w w w .  j a  v a 2  s .  co 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.");
    }
}