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

Example 81 with CellReference

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

the class TestXSSFDataValidation method testAddValidations.

@Test
public void testAddValidations() throws Exception {
    XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("DataValidations-49244.xlsx");
    Sheet sheet = wb1.getSheetAt(0);
    List<XSSFDataValidation> dataValidations = ((XSSFSheet) sheet).getDataValidations();
    /**
 * 		For each validation type, there are two cells with the same validation. This tests
 * 		application of a single validation definition to multiple cells.
 * 		
 * 		For list ( 3 validations for explicit and 3 for formula )
 * 			- one validation that allows blank. 
 * 			- one that does not allow blank.
 * 			- one that does not show the drop down arrow.
 * 		= 2
 * 
 * 		For number validations ( integer/decimal and text length ) with 8 different types of operators.
 *		= 50  
 * 
 * 		= 52 ( Total )
 */
    assertEquals(52, dataValidations.size());
    DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
    int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
    int[] singleOperandOperatorTypes = new int[] { OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL, OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL, OperatorType.EQUAL, OperatorType.NOT_EQUAL };
    int[] doubleOperandOperatorTypes = new int[] { OperatorType.BETWEEN, OperatorType.NOT_BETWEEN };
    BigDecimal value = new BigDecimal("10"), value2 = new BigDecimal("20");
    BigDecimal dvalue = new BigDecimal("10.001"), dvalue2 = new BigDecimal("19.999");
    final int lastRow = sheet.getLastRowNum();
    int offset = lastRow + 3;
    int lastKnownNumValidations = dataValidations.size();
    Row row = sheet.createRow(offset++);
    Cell cell = row.createCell(0);
    DataValidationConstraint explicitListValidation = dataValidationHelper.createExplicitListConstraint(new String[] { "MA", "MI", "CA" });
    CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
    cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
    DataValidation dataValidation = dataValidationHelper.createValidation(explicitListValidation, cellRangeAddressList);
    setOtherValidationParameters(dataValidation);
    sheet.addValidationData(dataValidation);
    lastKnownNumValidations++;
    row = sheet.createRow(offset++);
    cell = row.createCell(0);
    cellRangeAddressList = new CellRangeAddressList();
    cellRangeAddressList.addCellRangeAddress(cell.getRowIndex(), cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex());
    Cell firstCell = row.createCell(1);
    firstCell.setCellValue("UT");
    Cell secondCell = row.createCell(2);
    secondCell.setCellValue("MN");
    Cell thirdCell = row.createCell(3);
    thirdCell.setCellValue("IL");
    int rowNum = row.getRowNum() + 1;
    String listFormula = new StringBuilder("$B$").append(rowNum).append(":").append("$D$").append(rowNum).toString();
    DataValidationConstraint formulaListValidation = dataValidationHelper.createFormulaListConstraint(listFormula);
    dataValidation = dataValidationHelper.createValidation(formulaListValidation, cellRangeAddressList);
    setOtherValidationParameters(dataValidation);
    sheet.addValidationData(dataValidation);
    lastKnownNumValidations++;
    offset++;
    offset++;
    for (int i = 0; i < validationTypes.length; i++) {
        int validationType = validationTypes[i];
        offset = offset + 2;
        final Row row0 = sheet.createRow(offset++);
        Cell cell_10 = row0.createCell(0);
        cell_10.setCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "Integer" : "Text Length");
        offset++;
        for (int j = 0; j < singleOperandOperatorTypes.length; j++) {
            int operatorType = singleOperandOperatorTypes[j];
            final Row row1 = sheet.createRow(offset++);
            //For Integer (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
            final Row row2 = i == 0 && j < 2 ? sheet.createRow(offset++) : null;
            cell_10 = row1.createCell(0);
            cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());
            Cell cell_11 = row1.createCell(1);
            Cell cell_21 = row1.createCell(2);
            Cell cell_22 = i == 0 && j < 2 ? row2.createCell(2) : null;
            Cell cell_13 = row1.createCell(3);
            cell_13.setCellType(CellType.NUMERIC);
            cell_13.setCellValue(validationType == ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
            //First create value based validation;
            DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, value.toString(), null);
            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(), cell_11.getRowIndex(), cell_11.getColumnIndex(), cell_11.getColumnIndex()));
            DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
            setOtherValidationParameters(validation);
            sheet.addValidationData(validation);
            assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            //Now create real formula based validation.
            String formula1 = new CellReference(cell_13.getRowIndex(), cell_13.getColumnIndex()).formatAsString();
            constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, formula1, null);
            if (i == 0 && j == 0) {
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            } else if (i == 0 && j == 1) {
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_22.getRowIndex(), cell_22.getRowIndex(), cell_22.getColumnIndex(), cell_22.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            } else {
                cellRangeAddressList = new CellRangeAddressList();
                cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
                validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
                setOtherValidationParameters(validation);
                sheet.addValidationData(validation);
                assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            }
        }
        for (int operatorType : doubleOperandOperatorTypes) {
            final Row row1 = sheet.createRow(offset++);
            cell_10 = row1.createCell(0);
            cell_10.setCellValue(XSSFDataValidation.operatorTypeMappings.get(operatorType).toString());
            Cell cell_11 = row1.createCell(1);
            Cell cell_21 = row1.createCell(2);
            Cell cell_13 = row1.createCell(3);
            Cell cell_14 = row1.createCell(4);
            String value1String = validationType == ValidationType.DECIMAL ? dvalue.toString() : value.toString();
            cell_13.setCellType(CellType.NUMERIC);
            cell_13.setCellValue(validationType == ValidationType.DECIMAL ? dvalue.doubleValue() : value.intValue());
            String value2String = validationType == ValidationType.DECIMAL ? dvalue2.toString() : value2.toString();
            cell_14.setCellType(CellType.NUMERIC);
            cell_14.setCellValue(validationType == ValidationType.DECIMAL ? dvalue2.doubleValue() : value2.intValue());
            //First create value based validation;
            DataValidationConstraint constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, value1String, value2String);
            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_11.getRowIndex(), cell_11.getRowIndex(), cell_11.getColumnIndex(), cell_11.getColumnIndex()));
            DataValidation validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
            setOtherValidationParameters(validation);
            sheet.addValidationData(validation);
            assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
            //Now create real formula based validation.
            String formula1 = new CellReference(cell_13.getRowIndex(), cell_13.getColumnIndex()).formatAsString();
            String formula2 = new CellReference(cell_14.getRowIndex(), cell_14.getColumnIndex()).formatAsString();
            constraint = dataValidationHelper.createNumericConstraint(validationType, operatorType, formula1, formula2);
            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.addCellRangeAddress(new CellRangeAddress(cell_21.getRowIndex(), cell_21.getRowIndex(), cell_21.getColumnIndex(), cell_21.getColumnIndex()));
            validation = dataValidationHelper.createValidation(constraint, cellRangeAddressList);
            setOtherValidationParameters(validation);
            sheet.addValidationData(validation);
            assertEquals(++lastKnownNumValidations, ((XSSFSheet) sheet).getDataValidations().size());
        }
    }
    XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    Sheet sheetAt = wb2.getSheetAt(0);
    assertEquals(lastKnownNumValidations, ((XSSFSheet) sheetAt).getDataValidations().size());
    wb2.close();
}

