testdatavalidation.java

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

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

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

    //Custom Validation type
    System.out.print("    Create sheet for 'Custom' Data Validation type ... ");
    fSheet = wb.createSheet("Custom");
    this.createHeaderRow( wb, 4, style_4 );

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

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

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

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

    FileOutputStream fileOut = new FileOutputStream(resultFile);
    wb.write(fileOut);
    fileOut.close();
  }

  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);
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(sheet.getPhysicalNumberOfRows());
  }

  private void createDVDeescriptionRow(  HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle, String strTypeDescription )
  {
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    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);
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(sheet.getPhysicalNumberOfRows());
  }

  private void createHeaderRow( HSSFWorkbook wb, int sheetNo , HSSFCellStyle cellStyle )
  {
      HSSFSheet sheet = wb.getSheetAt(sheetNo);
      HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
      row.setHeight((short)400);
      for ( int i=0; i<6; i++ )
      {
         row.createCell((short)i).setCellStyle( cellStyle );
         if ( i==2 || i==3 || i==4 )
         {
            sheet.setColumnWidth( (short) i, (short) 3500);
         }
         else if ( i== 5)
         {
            sheet.setColumnWidth( (short) i, (short) 10000);
         }
         else
         {
            sheet.setColumnWidth( (short) i, (short) 8000);
         }
      }
      HSSFCell cell = row.getCell((short)0);
      cell.setCellValue("Data validation cells");
      cell = row.getCell((short)1);
      cell.setCellValue("Condition");
      cell = row.getCell((short)2);
      cell.setCellValue("Allow blank");
      cell = row.getCell((short)3);
      cell.setCellValue("Prompt box");
      cell = row.getCell((short)4);
      cell.setCellValue("Error box");
      cell = row.getCell((short)5);
      cell.setCellValue("Other settings");
  }

  private HSSFCellStyle createHeaderStyle(HSSFWorkbook wb)
  {
    HSSFFont font = wb.createFont();
    font.setColor( HSSFColor.WHITE.index );
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setLeftBorderColor(HSSFColor.WHITE.index);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    cellStyle.setTopBorderColor(HSSFColor.WHITE.index);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(HSSFColor.WHITE.index);
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(HSSFColor.WHITE.index);
    cellStyle.setFont(font);
    return cellStyle;
  }

  private HSSFCellStyle createStyle( HSSFWorkbook wb, short h_align, short color, boolean bold )
  {
    HSSFFont font = wb.createFont();
    if ( bold )
    {
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    }

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setFillForegroundColor(color);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    cellStyle.setAlignment(h_align);
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

    return cellStyle;
  }

  private HSSFCellStyle createStyle( HSSFWorkbook wb, short h_align )
  {
     return this.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") );
  }

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