【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);
上一篇:
Python 安装包管理工具 pip