POI 多个下拉列表展示

package com.oatest.test.jiansh.excle;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

public class Abc {
    // 传入下拉列表数组值，构造信息模板
    public static Workbook generateExcel(String[]typeArrays,String[]typeArrays1,String[]typeArrays2) {
        Workbook wb = new XSSFWorkbook();
        // 创建模板工作表
        Sheet sheet = wb.createSheet("AAA");
        // 创建下拉列表值存储工作表并设置值
        genearteOtherSheet(wb, typeArrays, typeArrays1, typeArrays2);

        // 创建模板列信息并绑定下拉列表值
        Row row = sheet.createRow(0);
        // 设置列信息样式 -- 当前样式对于列信息未居中
        setStyle(wb, sheet, 0);
        setStyle(wb, sheet, 1);
        // 绑定列信息
        Cell nameCell = row.createCell((int)0);
        nameCell.setCellValue("类型1");
        Cell typeCell = row.createCell((int)1);
        typeCell.setCellValue("类型2");
        Cell typeCell3 = row.createCell((int)3);
        typeCell3.setCellValue("类型3");

        // 设置下拉列表值绑定对哪一页起作用
        //"typelist!$A$1:$A$" + typeArrays.length 代表隐藏表的数据，，后边的firstRow 等是下拉表值开始作用到sheet 中的开始行，终止行，开始列,终止列
        sheet.addValidationData(SetDataValidation(wb, "typelist!$A$1:$A$" + typeArrays.length, 1, 0, typeArrays.length, 0));
        // 设置下拉列表值绑定对哪一页起作用
        sheet.addValidationData(SetDataValidation(wb, "typelist!$B$1:$B$" + typeArrays1.length, 1, 1, typeArrays1.length, 1));

        // 设置下拉列表值绑定对哪一页起作用
        sheet.addValidationData(SetDataValidation(wb, "typelist!$C$1:$C$" + typeArrays2.length, 1, 3, typeArrays2.length, 3));

        // 隐藏作为下拉列表值的Sheet
//        wb.setSheetHidden(wb.getSheetIndex("typelist"), 1);

        return wb;
    }

    // 创建下拉列表值存储工作表并设置值
    public static Sheet genearteOtherSheet(Workbook wb, String[]typeArrays, String[]typeArrays1,String[]typeArrays2) {
        // 创建下拉列表值存储工作表
        Sheet sheet = wb.createSheet("typelist");
        // 循环往该sheet中设置添加下拉列表的值
        int rowLengh = 0;
        //设置隐藏页的row大小
        if(typeArrays.length <= typeArrays1.length){
             rowLengh = typeArrays1.length+1 ;
        }else{
            rowLengh = typeArrays.length+1;
        }
        for (int i = 0; i < rowLengh; i++) {
            Row row = sheet.createRow(i);
            if(i < typeArrays.length){
                Cell cell = row.createCell((int)0);
                cell.setCellValue(typeArrays[i]);
            }
            if(i < typeArrays1.length){
                Cell cell = row.createCell((int)1);
                cell.setCellValue(typeArrays1[i]);
            }

            if(i < typeArrays2.length){
                Cell cell = row.createCell((int)2);
                cell.setCellValue(typeArrays2[i]);
            }
        }

        return sheet;
    }

    // 设置列信息样式
    public static void setStyle(Workbook wb, Sheet sheet, int colNum) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        DataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@"));

        sheet.setDefaultColumnStyle(colNum, cellStyle);
    }

    // 设置并引用其他Sheet作为绑定下拉列表数据
    public static DataValidation SetDataValidation(Workbook wb, String strFormula, int firstRow, int firstCol, int endRow, int endCol) {
        // 隐藏页的数据名称空间
        // String formula = "typelist!$A$1:$A$100" ;
        // 展示页面：设置 起始行 起始列 终止行 终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)wb.getSheet("typelist"));
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        DataValidation dataValidation = dvHelper.createValidation(formulaListConstraint, regions);

        return dataValidation;
    }

    public static void main(String[]args)throws Exception {
        List< String > typelist = new ArrayList< String >();
        for (int i = 0; i < 200; i++) {
            typelist.add("AAA" + (0 + i));
        }
        List< String > typelist1 = new ArrayList< String >();
        for (int i = 0; i < 100; i++) {
            typelist1.add("BBB" + (0 + i));
        }

        List< String > typelist2 = new ArrayList< String >();
        for (int i = 0; i < 10; i++) {
            typelist2.add("WE" + (0 + i));
        }
        String[]typeArrays = typelist.toArray(new String[typelist.size()]);
        String[]typeArrays1 = typelist1.toArray(new String[typelist1.size()]);
        String[]typeArrays2 = typelist2.toArray(new String[typelist2.size()]);
        Workbook wb = generateExcel(typeArrays,typeArrays1,typeArrays2);
        File tempFile = new File("C:\\test.xlsx");
        OutputStream os = new FileOutputStream(tempFile);

        wb.write(os);
        os.close();
    }

}