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

Example 1 with CellReference

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

the class HSSFWorkbook method setPrintArea.

/**
     * For the Convenience of Java Programmers maintaining pointers.
     * @see #setPrintArea(int, String)
     * @param sheetIndex Zero-based sheet index (0 = First Sheet)
     * @param startColumn Column to begin printarea
     * @param endColumn Column to end the printarea
     * @param startRow Row to begin the printarea
     * @param endRow Row to end the printarea
     */
@Override
public void setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) {
    //using absolute references because they don't get copied and pasted anyway
    CellReference cell = new CellReference(startRow, startColumn, true, true);
    String reference = cell.formatAsString();
    cell = new CellReference(endRow, endColumn, true, true);
    reference = reference + ":" + cell.formatAsString();
    setPrintArea(sheetIndex, reference);
}

Example 2 with CellReference

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

the class TestBug42464 method process.

private static void process(HSSFRow row, HSSFFormulaEvaluator eval) {
    Iterator<Cell> it = row.cellIterator();
    while (it.hasNext()) {
        HSSFCell cell = (HSSFCell) it.next();
        if (cell.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        FormulaRecordAggregate record = (FormulaRecordAggregate) cell.getCellValueRecord();
        FormulaRecord r = record.getFormulaRecord();
        Ptg[] ptgs = r.getParsedExpression();
        String cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex(), false, false).formatAsString();
        //			if(false && cellRef.equals("BP24")) { // TODO - replace System.out.println()s with asserts
        //				System.out.print(cellRef);
        //				System.out.println(" - has " + ptgs.length + " ptgs:");
        //				for(int i=0; i<ptgs.length; i++) {
        //					String c = ptgs[i].getClass().toString();
        //					System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
        //				}
        //				System.out.println("-> " + cell.getCellFormula());
        //			}
        CellValue evalResult = eval.evaluate(cell);
        assertNotNull(evalResult);
    }
}

Example 3 with CellReference

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

the class TestFormulas method operationRefTest.

private static void operationRefTest(String operator) throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet s = wb1.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;
    //get our minimum values
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("A2" + operator + "A3");
    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
        r = s.createRow(x);
        for (int y = 1; y < 256 && y > 0; y++) {
            String ref = null;
            String ref2 = null;
            short refx1 = 0;
            short refy1 = 0;
            short refx2 = 0;
            short refy2 = 0;
            if (x + 50 < Short.MAX_VALUE) {
                refx1 = (short) (x + 50);
                refx2 = (short) (x + 46);
            } else {
                refx1 = (short) (x - 4);
                refx2 = (short) (x - 3);
            }
            if (y + 50 < 255) {
                refy1 = (short) (y + 50);
                refy2 = (short) (y + 49);
            } else {
                refy1 = (short) (y - 4);
                refy2 = (short) (y - 3);
            }
            c = r.getCell(y);
            CellReference cr = new CellReference(refx1, refy1, false, false);
            ref = cr.formatAsString();
            cr = new CellReference(refx2, refy2, false, false);
            ref2 = cr.formatAsString();
            c = r.createCell(y);
            c.setCellFormula("" + ref + operator + ref2);
        }
    }
    //make sure we do the maximum value of the Int operator
    if (s.getLastRowNum() < Short.MAX_VALUE) {
        r = s.getRow(0);
        c = r.createCell(0);
        c.setCellFormula("" + "B1" + operator + "IV255");
    }
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    operationalRefVerify(operator, wb2);
    wb2.close();
}

Example 4 with CellReference

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

the class TestExternalNameReference method testEvaluate.

public void testEvaluate() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("XRefCalc.xls");
    HSSFWorkbook wb2 = HSSFTestDataSamples.openSampleWorkbook("XRefCalcData.xls");
    CellReference cellRef = new CellReference(wb.getName("QUANT").getRefersToFormula());
    HSSFCell cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cell.setCellValue(NEW_QUANT);
    cell = wb2.getSheet("CostSheet").getRow(1).getCell(1);
    cell.setCellValue(NEW_PART_COST);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFFormulaEvaluator evaluatorCost = new HSSFFormulaEvaluator(wb2);
    String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
    HSSFFormulaEvaluator[] evaluators = { evaluator, evaluatorCost };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
    cellRef = new CellReference(wb.getName("UNITCOST").getRefersToFormula());
    HSSFCell uccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("COST").getRefersToFormula());
    HSSFCell ccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("TOTALCOST").getRefersToFormula());
    HSSFCell tccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    evaluator.evaluateFormulaCellEnum(uccell);
    evaluator.evaluateFormulaCellEnum(ccell);
    evaluator.evaluateFormulaCellEnum(tccell);
    assertEquals(NEW_PART_COST, uccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT, ccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.getNumericCellValue());
}

Example 5 with CellReference

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

the class BaseTestFunctionsFromSpreadsheet method processFunctionRow.

@Test
public void processFunctionRow() throws Exception {
    HSSFRow r = sheet.getRow(formulasRowIdx);
    HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION);
    HSSFCell expectedCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
    CellReference cr = new CellReference(sheet.getSheetName(), formulasRowIdx, evalCell.getColumnIndex(), false, false);
    String msg = String.format(Locale.ROOT, "In %s %s {=%s} '%s'", filename, cr.formatAsString(), evalCell.getCellFormula(), testName);
    CellValue actualValue = evaluator.evaluate(evalCell);
    assertNotNull(msg + " - Bad setup data expected value is null", expectedCell);
    assertNotNull(msg + " - actual value was null", actualValue);
    if (expectedCell.getCellTypeEnum() == CellType.ERROR) {
        int expectedErrorCode = expectedCell.getErrorCellValue();
        assertEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum());
        assertEquals(msg, ErrorEval.getText(expectedErrorCode), actualValue.formatAsString());
        assertEquals(msg, expectedErrorCode, actualValue.getErrorValue());
        assertEquals(msg, ErrorEval.getText(expectedErrorCode), ErrorEval.getText(actualValue.getErrorValue()));
        return;
    }
    // unexpected error
    assertNotEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum());
    assertNotEquals(msg, formatValue(expectedCell), ErrorEval.getText(actualValue.getErrorValue()));
    // wrong type error
    assertEquals(msg, expectedCell.getCellTypeEnum(), actualValue.getCellTypeEnum());
    final CellType expectedCellType = expectedCell.getCellTypeEnum();
    switch(expectedCellType) {
        case BOOLEAN:
            assertEquals(msg, expectedCell.getBooleanCellValue(), actualValue.getBooleanValue());
            break;
        case // will never be used, since we will call method after formula evaluation
        FORMULA:
            fail("Cannot expect formula as result of formula evaluation: " + msg);
        case NUMERIC:
            assertEquals(expectedCell.getNumericCellValue(), actualValue.getNumberValue(), 0.0);
            break;
        case STRING:
            assertEquals(msg, expectedCell.getRichStringCellValue().getString(), actualValue.getStringValue());
            break;
        default:
            fail("Unexpected cell type: " + expectedCellType);
    }
}