Author: nick
Date: Mon Dec 3 06:14:02 2007
New Revision: 600544
URL: http://svn.apache.org/viewvc?rev=600544&view=rev
Log:
Usermodel support for the datavalidation code, and tests for it
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls
(with props)
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java
(with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=600544&r1=600543&r2=600544&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Mon Dec 3 06:14:02
2007
@@ -36,7 +36,8 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2007-??-??">
- <action dev="POI-DEVELOPERS" type="fix">43877 and 39512 - Fix for
handling mixed OBJ and CONTINUE records.</action>
+ <action dev="POI-DEVELOPERS" type="add">27511 - [PATCH] Support
for data validation, via DVRecord and DVALRecord</action>
+ <action dev="POI-DEVELOPERS" type="fix">43877 and 39512 - Fix for
handling mixed OBJ and CONTINUE records.</action>
<action dev="POI-DEVELOPERS" type="fix">43807 - Throw an
IllegalArgumentException if asked to create a merged region with invalid
columns or rows, rather than writing out a corrupt file</action>
<action dev="POI-DEVELOPERS" type="fix">43837 - [PATCH] Support
for unicode NameRecords</action>
<action dev="POI-DEVELOPERS" type="add">43721 - [PATCH] Support
for Chart Title Format records</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=600544&r1=600543&r2=600544&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Mon Dec 3 06:14:02
2007
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2007-??-??">
+ <action dev="POI-DEVELOPERS" type="add">27511 - [PATCH] Support
for data validation, via DVRecord and DVALRecord</action>
<action dev="POI-DEVELOPERS" type="fix">43877 - Fix for handling
mixed OBJ and CONTINUE records</action>
<action dev="POI-DEVELOPERS" type="fix">39512 - Fix for handling
mixed OBJ and CONTINUE records</action>
<action dev="POI-DEVELOPERS" type="fix">43837 - [PATCH] Support
for unicode NameRecords</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=600544&r1=600543&r2=600544&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Mon Dec 3
06:14:02 2007
@@ -23,9 +23,13 @@
package org.apache.poi.hssf.usermodel;
import org.apache.poi.ddf.EscherRecord;
+import org.apache.poi.hssf.model.FormulaParser;
import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.*;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.util.HSSFCellRangeAddress;
+import org.apache.poi.hssf.util.HSSFDataValidation;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.util.POILogFactory;
@@ -35,6 +39,7 @@
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
+import java.util.Stack;
import java.util.TreeMap;
import java.text.AttributedString;
import java.text.NumberFormat;
@@ -352,6 +357,94 @@
public int getLastRowNum()
{
return lastrow;
+ }
+
+ /**
+ * Creates a data validation object
+ * @param obj_validation The Data validation object settings
+ */
+ public void addValidationData(HSSFDataValidation obj_validation)
+ {
+ if ( obj_validation == null )
+ {
+ return;
+ }
+ DVALRecord dvalRec = (DVALRecord)sheet.findFirstRecordBySid(
DVALRecord.sid );
+ int eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
+ if ( dvalRec == null )
+ {
+ dvalRec = new DVALRecord();
+ sheet.getRecords().add( eofLoc, dvalRec );
+ }
+ int curr_dvRecNo = dvalRec.getDVRecNo();
+ dvalRec.setDVRecNo(curr_dvRecNo+1);
+
+ //create dv record
+ DVRecord dvRecord = new DVRecord();
+
+ //dv record's option flags
+ dvRecord.setDataType( obj_validation.getDataValidationType() );
+ dvRecord.setErrorStyle(obj_validation.getErrorStyle());
+ dvRecord.setEmptyCellAllowed(obj_validation.getEmptyCellAllowed());
+
dvRecord.setSurppresDropdownArrow(obj_validation.getSurppressDropDownArrow());
+ dvRecord.setShowPromptOnCellSelected(obj_validation.getShowPromptBox());
+ dvRecord.setShowErrorOnInvalidValue(obj_validation.getShowErrorBox());
+ dvRecord.setConditionOperator(obj_validation.getOperator());
+
+ //string fields
+ dvRecord.setStringField(
DVRecord.STRING_PROMPT_TITLE,obj_validation.getPromptBoxTitle());
+ dvRecord.setStringField( DVRecord.STRING_PROMPT_TEXT,
obj_validation.getPromptBoxText());
+ dvRecord.setStringField( DVRecord.STRING_ERROR_TITLE,
obj_validation.getErrorBoxTitle());
+ dvRecord.setStringField( DVRecord.STRING_ERROR_TEXT,
obj_validation.getErrorBoxText());
+
+ //formula fields ( size and data )
+ String str_formula = obj_validation.getFirstFormula();
+ FormulaParser fp = new FormulaParser(str_formula+";",book);
+ fp.parse();
+ Stack ptg_arr = new Stack();
+ Ptg[] ptg = fp.getRPNPtg();
+ int size = 0;
+ for (int k = 0; k < ptg.length; k++)
+ {
+ if ( ptg[k] instanceof org.apache.poi.hssf.record.formula.AreaPtg )
+ {
+ //we should set ptgClass to Ptg.CLASS_REF and explicit formula
string to false
+ ptg[k].setClass(Ptg.CLASS_REF);
+ obj_validation.setExplicitListFormula(false);
+ }
+ size += ptg[k].getSize();
+ ptg_arr.push(ptg[k]);
+ }
+ dvRecord.setFirstFormulaRPN(ptg_arr);
+ dvRecord.setFirstFormulaSize((short)size);
+
+
dvRecord.setListExplicitFormula(obj_validation.getExplicitListFormula());
+
+ if ( obj_validation.getSecondFormula() != null )
+ {
+ str_formula = obj_validation.getSecondFormula();
+ fp = new FormulaParser(str_formula+";",book);
+ fp.parse();
+ ptg_arr = new Stack();
+ ptg = fp.getRPNPtg();
+ size = 0;
+ for (int k = 0; k < ptg.length; k++)
+ {
+ size += ptg[k].getSize();
+ ptg_arr.push(ptg[k]);
+ }
+ dvRecord.setSecFormulaRPN(ptg_arr);
+ dvRecord.setSecFormulaSize((short)size);
+ }
+
+ //dv records cell range field
+ HSSFCellRangeAddress cell_range = new HSSFCellRangeAddress();
+ cell_range.addADDRStructure(obj_validation.getFirstRow(),
obj_validation.getFirstColumn(), obj_validation.getLastRow(),
obj_validation.getLastColumn());
+ dvRecord.setCellRangeAddress(cell_range);
+
+ //add dv record
+ eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
+ sheet.getRecords().add( eofLoc, dvRecord );
}
/**
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls?rev=600544&view=auto
==============================================================================
Binary file - no diff available.
Propchange:
poi/trunk/src/testcases/org/apache/poi/hssf/data/TestDataValidation.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java?rev=600544&view=auto
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java
(added)
+++
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java
Mon Dec 3 06:14:02 2007
@@ -0,0 +1,910 @@
+/* ====================================================================
+ 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 ( [EMAIL PROTECTED] )
+ */
+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);
+ 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);
+ 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);
+ 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);
+ }
+}
Propchange:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestDataValidation.java
------------------------------------------------------------------------------
svn:eol-style = native
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
