Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:TestCaseReaderTest.java

public void testSimple() throws Exception {
    //public List<TestScriptTemplate> readExcel(){

    FileInputStream file = new FileInputStream(
            new File("C:\\Users\\nbret00\\Documents\\SeleniumSmokeTest\\TestCases.xlsx"));

    List<TestScriptTemplate> tstList = new ArrayList<TestScriptTemplate>();
    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);

    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    List<TestScriptTemplate> TestScriptTemplateList = new ArrayList();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        //skip until row 5
        if (row.getRowNum() > 4) {

            TestScriptTemplate tscripttemp = new TestScriptTemplate();

            //Cell appcode = row.getCell(0); //this should be the item # on the list
            //System.out.println("application name #: "+itemnum.getStringCellValue());
            tscripttemp.setAppCode(getCellValueStr(row.getCell(1)));
            tscripttemp.setAppURL(getCellValueStr(row.getCell(2)));
            tscripttemp.setAppUserID(getCellValueStr(row.getCell(3)));
            tscripttemp.setAppPassword(getCellValueStr(row.getCell(4)));
            tscripttemp.setHomePageTitle(getCellValueStr(row.getCell(5)));
            tscripttemp.setHomePageElement(getCellValueStr(row.getCell(6)));
            tscripttemp.setLevel1URL(getCellValueStr(row.getCell(7)));
            tscripttemp.setLevel1PageTitle(getCellValueStr(row.getCell(8)));
            tscripttemp.setLevel1Element(getCellValueStr(row.getCell(9)));

            //tscripttemp.setAppUserID(row.getCell(3).getStringCellValue());
            //tscripttemp.setAppPassword(row.getCell(3).getStringCellValue());
            TestScriptTemplateList.add(tscripttemp);
            System.out.println("this to string: " + tscripttemp.toString());
        }//  w w w  . j a va  2 s  . c  o  m

    }
    file.close();
}

From source file:ImporteerExcelsheet.java

