testdatavalidation.java

介绍java核心技术的pio编程方法以及基本概念

    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, "-" );

    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, "-" );

    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" );

    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, "-" );

    //"Time" validation type
    this.createDVTypeRow( wb, 2, style_3, "Time ( cells are already formated as time - h:mm)");
    this.createHeaderRow( wb, 2, style_4 );

    df = new SimpleDateFormat("hh:mm");

    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" );

    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" );

    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" );

    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, "-" );

    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, "-" );

    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, "-" );

    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" );

    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, "-" );

    System.out.println("done !");

    //"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 );

    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" );

    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" );

    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" );

    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, "-" );

    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, "-" );

    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);