Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataValidation.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataValidation.java?rev=1885859&r1=1885858&r2=1885859&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataValidation.java (original) +++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestDataValidation.java Sat Jan 23 23:04:49 2021 @@ -17,14 +17,14 @@ 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.apache.poi.util.POILogFactory; -import org.apache.poi.util.POILogger; import org.junit.jupiter.api.Test; /** @@ -35,472 +35,458 @@ import org.junit.jupiter.api.Test; public abstract class BaseTestDataValidation { private final ITestDataProvider _testDataProvider; - private static final POILogger log = POILogFactory.getLogger(BaseTestDataValidation.class); - 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; - /** Convenient access to OPERATOR constants */ - protected static final DataValidationConstraint.OperatorType OP = 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); - } - } + /** + * 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"); - private static void log(String msg) { - log.log(POILogger.INFO, msg); + 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); } - /** - * 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 { - log("\nTest no. 2 - Test Excel's Data validation mechanism"); - Workbook wb = _testDataProvider.createWorkbook(); - WorkbookFormatter wf = new WorkbookFormatter(wb); - - log(" Create sheet for Data Validation's number types ... "); - addSimpleNumericValidations(wf); - log("done !"); - - log(" Create sheet for 'List' Data Validation type ... "); - addListValidations(wf, wb); - log("done !"); - - log(" Create sheet for 'Date' and 'Time' Data Validation types ... "); - addDateTimeValidations(wf, wb); - log("done !"); - - log(" Create sheet for 'Text length' Data Validation type... "); - addTextLengthValidations(wf); - log("done !"); - - // Custom Validation type - log(" Create sheet for 'Custom' Data Validation type ... "); - addCustomValidations(wf); - log("done !"); - - _testDataProvider.writeOutAndReadBack(wb).close(); - - wb.close(); - } + 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()); + } + } + } - /* package */ static void setCellValue(Cell cell, String text) { - cell.setCellValue(text); - } + static void setCellValue(Cell cell, String text) { + cell.setCellValue(text); + } } \ No newline at end of file
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestNamedRange.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestNamedRange.java?rev=1885859&r1=1885858&r2=1885859&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestNamedRange.java (original) +++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestNamedRange.java Sat Jan 23 23:04:49 2021 @@ -17,6 +17,7 @@ package org.apache.poi.ss.usermodel; +import static org.junit.jupiter.api.Assertions.assertDoesNotThrow; import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertFalse; import static org.junit.jupiter.api.Assertions.assertNotNull; @@ -25,21 +26,17 @@ import static org.junit.jupiter.api.Asse import static org.junit.jupiter.api.Assertions.assertTrue; import java.io.IOException; -import java.util.Arrays; import java.util.List; -import org.apache.poi.hssf.HSSFITestDataProvider; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.ITestDataProvider; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellReference; -import org.apache.poi.util.IOUtils; import org.junit.jupiter.api.Test; +import org.junit.jupiter.params.ParameterizedTest; +import org.junit.jupiter.params.provider.ValueSource; /** * Tests of implementations of {@link org.apache.poi.ss.usermodel.Name}. - * - * @author Yegor Kozlov */ public abstract class BaseTestNamedRange { @@ -103,13 +100,13 @@ public abstract class BaseTestNamedRange @Test public final void testUnicodeNamedRange() throws Exception { - try (HSSFWorkbook wb1 = new HSSFWorkbook()) { + try (Workbook wb1 = _testDataProvider.createWorkbook()) { wb1.createSheet("Test"); Name name = wb1.createName(); name.setNameName("\u03B1"); name.setRefersToFormula("Test!$D$3:$E$8"); - try (HSSFWorkbook wb2 = HSSFITestDataProvider.instance.writeOutAndReadBack(wb1)) { + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { Name name2 = wb2.getName("\u03B1"); assertNotNull(name2); @@ -637,71 +634,64 @@ public abstract class BaseTestNamedRange // bug 56781: name validation only checks for first character's validity and presence of spaces // bug 60246: validate name does not allow DOT in named ranges - @Test - void testValid() throws IOException { - Workbook wb = _testDataProvider.createWorkbook(); - - Name name = wb.createName(); - for (String valid : Arrays.asList( - "Hello", - "number1", - "_underscore", - "underscore_", - "p.e.r.o.i.d.s", - "\\Backslash", - "Backslash\\" - )) { - name.setNameName(valid); + @ParameterizedTest + @ValueSource(strings = {"Hello", "number1", "_underscore", "underscore_", "p.e.r.o.i.d.s", "\\Backslash", "Backslash\\"}) + void testValid(String valid) throws IOException { + try (Workbook wb = _testDataProvider.createWorkbook()) { + Name name = wb.createName(); + assertDoesNotThrow(() -> name.setNameName(valid)); } - - wb.close(); } - @Test - void testInvalid() { + @ParameterizedTest + @ValueSource(strings = { + "1number", "Sheet1!A1", "Exclamation!", "Has Space", "Colon:", "A-Minus", "A+Plus", "Dollar$", ".periodAtBeginning", + //special shorthand + "R", "C", + // A1-style cell reference + "A1", + // R1C1-style cell reference + "R1C1", + "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ + "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ + "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ + "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ + "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters" + }) + void testInvalid(String invalid) { Workbook wb = _testDataProvider.createWorkbook(); Name name = wb.createName(); - IllegalArgumentException e = assertThrows(IllegalArgumentException.class, () -> name.setNameName("")); + IllegalArgumentException e; + e = assertThrows(IllegalArgumentException.class, () -> name.setNameName("")); assertEquals("Name cannot be blank", e.getMessage()); - for (String invalid : Arrays.asList( - "1number", - "Sheet1!A1", - "Exclamation!", - "Has Space", - "Colon:", - "A-Minus", - "A+Plus", - "Dollar$", - ".periodAtBeginning", - "R", //special shorthand - "C", //special shorthand - "A1", // A1-style cell reference - "R1C1", // R1C1-style cell reference - "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ - "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ - "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ - "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters..."+ - "NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters.NameThatIsLongerThan255Characters" - )) { - e = assertThrows(IllegalArgumentException.class, () -> name.setNameName(invalid)); - assertTrue(e.getMessage().startsWith("Invalid name: '"+invalid+"'")); - } - + e = assertThrows(IllegalArgumentException.class, () -> name.setNameName(invalid)); + assertTrue(e.getMessage().startsWith("Invalid name: '"+invalid+"'")); } // bug 60260: renaming a sheet with a named range referring to a unicode (non-ASCII) sheet name @Test - void renameSheetWithNamedRangeReferringToUnicodeSheetName() { - Workbook wb = _testDataProvider.createWorkbook(); - wb.createSheet("Sheet\u30FB1"); + void renameSheetWithNamedRangeReferringToUnicodeSheetName() throws IOException { + String unicodeName = "Sheet\u30FB201"; + String asciiName = "Sheet 1"; + String rangeName = "test_named_range"; + try (Workbook wb1 = _testDataProvider.createWorkbook()) { + wb1.createSheet(unicodeName); - Name name = wb.createName(); - name.setNameName("test_named_range"); - name.setRefersToFormula("'Sheet\u30FB201'!A1:A6"); + Name name1 = wb1.createName(); + name1.setNameName(rangeName); + name1.setRefersToFormula("'"+unicodeName+"'!A1:A6"); - wb.setSheetName(0, "Sheet 1"); - IOUtils.closeQuietly(wb); + wb1.setSheetName(0, asciiName); + assertEquals(asciiName, name1.getSheetName()); + + try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) { + Name name2 = wb2.getName(rangeName); + assertNotNull(name2); + // Eventually this will be updated, but currently we don't update the sheet name + assertEquals(asciiName, name2.getSheetName()); + } + } } } Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java?rev=1885859&r1=1885858&r2=1885859&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java (original) +++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java Sat Jan 23 23:04:49 2021 @@ -18,6 +18,7 @@ package org.apache.poi.ss.usermodel; import static org.apache.poi.POITestCase.assertBetween; +import static org.junit.jupiter.api.Assertions.assertDoesNotThrow; import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertFalse; import static org.junit.jupiter.api.Assertions.assertNotEquals; @@ -39,6 +40,7 @@ import java.util.Map.Entry; import java.util.Set; import org.apache.poi.common.usermodel.HyperlinkType; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.ITestDataProvider; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.util.CellAddress; @@ -1039,7 +1041,7 @@ public abstract class BaseTestSheet { // here we can only verify that setting some zoom values works, // range-checking is different between the implementations - sheet.setZoom(75); + assertDoesNotThrow(() -> sheet.setZoom(75)); } } @@ -1048,6 +1050,10 @@ public abstract class BaseTestSheet { try (Workbook wb = _testDataProvider.createWorkbook()) { Sheet sheet = wb.createSheet(); sheet.showInPane(2, 3); + if (wb instanceof HSSFWorkbook) { + assertEquals(2, sheet.getTopRow()); + assertEquals(3, sheet.getLeftCol()); + } } } Modified: poi/trunk/src/testcases/org/apache/poi/ss/util/TestDateFormatConverter.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/util/TestDateFormatConverter.java?rev=1885859&r1=1885858&r2=1885859&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/util/TestDateFormatConverter.java (original) +++ poi/trunk/src/testcases/org/apache/poi/ss/util/TestDateFormatConverter.java Sat Jan 23 23:04:49 2021 @@ -22,22 +22,24 @@ package org.apache.poi.ss.util; import static java.text.DateFormat.getDateInstance; import static java.text.DateFormat.getDateTimeInstance; import static java.text.DateFormat.getTimeInstance; +import static org.apache.poi.ss.util.DateFormatConverter.getPrefixForLocale; +import static org.junit.jupiter.api.Assertions.assertDoesNotThrow; import static org.junit.jupiter.api.Assertions.assertEquals; import static org.junit.jupiter.api.Assertions.assertNotEquals; import static org.junit.jupiter.api.Assertions.assertNotNull; import static org.junit.jupiter.api.Assertions.assertTrue; -import java.io.File; -import java.io.FileOutputStream; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Arrays; +import java.util.Comparator; import java.util.Date; import java.util.Locale; import java.util.Set; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.stream.Collectors; +import java.util.stream.IntStream; import java.util.stream.Stream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; @@ -49,7 +51,6 @@ import org.apache.poi.ss.usermodel.Sheet import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.util.LocaleID; import org.apache.poi.util.NullOutputStream; -import org.apache.poi.util.TempFile; import org.junit.jupiter.api.Test; import org.junit.jupiter.params.ParameterizedTest; import org.junit.jupiter.params.provider.CsvSource; @@ -74,50 +75,54 @@ final class TestDateFormatConverter { "false, true, " + DateFormat.FULL + ", Full" }) void testJavaDateFormatsInExcel(boolean dates, boolean times, int style, String styleName ) throws Exception { + String sheetName = (dates) ? ((times) ? "DateTimes" : "Dates") : "Times"; + String[] headers = { + "locale", "DisplayName", "Excel " + styleName, "java.text.DateFormat", + "Equals", "Java pattern", "Excel pattern" + }; + + Locale[] locales = Arrays.stream(DateFormat.getAvailableLocales()) + // only use locale with known LocaleIDs + .filter(l -> !getPrefixForLocale(l).isEmpty() || Locale.ROOT.equals(l) || l.toLanguageTag().isEmpty()) + .sorted(Comparator.comparing(Locale::toString)) + .toArray(Locale[]::new); + + try (Workbook workbook = new HSSFWorkbook()) { - String sheetName = (dates) ? ((times) ? "DateTimes" : "Dates") : "Times"; Sheet sheet = workbook.createSheet(sheetName); Row header = sheet.createRow(0); - header.createCell(0).setCellValue("locale"); - header.createCell(1).setCellValue("DisplayName"); - header.createCell(2).setCellValue("Excel " + styleName); - header.createCell(3).setCellValue("java.text.DateFormat"); - header.createCell(4).setCellValue("Equals"); - header.createCell(5).setCellValue("Java pattern"); - header.createCell(6).setCellValue("Excel pattern"); + IntStream.range(0, headers.length).forEach(i -> header.createCell(i).setCellValue(headers[i])); int rowNum = 1; - for (Locale locale : DateFormat.getAvailableLocales()) { - Row row = sheet.createRow(rowNum++); - - row.createCell(0).setCellValue(locale.toString()); - row.createCell(1).setCellValue(locale.getDisplayName(Locale.ROOT)); + final Cell[] cell = new Cell[7]; + final Date date = new Date(); + for (Locale locale : locales) { DateFormat dateFormat = (dates) ? (times ? getDateTimeInstance(style, style, locale) : getDateInstance(style, locale)) : getTimeInstance(style, locale); - - Cell cell = row.createCell(2); - Date date = new Date(); - cell.setCellValue(date); - CellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle(); - String javaDateFormatPattern = ((SimpleDateFormat) dateFormat).toPattern(); String excelFormatPattern = DateFormatConverter.convert(locale, javaDateFormatPattern); - DataFormat poiFormat = row.getSheet().getWorkbook().createDataFormat(); + Row row = sheet.createRow(rowNum++); + IntStream.range(0, headers.length).forEach(i -> cell[i] = row.createCell(i)); + CellStyle cellStyle = workbook.createCellStyle(); + DataFormat poiFormat = workbook.createDataFormat(); cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern)); - row.createCell(3).setCellValue(dateFormat.format(date)); - cell.setCellStyle(cellStyle); + cell[0].setCellValue(locale.toString()); + cell[1].setCellValue(locale.getDisplayName(Locale.ROOT)); + cell[2].setCellValue(date); + cell[2].setCellStyle(cellStyle); + cell[3].setCellValue(dateFormat.format(date)); // the formula returns TRUE is the formatted date in column C equals to the string in column D - row.createCell(4).setCellFormula("TEXT(C" + rowNum + ",G" + rowNum + ")=D" + rowNum); - row.createCell(5).setCellValue(javaDateFormatPattern); - row.createCell(6).setCellValue(excelFormatPattern); + cell[4].setCellFormula("TEXT(C" + rowNum + ",G" + rowNum + ")=D" + rowNum); + cell[5].setCellValue(javaDateFormatPattern); + cell[6].setCellValue(excelFormatPattern); } - workbook.write(new NullOutputStream()); + assertDoesNotThrow(() -> workbook.write(new NullOutputStream())); } } @@ -125,7 +130,7 @@ final class TestDateFormatConverter { void testJDK8EmptyLocale() { // JDK 8 seems to add an empty locale-string to the list returned via DateFormat.getAvailableLocales() // therefore we now cater for this special locale as well - String prefix = DateFormatConverter.getPrefixForLocale(new Locale("")); + String prefix = getPrefixForLocale(new Locale("")); assertEquals("", prefix); } @@ -163,7 +168,7 @@ final class TestDateFormatConverter { continue; } - String prefix = DateFormatConverter.getPrefixForLocale(loc); + String prefix = getPrefixForLocale(loc); assertNotNull(prefix, "Prefix not found - language tag: "+partTag); assertNotEquals("", prefix, "Prefix not found - language tag: "+partTag); Matcher m = p.matcher(prefix); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