Example 82 with CellReference

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

the class TestXSSFFormulaParser method test58648FormulaParsing.

@Test
public void test58648FormulaParsing() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("58648.xlsx");
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet xsheet = wb.getSheetAt(i);
        for (Row row : xsheet) {
            for (Cell cell : row) {
                if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    try {
                        evaluator.evaluateFormulaCellEnum(cell);
                    } catch (Exception e) {
                        CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
                        throw new RuntimeException("error at: " + cellRef, e);
                    }
                }
            }
        }
    }
    Sheet sheet = wb.getSheet("my-sheet");
    Cell cell = sheet.getRow(1).getCell(4);
    assertEquals(5d, cell.getNumericCellValue(), 0d);
    wb.close();
}

Example 83 with CellReference

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

the class TestXSSFHyperlink method testCopyHSSFHyperlink.

@Test
public void testCopyHSSFHyperlink() throws IOException {
    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
    HSSFHyperlink hlink = hssfworkbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
    hlink.setAddress("http://poi.apache.org/");
    hlink.setFirstColumn(3);
    hlink.setFirstRow(2);
    hlink.setLastColumn(5);
    hlink.setLastRow(6);
    hlink.setLabel("label");
    XSSFHyperlink xlink = new XSSFHyperlink(hlink);
    assertEquals("http://poi.apache.org/", xlink.getAddress());
    assertEquals(new CellReference(2, 3), new CellReference(xlink.getCellRef()));
    // Are HSSFHyperlink.label and XSSFHyperlink.tooltip the same? If so, perhaps one of these needs renamed for a consistent Hyperlink interface
    // assertEquals("label", xlink.getTooltip());
    hssfworkbook.close();
}

Example 84 with CellReference

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

the class TestXSSFPivotTableName method setUp.

