java 如何在使用 POI 生成的 Excel 表中创建相关下拉列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11113804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create dependent drop downs in excel sheet generated using POI?
提问by ashishjmeshram
we have a function in our java based web application where user can download an excel sheet template from the web application. Fill their data in this template and then upload the same excel sheet.
我们在基于 Java 的 Web 应用程序中有一个功能，用户可以在其中从 Web 应用程序下载 Excel 表格模板。在此模板中填写他们的数据，然后上传相同的 Excel 表格。
The system then reads this excel file and save this data in the database.
然后系统读取这个excel文件并将这些数据保存在数据库中。
Below is snapshot of template file with some sample data in it.
下面是模板文件的快照，其中包含一些示例数据。
What I want is when users download template file (template file usually just has the headers, so users know which data goes in which column), excel sheet should have drop downs for Division, Product, secondary product , Region and country. So that users do not enter any invalid values in those columns.
我想要的是当用户下载模板文件（模板文件通常只有标题，所以用户知道哪些数据进入哪一列）时，excel 表应该有 Division、Product、secondary product、Region 和 country 的下拉菜单。这样用户就不会在这些列中输入任何无效值。
As well, products varies according to divisions and secondary product varies according to products. Its more like dependent drop downs.
同样，产品因部门而异，次级产品因产品而异。它更像是依赖下拉菜单。
Basically I will need to create the excel sheet using Apache POI in which users will chose values from the drop dowsn instead of typing it themselevs.
基本上，我需要使用 Apache POI 创建 Excel 表，用户将在其中从下拉列表中选择值，而不是输入它们。
Even though we do have server side validation where we check if the values entered by users are valid or not.
即使我们确实有服务器端验证，我们也会检查用户输入的值是否有效。
The reason we wnat to do this is that e.g. some users might enter country as US, some as USA and some as United states.
我们这样做的原因是例如一些用户可能输入国家为美国，一些为美国，一些为美国。
The same thing goes for products etc. user may enter product as GFFX or GFFX Structuring or gffx etc.
产品等也是如此。用户可以输入产品为 GFFX 或 GFFX Structuring 或 gffx 等。
Is it possible to do this in excel sheet using POI? If not what are the other possible solutions or wasy to make sure users know what they have to enter in each columns?
是否可以使用 POI 在 Excel 表中执行此操作？如果不是，其他可能的解决方案是什么，或者是什么来确保用户知道他们必须在每列中输入什么？
EDIT 1 :
编辑 1：
I could created the drop downs but is it possible to created the dependent drop downs?
我可以创建下拉菜单，但是否可以创建相关的下拉菜单？
回答by Olivier Coilland
I was about to suggest AurA's solution but it looks like you'll have to build the validation list at run-time indeed.
我正要推荐 AurA 的解决方案，但看起来您确实必须在运行时构建验证列表。
You should have a look at the POI Quick Guide, it seems they have exactly what you need:
您应该查看POI Quick Guide，它们似乎正是您所需要的：
hssf.usermodel (binary .xls format)
hssf.usermodel（二进制 .xls 格式）
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(new String[]{"10", "20", "30"});
DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
xssf.usermodel (.xlsx format)
xssf.usermodel（.xlsx 格式）
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(
dvConstraint, addressList);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
回答by wilu
You can get drop-down list (after clicking right mouse button) in case you've added (using POI) suggestions to the rows upper the first row that is visible to the user and should be filled (thus the rows beneath the header contain suggestions and are hgidden).
如果您已将（使用 POI）建议添加到用户可见且应填充的第一行上方的行（因此标题下方的行包含），您可以获得下拉列表（单击鼠标右键后）建议和隐藏）。
You won't get (AFAIK) category dependancy with POI or even pure excel (without VBA) drop-down list (that holds suggestions on the basis of values entered earlier).
您不会获得（AFAIK）与 POI 甚至纯 excel（无 VBA）下拉列表（根据之前输入的值提供建议）的类别依赖性。
What you can do, is to use POI to fill helper sheet with appropriate raw data and use VBA to dynamically generate drop-downs that would allow to pick a value from a list.
您可以做的是使用 POI 用适当的原始数据填充辅助表，并使用 VBA 动态生成允许从列表中选择值的下拉列表。
