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

Example 21 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList 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 22 with CellRangeAddressList

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

the class TestXSSFDataValidation method test53965.

@Test
public void test53965() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    try {
        XSSFSheet sheet = wb.createSheet();
        //<-- works
        List<XSSFDataValidation> lst = sheet.getDataValidations();
        assertEquals(0, lst.size());
        //create the cell that will have the validation applied
        sheet.createRow(0).createCell(0);
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createCustomConstraint("SUM($A$1:$A$1) <= 3500");
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        sheet.addValidationData(validation);
        // this line caused XmlValueOutOfRangeException , see Bugzilla 3965
        lst = sheet.getDataValidations();
        assertEquals(1, lst.size());
    } finally {
        wb.close();
    }
}

Example 23 with CellRangeAddressList

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

the class HSSFSheet method getDataValidations.

@Override
public List<HSSFDataValidation> getDataValidations() {
    DataValidityTable dvt = _sheet.getOrCreateDataValidityTable();
    final List<HSSFDataValidation> hssfValidations = new ArrayList<HSSFDataValidation>();
    RecordVisitor visitor = new RecordVisitor() {

        private HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(getWorkbook());

        @Override
        public void visitRecord(Record r) {
            if (!(r instanceof DVRecord)) {
                return;
            }
            DVRecord dvRecord = (DVRecord) r;
            CellRangeAddressList regions = dvRecord.getCellRangeAddress().copy();
            DVConstraint constraint = DVConstraint.createDVConstraint(dvRecord, book);
            HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, constraint);
            hssfDataValidation.setErrorStyle(dvRecord.getErrorStyle());
            hssfDataValidation.setEmptyCellAllowed(dvRecord.getEmptyCellAllowed());
            hssfDataValidation.setSuppressDropDownArrow(dvRecord.getSuppressDropdownArrow());
            hssfDataValidation.createPromptBox(dvRecord.getPromptTitle(), dvRecord.getPromptText());
            hssfDataValidation.setShowPromptBox(dvRecord.getShowPromptOnCellSelected());
            hssfDataValidation.createErrorBox(dvRecord.getErrorTitle(), dvRecord.getErrorText());
            hssfDataValidation.setShowErrorBox(dvRecord.getShowErrorOnInvalidValue());
            hssfValidations.add(hssfDataValidation);
        }
    };
    dvt.visitContainedRecords(visitor);
    return hssfValidations;
}

Example 24 with CellRangeAddressList

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

the class BaseTestBugzillaIssues method test59200.

@Test
public void test59200() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    final Sheet sheet = wb.createSheet();
    DataValidation dataValidation;
    CellRangeAddressList headerCell = new CellRangeAddressList(0, 1, 0, 1);
    DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\"");
    dataValidation = sheet.getDataValidationHelper().createValidation(constraint, headerCell);
    // HSSF has 32/255 limits as part of the Spec, XSSF has no limit in the spec, but Excel applies a 255 length limit!
    // more than 255 fail for all
    checkFailures(dataValidation, TEST_256, TEST_32, true);
    checkFailures(dataValidation, TEST_32, TEST_256, true);
    // null does work
    checkFailures(dataValidation, null, null, false);
    // more than 32 title fail for HSSFWorkbook
    checkFailures(dataValidation, TEST_255, TEST_32, wb instanceof HSSFWorkbook);
    // special characters work
    checkFailures(dataValidation, TEST_SPECIAL_TITLE, TEST_SPECIAL, false);
    // 32 length title and 255 length text work for both
    checkFailures(dataValidation, TEST_32, TEST_255, false);
    dataValidation.setShowErrorBox(false);
    sheet.addValidationData(dataValidation);
    // write out and read back in to trigger some more validation
    final Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb);
    final Sheet sheetBack = wbBack.getSheetAt(0);
    final List<? extends DataValidation> dataValidations = sheetBack.getDataValidations();
    assertEquals(1, dataValidations.size());
    /*String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx";
        OutputStream str = new FileOutputStream("C:\\temp\\59200" + ext);
        try {
            wb.write(str);
        } finally {
            str.close();
        }*/
    wb.close();
}

Example 25 with CellRangeAddressList

use of org.apache.poi.ss.util.CellRangeAddressList in project swift by luastar.

the class ExcelUtils method writeXlsSheet.

/**
 * 将数据写入sheet
 *
 * @param workbook
 * @param sheetConfig
 * @throws Exception
 */
