【Java】Excel中添加下拉框

0、两种方式

有两种方式可以实现，我仅在此记录一下

POI Hutool

1、使用 POI

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * Sheet 工具类
 **/
public class SheetUtils {
          
   

    /**
     * 创建下拉列表选项
     *
     * @param sheet    所在Sheet页面
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    public static void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
          
   
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
          
   
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
          
   
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

}

2、使用 Hutool

注意：Hutool 版本需要在 4.6.2 或更高。

ExcelWriter writer = ExcelUtil.getWriter(new File("D:/COVER_HOUSE_TEMPLATE.xlsx"));

// writer.setOnlyAlias(true);
writer.setColumnWidth(0, 0);

Sheet sheet = writer.getSheet();

String[] values = {
          
    "男", "女", "未知" };
int firstRow = 0;
int lastRow = 5000;
int firstCol = 1;
int lastCol = 5;

CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

StyleSet styleSet = writer.getStyleSet();
CellStyle cellStyle = styleSet.getCellStyle();
cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));

DataValidationHelper helper = sheet.getDataValidationHelper();
// 设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
DataValidation dataValidation = helper.createValidation(constraint, addressList);

writer.addValidationData(dataValidation);