testdatavalidation.java

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

/* ====================================================================
   Copyright 2002-2004   Apache Software Foundation

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.hssf.usermodel;

import junit.framework.TestCase;

import org.apache.poi.hssf.util.*;

import java.io.*;
import java.util.*;
import java.text.SimpleDateFormat;

/**
 * <p>Title: TestDataValidation</p>
 * <p>Description: Class for testing Excel's data validation mechanism
 *    Second test :
 *        -
 * </p>
 * @author Dragos Buleandra ( [e-mail] )
 */
public class TestDataValidation extends TestCase
{
  public TestDataValidation(String name)
  {
    super(name);
  }

  protected void setUp()
  {
    String filename = System.getProperty("HSSF.testdata.path");
    if (filename == null)
    {
       System.setProperty("HSSF.testdata.path", "src/testcases/org/apache/poi/hssf/data");
    }
  }

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

    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;

    //data validation's number types
    System.out.print("    Create sheet for Data Validation's number types ... ");
    HSSFSheet fSheet = wb.createSheet("Number types");

    //"Whole number" validation type
    this.createDVTypeRow( wb, 0, style_3, "Whole number");
    this.createHeaderRow( wb, 0, style_4 );

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

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