如何使用POI生成Excel工作表中创建相关的下拉菜单？ [英] How to create dependent drop downs in excel sheet generated using POI?
问题描述
我们有我们的基于Java的Web应用程序的功能中，用户可以从Web应用程序下载一个Excel工作表模板。填写他们的数据在这个模板，然后上传同一个Excel工作表。
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.
然后，系统读取该excel文件，并保存在数据库中的这些数据。
The system then reads this excel file and save this data in the database.
下面是它的一些样本数据模板文件的快照。
Below is snapshot of template file with some sample data in it.
我要当用户下载模板文件（模板文件通常只是有头，让用户知道哪些数据在列），Excel工作表应该有分部，产品，辅助产品，地区和国家的下拉菜单。所以用户不要在这些列中输入任何无效值。
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.
同时，产品根据不同产品按部门和次要产品而异。它更像是依赖下拉菜单。
As well, products varies according to divisions and secondary product varies according to products. Its more like dependent drop downs.
基本上我需要创建使用Apache POI Excel工作表中，用户将代替dowsn键入它themselevs的降选择值。
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.
虽然我们有服务器端验证，我们检查，如果用户输入的值是有效还是无效。
Even though we do have server side validation where we check if the values entered by users are valid or not.
我们wnat这样做的原因是，例如有些用户可能输入国为美国，一些人认为美国和一些为美国。
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.
同样的事情也适用于产品等。用户可以输入产品的GFFX或GFFX结构化或gffx等。
The same thing goes for products etc. user may enter product as GFFX or GFFX Structuring or gffx etc.
是否有可能使用POI为此在Excel工作表？如果不是有什么其他可能的解决方案或WASY以确保用户知道他们有什么在每列中输入？
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?
编辑1：
我可以创建的下拉菜单，但是有可能创建的依赖下拉菜单
I could created the drop downs but is it possible to created the dependent drop downs?
推荐答案
我正要建议灵气的解决方案，但它看起来像你必须建立在运行时确实验证列表。
I was about to suggest AurA's solution but it looks like you'll have to build the validation list at run-time indeed.
您应该看一看在 POI快速指南，看来他们有你需要的东西：
You should have a look at the POI Quick Guide, it seems they have exactly what you need:
hssf.usermodel（二进制.xls格式）
hssf.usermodel (binary .xls format)
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格式）
xssf.usermodel (.xlsx format)
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);
这篇关于如何使用POI生成Excel工作表中创建相关的下拉菜单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
