POI使用指南

获取EXCEL表头信息

FileInputStream fis =FileInputStream(new File("文件名"))
WorkBook workBook = WorkbookFactory.create(fis);
//获取sheet
Sheet sheet = wb.getSheet("yourSheetName");
//主要代码
Row firstRow = sheet.getRow(0); //获取第一行row
int rowSize = firstRow.getPhysicalNumberOfCells(); //获取row的cell数量
String[] keys = new String[rowSize]; 
for (int i = 0; i < rowSize; i++){
    //默认cell为String格式，如果有多种格式，需要自己定义swich条件
    keys[i] = firstRow.getCell(i).getRichStringCellValue().getString();
}

导入下拉框

/**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint
                .createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }

导入时下拉列表值超过255的问题（String literals in formulas can’t be bigger than 255 characters ASCII）

 public static HSSFDataValidation getDataValidationList4Col(Sheet sheet, int firstRow, int firstCol, int endRow, int endCol, List<String> colName, Workbook wbCreat) {
        String[] dataArray = colName.toArray(new String[0]);
        Sheet hidden = wbCreat.createSheet("hidden");
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = wbCreat.createName();
        namedCell.setNameName("hidden");
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

        //将第二个sheet设置为隐藏
        wbCreat.setSheetHidden(1, true);

        if (null != validation) {
            sheet.addValidationData(validation);
        }
        return validation;
    }
    

方法参考：
https://blog.csdn.net/wangchaox123/article/details/97787470?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
如有任何问题，联系作者删除。