[Go] excel--data validity with Npoi operation

This article transferred from: http://www.cnblogs.com/atao/archive/2009/09/22/1572170.html

In some cases (such as the introduction of Excel), we may not allow users to enter some invalid data in Excel, then we need to add some validation of data validation in the template. In Excel, the steps to set the data's validity are as follows: (1) First select an area, (2) Set data validation () in the "Data? Data validation" menu.
Similarly, using Npoi, code can also be implemented:

Hssfsheet Sheet1=Hssfworkbook. Createsheet ("Sheet1");
Sheet1. CreateRow (0). Createcell (0). Setcellvalue ("Date column"); Cellrangeaddresslist regions1=NewCellrangeaddresslist (1,65535,0,0); Dvconstraint Constraint1=Dvconstraint.createdateconstraint (DVConstraint.OperatorType.BETWEEN,"1900-01-01","2999-12-31, < Span style= "color: #800000;" > "yyyy-mm-dd" ); Hssfdatavalidation datavalidate1 =  new hssfdatavalidation (regions1,  CONSTRAINT1); Datavalidate1.createerrorbox ( "error ", " you  Must input a date. "); Sheet1. Addvalidationdata (dataValidate1);

The above is an example of a validation that requires a date from 1900-1-1 to 2999-12-31 to be entered in the first column, and the resulting Excel effect is as follows, and warns when the input is illegal:
Here are some of the methods you have just used to illustrate: The Cellrangeaddresslist class represents an area, and the four parameters in the constructor represent the starting line ordinal, the terminating row ordinal, the starting column ordinal, and the terminating column ordinal. So the first column area is represented as:

// All ordinal numbers are counted from zero, except for the first row of header row, so the first parameter is 1,65535 is the maximum number of rows for a sheet new cellrangeaddresslist (1, 65535, 0, 0);

In addition, createdateconstraint the first parameter in addition to set to DVConstraint.OperatorType.BETWEEN, but also can be set to some of the following values, you can try to see their own results:

Finally, Datavalidate1.createerrorbox (Title,text), which is used to create a prompt message when an error occurs. The first parameter represents the caption of the cue box, and the second parameter represents the contents of the Prompt box.

Understanding the above, creating an integer type of validation is also not difficult to implement:

Sheet1. CreateRow (0). Createcell (1). Setcellvalue ("numeric columns"); Cellrangeaddresslist Regions2=NewCellrangeaddresslist (1,65535,1,1); Dvconstraint Constraint2=Dvconstraint.createnumericconstraint (Dvconstraint.validationtype.integer,dvconstraint.operatortype.between,"0,  "100" ); Hssfdatavalidation datavalidate2 =  new hssfdatavalidation (regions2,  CONSTRAINT2); Datavalidate2.createerrorbox ( "error ", " you  Must input a numeric between 0 and 100. "); Sheet1. Addvalidationdata (DATAVALIDATE2);

The resulting Excel effect is:
In the next section we will learn the examples of using data validation to create a drop-down list.

Back to Catalog

[Go] excel--data validity with Npoi operation