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

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

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

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:at.mukprojects.exclycore.model.ExclyDoubleTest.java

License:Open Source License

/**
 * Tests the ExclyDouble setCell function.
 *///  w w w.j  av a  2 s .c om
@Test
public void testExclyDoubleSetCell() throws Exception {
    Row row = sheet.createRow(0);

    doubleOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getNumericCellValue() + " / " + 5.5);
    assertTrue(row.getCell(0).getNumericCellValue() == 5.5);

    doubleError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    doubleBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyIntegerTest.java

License:Open Source License

/**
 * Tests the ExclyInteger setCell function.
 *///from   w w w .  j a  va2s  .  c o  m
@Test
public void testExclyIntegerSetCell() throws Exception {
    Row row = sheet.createRow(0);

    integerOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getNumericCellValue() + " / " + 5);
    assertTrue(row.getCell(0).getNumericCellValue() == 5);

    integerError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    integerBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyLongTest.java

License:Open Source License

/**
 * Tests the ExclyLong setCell function.
 *///from  w w w .j a  v  a 2 s.c o  m
@Test
public void testExclyLongSetCell() throws Exception {
    Row row = sheet.createRow(0);

    longOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getNumericCellValue() + " / " + 5);
    assertTrue(row.getCell(0).getNumericCellValue() == 5);

    longError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    longBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyStringTest.java

License:Open Source License

/**
 * Tests the ExclyString setCell function.
 *//*from   w ww .  ja  v a  2 s .c o m*/
@Test
public void testExclyStringSetCell() throws Exception {
    Row row = sheet.createRow(0);

    stringOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getStringCellValue() + " / Hello ");
    assertEquals(row.getCell(0).getStringCellValue(), "Hello ");

    stringError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");
}

From source file:athena.Controller.java

License:Open Source License

private void convertExceltoCSV(String inputFile, String outputFilePath) {
    InputStream inp = null;/*w  w  w .j  a v a 2  s.com*/
    try {
        inp = new FileInputStream(inputFile);
        wb = new XSSFWorkbook(inp);

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet thisSheet = wb.getSheetAt(i);
            int rowEnd = Math.max(1400, thisSheet.getLastRowNum());

            view.setOutput("Writting.. " + thisSheet.getSheetName());

            String csvRawString = "";
            String outputFileName = outputFilePath + thisSheet.getSheetName() + ".csv";

            try {
                OutputStream os;

                File testFile = new File(outputFileName);

                if (testFile.exists() && !testFile.isDirectory()) {
                    os = new FileOutputStream(outputFilePath + thisSheet.getSheetName() + "(1).csv");
                } else {
                    os = new FileOutputStream(outputFileName);
                }

                PrintWriter w = new PrintWriter(new OutputStreamWriter(os, "UTF-8"));

                for (int j = 0; j < rowEnd; j++) {
                    Row row = thisSheet.getRow(j);

                    if (row != null) {
                        for (int k = 0; k < row.getLastCellNum(); k++) {
                            if (k == (row.getLastCellNum() - 1)) {
                                if (row.getCell(k) != null) {
                                    csvRawString = csvRawString + row.getCell(k);
                                }
                            } else {
                                if (row.getCell(k) == null) {
                                    csvRawString = csvRawString + ",";
                                } else {
                                    csvRawString = csvRawString + row.getCell(k) + ",";
                                }
                            }
                        }
                    } else {
                        csvRawString = csvRawString + ",";
                    }

                    csvRawString = csvRawString + "\n";
                    w.print(csvRawString);
                    w.flush();
                    csvRawString = "";
                }

                w.close();
                view.setOutput("Done.. " + thisSheet.getSheetName());
            } catch (FileNotFoundException e) {
                view.setOutput("I'm confused.. File not found!");
            } catch (UnsupportedEncodingException e) {
                view.setOutput("Call 911.. or Jake");
            }
        }
    } catch (IOException e) {
        view.setOutput("Uh oh.. Fail to read file!");
    }

    finally {
        try {
            inp.close();
            view.setOutput("Done conversion.. " + model.getInputFilePath() + "\n");
            model.setInputFilePath(null);
            model.setOutputFilePath(null);
            view.refreshIntputPath();
            view.refreshOutputPath();
        } catch (IOException e) {
            view.setOutput("Damn input stream..");
        }
    }
}

From source file:attendance.CheckIn.java

private static void checkName() throws FileNotFoundException, IOException {
    idNum = jInputField.getText() + ".0";
    jInputField.setText("");

    for (Sheet sheet : sheets) {
        for (Row row : sheet) {
            String cell1 = row.getCell(ID_COL).toString();

            if (cell1.equals(idNum)) {
                Cell name = row.getCell(NAME_COL);
                addingName = name.getStringCellValue();

                String paidStatus = row.getCell(PAIDSTATUS_COL).toString();

                if (row.getCell(CHECKED_IN_COL).toString().equals("Checked In")) {
                    jCheckedInTextArea.append(addingName + "\n");

                    flashColor(Color.red, jCheckedInTextArea);
                } else if (!paidStatus.equals("")) {
                    jPaidTextArea.append(addingName + "\n");
                    row.getCell(CHECKED_IN_COL).setCellValue("Checked In");
                    alreadyPaidAL.add(addingName);
                    flashColor(Color.GREEN, jPaidTextArea);
                } else {
                    jNotPaidTextArea.append(addingName + "\n");
                    row.getCell(CHECKED_IN_COL).setCellValue("Checked In");
                    flashColor(Color.red, jNotPaidTextArea);
                }/*from   ww  w  . ja  v a 2s  .  c o m*/
            }
        }
    }
}

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  a v  a2 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: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;//  w ww  . ja va 2  s. c o m
    }
    // 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  w  w.ja v  a 2 s . c  o  m
    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();//from  w  ww  .  j a v a2 s  .c  o m
    Sheet tarSheet = getTargetSheet();
    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++;
            }
        }
    }
}