Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:au.com.onegeek.lambda.parser.Excel2SeleniumParser.java

License:Apache License

private void parse(InputStream stream)
        throws CannotCompileException, NotFoundException, CannotCreateTestClassException,
        InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {

    logger.debug("Parsing...");

    if (this.dataMap != null && this.tests != null) {
        return;/*from w w w  .  j  av  a 2  s . com*/
    }

    this.dataMap = new ArrayList<Map<String, Object>>();
    this.tests = new ArrayList<Class<Test>>();

    Workbook workbook = null;
    try {
        workbook = new XSSFWorkbook(stream);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    logger.debug("workbook" + workbook.toString());

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet.getSheetName().startsWith("data")) {
            // parse key\value pairs
            HashMap<String, Object> map = new HashMap<String, Object>();
            dataMap.add(map);
            boolean done = false;

            Row row = sheet.getRow(sheet.getFirstRowNum());

            while (!done && row != null && row.getPhysicalNumberOfCells() > 0) {
                // TODO: parse numerics correctly (i.e. don't add decimal points if not needed)
                String key = (String) XslxUtil.objectFrom(workbook, row.getCell(0));
                String value = null;
                try {
                    value = (String) XslxUtil.objectFrom(workbook, row.getCell(1));
                    logger.debug("Adding variable to map: " + key + ":" + value);
                    map.put(key, value);

                    row = sheet.getRow(row.getRowNum() + 1);

                    if (row == null || (row.getRowNum() == sheet.getLastRowNum() + 1)) {
                        done = true;
                    }
                } catch (NullPointerException e) {
                    //throw new CannotCreateVariableException("No value found for variable '" + key + "' in dataset: " + sheet.getSheetName());
                    done = true;
                }
            }
        }
    }

    JavassistTestBuilderImpl builder = JavassistTestBuilderImpl.getInstance();

    // Parse Test sheets into Test objects
    for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
        Sheet sheet = workbook.getSheetAt(s);
        int i = 0;

        // Ignore data sheets
        if (sheet.getSheetName().startsWith("suite")) {

            int maxRows = sheet.getPhysicalNumberOfRows();
            int currentRow = sheet.getFirstRowNum();
            logger.debug("Nr rows in sheet: " + maxRows);

            // Create Test Class
            String testCaseName = "Test" + Excel2SeleniumParser.toCamelCase(sheet.getSheetName());
            logger.debug("Creating Test class with name: " + testCaseName);
            builder.makeTestClass(testCaseName, this.dataMap);
            boolean testCaseInProgress = false;
            boolean dataProviderAdded = false;

            // Get First row, containing the test name and the data to be injected
            while (i < maxRows) {
                logger.debug("i: " + i);
                logger.debug("currentRow: " + currentRow);
                Row row = sheet.getRow(currentRow);
                TestCommand command = null;

                // Check for empty row
                if (row != null && row.getPhysicalNumberOfCells() != 0) {
                    i++;

                    // Get Cells
                    Iterator<Cell> iterator = row.cellIterator();
                    while (iterator.hasNext()) {
                        Cell cell = iterator.next();
                        String cellValue = (cell == null || cell.toString() == "") ? ""
                                : XslxUtil.objectFrom(workbook, cell).toString();
                        logger.debug("Cell: " + cellValue);

                        if (cellValue.startsWith("test")) {
                            logger.debug("Test case found: " + cellValue + ". Creating Test Case");

                            // Create new Test CASE
                            try {
                                builder.addTest(cellValue);
                                testCaseInProgress = true;
                                dataProviderAdded = false;
                            } catch (CannotModifyTestMethodException e) {
                                e.printStackTrace();
                                throw new CannotCreateTestClassException(
                                        "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: "
                                                + e.getMessage());
                            } catch (VariableNotFoundException e) {
                                e.printStackTrace();
                                throw new CannotCreateTestClassException(
                                        "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: "
                                                + e.getMessage());
                            }
                            break;
                        } else {
                            if (command == null & !cellValue.equals("")) {
                                logger.debug("Command found: " + cellValue + ". Creating new TestCommand");
                                command = new TestCommand(cellValue);
                            } else if (!cellValue.equals("")) {
                                logger.debug("Command argument found: " + cellValue);
                                command.addParameter(cellValue);
                            }
                        }
                    }
                } else {
                    // Blank row could mean a test case has just been completed
                    // Complete last test case by adding a data provider
                    if (testCaseInProgress && !dataProviderAdded) {
                        try {
                            logger.debug("In Progress Test Case now being closed off and added to class...");
                            builder.addDataProvider();
                            dataProviderAdded = true;
                            logger.debug("In Progress Test Case now closed off!");
                        } catch (CannotCreateDataProviderException e) {
                            throw new CannotCreateTestClassException(
                                    "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: "
                                            + e.getMessage());
                        }
                    }
                }
                try {
                    if (command != null) {
                        logger.debug("Adding command to method");
                        builder.appendTestToLastMethod(command);
                    }
                } catch (CannotModifyTestMethodException e) {
                    throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString()
                            + "' to Test Class. Embedded exception: " + e.getMessage());
                } catch (VariableNotFoundException e) {
                    throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString()
                            + "' to Test Class as a variable was not found. Embedded exception: "
                            + e.getMessage());
                }
                currentRow++;
            }
            // Blank row could mean a test case has just been completed
            // Complete last test case by adding a data provider
            logger.debug(
                    "End of rows...Checking if In Progress Test Case now being closed off and added to class...");
            if (testCaseInProgress && !dataProviderAdded) {
                logger.debug(" In Progress Test Case now being closed off and added to class...");
                try {
                    builder.addDataProvider();
                    dataProviderAdded = true;
                    logger.debug("In Progress Test Case now closed off!");
                } catch (CannotCreateDataProviderException e) {
                    throw new CannotCreateTestClassException(
                            "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: "
                                    + e.getMessage());
                }
            }

            if (testCaseInProgress) {
                logger.debug("Generating class file");
                try {
                    this.tests.add(builder.getCreatedClass());
                } catch (CannotModifyTestMethodException e) {
                    e.printStackTrace();
                    throw new CannotCreateTestClassException(
                            "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: "
                                    + e.getMessage());
                }
                testCaseInProgress = false;
            }
        }
    }

    try {
        stream.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    logger.info("Looking at our classes...");

    // Look at the Test Objects
    for (Class<Test> clazz : tests) {
        logger.info("Class: " + clazz.getName());
        for (Method m : clazz.getMethods()) {
            logger.info("Method: " + m);
            if (m.getName().equalsIgnoreCase("testRetailDataProvider")) {
                logger.info("invoking data provider");
                Test test = clazz.newInstance();
                Object[][] data = (Object[][]) m.invoke(test);
                for (Object[] obs : data) {
                    for (Object o : obs) {
                        logger.info("data value: " + o);
                    }
                }
            }
        }
    }
}

