Esempio n. 1
0
        public void TestDataValidation()
        {
            Log("\nTest no. 2 - Test Excel's Data validation mechanism");
            IWorkbook 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 !");

            wb = _testDataProvider.WriteOutAndReadBack(wb);
        }
Esempio n. 2
0
        public void TestDataValidation()
        {
            Log("\nTest no. 2 - Test Excel's Data validation mechanism");
            IWorkbook         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 !");

            wb = _testDataProvider.WriteOutAndReadBack(wb);
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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);
        }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        private static void AddDateTimeValidations(WorkbookFormatter wf, IWorkbook wb)
        {
            wf.CreateSheet("Dates and Times");

            IDataFormat dataFormat     = wb.CreateDataFormat();
            short       fmtDate        = dataFormat.GetFormat("m/d/yyyy");
            short       fmtTime        = dataFormat.GetFormat("h:mm");
            ICellStyle  cellStyle_date = wb.CreateCellStyle();

            cellStyle_date.DataFormat = (/*setter*/ fmtDate);
            ICellStyle cellStyle_time = wb.CreateCellStyle();

            cellStyle_time.DataFormat = (/*setter*/ 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);
        }
Esempio n. 7
0
        private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
        {
            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 dataSheetName = "list_data";
            // "List" Data Validation type
            ISheet fSheet    = wf.CreateSheet("Lists");
            ISheet 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(",".ToCharArray());
            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);
            IName namedRange = wb.CreateName();

            namedRange.NameName        = (/*setter*/ "myName");
            namedRange.RefersToFormula = (/*setter*/ 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++)
            {
                IRow currRow = fSheet.CreateRow(i + 29);
                SetCellValue(currRow.CreateCell(0), cellStrValue);
            }
            // add list data on another sheet
            for (int i = 0; i < 10; i++)
            {
                IRow 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);
            }
        }
Esempio n. 8
0
        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);
        }
Esempio n. 9
0
        private static void AddDateTimeValidations(WorkbookFormatter wf, IWorkbook wb)
        {
            wf.CreateSheet("Dates and Times");

            IDataFormat dataFormat = wb.CreateDataFormat();
            short fmtDate = dataFormat.GetFormat("m/d/yyyy");
            short fmtTime = dataFormat.GetFormat("h:mm");
            ICellStyle cellStyle_date = wb.CreateCellStyle();
            cellStyle_date.DataFormat = (/*setter*/fmtDate);
            ICellStyle cellStyle_time = wb.CreateCellStyle();
            cellStyle_time.DataFormat = (/*setter*/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);
        }
Esempio n. 10
0
        private static void AddListValidations(WorkbookFormatter wf, IWorkbook wb)
        {
            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 dataSheetName = "list_data";
            // "List" Data Validation type
            ISheet fSheet = wf.CreateSheet("Lists");
            ISheet 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(",".ToCharArray());
            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);
            IName namedRange = wb.CreateName();
            namedRange.NameName = (/*setter*/"myName");
            namedRange.RefersToFormula = (/*setter*/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++)
            {
                IRow currRow = fSheet.CreateRow(i + 29);
                SetCellValue(currRow.CreateCell(0), cellStrValue);
            }
            // add list data on another sheet
            for (int i = 0; i < 10; i++)
            {
                IRow 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);
            }
        }
Esempio n. 11
0
        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);
        }
Esempio n. 12
0
        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);
        }
Esempio n. 13
0
        public void TestDataValidation1()
        {
            log("\nTest no. 2 - Test Excel's Data validation mechanism");
            HSSFWorkbook      wb = new HSSFWorkbook();
            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 !");

            MemoryStream baos = new MemoryStream(22000);

            try {
                wb.Write(baos);
                baos.Close();
            } catch (IOException) {
                throw;
            }
            byte[] generatedContent = baos.ToArray();
            bool   isSame;

            if (false)
            {
                // TODO - Add proof spreadsheet and compare
                Stream proofStream = HSSFTestDataSamples.OpenSampleFileStream("TestDataValidation.xls");
                isSame = CompareStreams(proofStream, generatedContent);
            }
            isSame = true;

            if (isSame)
            {
                return;
            }
            string     tempdir       = System.Configuration.ConfigurationSettings.AppSettings["java.io.tmpdir"];
            FileStream generatedFile = File.Open(tempdir + "GeneratedTestDataValidation.xls", FileMode.OpenOrCreate);

            try {
                generatedFile.Write(generatedContent, 0, generatedContent.Length);
                generatedFile.Close();
            } catch (IOException) {
                throw;
            }



            Console.WriteLine("This Test case has Assert.Failed because the generated file differs from proof copy '"
                              );   // TODO+ proofFile.GetAbsolutePath() + "'.");
            Console.WriteLine("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.");
            Console.WriteLine("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).");
            Console.WriteLine("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.");
            Console.WriteLine("");
            Console.WriteLine("One other possible (but less likely) cause of a Assert.Failed Test is a problem in the "
                              + "comparison logic used here. Perhaps some extra file regions need to be ignored.");
            Console.WriteLine("The generated file has been saved to '" + Path.GetFullPath(tempdir + "GeneratedTestDataValidation.xls") + "' for manual inspection.");

            Assert.Fail("Generated file differs from proof copy.  See sysout comments for details on how to fix.");
        }