POI 导出excel文件下拉框问题

周杰伦本人

发布于 2023-10-12 14:14:21

5320

发布于 2023-10-12 14:14:21

文章被收录于专栏：同步文章

今天捣鼓那个下拉框呢这个Workbook有三个实现类

首先po一个xssf的下拉框实现工具类：

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

import java.io.FileOutputStream;

/**
 * describe:
 *
 * @author xiepanpan
 * @date 2018/07/11
 */
public class Test {

    public static void main(String[] args) {
        try
        {
            dropDownList42007("D:\\test.xlsx");
        }
        catch (Exception e) {

            e.printStackTrace();
        }
    }


    public static void dropDownList42007(String filePath)
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("下拉列表测试");
        String[] datas = new String[] {"维持","恢复","调整"};
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(datas);
        CellRangeAddressList addressList = null;
        XSSFDataValidation validation = null;
        for (int i = 0; i < 100; i++) {
            addressList = new CellRangeAddressList(i, i, 0, 0);
            validation = (XSSFDataValidation) dvHelper.createValidation(
                    dvConstraint, addressList);
            // 07默认setSuppressDropDownArrow(true);
            // validation.setSuppressDropDownArrow(true);
            // validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
        FileOutputStream stream = new FileOutputStream(filePath);
        workbook.write(stream);
        stream.close();
        addressList = null;
        validation = null;
    }
}

HSSFWorkbook 的下拉框设置：

//加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(formulaString);
        //设置数据有效性加载在哪个单元格上。
        //四个参数分别是：起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex-1;
        int lastRow = naturalRowIndex-1;
        int firstCol = naturalColumnIndex-1;
        int lastCol = naturalColumnIndex-1;
        CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);
        //数据有效性对象
        DataValidation dataValidation = new HSSFDataValidation(regions,constraint);
        sheet.addValidationData(dataValidation);

SXSSFWorkbook的下拉框设置方法：

/**
     * 设置下拉框
     * @param formulaString 下拉框数组
     * @param firstRow 起始行 从0开始
     * @param lastRow 终止行
     * @param firstCol 起始列
     * @param lastCol 终止列
     * @param <E>
     * @return
     */
    public <E> MyExportExcel setDropDownBox(String[] formulaString,int firstRow, int lastRow,int firstCol,int lastCol){
        //加载下拉列表内容
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(formulaString);
        //设置下拉框位置
        CellRangeAddressList addressList = null;
        addressList = new CellRangeAddressList(firstRow, 500, 1, 1);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //处理Excel兼容性问题
        if(dataValidation instanceof XSSFDataValidation){
            //数据校验
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }else{
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
        return this;
    }

效果：