From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java

License:Open Source License

public static void refineExperimentFromExcel(Experiment experiment, InputStream input) throws IOException {
    // Sample environment is not supported at this stage
    if (experiment.isControlledEnvironment()) {
        return;//from  www .ja  va  2s .c om
    }
    // Clear existing acquisition entries
    experiment.getNormalAcquisition().getEntries().clear();
    // Read from a Excel file
    Workbook workbook = new HSSFWorkbook(input);
    Sheet sheet = workbook.getSheetAt(0);
    // Start from row 3
    for (int i = 2; i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        int samplePosition = Integer.parseInt(row.getCell(1).getStringCellValue());
        Sample sample = experiment.getSamples().get(samplePosition);
        AcquisitionEntry entry = new AcquisitionEntry(sample);
        experiment.getNormalAcquisition().getEntries().add(entry);
    }
}

From source file:b01.officeLink.excel.ExcelRefiller.java

License:Apache License

public void fillGroupDefinition(FocExcelDocument excel) {
    setFocExcelDocument(excel);/* w ww.j a  va2s . c om*/
    Sheet sourceSheet = excel.getSheetAt(1);

    for (int i = 0; i < 100; i++) {
        Row sRow = sourceSheet.getRow(i);
        Cell sCell = sRow != null ? sRow.getCell(0) : null;

        RichTextString groupColValue = sCell != null ? sCell.getRichStringCellValue() : null;
        String groupColValueStr = groupColValue != null ? groupColValue.getString() : null;

        if (groupColValueStr != null) {
            ExcelGroupDefinition groupDef = groupMap.get(groupColValueStr);
            if (groupDef == null) {
                groupDef = new ExcelGroupDefinition();
                groupMap.put(groupColValueStr, groupDef);
            }
            groupDef.addRow(i);
        }
    }
}

From source file:b01.officeLink.excel.ExcelRefiller.java

License:Apache License

