| /* ==================================================================== |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to You under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| ==================================================================== */ |
| |
| package org.apache.poi.ss.usermodel; |
| |
| import static org.junit.jupiter.api.Assertions.assertEquals; |
| |
| import org.apache.poi.ss.ITestDataProvider; |
| import org.apache.poi.ss.usermodel.DataValidation.ErrorStyle; |
| import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType; |
| import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType; |
| import org.apache.poi.ss.util.CellRangeAddress; |
| import org.apache.poi.ss.util.CellRangeAddressList; |
| import org.junit.jupiter.api.Test; |
| |
| /** |
| * Class for testing Excel's data validation mechanism |
| * |
| * @author Dragos Buleandra ( dragos.buleandra@trade2b.ro ) |
| */ |
| public abstract class BaseTestDataValidation { |
| private final ITestDataProvider _testDataProvider; |
| |
| protected BaseTestDataValidation(ITestDataProvider testDataProvider) { |
| _testDataProvider = testDataProvider; |
| } |
| |
| /** |
| * Convenient access to ERROR_STYLE constants |
| */ |
| protected static final DataValidation.ErrorStyle ES = null; |
| /** |
| * Convenient access to OPERATOR constants |
| */ |
| protected static final DataValidationConstraint.ValidationType VT = null; |
| |
| private static final class ValidationAdder { |
| |
| private final CellStyle _style_1; |
| private final CellStyle _style_2; |
| private final int _validationType; |
| private final Sheet _sheet; |
| private int _currentRowIndex; |
| private final CellStyle _cellStyle; |
| |
| public ValidationAdder(Sheet fSheet, CellStyle style_1, CellStyle style_2, |
| CellStyle cellStyle, int validationType) { |
| _sheet = fSheet; |
| _style_1 = style_1; |
| _style_2 = style_2; |
| _cellStyle = cellStyle; |
| _validationType = validationType; |
| _currentRowIndex = fSheet.getPhysicalNumberOfRows(); |
| } |
| |
| void addValidation(int operatorType, String firstFormula, String secondFormula, |
| int errorStyle, String ruleDescr, String promptDescr, |
| boolean allowEmpty, boolean inputBox, boolean errorBox) { |
| String[] explicitListValues = null; |
| |
| addValidationInternal(operatorType, firstFormula, secondFormula, errorStyle, ruleDescr, |
| promptDescr, allowEmpty, inputBox, errorBox, true, |
| explicitListValues); |
| } |
| |
| private void addValidationInternal(int operatorType, String firstFormula, |
| String secondFormula, int errorStyle, String ruleDescr, String promptDescr, |
| boolean allowEmpty, boolean inputBox, boolean errorBox, boolean suppressDropDown, |
| String[] explicitListValues) { |
| int rowNum = _currentRowIndex++; |
| |
| DataValidationHelper dataValidationHelper = _sheet.getDataValidationHelper(); |
| DataValidationConstraint dc = createConstraint(dataValidationHelper, operatorType, firstFormula, secondFormula, explicitListValues); |
| |
| DataValidation dv = dataValidationHelper.createValidation(dc, new CellRangeAddressList(rowNum, rowNum, 0, 0)); |
| |
| dv.setEmptyCellAllowed(allowEmpty); |
| dv.setErrorStyle(errorStyle); |
| dv.createErrorBox("Invalid Input", "Something is wrong - check condition!"); |
| dv.createPromptBox("Validated Cell", "Allowable values have been restricted"); |
| |
| dv.setShowPromptBox(inputBox); |
| dv.setShowErrorBox(errorBox); |
| dv.setSuppressDropDownArrow(suppressDropDown); |
| |
| |
| _sheet.addValidationData(dv); |
| writeDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty, |
| inputBox, errorBox); |
| if (_cellStyle != null) { |
| Row row = _sheet.getRow(_sheet.getPhysicalNumberOfRows() - 1); |
| Cell cell = row.createCell(0); |
| cell.setCellStyle(_cellStyle); |
| } |
| writeOtherSettings(_sheet, _style_1, promptDescr); |
| } |
| |
| private DataValidationConstraint createConstraint(DataValidationHelper dataValidationHelper, int operatorType, String firstFormula, |
| String secondFormula, String[] explicitListValues) { |
| if (_validationType == ValidationType.LIST) { |
| if (explicitListValues != null) { |
| return dataValidationHelper.createExplicitListConstraint(explicitListValues); |
| } |
| return dataValidationHelper.createFormulaListConstraint(firstFormula); |
| } |
| if (_validationType == ValidationType.TIME) { |
| return dataValidationHelper.createTimeConstraint(operatorType, firstFormula, secondFormula); |
| } |
| if (_validationType == ValidationType.DATE) { |
| return dataValidationHelper.createDateConstraint(operatorType, firstFormula, secondFormula, null); |
| } |
| if (_validationType == ValidationType.FORMULA) { |
| return dataValidationHelper.createCustomConstraint(firstFormula); |
| } |
| |
| if (_validationType == ValidationType.INTEGER) { |
| return dataValidationHelper.createIntegerConstraint(operatorType, firstFormula, secondFormula); |
| } |
| if (_validationType == ValidationType.DECIMAL) { |
| return dataValidationHelper.createDecimalConstraint(operatorType, firstFormula, secondFormula); |
| } |
| if (_validationType == ValidationType.TEXT_LENGTH) { |
| return dataValidationHelper.createTextLengthConstraint(operatorType, firstFormula, secondFormula); |
| } |
| return null; |
| } |
| |
| /** |
| * writes plain text values into cells in a tabular format to form comments readable from within |
| * the spreadsheet. |
| */ |
| private static void writeDataValidationSettings(Sheet sheet, CellStyle style_1, |
| CellStyle style_2, String strCondition, boolean allowEmpty, boolean inputBox, |
| boolean errorBox) { |
| Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); |
| // condition's string |
| Cell cell = row.createCell(1); |
| cell.setCellStyle(style_1); |
| setCellValue(cell, strCondition); |
| // allow empty cells |
| cell = row.createCell(2); |
| cell.setCellStyle(style_2); |
| setCellValue(cell, ((allowEmpty) ? "yes" : "no")); |
| // show input box |
| cell = row.createCell(3); |
| cell.setCellStyle(style_2); |
| setCellValue(cell, ((inputBox) ? "yes" : "no")); |
| // show error box |
| cell = row.createCell(4); |
| cell.setCellStyle(style_2); |
| setCellValue(cell, ((errorBox) ? "yes" : "no")); |
| } |
| |
| private static void writeOtherSettings(Sheet sheet, CellStyle style, |
| String strStettings) { |
| Row row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1); |
| Cell cell = row.createCell(5); |
| cell.setCellStyle(style); |
| setCellValue(cell, strStettings); |
| } |
| |
| void addListValidation(String[] explicitListValues, String listFormula, String listValsDescr, |
| boolean allowEmpty, boolean suppressDropDown) { |
| String promptDescr = (allowEmpty ? "empty ok" : "not empty") |
| + ", " + (suppressDropDown ? "no drop-down" : "drop-down"); |
| addValidationInternal(ValidationType.LIST, listFormula, null, ErrorStyle.STOP, listValsDescr, promptDescr, |
| allowEmpty, false, true, suppressDropDown, explicitListValues); |
| } |
| } |
| |
| /** |
| * Manages the cell styles used for formatting the output spreadsheet |
| */ |
| private static final class WorkbookFormatter { |
| |
| private final Workbook _wb; |
| private final CellStyle _style_1; |
| private final CellStyle _style_2; |
| private final CellStyle _style_3; |
| private final CellStyle _style_4; |
| private Sheet _currentSheet; |
| |
| public WorkbookFormatter(Workbook wb) { |
| _wb = wb; |
| _style_1 = createStyle(wb, HorizontalAlignment.LEFT); |
| _style_2 = createStyle(wb, HorizontalAlignment.CENTER); |
| _style_3 = createStyle(wb, HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.getIndex(), true); |
| _style_4 = createHeaderStyle(wb); |
| } |
| |
| private static CellStyle createStyle(Workbook wb, HorizontalAlignment h_align, short color, |
| boolean bold) { |
| Font font = wb.createFont(); |
| if (bold) { |
| font.setBold(true); |
| } |
| |
| CellStyle cellStyle = wb.createCellStyle(); |
| cellStyle.setFont(font); |
| cellStyle.setFillForegroundColor(color); |
| cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); |
| cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); |
| cellStyle.setAlignment(h_align); |
| cellStyle.setBorderLeft(BorderStyle.THIN); |
| cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); |
| cellStyle.setBorderTop(BorderStyle.THIN); |
| cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); |
| cellStyle.setBorderRight(BorderStyle.THIN); |
| cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); |
| cellStyle.setBorderBottom(BorderStyle.THIN); |
| cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); |
| |
| return cellStyle; |
| } |
| |
| private static CellStyle createStyle(Workbook wb, HorizontalAlignment h_align) { |
| return createStyle(wb, h_align, IndexedColors.WHITE.getIndex(), false); |
| } |
| |
| private static CellStyle createHeaderStyle(Workbook wb) { |
| Font font = wb.createFont(); |
| font.setColor(IndexedColors.WHITE.getIndex()); |
| font.setBold(true); |
| |
| CellStyle cellStyle = wb.createCellStyle(); |
| cellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); |
| cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); |
| cellStyle.setAlignment(HorizontalAlignment.CENTER); |
| cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); |
| cellStyle.setBorderLeft(BorderStyle.THIN); |
| cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex()); |
| cellStyle.setBorderTop(BorderStyle.THIN); |
| cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex()); |
| cellStyle.setBorderRight(BorderStyle.THIN); |
| cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex()); |
| cellStyle.setBorderBottom(BorderStyle.THIN); |
| cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex()); |
| cellStyle.setFont(font); |
| return cellStyle; |
| } |
| |
| |
| public Sheet createSheet(String sheetName) { |
| _currentSheet = _wb.createSheet(sheetName); |
| return _currentSheet; |
| } |
| |
| void createDVTypeRow(String strTypeDescription) { |
| Sheet sheet = _currentSheet; |
| Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); |
| sheet.addMergedRegion(new CellRangeAddress(sheet.getPhysicalNumberOfRows() - 1, sheet.getPhysicalNumberOfRows() - 1, 0, 5)); |
| Cell cell = row.createCell(0); |
| setCellValue(cell, strTypeDescription); |
| cell.setCellStyle(_style_3); |
| row = sheet.createRow(sheet.getPhysicalNumberOfRows()); |
| } |
| |
| void createHeaderRow() { |
| Sheet sheet = _currentSheet; |
| Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); |
| row.setHeight((short) 400); |
| for (int i = 0; i < 6; i++) { |
| row.createCell(i).setCellStyle(_style_4); |
| if (i == 2 || i == 3 || i == 4) { |
| sheet.setColumnWidth(i, 3500); |
| } else if (i == 5) { |
| sheet.setColumnWidth(i, 10000); |
| } else { |
| sheet.setColumnWidth(i, 8000); |
| } |
| } |
| Cell cell = row.getCell(0); |
| setCellValue(cell, "Data validation cells"); |
| cell = row.getCell(1); |
| setCellValue(cell, "Condition"); |
| cell = row.getCell(2); |
| setCellValue(cell, "Allow blank"); |
| cell = row.getCell(3); |
| setCellValue(cell, "Prompt box"); |
| cell = row.getCell(4); |
| setCellValue(cell, "Error box"); |
| cell = row.getCell(5); |
| setCellValue(cell, "Other settings"); |
| } |
| |
| public ValidationAdder createValidationAdder(CellStyle cellStyle, int dataValidationType) { |
| return new ValidationAdder(_currentSheet, _style_1, _style_2, cellStyle, dataValidationType); |
| } |
| |
| void createDVDescriptionRow(String strTypeDescription) { |
| Sheet sheet = _currentSheet; |
| Row row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1); |
| sheet.addMergedRegion(new CellRangeAddress(sheet.getPhysicalNumberOfRows() - 1, sheet.getPhysicalNumberOfRows() - 1, 0, 5)); |
| Cell cell = row.createCell(0); |
| setCellValue(cell, strTypeDescription); |
| cell.setCellStyle(_style_3); |
| row = sheet.createRow(sheet.getPhysicalNumberOfRows()); |
| } |
| } |
| |
| |
| private void addCustomValidations(WorkbookFormatter wf) { |
| wf.createSheet("Custom"); |
| wf.createHeaderRow(); |
| |
| ValidationAdder va = wf.createValidationAdder(null, ValidationType.FORMULA); |
| va.addValidation(OperatorType.BETWEEN, "ISNUMBER($A2)", null, ErrorStyle.STOP, "ISNUMBER(A2)", "Error box type = STOP", true, true, true); |
| va.addValidation(OperatorType.BETWEEN, "IF(SUM(A2:A3)=5,TRUE,FALSE)", null, ErrorStyle.WARNING, "IF(SUM(A2:A3)=5,TRUE,FALSE)", "Error box type = WARNING", false, false, true); |
| } |
| |
| private static void addSimpleNumericValidations(WorkbookFormatter wf) { |
| // data validation's number types |
| wf.createSheet("Numbers"); |
| |
| // "Whole number" validation type |
| wf.createDVTypeRow("Whole number"); |
| wf.createHeaderRow(); |
| |
| ValidationAdder va = wf.createValidationAdder(null, ValidationType.INTEGER); |
| va.addValidation(OperatorType.BETWEEN, "2", "6", ErrorStyle.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); |
| va.addValidation(OperatorType.NOT_BETWEEN, "2", "6", ErrorStyle.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); |
| va.addValidation(OperatorType.EQUAL, "=3+2", null, ErrorStyle.WARNING, "Equal to (3+2)", "Error box type = WARNING", false, false, true); |
| va.addValidation(OperatorType.NOT_EQUAL, "3", null, ErrorStyle.WARNING, "Not equal to 3", "-", false, false, false); |
| va.addValidation(OperatorType.GREATER_THAN, "3", null, ErrorStyle.WARNING, "Greater than 3", "-", true, false, false); |
| va.addValidation(OperatorType.LESS_THAN, "3", null, ErrorStyle.WARNING, "Less than 3", "-", true, true, false); |
| va.addValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ErrorStyle.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); |
| va.addValidation(OperatorType.LESS_OR_EQUAL, "4", null, ErrorStyle.STOP, "Less than or equal to 4", "-", false, true, false); |
| |
| // "Decimal" validation type |
| wf.createDVTypeRow("Decimal"); |
| wf.createHeaderRow(); |
| |
| va = wf.createValidationAdder(null, ValidationType.DECIMAL); |
| va.addValidation(OperatorType.BETWEEN, "2", "6", ErrorStyle.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); |
| va.addValidation(OperatorType.NOT_BETWEEN, "2", "6", ErrorStyle.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); |
| va.addValidation(OperatorType.EQUAL, "3", null, ErrorStyle.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); |
| va.addValidation(OperatorType.NOT_EQUAL, "3", null, ErrorStyle.WARNING, "Not equal to 3", "-", false, false, false); |
| va.addValidation(OperatorType.GREATER_THAN, "=12/6", null, ErrorStyle.WARNING, "Greater than (12/6)", "-", true, false, false); |
| va.addValidation(OperatorType.LESS_THAN, "3", null, ErrorStyle.WARNING, "Less than 3", "-", true, true, false); |
| va.addValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ErrorStyle.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); |
| va.addValidation(OperatorType.LESS_OR_EQUAL, "4", null, ErrorStyle.STOP, "Less than or equal to 4", "-", false, true, false); |
| } |
| |
| private static void addListValidations(WorkbookFormatter wf, Workbook wb) { |
| final String cellStrValue |
| = "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 " |
| + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 " |
| + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 " |
| + "a b c d e f g h i j k l m n o p r s t u v x y z w 0 1 2 3 4 "; |
| final String dataSheetName = "list_data"; |
| // "List" Data Validation type |
| Sheet fSheet = wf.createSheet("Lists"); |
| Sheet dataSheet = wb.createSheet(dataSheetName); |
| |
| |
| wf.createDVTypeRow("Explicit lists - list items are explicitly provided"); |
| wf.createDVDescriptionRow("Disadvantage - sum of item's length should be less than 255 characters"); |
| wf.createHeaderRow(); |
| |
| ValidationAdder va = wf.createValidationAdder(null, ValidationType.LIST); |
| String listValsDescr = "POIFS,HSSF,HWPF,HPSF"; |
| String[] listVals = listValsDescr.split(","); |
| va.addListValidation(listVals, null, listValsDescr, false, false); |
| va.addListValidation(listVals, null, listValsDescr, false, true); |
| va.addListValidation(listVals, null, listValsDescr, true, false); |
| va.addListValidation(listVals, null, listValsDescr, true, true); |
| |
| |
| wf.createDVTypeRow("Reference lists - list items are taken from others cells"); |
| wf.createDVDescriptionRow("Advantage - no restriction regarding the sum of item's length"); |
| wf.createHeaderRow(); |
| va = wf.createValidationAdder(null, ValidationType.LIST); |
| String strFormula = "$A$30:$A$39"; |
| va.addListValidation(null, strFormula, strFormula, false, false); |
| |
| strFormula = dataSheetName + "!$A$1:$A$10"; |
| va.addListValidation(null, strFormula, strFormula, false, false); |
| Name namedRange = wb.createName(); |
| namedRange.setNameName("myName"); |
| namedRange.setRefersToFormula(dataSheetName + "!$A$2:$A$7"); |
| strFormula = "myName"; |
| va.addListValidation(null, strFormula, strFormula, false, false); |
| strFormula = "offset(myName, 2, 1, 4, 2)"; // Note about last param '2': |
| // - Excel expects single row or single column when entered in UI, but process this OK otherwise |
| va.addListValidation(null, strFormula, strFormula, false, false); |
| |
| // add list data on same sheet |
| for (int i = 0; i < 10; i++) { |
| Row currRow = fSheet.createRow(i + 29); |
| setCellValue(currRow.createCell(0), cellStrValue); |
| } |
| // add list data on another sheet |
| for (int i = 0; i < 10; i++) { |
| Row currRow = dataSheet.createRow(i + 0); |
| setCellValue(currRow.createCell(0), "Data a" + i); |
| setCellValue(currRow.createCell(1), "Data b" + i); |
| setCellValue(currRow.createCell(2), "Data c" + i); |
| } |
| } |
| |
| private static void addDateTimeValidations(WorkbookFormatter wf, Workbook wb) { |
| wf.createSheet("Dates and Times"); |
| |
| DataFormat dataFormat = wb.createDataFormat(); |
| short fmtDate = dataFormat.getFormat("m/d/yyyy"); |
| short fmtTime = dataFormat.getFormat("h:mm"); |
| CellStyle cellStyle_date = wb.createCellStyle(); |
| cellStyle_date.setDataFormat(fmtDate); |
| CellStyle cellStyle_time = wb.createCellStyle(); |
| cellStyle_time.setDataFormat(fmtTime); |
| |
| wf.createDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)"); |
| wf.createHeaderRow(); |
| |
| ValidationAdder va = wf.createValidationAdder(cellStyle_date, ValidationType.DATE); |
| va.addValidation(OperatorType.BETWEEN, "2004/01/02", "2004/01/06", ErrorStyle.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true); |
| va.addValidation(OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", ErrorStyle.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true); |
| va.addValidation(OperatorType.EQUAL, "2004/03/02", null, ErrorStyle.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true); |
| va.addValidation(OperatorType.NOT_EQUAL, "2004/03/02", null, ErrorStyle.WARNING, "Not equal to 3/2/2004", "-", false, false, false); |
| va.addValidation(OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, ErrorStyle.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false); |
| va.addValidation(OperatorType.LESS_THAN, "2004/03/02", null, ErrorStyle.WARNING, "Less than 3/2/2004", "-", true, true, false); |
| va.addValidation(OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, ErrorStyle.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true); |
| va.addValidation(OperatorType.LESS_OR_EQUAL, "2004/03/04", null, ErrorStyle.STOP, "Less than or equal to 3/4/2004", "-", false, true, false); |
| |
| // "Time" validation type |
| wf.createDVTypeRow("Time ( cells are already formated as time - h:mm)"); |
| wf.createHeaderRow(); |
| |
| va = wf.createValidationAdder(cellStyle_time, ValidationType.TIME); |
| va.addValidation(OperatorType.BETWEEN, "12:00", "16:00", ErrorStyle.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true); |
| va.addValidation(OperatorType.NOT_BETWEEN, "12:00", "16:00", ErrorStyle.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true); |
| va.addValidation(OperatorType.EQUAL, "13:35", null, ErrorStyle.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true); |
| va.addValidation(OperatorType.NOT_EQUAL, "13:35", null, ErrorStyle.WARNING, "Not equal to 13:35", "-", false, false, false); |
| va.addValidation(OperatorType.GREATER_THAN, "12:00", null, ErrorStyle.WARNING, "Greater than 12:00", "-", true, false, false); |
| va.addValidation(OperatorType.LESS_THAN, "=1/2", null, ErrorStyle.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false); |
| va.addValidation(OperatorType.GREATER_OR_EQUAL, "14:00", null, ErrorStyle.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true); |
| va.addValidation(OperatorType.LESS_OR_EQUAL, "14:00", null, ErrorStyle.STOP, "Less than or equal to 14:00", "-", false, true, false); |
| } |
| |
| private static void addTextLengthValidations(WorkbookFormatter wf) { |
| wf.createSheet("Text lengths"); |
| wf.createHeaderRow(); |
| |
| ValidationAdder va = wf.createValidationAdder(null, ValidationType.TEXT_LENGTH); |
| va.addValidation(OperatorType.BETWEEN, "2", "6", ErrorStyle.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); |
| va.addValidation(OperatorType.NOT_BETWEEN, "2", "6", ErrorStyle.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); |
| va.addValidation(OperatorType.EQUAL, "3", null, ErrorStyle.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); |
| va.addValidation(OperatorType.NOT_EQUAL, "3", null, ErrorStyle.WARNING, "Not equal to 3", "-", false, false, false); |
| va.addValidation(OperatorType.GREATER_THAN, "3", null, ErrorStyle.WARNING, "Greater than 3", "-", true, false, false); |
| va.addValidation(OperatorType.LESS_THAN, "3", null, ErrorStyle.WARNING, "Less than 3", "-", true, true, false); |
| va.addValidation(OperatorType.GREATER_OR_EQUAL, "4", null, ErrorStyle.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); |
| va.addValidation(OperatorType.LESS_OR_EQUAL, "4", null, ErrorStyle.STOP, "Less than or equal to 4", "-", false, true, false); |
| } |
| |
| @Test |
| void testDataValidation() throws Exception { |
| try (Workbook wb1 = _testDataProvider.createWorkbook()) { |
| WorkbookFormatter wf1 = new WorkbookFormatter(wb1); |
| addSimpleNumericValidations(wf1); |
| addListValidations(wf1, wb1); |
| addDateTimeValidations(wf1, wb1); |
| addTextLengthValidations(wf1); |
| // Custom Validation type |
| addCustomValidations(wf1); |
| try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { |
| Sheet sh = wb2.getSheet("Numbers"); |
| assertEquals(16, sh.getDataValidations().size()); |
| } |
| |
| } |
| } |
| |
| |
| static void setCellValue(Cell cell, String text) { |
| cell.setCellValue(text); |
| } |
| |
| } |
