testdatavalidation.java

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

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

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

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

    this.createDVTypeRow( wb, 2, style_3, "Date ( cells are already formated as date - m/d/yyyy)");
    this.createHeaderRow( wb, 2, 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_DATE);
    data_validation.setOperator(HSSFDataValidation.OPERATOR_BETWEEN);

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

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

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

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

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

    data_validation.setFirstRow((short)(start_row+4));
    data_validation.setLastRow((short)(start_row+4));
    data_validation.setEmptyCellAllowed(true);
    data_validation.setShowPromptBox(false);