@Override
@Before
public void setUp() {
    wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    Row row1 = sheet.createRow(0);
    // Create a cell and put a value in it.
    Cell cell = row1.createCell(0);
    cell.setCellValue("Names");
    Cell cell2 = row1.createCell(1);
    cell2.setCellValue("#");
    Cell cell7 = row1.createCell(2);
    cell7.setCellValue("Data");
    Cell cell10 = row1.createCell(3);
    cell10.setCellValue("Value");
    Row row2 = sheet.createRow(1);
    Cell cell3 = row2.createCell(0);
    cell3.setCellValue("Jan");
    Cell cell4 = row2.createCell(1);
    cell4.setCellValue(10);
    Cell cell8 = row2.createCell(2);
    cell8.setCellValue("Apa");
    Cell cell11 = row1.createCell(3);
    cell11.setCellValue(11.11);
    Row row3 = sheet.createRow(2);
    Cell cell5 = row3.createCell(0);
    cell5.setCellValue("Ben");
    Cell cell6 = row3.createCell(1);
    cell6.setCellValue(9);
    Cell cell9 = row3.createCell(2);
    cell9.setCellValue("Bepa");
    Cell cell12 = row1.createCell(3);
    cell12.setCellValue(12.12);
    XSSFName namedRange = sheet.getWorkbook().createName();
    namedRange.setRefersToFormula(sheet.getSheetName() + "!" + "A1:C2");
    pivotTable = sheet.createPivotTable(namedRange, new CellReference("H5"));
    XSSFSheet offsetSheet = wb.createSheet();
    Row tableRow_1 = offsetSheet.createRow(1);
    offsetOuterCell = tableRow_1.createCell(1);
    offsetOuterCell.setCellValue(-1);
    Cell tableCell_1_1 = tableRow_1.createCell(2);
    tableCell_1_1.setCellValue("Row #");
    Cell tableCell_1_2 = tableRow_1.createCell(3);
    tableCell_1_2.setCellValue("Exponent");
    Cell tableCell_1_3 = tableRow_1.createCell(4);
    tableCell_1_3.setCellValue("10^Exponent");
    Row tableRow_2 = offsetSheet.createRow(2);
    Cell tableCell_2_1 = tableRow_2.createCell(2);
    tableCell_2_1.setCellValue(0);
    Cell tableCell_2_2 = tableRow_2.createCell(3);
    tableCell_2_2.setCellValue(0);
    Cell tableCell_2_3 = tableRow_2.createCell(4);
    tableCell_2_3.setCellValue(1);
    Row tableRow_3 = offsetSheet.createRow(3);
    Cell tableCell_3_1 = tableRow_3.createCell(2);
    tableCell_3_1.setCellValue(1);
    Cell tableCell_3_2 = tableRow_3.createCell(3);
    tableCell_3_2.setCellValue(1);
    Cell tableCell_3_3 = tableRow_3.createCell(4);
    tableCell_3_3.setCellValue(10);
    Row tableRow_4 = offsetSheet.createRow(4);
    Cell tableCell_4_1 = tableRow_4.createCell(2);
    tableCell_4_1.setCellValue(2);
    Cell tableCell_4_2 = tableRow_4.createCell(3);
    tableCell_4_2.setCellValue(2);
    Cell tableCell_4_3 = tableRow_4.createCell(4);
    tableCell_4_3.setCellValue(100);
    namedRange = sheet.getWorkbook().createName();
    namedRange.setRefersToFormula("C2:E4");
    namedRange.setSheetIndex(sheet.getWorkbook().getSheetIndex(sheet));
    offsetPivotTable = offsetSheet.createPivotTable(namedRange, new CellReference("C6"));
}

Example 85 with CellReference

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

the class TestXSSFFormulaEvaluation method testEvaluateColumnGreaterThan255.

/**
     * Evaluation of cell references with column indexes greater than 255. See bugzilla 50096
     */
@Test
public void testEvaluateColumnGreaterThan255() throws IOException {
    XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("50096.xlsx");
    XSSFFormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    /**
         *  The first row simply contains the numbers 1 - 300.
         *  The second row simply refers to the cell value above in the first row by a simple formula.
         */
    for (int i = 245; i < 265; i++) {
        XSSFCell cell_noformula = wb.getSheetAt(0).getRow(0).getCell(i);
        XSSFCell cell_formula = wb.getSheetAt(0).getRow(1).getCell(i);
        CellReference ref_noformula = new CellReference(cell_noformula.getRowIndex(), cell_noformula.getColumnIndex());
        CellReference ref_formula = new CellReference(cell_noformula.getRowIndex(), cell_noformula.getColumnIndex());
        String fmla = cell_formula.getCellFormula();
        // assure that the formula refers to the cell above.
        // the check below is 'deep' and involves conversion of the shared formula:
        // in the sample file a shared formula in GN1 is spanned in the range GN2:IY2,
        assertEquals(ref_noformula.formatAsString(), fmla);
        CellValue cv_noformula = evaluator.evaluate(cell_noformula);
        CellValue cv_formula = evaluator.evaluate(cell_formula);
        assertEquals("Wrong evaluation result in " + ref_formula.formatAsString(), cv_noformula.getNumberValue(), cv_formula.getNumberValue(), 0);
    }
    wb.close();
}