public void importeerExcelsheet(String filePath) {
    try {/*from   w  ww . j a v a  2s.  c o  m*/
        FileInputStream file = new FileInputStream(new File(filePath));

        if (filePath.toLowerCase().indexOf(xlsx.toLowerCase()) != -1) {
            XSSFWorkbook wbXlsx = new XSSFWorkbook(file);
            sheet = wbXlsx.getSheetAt(0);
            Row row = sheet.getRow(3);
            Cell cell = row.getCell(1);

            try {
                // convert String dd-mm-yyyy naar Date yyyy-mm-dd 
                String excelDatum1 = cell.getStringCellValue();
                Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1);
                this.excelDatum = this.formatter.format(date);
            } catch (Exception e) {
                double date1 = cell.getNumericCellValue();
                //convert excel double naar datum
                Date date2 = DateUtil.getJavaDate((double) date1);
                this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2);
            }
            //Iterate through each rows one by one
            rowIterator = sheet.iterator();
        } else {
            HSSFWorkbook wbXls = new HSSFWorkbook(file);
            sheet1 = wbXls.getSheetAt(0);
            Row row = sheet1.getRow(3);
            Cell cell = row.getCell(1);

            // convert String dd-mm-yyyy naar Date yyyy-mm-dd 
            try {
                String excelDatum1 = cell.getStringCellValue();
                Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1);
                this.excelDatum = this.formatter.format(date);
            } catch (Exception e) {
                double date1 = cell.getNumericCellValue();
                //convert excel double naar datum
                Date date2 = DateUtil.getJavaDate((double) date1);
                this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2);
            }
            //Iterate through each rows one by one
            rowIterator = sheet1.iterator();
        }

        //Iterate through each rows one by one
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //skip first 5 rows
            if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2 || row.getRowNum() == 3
                    || row.getRowNum() == 4) {
                continue;
            }

            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    //                            System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    //                            System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_BLANK:
                    //                            System.out.print(cell.getStringCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    //                            System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }
            }
            // Tabel client
            int kaartnummer = (int) row.getCell(0).getNumericCellValue();
            String naam = row.getCell(1).getStringCellValue();
            String naamPartner = row.getCell(2).getStringCellValue();
            String telefoonnummer = row.getCell(3).getStringCellValue();
            String email = row.getCell(4).getStringCellValue();
            String mobiel = row.getCell(5).getStringCellValue();
            int aantalPersonen = (int) row.getCell(6).getNumericCellValue();
            int aantalPersonenInDeNorm = (int) row.getCell(7).getNumericCellValue();
            double gebruikInMaanden = (double) row.getCell(8).getNumericCellValue();
            String idSoort = row.getCell(9).getStringCellValue();
            //convert excel double naar datum
            double datumUitgifteId1 = row.getCell(10).getNumericCellValue();
            if (datumUitgifteId1 == 0.0) {
                this.datumUitgifteId = null;
            } else {
                Date datumUitgifteId2 = DateUtil.getJavaDate((double) datumUitgifteId1);
                this.datumUitgifteId = new SimpleDateFormat("yyyy-MM-dd").format(datumUitgifteId2);
            }
            ////////////////////////////////////////////////////////////////////////////////////
            String idNummer = row.getCell(11).getStringCellValue();
            String plaatsUitgifteId = row.getCell(12).getStringCellValue();
            String adres = row.getCell(13).getStringCellValue();
            String postcode = row.getCell(14).getStringCellValue();
            String plaats = row.getCell(15).getStringCellValue();
            String status = row.getCell(16).getStringCellValue();

            // Tabel intake
            // kaartnummer gaat hier ook in de query
            String intaker = row.getCell(17).getStringCellValue();
            double intakeDatum1 = row.getCell(18).getNumericCellValue();
            //convert excel double naar datum
            Date intakeDatum2 = DateUtil.getJavaDate((double) intakeDatum1);
            String intakeDatum = new SimpleDateFormat("yyyy-MM-dd").format(intakeDatum2);
            /////////////////////////////////////////////////////////////////////////
            double startDatumUitgifte1 = row.getCell(19).getNumericCellValue();
            //convert excel double naar datum
            Date startDatumUitgifte2 = DateUtil.getJavaDate((double) startDatumUitgifte1);
            String startDatumUitgifte = new SimpleDateFormat("yyyy-MM-dd").format(startDatumUitgifte2);
            //////////////////////////////////////////////////////////////////////////////////
            double datumHerintake1 = row.getCell(20).getNumericCellValue();
            //convert excel double naar datum
            Date datumHerintake2 = DateUtil.getJavaDate((double) datumHerintake1);
            String datumHerintake = new SimpleDateFormat("yyyy-MM-dd").format(datumHerintake2);
            /////////////////////////////////////////////////////////////////////////////////
            // Tabel Stopt
            double datumStopzetting1 = row.getCell(21).getNumericCellValue();
            //convert excel numbers naar datum
            Date datumStopzetting2 = DateUtil.getJavaDate((double) datumStopzetting1);
            String datumStopzetting = new SimpleDateFormat("yyyy-MM-dd").format(datumStopzetting2);
            //////////////////////////////////////////////////////////////////////////////////////
            String redenStopzetting = row.getCell(22).getStringCellValue();

            // Tabel verwijzer
            String verwijzerNaam = row.getCell(23).getStringCellValue();
            String verwijzersDoorContactpersoon1 = row.getCell(24).getStringCellValue();
            String verwijzersDoorContactpersoon = verwijzersDoorContactpersoon1.toLowerCase();
            String verwijzersDoorTelefoonnummer = row.getCell(25).getStringCellValue();
            String verwijzersDoorEmail = row.getCell(26).getStringCellValue();
            String verwijzersNaar = row.getCell(27).getStringCellValue();
            String verwijzersNaarContactpersoon = row.getCell(28).getStringCellValue();
            String verwijzersNaarTelefoonnummer = row.getCell(29).getStringCellValue();
            String verwijzersNaarEmail = row.getCell(30).getStringCellValue();

            // Uitgiftepunt gaat in de tabel intake
            String uitgiftepunt = row.getCell(31).getStringCellValue();

            // PakketSoort gaat in de tabel client
            String pakketSoort = row.getCell(32).getStringCellValue();

            // Query's om de excelsheet in de database te krijgen
            SQLExcelSheetInsert excelSQL = new SQLExcelSheetInsert();
            SQLget getSQL = new SQLget();

            // Losse query uit de tabel om bepaalde gegevens te krijgen om te controleren of die al bestaat of niet
            int Verwijzer = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon);
            int checkKaartnr = getSQL.getCheckKaartnummer(kaartnummer);
            int checkUitgiftepunt = getSQL.getUitgiftepunt(uitgiftepunt);

            if (Verwijzer == 0) {
                excelSQL.insertExcelVerwijzer(verwijzerNaam, verwijzersDoorContactpersoon,
                        verwijzersDoorTelefoonnummer, verwijzersDoorEmail, verwijzersNaar,
                        verwijzersNaarContactpersoon, verwijzersNaarTelefoonnummer, verwijzersNaarEmail);
            }

            if (checkUitgiftepunt == 0) {
                int maxVolgorde = getSQL.getCheckVolgordeLijst();
                excelSQL.insertUitgiftepunt(uitgiftepunt, maxVolgorde);
            }

            int Verwijzer2 = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon);

            /// Wanneer er geen kaartnummer is die bekend gaat die door naar insert
            if (checkKaartnr == 0) {
                // Wanneer kaartnummer alles behalve 0 is insert die
                if (kaartnummer > 0) {
                    excelSQL.insertExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel,
                            aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort,
                            this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status,
                            pakketSoort, Verwijzer2);
                } else {
                    break;
                }
            } else {
                excelSQL.updateExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel,
                        aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId,
                        idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2);
            }

            int checkIntake = getSQL.getCheckIntake(intakeDatum, startDatumUitgifte, datumHerintake,
                    kaartnummer);

            if (checkIntake == 0) {
                excelSQL.insertExcelIntake(intaker, intakeDatum, startDatumUitgifte, datumHerintake,
                        kaartnummer, uitgiftepunt);
                int intakeId = getSQL.getIntakeId(kaartnummer);
                excelSQL.insertExcelStopt(datumStopzetting, redenStopzetting, intakeId);
            }

            // Kan upgedate worden aan een knop voor een query aan status in de table voedselpakket wel of niet opgehaald
            String status1 = null;

            String checkStatus = getSQL.getStatus(kaartnummer);
            this.pakketAantal = getSQL.getPakketAantal(kaartnummer);
            int intakeId = getSQL.getIntakeId(kaartnummer);
            int checkPakket = getSQL.getPakket(this.excelDatum, intakeId);

            if (checkPakket == 0) {
                if (checkStatus != null) {
                    if (checkStatus.equals("Actief")) {
                        int intakeId2 = getSQL.getIntakeId(kaartnummer);
                        excelSQL.insertVoedselpakket(this.excelDatum, this.pakketAantal, status1, intakeId2,
                                uitgiftepunt);
                    }
                }
            }
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {
    if (args.length == 0) {
        throw new IllegalArgumentException("Filename must be given");
    }/*from  ww  w .j  ava2s . com*/

    Workbook wb = WorkbookFactory.create(new File(args[0]));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}