private static void writeXlsSheet(HSSFWorkbook workbook, ExportSheet sheetConfig) throws Exception {
    if (workbook == null || sheetConfig == null || CollectionUtils.isEmpty(sheetConfig.getColumnList())) {
        throw new IllegalArgumentException("excel导出参数错误！");
    }
    CreationHelper createHelper = workbook.getCreationHelper();
    DataFormat dataFormat = createHelper.createDataFormat();
    HSSFSheet sheet = workbook.createSheet(ObjUtils.ifNull(sheetConfig.getName(), "sheet1"));
    // 设置标题
    int columnNum = sheetConfig.getColumnList().size();
    List<ExportColumn> columnList = sheetConfig.getColumnList();
    HSSFRow rowTitle = sheet.createRow(0);
    for (int i = 0; i < columnNum; i++) {
        ExportColumn column = columnList.get(i);
        String title = ObjUtils.ifNull(column.getTitle(), "");
        HSSFCell cell = rowTitle.createCell(i);
        cell.setCellValue(createHelper.createRichTextString(title));
        cell.setCellStyle(ObjUtils.ifNull(column.getTitleStyle(), sheetConfig.getTitleStyle()));
        ExcelUtils.setColumnWidthTitle(column, sheet, i, title);
        sheet.setColumnHidden(i, column.isHidden());
    }
    if (CollectionUtils.isEmpty(sheetConfig.getDataList())) {
        logger.info("sheet {} 数据为空", sheet.getSheetName());
        return;
    }
    // 设置内容
    int rowNum = sheetConfig.getDataList().size();
    for (int i = 0; i < rowNum; i++) {
        HSSFRow row = sheet.createRow(i + 1);
        logger.info("写入第{}/{}条数据", row.getRowNum(), rowNum);
        Object data = sheetConfig.getDataList().get(i);
        for (int j = 0; j < columnNum; j++) {
            ExportColumn column = columnList.get(j);
            HSSFCell hssfCell = row.createCell(j);
            hssfCell.setCellStyle(ObjUtils.ifNull(column.getRowStyle(), sheetConfig.getRowStyle()));
            if (i == 0 && column.getType() == ExcelDataType.EnumValue && ArrayUtils.isNotEmpty(column.getValueArray())) {
                CellRangeAddressList addressList = new CellRangeAddressList(1, rowNum + 1, j, j);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(column.getValueArray());
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
                sheet.addValidationData(dataValidation);
            }
            // 获取列值
            Object valueObj;
            if (data instanceof Map) {
                valueObj = ((Map) data).get(column.getProp());
            } else {
                valueObj = PropertyUtils.getProperty(data, column.getProp());
            }
            if (valueObj == null) {
                continue;
            }
            // 设置宽度
            ExcelUtils.setColumnWidthRow(column, sheet, j, ObjUtils.toString(valueObj));
            // 设置不同类型的值
            if (column.getType() == ExcelDataType.EnumValue && valueObj instanceof IExcelEnum) {
                String value = ((IExcelEnum) valueObj).getValue();
                hssfCell.setCellValue(ObjUtils.ifNull(value, ""));
            } else if (column.getType() == ExcelDataType.IntegerValue || column.getType() == ExcelDataType.LongValue) {
                BigDecimal value = ObjUtils.toBigDecimal(valueObj, BigDecimal.ZERO).setScale(0);
                if (value.toString().length() <= 12) {
                    HSSFCellStyle cellStyle = hssfCell.getCellStyle();
                    cellStyle.setDataFormat(dataFormat.getFormat("#0"));
                    hssfCell.setCellStyle(cellStyle);
                    hssfCell.setCellValue(value.longValue());
                } else {
                    hssfCell.setCellValue(value.toString());
                }
            } else if (column.getType() == ExcelDataType.BigDecimalValue) {
                BigDecimal value = ObjUtils.toBigDecimal(valueObj, BigDecimal.ZERO).setScale(column.getScale(), BigDecimal.ROUND_HALF_UP);
                String format = "#,##0.00";
                if (column.getScale() > 0 && column.getScale() <= 8) {
                    format = "#,##0." + StringUtils.repeat("0", column.getScale());
                }
                HSSFCellStyle cellStyle = hssfCell.getCellStyle();
                cellStyle.setDataFormat(dataFormat.getFormat(format));
                hssfCell.setCellStyle(cellStyle);
                hssfCell.setCellValue(value.doubleValue());
            } else if (column.getType() == ExcelDataType.DateValue) {
                hssfCell.setCellValue(DateUtils.format((Date) (valueObj)));
            } else {
                hssfCell.setCellValue(ObjUtils.toString(valueObj, ""));
            }
        }
    }
}