Examples with CellReference org.apache.poi.hssf.util.CellReference used on opensource projects

Example 11 with CellReference

use of org.apache.poi.hssf.util.CellReference in project poi by apache.

the class TestFormulas method operationalRefVerify.

/**
     * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
     * all match what we expect (x operator y)
     */
private static void operationalRefVerify(String operator, HSSFWorkbook wb) {
    HSSFSheet s = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;
    //get our minimum values
    r = s.getRow(0);
    c = r.getCell(1);
    //get our minimum values
    assertTrue("minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(), (("A2" + operator + "A3").equals(c.getCellFormula())));
    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
        r = s.getRow(x);
        for (int y = 1; y < 256 && y > 0; y++) {
            int refx1;
            int refy1;
            int refx2;
            int refy2;
            if (x + 50 < Short.MAX_VALUE) {
                refx1 = x + 50;
                refx2 = x + 46;
            } else {
                refx1 = x - 4;
                refx2 = x - 3;
            }
            if (y + 50 < 255) {
                refy1 = y + 50;
                refy2 = y + 49;
            } else {
                refy1 = y - 4;
                refy2 = y - 3;
            }
            c = r.getCell(y);
            CellReference cr = new CellReference(refx1, refy1, false, false);
            String ref = cr.formatAsString();
            ref = cr.formatAsString();
            cr = new CellReference(refx2, refy2, false, false);
            String ref2 = cr.formatAsString();
            assertTrue("loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(), (("" + ref + operator + ref2).equals(c.getCellFormula())));
        }
    }
    //test our maximum values
    r = s.getRow(0);
    c = r.getCell(0);
    assertEquals("B1" + operator + "IV255", c.getCellFormula());
}

Example 12 with CellReference

use of org.apache.poi.hssf.util.CellReference in project ocvn by devgateway.

the class XExcelFileReader method getDataRow.

private String[] getDataRow() throws XMLStreamException {
    List<String> rowValues = new ArrayList<String>();
    while (xmlReader.hasNext()) {
        xmlReader.next();
        if (xmlReader.isStartElement()) {
            if (xmlReader.getLocalName().equals("c")) {
                CellReference cellReference = new CellReference(xmlReader.getAttributeValue(null, "r"));
                // Fill in the possible blank cells!
                while (rowValues.size() < cellReference.getCol()) {
                    rowValues.add("");
                }
                String cellType = xmlReader.getAttributeValue(null, "t");
                rowValues.add(getCellValue(cellType));
            }
        } else if (xmlReader.isEndElement() && xmlReader.getLocalName().equals("row")) {
            break;
        }
    }
    return rowValues.toArray(new String[rowValues.size()]);
}

Example 13 with CellReference

use of org.apache.poi.hssf.util.CellReference in project drools by kiegroup.

the class XLSEventDataCollector method fulfillExpectation.

private void fulfillExpectation(int currentRowCtr, int currentColCtr, Object cellValue, Class expectedClass) throws ScorecardParseException {
    List<DataExpectation> dataExpectations = resolveExpectations(currentRowCtr, currentColCtr);
    CellReference cellRef = new CellReference(currentRowCtr, currentColCtr);
    Method method = null;
    for (DataExpectation dataExpectation : dataExpectations) {
        try {
            if (dataExpectation != null && dataExpectation.object != null) {
                if (cellValue == null || StringUtils.isEmpty(cellValue.toString())) {
                    if (dataExpectation.errorMessage != null && !StringUtils.isEmpty(dataExpectation.errorMessage)) {
                        parseErrors.add(new ScorecardError(cellRef.formatAsString(), dataExpectation.errorMessage));
                        return;
                    }
                }
                String setter = "set" + Character.toUpperCase(dataExpectation.property.charAt(0)) + dataExpectation.property.substring(1);
                method = getSuitableMethod(cellValue, expectedClass, dataExpectation, setter);
                if (method == null) {
                    if (cellValue != null && !StringUtils.isEmpty(cellValue.toString())) {
                        parseErrors.add(new ScorecardError(cellRef.formatAsString(), "Unexpected Value! Wrong Datatype?"));
                    }
                    return;
                }
                if (method.getParameterTypes()[0] == Double.class) {
                    cellValue = Double.parseDouble(cellValue.toString());
                }
                if (method.getParameterTypes()[0] == Boolean.class) {
                    cellValue = Boolean.valueOf(cellValue.toString());
                }
                if (method.getParameterTypes()[0] == String.class && !(cellValue instanceof String) && cellValue != null) {
                    cellValue = cellValue.toString();
                }
                method.invoke(dataExpectation.object, cellValue);
                if (dataExpectation.object instanceof Extension && ("cellRef".equals(((Extension) dataExpectation.object).getName()))) {
                    ((Extension) dataExpectation.object).setValue(cellRef.formatAsString());
                }
            // dataExpectations.remove(dataExpectation);
            }
        } catch (Exception e) {
            throw new ScorecardParseException(e);
        }
    }
}

Example 14 with CellReference

use of org.apache.poi.hssf.util.CellReference in project my_curd by qinyou.

the class ExcelHelper method excelFileConvertToList.

/**
 * @param filename
 * @return
 * @throws Exception
 */
public List<List> excelFileConvertToList(String filename) throws Exception {
    Workbook wb = null;
    // 此处为兼容2003 与2oo7
    String ext = getExtensionName(filename);
    if (ext.toLowerCase().equals("xlsx")) {
        wb = new XSSFWorkbook(filename);
    } else {
        wb = WorkbookFactory.create(new FileInputStream(filename));
    }
    Sheet sheet = wb.getSheetAt(0);
    List<List> rows = new ArrayList<List>();
    for (Row row : sheet) {
        List<Object> cells = new ArrayList<Object>();
        for (Cell cell : row) {
            Object obj = null;
            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
            switch(cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    obj = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        obj = new JDateTime(cell.getDateCellValue());
                    } else {
                        obj = cell.getNumericCellValue();
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    obj = cell.getNumericCellValue();
                    break;
                default:
                    obj = null;
            }
            cells.add(obj);
        }
        rows.add(cells);
    }
    return rows;
}