生成带下拉选项的excel(xls、xlsx)

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

@SpringBootTest
@RunWith(SpringRunner.class)
public class TestDemo {

    @Autowired
    private TestServiceImpl testService;

    @Test
    public void test1() {
        testService.createExcel();
    }
}

@Service
public class TestServiceImpl {
    public void createExcel() {
        String fileName = "d:/test/员工信息表.xlsx";
        String[] heads = {"姓名", "性别", "证件类型", "证件号码", "服务结束时间", "参保地", "民族"};

        //下拉框数据
        String[] str1 = {"男", "女", "未知"};
        String[] str2 = {"北京", "上海", "广州", "深圳", "武汉", "长沙", "湘潭"};
        String[] str3 = {"01-汉族", "02-蒙古族", "03-回族", "04-藏族", "05-维吾尔族", "06-苗族", "07-彝族", "08-壮族", "09-布依族",
                "10-朝鲜族", "11-满族", "12-侗族", "13-瑶族", "14-白族", "15-土家族", "16-哈尼族", "17-哈萨克族", "18-傣族", "19-黎族", "20-傈僳族",
                "21-佤族", "22-畲族", "23-高山族", "24-拉祜族", "25-水族", "26-东乡族", "27-纳西族", "28-景颇族", "29-柯尔克孜族", "30-土族",
                "31-达斡尔族", "32-仫佬族", "33-羌族", "34-布朗族", "35-撒拉族", "36-毛难族", "37-仡佬族", "38-锡伯族", "39-阿昌族", "40-普米族",
                "41-塔吉克族", "42-怒族", "43-乌孜别克族", "44-俄罗斯族", "45-鄂温克族", "46-德昂族", "47-保安族", "48-裕固族", "49-京族", "50-塔塔尔族",
                "51-独龙族", "52-鄂伦春族", "53-赫哲族", "54-门巴族", "55-珞巴族", "56-基诺族", "98-外国血统", "99-其他"};

        HashMap<Integer, String[]> dataMap = new HashMap<>();
        dataMap.put(1, str1);
        dataMap.put(5, str2);
        dataMap.put(6, str3);

        ExcelUtils.createExcel(fileName, heads, dataMap);
    }
}

public class ExcelUtils {

    public static void createExcel(String filePathAndName, String[] heads, Map<Integer, String[]> dataMap) {

        Workbook wb;

        String fileType = filePathAndName.substring(filePathAndName.lastIndexOf(".") + 1);
        if ("xlsx".equalsIgnoreCase(fileType)) {
            wb = new XSSFWorkbook();
        } else if ("xls".equalsIgnoreCase(fileType)) {
            wb = new HSSFWorkbook();
        } else {
            return;
        }

        Sheet sheet1 = wb.createSheet("Sheet1");
        
        // 样式
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short) 12);
        cellStyle.setFont(fontStyle);
        
        // head行
        Row firstRow = sheet1.createRow(0);
        for (int i = 0; i < heads.length; i++) {
            Cell cell = firstRow.createCell(i);
            sheet1.setColumnWidth(i, 4000);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(heads[i]);
        }


        Sheet sheet2 = wb.createSheet("Sheet2");

        // 设置sheet2页可见性
        // wb.setSheetVisibility(1, SheetVisibility.HIDDEN);

        int index = 0;
        for (Map.Entry<Integer, String[]> entry : dataMap.entrySet()) {
            Integer key = entry.getKey();
            String[] value = entry.getValue();
            String formula = "Sheet2!$" + (char) ('A' + index) + "$" + (index + 1) + ":$" + (char) ('A' + index) + "$" + value.length;
            sheet1.addValidationData(createDataValidation(fileType, formula, key, key));

            for (int i = 0; i < value.length; i++) {
                Row row = sheet2.getRow(i);
                if (null == row) {
                    row = sheet2.createRow(i);
                    sheet2.setColumnWidth(i, 4000);
                }
                row.createCell(index).setCellValue(value[i]);
            }
            index++;
        }

        writeDataToFile(filePathAndName, wb);
    }

    /**
     * 将工作薄数据写入文件
     */
    private static void writeDataToFile(String filePathAndName, Workbook workbook) {
        try {

            File file = new File(filePathAndName);
            if (!file.getParentFile().exists() && file.getParentFile().mkdirs()) {
                System.out.println("路径不存在，新建。。。");
            }
            if (!file.exists() && file.createNewFile()) {
                System.out.println("文件不存在，新建。。。");
            }

            FileOutputStream out = new FileOutputStream(file);
            out.flush();
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置下拉框绑定的数据，支持xls、xlsx
     *
     * @param formula 绑定表达式
     */
    private static DataValidation createDataValidation(String fileType, String formula, int firstCol, int endCol) {

        CellRangeAddressList cellRange = new CellRangeAddressList(1, 5000, firstCol, endCol);

        if ("xlsx".equalsIgnoreCase(fileType)) {
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper(null);
            DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
            return helper.createValidation(constraint, cellRange);
        }
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formula);
        return new HSSFDataValidation(cellRange, constraint);
    }
}