如何使用POI生成Excel表单中的依赖下拉列表？ [英] How to create dependent drop downs in excel sheet generated using POI?

问题描述

我们在基于Java的Web应用程序中有一个功能，用户可以从Web应用程序下载Excel表单模板。在这个模板中填写他们的数据，然后上传相同的Excel表。

系统然后读取此excel文件并将此数据保存在数据库中。

以下是其中包含一些示例数据的模板文件的快照。

我想要的是当用户下载模板文件（模板文件通常只有标题，所以用户知道哪个数据在哪列中），Excel表应该有下拉列表部门，产品，二次产品，地区和国家。使用户不要在这些列中输入任何无效的值。

同样，产品因部门而异，次要产品因产品而异。它更像是依赖下降。

基本上，我将需要使用Apache POI创建Excel表，其中用户将从drop dowsn中选择值，而不是输入themselevs。

即使我们确实有服务器端验证，我们检查用户输入的值是否有效。

我们这样做的原因是一些用户可能会进入美国，一些是美国，一些是美国。

产品等同样的事情，用户可以输入GFFX或GFFX结构化或gffx等。

使用POI可以在excel表中做到这一点吗？如果不是其他可能的解决方案还是确保用户知道他们在每列中输入什么？

编辑1： / p>

我可以创建下拉菜单，但是是否可以创建依赖的下拉列表？

解决方案

我即将提出AurA的解决方案，但是看起来你必须在运行时建立验证列表。

你应该看看<一个href =http://poi.apache.org/spreadsheet/quick-guide.html#Validation =nofollow> POI快速指南，它似乎有你所需要的：

hssf.usermodel（binary .xls格式）

  HSSFWorkbook工作簿=新HSSFWorkbook （）; 
 HSSFSheet sheet = workbook.createSheet（数据验证）; 
 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格式）

  XSSFWorkbook工作簿=新XSSFWorkbook（）; 
 XSSFSheet sheet = workbook.createSheet（数据验证）; 
 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper（sheet）; 
 XSSFDataValidationConstraint dvConstraint =（XSSFDataValidationConstraint）
 dvHelper.createExplicitListConstraint（new String [] {11，21，31}）; 
 CellRangeAddressList addressList = new CellRangeAddressList（0，0，0，0）; 
 XSSFDataValidation验证=（XSSFDataValidation）dvHelper.createValidation（
 dvConstraint，addressList）; 
 validation.setShowErrorBox（true）; 
 sheet.addValidationData（validation）; 
  

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.

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.

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.

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.

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.

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?

EDIT 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.

You should have a look at the POI Quick Guide, it seems they have exactly what you need:

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 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屋！