From source file:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/*from ww  w . java2  s  .  c o  m*/
 * @param title ?
 * @param headerList 
 */
private void initialize(String title, List<String> headerList) {
    this.wb = new SXSSFWorkbook(500);
    this.sheet = wb.createSheet("Export");
    this.styles = createStyles(wb);
    // Create title
    if (StringUtils.isNotBlank(title)) {
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(30);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(styles.get("title"));
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(),
                titleRow.getRowNum(), headerList.size() - 1));
    }
    // Create header
    if (headerList == null) {
        throw new RuntimeException("headerList not null!");
    }
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(styles.get("header"));
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = this.sheet.createDrawingPatriarch()
                    .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
    log.debug("Initialize success.");
}

From source file:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/*from   ww  w .ja  v  a  2 s. c o m*/
 * @param row 
 * @param column ?
 * @param val 
 * @param align ??1?23??
 * @return ?
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

From source file:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?annotation.ExportField?//from   w w  w.  j  a v a  2  s . c om
 * @return list ?
 */
public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = Reflections.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }
                }
                // If is dict, get dict label
                if (StringUtils.isNotBlank(ef.dictType())) {
                    val = Dicts.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
                }
            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

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 ww  . j  av  a 2  s.  c  o  m*/
    }

    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:automatedhgl.AutomatedHGL.java