public void fillGroupContent(String groupStr, FocObject object) {
    ExcelGroupDefinition grpDef = getGroupDefinition(groupStr);
    Sheet srcSheet = getSourceSheet();
    Sheet tarSheet = getTargetSheet();/*from  w  w  w  . j a  v a 2s  .co m*/
    if (grpDef != null) {
        for (int i = 0; i < grpDef.getRowCount(); i++) {
            int rowIdx = grpDef.getRowAt(i);
            Row sRow = srcSheet.getRow(rowIdx);
            if (sRow != null) {
                Row tRow = tarSheet.getRow(currentRow);
                if (tRow == null) {
                    tRow = tarSheet.createRow(currentRow);
                }
                if (tRow != null) {
                    tRow.setHeight(sRow.getHeight());
                    for (int c = 0; c < 20; c++) {
                        Cell sCell = sRow.getCell(c + 1);
                        if (sCell != null) {
                            Cell tCell = tRow.getCell(c);
                            if (tCell == null) {
                                tCell = tRow.createCell(c);
                            }
                            if (tCell != null) {
                                tCell.setCellStyle(sCell.getCellStyle());

                                String str = "";
                                if (sCell.getCellType() == Cell.CELL_TYPE_STRING) {
                                    RichTextString rts = sCell.getRichStringCellValue();
                                    str = rts.getString();
                                    str = analyseContent(str, object);
                                } else if (sCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    str = String.valueOf(sCell.getNumericCellValue());
                                }

                                if (str != null && !str.isEmpty()) {
                                    int iVal = convertString2Integer(str);
                                    double dVal = convertString2Double(str);
                                    if (iVal != Integer.MAX_VALUE) {
                                        tCell.setCellValue(iVal);
                                    } else if (!Double.isNaN(dVal)) {
                                        tCell.setCellValue(dVal);
                                    } else {
                                        if (getFocExcelDocument() != null
                                                && getFocExcelDocument().getWorkbook() != null) {
                                            tCell.setCellValue(getFocExcelDocument().getWorkbook()
                                                    .getCreationHelper().createRichTextString(str));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                currentRow++;
            }
        }
    }
}

From source file:bad.robot.excel.matchers.RowInSheetMatcher.java

License:Apache License

@Override
protected boolean matchesSafely(Sheet actualSheet, Description mismatch) {
    Row actual = actualSheet.getRow(rowIndex);

    if (!rowIsPresent(expected).matchesSafely(actual, mismatch))
        return false;

    if (!hasSameNumberOfCellsAs(expected).matchesSafely(actual, mismatch))
        return false;

    if (!hasSameCellsAs(expected).matchesSafely(actual, mismatch))
        return false;

    return true;/*from  ww w  .jav a2  s  .  com*/
}

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

/**
 * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is
 * already occupied, shift all rows down to make room.
 *
 *//* w  w  w . jav a  2s  .c  o m*/
public static void copyRow(Workbook workbook, Sheet worksheet, RowIndex from, RowIndex to) {
    Row sourceRow = worksheet.getRow(from.value());
    Row newRow = worksheet.getRow(to.value());

    if (alreadyExists(newRow))
        worksheet.shiftRows(to.value(), worksheet.getLastRowNum(), 1);
    else
        newRow = worksheet.createRow(to.value());

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);
        if (oldCell != null) {
            copyCellStyle(workbook, oldCell, newCell);
            copyCellComment(oldCell, newCell);
            copyCellHyperlink(oldCell, newCell);
            copyCellDataTypeAndValue(oldCell, newCell);
        }
    }

    copyAnyMergedRegions(worksheet, sourceRow, newRow);
}

From source file:bad.robot.excel.workbook.NavigablePoiWorkbook.java

License:Apache License

private org.apache.poi.ss.usermodel.Row getRowForCoordinate(RowIndex rowIndex, SheetIndex sheetIndex) {
    Sheet sheet = poi.getSheetAt(sheetIndex.value());
    org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex.value());
    if (row == null)
        row = sheet.createRow(rowIndex.value());
    return row;// w  w w  . ja  v  a 2 s  . c o m
}

From source file:bad.robot.excel.workbook.PoiWorkbook.java

License:Apache License

private org.apache.poi.ss.usermodel.Row getRowForCoordinate(Coordinate coordinate) {
    Sheet sheet = workbook.getSheetAt(coordinate.getSheet().value());
    org.apache.poi.ss.usermodel.Row row = sheet.getRow(coordinate.getRow().value());
    if (row == null)
        row = sheet.createRow(coordinate.getRow().value());
    return row;/*from w ww.  ja v a2s . c om*/
}

From source file:bad.robot.excel.WorkbookResource.java

License:Apache License

public static org.apache.poi.ss.usermodel.Row firstRowOf(Sheet sheet) {
    return sheet.getRow(0);
}

From source file:bad.robot.excel.WorkbookResource.java

License:Apache License

public static org.apache.poi.ss.usermodel.Row getRowForCoordinate(Coordinate coordinate, Workbook workbook)
        throws IOException {
    Sheet sheet = workbook.getSheetAt(coordinate.getSheet().value());
    org.apache.poi.ss.usermodel.Row row = sheet.getRow(coordinate.getRow().value());
    if (row == null)
        throw new IllegalStateException("expected to find a row");
    return row;/*from  w  w w. j  a  v  a2 s . c o m*/
}