Index: src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java =================================================================== --- src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java (revision 627921) +++ src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java (working copy) @@ -16,14 +16,25 @@ package org.apache.poi.hssf.usermodel; +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.File; +import java.io.FileInputStream; +import java.io.FileNotFoundException; +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.io.PrintStream; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Date; + import junit.framework.TestCase; -import org.apache.poi.hssf.util.*; +import org.apache.poi.hssf.util.HSSFColor; +import org.apache.poi.hssf.util.HSSFDataValidation; +import org.apache.poi.hssf.util.Region; -import java.io.*; -import java.util.*; -import java.text.SimpleDateFormat; - /** *

Title: TestDataValidation

*

Description: Class for testing Excel's data validation mechanism @@ -32,13 +43,30 @@ *

* @author Dragos Buleandra ( dragos.buleandra@trade2b.ro ) */ -public class TestDataValidation extends TestCase -{ - public TestDataValidation(String name) - { - super(name); - } +public final class TestDataValidation extends TestCase { + /** + * Convenient access to ERROR_STYLE constants + */ + private static final class ES { + public static final int STOP = HSSFDataValidation.ERROR_STYLE_STOP; + public static final int WARNING = HSSFDataValidation.ERROR_STYLE_WARNING; + public static final int INFO = HSSFDataValidation.ERROR_STYLE_INFO; + } + /** + * Convenient access to OPERATOR constants + */ + private static final class OP { + public static final int BETWEEN = HSSFDataValidation.OPERATOR_BETWEEN; + public static final int NOT_BETWEEN = HSSFDataValidation.OPERATOR_NOT_BETWEEN; + public static final int EQUAL = HSSFDataValidation.OPERATOR_EQUAL; + public static final int NOT_EQUAL = HSSFDataValidation.OPERATOR_NOT_EQUAL; + public static final int GREATER_THAN = HSSFDataValidation.OPERATOR_GREATER_THAN; + public static final int LESS_THAN = HSSFDataValidation.OPERATOR_LESS_THAN; + public static final int GREATER_OR_EQUAL = HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL; + public static final int LESS_OR_EQUAL = HSSFDataValidation.OPERATOR_LESS_OR_EQUAL; + } + protected void setUp() { String filename = System.getProperty("HSSF.testdata.path"); @@ -48,721 +76,401 @@ } } - public void testDataValidation() throws Exception - { - System.out.println("\nTest no. 2 - Test Excel's Data validation mechanism"); - String resultFile = System.getProperty("HSSF.testdata.path")+"/TestDataValidation.xls"; - HSSFWorkbook wb = new HSSFWorkbook(); + private static void logln(String msg) { + log(msg); + log(System.getProperty("line.separator")); + } + private static void log(String msg) { + + if (false) { // well behaved tests should not have any console output + System.out.print(msg); + } + } + + private static final class ValidationAdder { + + private final HSSFCellStyle _style_1; + private final HSSFCellStyle _style_2; + private final int _validationType; + private final HSSFSheet _sheet; + private int _currentRowIndex; + private final HSSFCellStyle _cellStyle; + private boolean _discrepancy; - HSSFCellStyle style_1 = this.createStyle( wb, HSSFCellStyle.ALIGN_LEFT ); - HSSFCellStyle style_2 = this.createStyle( wb, HSSFCellStyle.ALIGN_CENTER ); - HSSFCellStyle style_3 = this.createStyle( wb, HSSFCellStyle.ALIGN_CENTER, HSSFColor.GREY_25_PERCENT.index, true ); - HSSFCellStyle style_4 = this.createHeaderStyle(wb); - HSSFDataValidation data_validation = null; + public ValidationAdder(HSSFSheet fSheet, HSSFCellStyle style_1, HSSFCellStyle style_2, + HSSFCellStyle cellStyle, int validationType) { + _sheet = fSheet; + _style_1 = style_1; + _style_2 = style_2; + _cellStyle = cellStyle; + _validationType = validationType; + _currentRowIndex = fSheet.getPhysicalNumberOfRows(); + } + public void addValidation(int operatorType, String firstFormula, String secondFormula, + int errorStyle, String ruleDescr, String promptDescr, + boolean allowEmpty, boolean inputBox, boolean errorBox) { + addValidation(operatorType, firstFormula, secondFormula, + errorStyle, ruleDescr, promptDescr, + allowEmpty, inputBox, errorBox, false); + } - //data validation's number types - System.out.print(" Create sheet for Data Validation's number types ... "); - HSSFSheet fSheet = wb.createSheet("Number types"); + public void addValidation(int operatorType, String firstFormula, String secondFormula, + int errorStyle, String ruleDescr, String promptDescr, boolean allowEmpty, + boolean inputBox, boolean errorBox, boolean suppressDropDown) { - //"Whole number" validation type - this.createDVTypeRow( wb, 0, style_3, "Whole number"); - this.createHeaderRow( wb, 0, style_4 ); + short rowNum = (short) _currentRowIndex++; + HSSFDataValidation data_validation = new HSSFDataValidation(rowNum, (short) 0, rowNum, + (short) 0); - short start_row = (short)fSheet.getPhysicalNumberOfRows(); - data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_INTEGER); - data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); - data_validation.setFirstFormula("2"); - data_validation.setSecondFormula("6"); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 2 and 6 ", true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + data_validation.setDataValidationType(_validationType); + data_validation.setEmptyCellAllowed(allowEmpty); + data_validation.setOperator(operatorType); + data_validation.setFirstFormula(firstFormula); + data_validation.setSecondFormula(secondFormula); + data_validation.setErrorStyle(errorStyle); + data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); + if (!_discrepancy) { + data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); + } + _discrepancy = false; + data_validation.setShowPromptBox(inputBox); + data_validation.setShowErrorBox(errorBox); + if(suppressDropDown) { + data_validation.setSurppressDropDownArrow(true); + } + + + _sheet.addValidationData(data_validation); + writeDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty, + inputBox, errorBox); + if (_cellStyle != null) { + HSSFRow row = _sheet.getRow(_sheet.getPhysicalNumberOfRows() - 1); + HSSFCell cell = row.createCell((short) 0); + cell.setCellStyle(_cellStyle); + } + writeOtherSettings(_sheet, _style_1, promptDescr); - data_validation.setFirstRow((short)(start_row+1)); - data_validation.setLastRow((short)(start_row+1)); - data_validation.setEmptyCellAllowed(false); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 2 and 6 ", false, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + } + /** + * writes plain text values into cells in a tabular format to form comments readable from within + * the spreadsheet. + */ + private static void writeDataValidationSettings(HSSFSheet sheet, HSSFCellStyle style_1, + HSSFCellStyle style_2, String strCondition, boolean allowEmpty, boolean inputBox, + boolean errorBox) { + HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows()); + // condition's string + HSSFCell cell = row.createCell((short) 1); + cell.setCellStyle(style_1); + setCellValue(cell, strCondition); + // allow empty cells + cell = row.createCell((short) 2); + cell.setCellStyle(style_2); + setCellValue(cell, ((allowEmpty) ? "yes" : "no")); + // show input box + cell = row.createCell((short) 3); + cell.setCellStyle(style_2); + setCellValue(cell, ((inputBox) ? "yes" : "no")); + // show error box + cell = row.createCell((short) 4); + cell.setCellStyle(style_2); + setCellValue(cell, ((errorBox) ? "yes" : "no")); + } + private static void writeOtherSettings(HSSFSheet sheet, HSSFCellStyle style, + String strStettings) { + HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows() - 1); + HSSFCell cell = row.createCell((short) 5); + cell.setCellStyle(style); + setCellValue(cell, strStettings); + } - data_validation.setFirstRow((short)(start_row+2)); - data_validation.setLastRow((short)(start_row+2)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3", false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + /** + * This method exists because of discrepancies in previous versions of this test. + * It is only present to mimic the same inconsistencies in the proof spreadsheet. + * The proof should be updated and this method deleted + */ + public void introduceDiscrepancy() { + _discrepancy = true; - data_validation.setFirstRow((short)(start_row+3)); - data_validation.setLastRow((short)(start_row+3)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3", false, false, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - data_validation.setFirstRow((short)(start_row+4)); - data_validation.setLastRow((short)(start_row+4)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3", true, false, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - data_validation.setFirstRow((short)(start_row+5)); - data_validation.setLastRow((short)(start_row+5)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3", true, true, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - data_validation.setFirstRow((short)(start_row+6)); - data_validation.setLastRow((short)(start_row+6)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); - data_validation.setShowErrorBox(true); - data_validation.setFirstFormula("4"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 4", true, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); - - data_validation.setFirstRow((short)(start_row+7)); - data_validation.setLastRow((short)(start_row+7)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("4"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 4", false, true, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - //"Decimal" validation type - this.createDVTypeRow( wb, 0, style_3, "Decimal"); - this.createHeaderRow( wb, 0, style_4 ); - - start_row += (short)(8+4); - data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_DECIMAL); - data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); - data_validation.setFirstFormula("2"); - data_validation.setSecondFormula("6"); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 2 and 6 ", true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); - - data_validation.setFirstRow((short)(start_row+1)); - data_validation.setLastRow((short)(start_row+1)); - data_validation.setEmptyCellAllowed(false); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 2 and 6 ", false, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); - - data_validation.setFirstRow((short)(start_row+2)); - data_validation.setLastRow((short)(start_row+2)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3", false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); - - data_validation.setFirstRow((short)(start_row+3)); - data_validation.setLastRow((short)(start_row+3)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3", false, false, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - data_validation.setFirstRow((short)(start_row+4)); - data_validation.setLastRow((short)(start_row+4)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3", true, false, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - data_validation.setFirstRow((short)(start_row+5)); - data_validation.setLastRow((short)(start_row+5)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3", true, true, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - data_validation.setFirstRow((short)(start_row+6)); - data_validation.setLastRow((short)(start_row+6)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); - data_validation.setShowErrorBox(true); - data_validation.setFirstFormula("4"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 4", true, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); - - data_validation.setFirstRow((short)(start_row+7)); - data_validation.setLastRow((short)(start_row+7)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("4"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 4", false, true, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - - System.out.println("done !"); - - //"List" Data Validation type - /** @todo List*/ - System.out.print(" Create sheet for 'List' Data Validation type ... "); - fSheet = wb.createSheet("Lists"); - - this.createDVTypeRow( wb, 1, style_3, "Explicit lists - list items are explicitly provided"); - this.createDVDeescriptionRow( wb, 1, style_3, "Disadvantage - sum of item's length should be less than 255 characters"); - this.createHeaderRow( wb, 1, style_4 ); - - start_row = (short)fSheet.getPhysicalNumberOfRows(); - data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula("1+2+3"); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(false); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); - - data_validation = new HSSFDataValidation((short)(start_row+1),(short)0,(short)(start_row+1),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula("4+5+6+7"); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(false); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); - - data_validation = new HSSFDataValidation((short)(start_row+2),(short)0,(short)(start_row+2),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula("7+21"); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(true); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); - - data_validation = new HSSFDataValidation((short)(start_row+3),(short)0,(short)(start_row+3),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula("8/2"); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(true); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "POIFS,HSSF,HWPF,HPSF", false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); - - this.createDVTypeRow( wb, 1, style_3, "Reference lists - list items are taken from others cells"); - this.createDVDeescriptionRow( wb, 1, style_3, "Advantage - no restriction regarding the sum of item's length"); - this.createHeaderRow( wb, 1, style_4 ); - - start_row += (short)(4+5); - 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 "; - - String strFormula = "$A$100:$A$120"; - data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula(strFormula); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(false); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); - - data_validation = new HSSFDataValidation((short)(start_row+1),(short)0,(short)(start_row+1),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula(strFormula); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(false); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=yes" ); - - data_validation = new HSSFDataValidation((short)(start_row+2),(short)0,(short)(start_row+2),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula(strFormula); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(true); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); - - data_validation = new HSSFDataValidation((short)(start_row+3),(short)0,(short)(start_row+3),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstFormula(strFormula); - data_validation.setSecondFormula(null); - data_validation.setSurppressDropDownArrow(true); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, strFormula, false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type=STOP ; In-cell dropdown=no" ); - - for (int i=100; i<=120; i++) - { - HSSFRow currRow = fSheet.createRow(i); - currRow.createCell((short)0).setCellValue(cellStrValue); -// currRow.hide( true ); + } } + + + public void testDataValidation() { + logln("\nTest no. 2 - Test Excel's Data validation mechanism"); + String resultFile = System.getProperty("HSSF.testdata.path")+"/TestDataValidation.xls"; + HSSFWorkbook wb = new HSSFWorkbook(); - System.out.println("done !"); + HSSFCellStyle style_1 = createStyle( wb, HSSFCellStyle.ALIGN_LEFT ); + HSSFCellStyle style_2 = createStyle( wb, HSSFCellStyle.ALIGN_CENTER ); + HSSFCellStyle style_3 = createStyle( wb, HSSFCellStyle.ALIGN_CENTER, HSSFColor.GREY_25_PERCENT.index, true ); + HSSFCellStyle style_4 = createHeaderStyle(wb); + + // data validation's number types + log(" Create sheet for Data Validation's number types ... "); + HSSFSheet fSheet = wb.createSheet("Number types"); - //Date/Time Validation type - System.out.print(" Create sheet for 'Date' and 'Time' Data Validation types ... "); - fSheet = wb.createSheet("Date_Time"); - SimpleDateFormat df = new SimpleDateFormat("m/d/yyyy"); - HSSFDataFormat dataFormat = wb.createDataFormat(); - short fmtDate = dataFormat.getFormat("m/d/yyyy"); - short fmtTime = dataFormat.getFormat("h:mm"); - HSSFCellStyle cellStyle_data = wb.createCellStyle(); - cellStyle_data.setDataFormat(fmtDate); - HSSFCellStyle cellStyle_time = wb.createCellStyle(); - cellStyle_time.setDataFormat(fmtTime); + // "Whole number" validation type + createDVTypeRow(wb, 0, style_3, "Whole number"); + createHeaderRow(wb, 0, style_4); - this.createDVTypeRow( wb, 2, style_3, "Date ( cells are already formated as date - m/d/yyyy)"); - this.createHeaderRow( wb, 2, style_4 ); + ValidationAdder va = new ValidationAdder(fSheet, style_1, style_2, null, + HSSFDataValidation.DATA_TYPE_INTEGER); - start_row = (short)fSheet.getPhysicalNumberOfRows(); - data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_DATE); - data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); + va.addValidation(OP.BETWEEN, "2", "6", ES.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); + va.addValidation(OP.NOT_BETWEEN, "2", "6", ES.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); + va.addValidation(OP.EQUAL, "3", null, ES.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); + va.addValidation(OP.NOT_EQUAL, "3", null, ES.WARNING, "Not equal to 3", "-", false, false, false); + va.addValidation(OP.GREATER_THAN, "3", null, ES.WARNING, "Greater than 3", "-", true, false, false); + va.addValidation(OP.LESS_THAN, "3", null, ES.WARNING, "Less than 3", "-", true, true, false); + va.addValidation(OP.GREATER_OR_EQUAL, "4", null, ES.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); + va.addValidation(OP.LESS_OR_EQUAL, "4", null, ES.STOP, "Less than or equal to 4", "-", false, true, false); - data_validation.setFirstFormula( String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("1/2/2004"))) ); - data_validation.setSecondFormula( String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("1/6/2004"))) ); + // "Decimal" validation type + createDVTypeRow(wb, 0, style_3, "Decimal"); + createHeaderRow(wb, 0, style_4); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 1/2/2004 and 1/6/2004 ", true, true, true ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + va = new ValidationAdder(fSheet, style_1, style_2, null, HSSFDataValidation.DATA_TYPE_DECIMAL); - data_validation.setFirstRow((short)(start_row+1)); - data_validation.setLastRow((short)(start_row+1)); - data_validation.setEmptyCellAllowed(false); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 1/2/2004 and 1/6/2004 ", false, true, true ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + va.addValidation(OP.BETWEEN, "2", "6", ES.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); + va.addValidation(OP.NOT_BETWEEN, "2", "6", ES.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); + va.addValidation(OP.EQUAL, "3", null, ES.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); + va.addValidation(OP.NOT_EQUAL, "3", null, ES.WARNING, "Not equal to 3", "-", false, false, false); + va.addValidation(OP.GREATER_THAN, "3", null, ES.WARNING, "Greater than 3", "-", true, false, false); + va.addValidation(OP.LESS_THAN, "3", null, ES.WARNING, "Less than 3", "-", true, true, false); + va.addValidation(OP.GREATER_OR_EQUAL, "4", null, ES.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); + va.addValidation(OP.LESS_OR_EQUAL, "4", null, ES.STOP, "Less than or equal to 4", "-", false, true, false); - data_validation.setFirstRow((short)(start_row+2)); - data_validation.setLastRow((short)(start_row+2)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3/2/2004", false, false, true ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + logln("done !"); - data_validation.setFirstRow((short)(start_row+3)); - data_validation.setLastRow((short)(start_row+3)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3/2/2004", false, false, false ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "-" ); + // "List" Data Validation type + log(" Create sheet for 'List' Data Validation type ... "); + fSheet = wb.createSheet("Lists"); - data_validation.setFirstRow((short)(start_row+4)); - data_validation.setLastRow((short)(start_row+4)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3/2/2004", true, false, false ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "-" ); + createDVTypeRow(wb, 1, style_3, "Explicit lists - list items are explicitly provided"); + createDVDeescriptionRow(wb, 1, style_3, + "Disadvantage - sum of item's length should be less than 255 characters"); + createHeaderRow(wb, 1, style_4); - data_validation.setFirstRow((short)(start_row+5)); - data_validation.setLastRow((short)(start_row+5)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3/2/2004", true, true, false ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "-" ); + va = new ValidationAdder(fSheet, style_1, style_2, null, HSSFDataValidation.DATA_TYPE_LIST); - data_validation.setFirstRow((short)(start_row+6)); - data_validation.setLastRow((short)(start_row+6)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); - data_validation.setShowErrorBox(true); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/2/2004")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 3/2/2004", true, false, true ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + va.addValidation(OP.BETWEEN, "1+2+3", null, ES.STOP, "POIFS,HSSF,HWPF,HPSF", "Error box type=STOP ; In-cell dropdown=yes", true, true, true); + va.introduceDiscrepancy(); + va.addValidation(OP.BETWEEN, "4+5+6+7", null, ES.STOP, "POIFS,HSSF,HWPF,HPSF", "Error box type=STOP ; In-cell dropdown=yes", false, false, true, false); + va.addValidation(OP.BETWEEN, "7+21", null, ES.STOP, "POIFS,HSSF,HWPF,HPSF", "Error box type=STOP ; In-cell dropdown=no", true, true, true, true); + va.introduceDiscrepancy(); + va.addValidation(OP.BETWEEN, "8/2", null, ES.STOP, "POIFS,HSSF,HWPF,HPSF", "Error box type=STOP ; In-cell dropdown=no", false, false, true, true); - data_validation.setFirstRow((short)(start_row+7)); - data_validation.setLastRow((short)(start_row+7)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("3/4/2004")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 3/4/2004", false, true, false ); - this.setCellFormat( fSheet, cellStyle_data ); - this.writeOtherSettings( fSheet, style_1, "-" ); + createDVTypeRow(wb, 1, style_3, "Reference lists - list items are taken from others cells"); + createDVDeescriptionRow(wb, 1, style_3, + "Advantage - no restriction regarding the sum of item's length"); + createHeaderRow(wb, 1, style_4); - //"Time" validation type - this.createDVTypeRow( wb, 2, style_3, "Time ( cells are already formated as time - h:mm)"); - this.createHeaderRow( wb, 2, style_4 ); + va = new ValidationAdder(fSheet, style_1, style_2, null, HSSFDataValidation.DATA_TYPE_LIST); + + 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 "; - df = new SimpleDateFormat("hh:mm"); + String strFormula = "$A$100:$A$120"; - start_row += (short)(8+4); - data_validation = new HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_TIME); - data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); - data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("12:00")))); - data_validation.setSecondFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("16:00")))); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 12:00 and 16:00 ", true, true, true ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + va.addValidation(OP.BETWEEN, strFormula, null, ES.STOP, strFormula, "Error box type=STOP ; In-cell dropdown=yes", true, true, true, false); + va.introduceDiscrepancy(); + va.addValidation(OP.BETWEEN, strFormula, null, ES.STOP, strFormula, "Error box type=STOP ; In-cell dropdown=yes", false, false, true, false); + va.addValidation(OP.BETWEEN, strFormula, null, ES.STOP, strFormula, "Error box type=STOP ; In-cell dropdown=no", true, true, true, true); + va.introduceDiscrepancy(); + va.addValidation(OP.BETWEEN, strFormula, null, ES.STOP, strFormula, "Error box type=STOP ; In-cell dropdown=no", false, false, true, true); - data_validation.setFirstRow((short)(start_row+1)); - data_validation.setLastRow((short)(start_row+1)); - data_validation.setEmptyCellAllowed(false); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 12:00 and 16:00 ", false, true, true ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + for (int i = 100; i <= 120; i++) { + HSSFRow currRow = fSheet.createRow(i); + setCellValue(currRow.createCell((short) 0), cellStrValue); + } - data_validation.setFirstRow((short)(start_row+2)); - data_validation.setLastRow((short)(start_row+2)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setFirstFormula(String.valueOf((int)HSSFDateUtil.getExcelDate(df.parse("13:35")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 13:35", false, false, true ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + logln("done !"); - data_validation.setFirstRow((short)(start_row+3)); - data_validation.setLastRow((short)(start_row+3)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("13:35")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 13:35", false, false, false ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "-" ); + // Date/Time Validation type + log(" Create sheet for 'Date' and 'Time' Data Validation types ... "); + fSheet = wb.createSheet("Date_Time"); + HSSFDataFormat dataFormat = wb.createDataFormat(); + short fmtDate = dataFormat.getFormat("m/d/yyyy"); + short fmtTime = dataFormat.getFormat("h:mm"); + HSSFCellStyle cellStyle_date = wb.createCellStyle(); + cellStyle_date.setDataFormat(fmtDate); + HSSFCellStyle cellStyle_time = wb.createCellStyle(); + cellStyle_time.setDataFormat(fmtTime); - data_validation.setFirstRow((short)(start_row+4)); - data_validation.setLastRow((short)(start_row+4)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("12:00")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 12:00", true, false, false ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "-" ); + createDVTypeRow(wb, 2, style_3, "Date ( cells are already formated as date - m/d/yyyy)"); + createHeaderRow(wb, 2, style_4); - data_validation.setFirstRow((short)(start_row+5)); - data_validation.setLastRow((short)(start_row+5)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("12:00")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 12:00", true, true, false ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "-" ); + va = new ValidationAdder(fSheet, style_1, style_2, cellStyle_date, + HSSFDataValidation.DATA_TYPE_DATE); - data_validation.setFirstRow((short)(start_row+6)); - data_validation.setLastRow((short)(start_row+6)); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); - data_validation.setShowErrorBox(true); - data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("14:00")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 14:00", true, false, true ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + va.addValidation(OP.BETWEEN, convertDateStr("1/2/2004"), convertDateStr("1/6/2004"), ES.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true); + va.addValidation(OP.NOT_BETWEEN, convertDateStr("1/2/2004"), convertDateStr("1/6/2004"), ES.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true); + va.addValidation(OP.EQUAL, convertDateStr("3/2/2004"), null, ES.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true); + va.addValidation(OP.NOT_EQUAL, convertDateStr("3/2/2004"), null, ES.WARNING, "Not equal to 3/2/2004", "-", false, false, false); + va.addValidation(OP.GREATER_THAN, convertDateStr("3/2/2004"), null, ES.WARNING, "Greater than 3/2/2004", "-", true, false, false); + va.addValidation(OP.LESS_THAN, convertDateStr("3/2/2004"), null, ES.WARNING, "Less than 3/2/2004", "-", true, true, false); + va.addValidation(OP.GREATER_OR_EQUAL, convertDateStr("3/2/2004"), null, ES.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true); + va.addValidation(OP.LESS_OR_EQUAL, convertDateStr("3/4/2004"), null, ES.STOP, "Less than or equal to 3/4/2004", "-", false, true, false); - data_validation.setFirstRow((short)(start_row+7)); - data_validation.setLastRow((short)(start_row+7)); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula(String.valueOf(HSSFDateUtil.getExcelDate(df.parse("14:00")))); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 14:00", false, true, false ); - this.setCellFormat( fSheet, cellStyle_time ); - this.writeOtherSettings( fSheet, style_1, "-" ); + // "Time" validation type + createDVTypeRow(wb, 2, style_3, "Time ( cells are already formated as time - h:mm)"); + createHeaderRow(wb, 2, style_4); - System.out.println("done !"); + va = new ValidationAdder(fSheet, style_1, style_2, cellStyle_time, + HSSFDataValidation.DATA_TYPE_TIME); - //"Text length" validation type - System.out.print(" Create sheet for 'Text length' Data Validation type... "); - fSheet = wb.createSheet("Text length"); - this.createHeaderRow( wb, 3, style_4 ); + va.addValidation(OP.BETWEEN, convertTimeStr("12:00"), convertTimeStr("16:00"), ES.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true); + va.addValidation(OP.NOT_BETWEEN, convertTimeStr("12:00"), convertTimeStr("16:00"), ES.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true); + // TODO - bug in original test. This (int) cast should not be present + String dateStr = convertTimeStr("13:35"); + dateStr = String.valueOf((int) Double.parseDouble(dateStr)); + va.addValidation(OP.EQUAL, dateStr, null, ES.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true); + va.addValidation(OP.NOT_EQUAL, convertTimeStr("13:35"), null, ES.WARNING, "Not equal to 13:35", "-", false, false, false); + va.addValidation(OP.GREATER_THAN, convertTimeStr("12:00"), null, ES.WARNING, "Greater than 12:00", "-", true, false, false); + va.addValidation(OP.LESS_THAN, convertTimeStr("12:00"), null, ES.WARNING, "Less than 12:00", "-", true, true, false); + va.addValidation(OP.GREATER_OR_EQUAL, convertTimeStr("14:00"), null, ES.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true); + va.addValidation(OP.LESS_OR_EQUAL, convertTimeStr("14:00"), null, ES.STOP, "Less than or equal to 14:00", "-", false, true, false); - data_validation = new HSSFDataValidation((short)1,(short)0,(short)1,(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_TEXT_LENGTH); - data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN); - data_validation.setFirstFormula("2"); - data_validation.setSecondFormula("6"); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Between 2 and 6 ", true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + logln("done !"); - data_validation.setFirstRow((short)2); - data_validation.setLastRow((short)2); - data_validation.setEmptyCellAllowed(false); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_BETWEEN); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_INFO); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not between 2 and 6 ", false, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = INFO" ); + // "Text length" validation type + log(" Create sheet for 'Text length' Data Validation type... "); + fSheet = wb.createSheet("Text length"); + createHeaderRow(wb, 3, style_4); - data_validation.setFirstRow((short)3); - data_validation.setLastRow((short)3); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_EQUAL); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Equal to 3", false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + va = new ValidationAdder(fSheet, style_1, style_2, null, + HSSFDataValidation.DATA_TYPE_TEXT_LENGTH); - data_validation.setFirstRow((short)4); - data_validation.setLastRow((short)4); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_NOT_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Not equal to 3", false, false, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); + va.addValidation(OP.BETWEEN, "2", "6", ES.STOP, "Between 2 and 6 ", "Error box type = STOP", true, true, true); + va.addValidation(OP.NOT_BETWEEN, "2", "6", ES.INFO, "Not between 2 and 6 ", "Error box type = INFO", false, true, true); + va.addValidation(OP.EQUAL, "3", null, ES.WARNING, "Equal to 3", "Error box type = WARNING", false, false, true); + va.addValidation(OP.NOT_EQUAL, "3", null, ES.WARNING, "Not equal to 3", "-", false, false, false); + va.addValidation(OP.GREATER_THAN, "3", null, ES.WARNING, "Greater than 3", "-", true, false, false); + va.addValidation(OP.LESS_THAN, "3", null, ES.WARNING, "Less than 3", "-", true, true, false); + va.addValidation(OP.GREATER_OR_EQUAL, "4", null, ES.STOP, "Greater than or equal to 4", "Error box type = STOP", true, false, true); + va.addValidation(OP.LESS_OR_EQUAL, "4", null, ES.STOP, "Less than or equal to 4", "-", false, true, false); - data_validation.setFirstRow((short)5); - data_validation.setLastRow((short)5); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than 3", true, false, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); + logln("done !"); - data_validation.setFirstRow((short)6); - data_validation.setLastRow((short)6); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("3"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_THAN); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than 3", true, true, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); + // Custom Validation type + log(" Create sheet for 'Custom' Data Validation type ... "); + fSheet = wb.createSheet("Custom"); + createHeaderRow(wb, 4, style_4); - data_validation.setFirstRow((short)7); - data_validation.setLastRow((short)7); - data_validation.setEmptyCellAllowed(true); - data_validation.setShowPromptBox(false); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_STOP); - data_validation.setShowErrorBox(true); - data_validation.setFirstFormula("4"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_GREATER_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Greater than or equal to 4", true, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + va = new ValidationAdder(fSheet, style_1, style_2, null, + HSSFDataValidation.DATA_TYPE_FORMULA); - data_validation.setFirstRow((short)8); - data_validation.setLastRow((short)8); - data_validation.setEmptyCellAllowed(false); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(false); - data_validation.setFirstFormula("4"); - data_validation.setSecondFormula(null); - data_validation.setOperator(HSSFDataValidation.OPERATOR_LESS_OR_EQUAL); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "Less than or equal to 4", false, true, false ); - this.writeOtherSettings( fSheet, style_1, "-" ); - System.out.println("done !"); + va.addValidation(OP.BETWEEN, "ISNUMBER($A2)", null, ES.STOP, "ISNUMBER(A2)", "Error box type = STOP", true, true, true); + va.introduceDiscrepancy(); + va.addValidation(OP.BETWEEN, "IF(SUM(A2:A3)=5,TRUE,FALSE)", null, ES.WARNING, "IF(SUM(A2:A3)=5,TRUE,FALSE)", "Error box type = WARNING", false, false, true); - //Custom Validation type - System.out.print(" Create sheet for 'Custom' Data Validation type ... "); - fSheet = wb.createSheet("Custom"); - this.createHeaderRow( wb, 4, style_4 ); + logln("done !"); - data_validation = new HSSFDataValidation((short)1,(short)0,(short)1,(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_FORMULA); - data_validation.setFirstFormula("ISNUMBER($A2)"); - data_validation.setSecondFormula(null); - data_validation.setShowPromptBox(true); - data_validation.setShowErrorBox(true); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.createPromptBox("Hi , dear user !", "So , you just selected me ! Thanks !"); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "ISNUMBER(A2)", true, true, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = STOP" ); + File resultFileFile = new File(resultFile); - data_validation = new HSSFDataValidation((short)2,(short)0,(short)2,(short)0); - data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_FORMULA); - data_validation.setFirstFormula("IF(SUM(A2:A3)=5,TRUE,FALSE)"); - data_validation.setSecondFormula(null); - data_validation.setShowPromptBox(false); - data_validation.setShowErrorBox(true); - data_validation.setErrorStyle(HSSFDataValidation.ERROR_STYLE_WARNING); - data_validation.createErrorBox("Invalid input !", "Something is wrong ; check condition !"); - data_validation.setEmptyCellAllowed(false); - fSheet.addValidationData(data_validation); - this.writeDataValidationSettings( fSheet, style_1, style_2, "IF(SUM(A2:A3)=5,TRUE,FALSE)", false, false, true ); - this.writeOtherSettings( fSheet, style_1, "Error box type = WARNING" ); + ByteArrayOutputStream baos = new ByteArrayOutputStream((int) resultFileFile.length()); + try { + wb.write(baos); + baos.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + compareStreams(resultFileFile, baos.toByteArray()); + } + + private static void compareStreams(File proofFile, byte[] generatedContent) { - System.out.println("done !"); + InputStream isA; + try { + isA = new FileInputStream(proofFile); + } catch (FileNotFoundException e) { + throw new RuntimeException(e); + } + InputStream isB = new ByteArrayInputStream(generatedContent); - //so , everything it's ok for now ; it remains for you to open the file - System.out.println("\n Everything it's ok since we've got so far -:) !\n"+ - " In order to complete the test , it remains for you to open the file \n"+ - " and see if there are four sheets , as described !"); - System.out.println(" File was saved in \""+resultFile+"\""); + // The allowable regions where the generated file can differ from the + // proof should be small (i.e. much less than 1K) + int[] allowableDifferenceRegions = { + 0x0228, 16, // a region of the file containing the OS username + 0x506C, 8, // See RootProperty (super fields _seconds_2 and _days_2) + }; + int[] diffs = StreamUtility.diffStreams(isA, isB, allowableDifferenceRegions); + if (diffs == null) { + return; + } + System.err.println("Diff from proof: " + proofFile.getAbsolutePath()); + for (int i = 0; i < diffs.length; i++) { + System.err.println("diff at offset: 0x" + Integer.toHexString(diffs[i])); - FileOutputStream fileOut = new FileOutputStream(resultFile); - wb.write(fileOut); - fileOut.close(); - } + } + File tempDir = new File(System.getProperty("java.io.tmpdir")); + File generatedFile = new File(tempDir, "GeneratedTestDataValidation.xls"); + try { + FileOutputStream fileOut = new FileOutputStream(generatedFile); + fileOut.write(generatedContent); + fileOut.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + + PrintStream ps = System.out; + + ps.println("This test case has failed because the generated file differs from proof copy '" + + proofFile.getAbsolutePath() + "'."); + ps.println("The cause is usually a change to this test, or some common spreadsheet generation code. " + + "The developer has to decide whether the changes were wanted or unwanted."); + ps.println("If the changes to the generated version were unwanted, " + + "make the fix elsewhere (do not modify this test or the proof spreadsheet to get the test working)."); + ps.println("If the changes were wanted, make sure to open the newly generated file in Excel " + + "and verify it manually. The new proof file should be submitted after it is verified to be correct."); + ps.println(""); + ps.println("One other possible (but less likely) cause of a failed test is a problem in the " + + "comparison logic used here. Perhaps some extra file regions need to be ignored."); + ps.println("The generated file has been saved to '" + generatedFile.getAbsolutePath() + "' for manual inspection."); + + fail("Generated file differs from proof copy. See sysout comments for details on how to fix."); + } + + /** + * @return a string representation of an integer which has the value that Excel would ascribe + * to the specified date passed as a string + */ + private static String convertDateStr(String mmddyyyyDate ) { + SimpleDateFormat df = new SimpleDateFormat("m/d/yyyy"); + Date dateValue; + try { + dateValue = df.parse(mmddyyyyDate); + } catch (ParseException e) { + throw new RuntimeException(e); + } + return String.valueOf((int)HSSFDateUtil.getExcelDate(dateValue)); + } + /** + * @return a string representation of an integer which has the value that Excel would ascribe + * to the specified date passed as a string + */ + private static String convertTimeStr(String hhmmTime ) { + SimpleDateFormat df = new SimpleDateFormat("hh:mm"); + Date dateValue; + try { + dateValue = df.parse(hhmmTime); + } catch (ParseException e) { + throw new RuntimeException(e); + } + return String.valueOf(HSSFDateUtil.getExcelDate(dateValue)); + } - private void createDVTypeRow( HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle, String strTypeDescription) + private void createDVTypeRow( HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle, String strTypeDescription) { HSSFSheet sheet = wb.getSheetAt(sheetNo); HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows()); row = sheet.createRow(sheet.getPhysicalNumberOfRows()); sheet.addMergedRegion(new Region((short)(sheet.getPhysicalNumberOfRows()-1),(short)0,(short)(sheet.getPhysicalNumberOfRows()-1),(short)5)); HSSFCell cell = row.createCell((short)0); - cell.setCellValue(strTypeDescription); + setCellValue(cell, strTypeDescription); cell.setCellStyle(cellStyle); row = sheet.createRow(sheet.getPhysicalNumberOfRows()); } @@ -773,7 +481,7 @@ HSSFRow row = sheet.getRow(sheet.getPhysicalNumberOfRows()-1); sheet.addMergedRegion(new Region((short)(sheet.getPhysicalNumberOfRows()-1),(short)0,(short)(sheet.getPhysicalNumberOfRows()-1),(short)5)); HSSFCell cell = row.createCell((short)0); - cell.setCellValue(strTypeDescription); + setCellValue(cell, strTypeDescription); cell.setCellStyle(cellStyle); row = sheet.createRow(sheet.getPhysicalNumberOfRows()); } @@ -800,17 +508,17 @@ } } HSSFCell cell = row.getCell((short)0); - cell.setCellValue("Data validation cells"); + setCellValue(cell, "Data validation cells"); cell = row.getCell((short)1); - cell.setCellValue("Condition"); + setCellValue(cell, "Condition"); cell = row.getCell((short)2); - cell.setCellValue("Allow blank"); + setCellValue(cell, "Allow blank"); cell = row.getCell((short)3); - cell.setCellValue("Prompt box"); + setCellValue(cell, "Prompt box"); cell = row.getCell((short)4); - cell.setCellValue("Error box"); + setCellValue(cell, "Error box"); cell = row.getCell((short)5); - cell.setCellValue("Other settings"); + setCellValue(cell, "Other settings"); } private HSSFCellStyle createHeaderStyle(HSSFWorkbook wb) @@ -864,45 +572,14 @@ private HSSFCellStyle createStyle( HSSFWorkbook wb, short h_align ) { - return this.createStyle(wb, h_align, HSSFColor.WHITE.index, false); + return createStyle(wb, h_align, HSSFColor.WHITE.index, false); } - private void writeDataValidationSettings( HSSFSheet sheet, HSSFCellStyle style_1, HSSFCellStyle style_2, String strCondition, boolean allowEmpty, boolean inputBox, boolean errorBox ) - { - HSSFRow row = sheet.createRow( sheet.getPhysicalNumberOfRows() ); - //condition's string - HSSFCell cell = row.createCell((short)1); - cell.setCellStyle(style_1); - cell.setCellValue(strCondition); - //allow empty cells - cell = row.createCell((short)2); - cell.setCellStyle(style_2); - cell.setCellValue( ((allowEmpty) ? "yes" : "no") ); - //show input box - cell = row.createCell((short)3); - cell.setCellStyle(style_2); - cell.setCellValue( ((inputBox) ? "yes" : "no") ); - //show error box - cell = row.createCell((short)4); - cell.setCellStyle(style_2); - cell.setCellValue( ((errorBox) ? "yes" : "no") ); + /* package */ static void setCellValue(HSSFCell cell, String text) { + cell.setCellValue(new HSSFRichTextString(text)); + } - - private void setCellFormat( HSSFSheet sheet, HSSFCellStyle cell_style ) - { - HSSFRow row = sheet.getRow( sheet.getPhysicalNumberOfRows() -1 ); - HSSFCell cell = row.createCell((short)0); - cell.setCellStyle(cell_style); - } - - private void writeOtherSettings( HSSFSheet sheet, HSSFCellStyle style, String strStettings ) - { - HSSFRow row = sheet.getRow( sheet.getPhysicalNumberOfRows() -1 ); - HSSFCell cell = row.createCell((short)5); - cell.setCellStyle(style); - cell.setCellValue(strStettings); - } - + public static void main(String[] args) { junit.textui.TestRunner.run(TestDataValidation.class);