public static void main(String[] args) {

    try {//from   w  w  w.ja v  a2  s .co  m

        FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME));

        //create workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

        //get first desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //create workbook instance to output excel file
        XSSFWorkbook workbookHGL = new XSSFWorkbook();

        //create sheet in output excel file
        XSSFSheet sheetHGL = workbookHGL.createSheet("HGL");

        //iterate through each row one by one
        Iterator<Row> rowiterator = sheet.iterator();

        while (rowiterator.hasNext()) {
            Row row = rowiterator.next();

            //for each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                if (row.getRowNum() > 7 && count < 23) //to filter column headings
                {

                    //check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        count++;

                        //assign get value to correct variable
                        if (count == 1) {
                            InletStr = cell.getNumericCellValue();
                        } else if (count == 2) {
                            OutWSE = cell.getNumericCellValue();
                        }

                        System.out.print(cell.getNumericCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_STRING:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getStringCellValue();}*/

                        System.out.print(cell.getStringCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/

                        System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") ");
                        break;
                    }
                }

                else {
                    count = 0; //reset the count at the end of the row
                }

            }

            System.out.println("return");
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    //Output Excel file

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
    Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 },
            { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 },
            { "String", "Non-Primitive", "No fixed size" } };

    int rowNum = 0;
    System.out.println("Creating excel");

    for (Object[] datatype : datatypes) {
        Row row = sheet.createRow(rowNum++);
        int colNum = 0;
        for (Object field : datatype) {
            Cell cell = row.createCell(colNum++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }
    }

    try {
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.print(InletStr + " ");
    System.out.print(OutWSE + " ");
    System.out.println("HGL Done");

}

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

License:Apache License

public void exportLocally(FocObject object) {
    try {//from ww  w .j ava  2 s  .  c o  m
        Sheet sheet = workbook.getSheetAt(0);
        // Iterate over each row in the sheet
        Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = (Row) rows.next();
            System.out.println("Row #" + row.getRowNum());
            // Iterate over each cell in the row and print out the cell's content
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = (Cell) cells.next();
                System.out.println("Cell #" + cell.getColumnIndex());
                /* System.out.println(String.valueOf(cell.getRichStringCellValue())); */
                String str = null;
                try {
                    str = String.valueOf(cell.getRichStringCellValue());
                } catch (Exception e) {
                    Globals.logExceptionWithoutPopup(e);
                    str = "";
                }
                String result = analyseContent(str, object);
                if (result != null) {

                    if (getWorkbook() != null) {
                        cell.setCellValue(getWorkbook().getCreationHelper().createRichTextString(result));
                    }
                }
                /*
                 * switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC:
                 * System.out.println(cell.getNumericCellValue()); String result =
                 * analyseContent(String.valueOf(cell.getNumericCellValue()), object);
                 * if (result != null){ cell.setCellValue(Double.valueOf(result)); }
                 * break; case HSSFCell.CELL_TYPE_STRING:
                 * System.out.println(cell.getRichStringCellValue()); result =
                 * analyseContent(String.valueOf(cell.getRichStringCellValue()),
                 * object); if (result != null){ cell.setCellValue(new
                 * HSSFRichTextString(result)); } break; default:
                 * System.out.println("unsuported cell type"); break; }
                 */}
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

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

License:Apache License

private RowInSheetMatcher(Row expected) {
    this.expected = expected;
    this.rowIndex = expected.getRowNum();
}