2.6.8 use npoi to Operate Excel -- generate a drop-down list

In the previous section, we talked about simple Data Validity verification. This section describes another application of Data Validity-the drop-down list. In Excel, there are no drop-down controls similar to those in the web. The drop-down effect is achieved through data validity. The procedure is as follows:
(1) Select an area to generate a drop-down list;
(2) set the data validity as a sequence, and fill in the optional drop-down values in the source, separated ().

The results are as follows:

Similarly, using npoiCodeYou can also achieve the following results: Hssfsheet sheet1=Hssfworkbook. createsheet ("Sheet1");

Cellrangeaddresslist regions =   New Cellrangeaddresslist ( 0 , 65535 , 0 , 0 );
Dvconstraint Constraint = Dvconstraint. createexplicitlistconstraint ( New   String [] { " Itema " , " Itemb " , " Itemc " });
Hssfdatavalidation datavalidate =   New Hssfdatavalidation (regions, constraint );
Sheet1.addvalidationdata (datavalidate );

The following is a brief description of the Code:
First, set a drop-down area. For details about cellrangeaddresslist constructor parameters, see the previous section: Cellrangeaddresslist regions= NewCellrangeaddresslist (0,65535,0,0);

Then, pass the drop-down item as an array to createexplicitlistconstraint as a parameter to create a constraint. You can create Data Validity based on the region and constraints to be controlled.

However, there is a problem: the source length of the sequence allowed in Excel is up to 255 characters, that is, an error occurs when the total length of the string in the drop-down list exceeds 255. What should I do if there are many drop-down items? The answer is by reference. The procedure is as follows:
Create a sheet to store the values of the drop-down items, and write the values of the drop-down items to it: Hssfsheet sheet2 = Hssfworkbook. createsheet ( " Shtdictionary " );
Sheet2.createrow ( 0 ). Createcell ( 0 ). Setcellvalue ( " Itema " );
Sheet2.createrow ( 1 ). Createcell ( 0 ). Setcellvalue ( " Itemb " );
Sheet2.createrow ( 2 ). Createcell ( 0 ). Setcellvalue ( " Itemc " );

Then define a Name Pointing to the area of the drop-down item just created: Hssfname range=Hssfworkbook. createname ();
Range. Reference= "Shtdictionary! $ A1: $ A3";
Range. namename= "Dicrange";

Finally, set the data constraints to point to this name rather than the character array: Hssfsheet sheet1 = Hssfworkbook. createsheet ( " Sheet1 " );
Cellrangeaddresslist regions =   New Cellrangeaddresslist ( 0 , 65535 , 0 , 0 );

Dvconstraint Constraint=Dvconstraint. createformulalistconstraint ("Dicrange");
Hssfdatavalidation datavalidate= NewHssfdatavalidation (regions, constraint );
Sheet1.addvalidationdata (datavalidate );

Run this code to generate the following Excel files:

In the name manager, you will find a name named "dicrange" pointing to "shtdictionary! Drop-down list of $ A1: $ A3:

In data validity, the source is changed to "= dicrange", pointing to the name defined above. Instead of the previous "itema, itemb, itemc ":

 

Returned directory