一个xls格式excel 第9列的下拉框的默认值是“是”和“否”,讲帮我使用apache poi,从第5行开始修改他的下拉值为“男”、“女”、和“第三性别”
import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.Arrays; public class ExcelDropDownModifier { public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream("path/to/your/excel.xls"); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); // 假设修改第一个工作表 String[] cers = Arrays.asList("男", "女", "第三性别").toArray(new String[0]); // 创建数据验证帮助器 DataValidationHelper helper = sheet.getDataValidationHelper(); // 参数分别为 起始行索引(第5行)、结束行索引、起始列索引(第9列)、结束列索引(第9列) CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(4, sheet.getLastRowNum(), 8, 8); // 创建数据验证约束,查询数据作为下拉列表的选项 DataValidationConstraint constraint = helper.createExplicitListConstraint(cers); // 创建数据验证 DataValidation validation = helper.createValidation(constraint, cellRangeAddressList); // 应用数据验证到工作表 sheet.addValidationData(validation); // 写回修改 FileOutputStream fos = new FileOutputStream("path/to/your/excel_updated.xls"); workbook.write(fos); fos.close(); // 关闭资源 workbook.close(); fis.close(); } }