java-如何使用[Apache POI]创建从属下拉列表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了java-如何使用[Apache POI]创建从属下拉列表,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6086字,纯文字阅读大概需要9分钟。
内容图文
![java-如何使用[Apache POI]创建从属下拉列表](/upload/InfoBanner/zyjiaocheng/670/b0fa0e74ffcf4c58b5d898e4953f2717.jpg)
在这里,我使用[Apache POI]创建了一个excel模板,该用户可以填充数据.在那两个下拉列表中.想要创建一个依赖于上一个单元格的下拉列表的下拉列表.例如:如果我在单元格3中选择蔬菜类别,并且“大米,凝乳,牛奶”项目将出现在单元格4的从属下拉列表中.代码如下.
这里收集数据
List<InternetItemResponse> internetItems = internetItemService.getAllByHotelId(hotelId);
if (CollectionUtils.isNotEmpty(internetItems)) {
String[] itemsName = new String[internetItems.size()];
String[] itemsCategory = new String[internetItems.size()];
String itemName;
String itemCategory;
Map<String, Set<String>> categoryVsItemName = new HashMap<>();
Set<String> itemList;
for (int i = 0; i < internetItems.size(); i++) {
InternetItemResponse itemResponse = internetItems.get(i);
if (itemResponse != null) {
itemCategory = itemResponse.getCategory();
if (!StringUtils.isEmpty(itemCategory)) {
itemsCategory[i] = itemCategory;
itemName = itemResponse.getTitle();
itemsName[i] = itemName;
if (CollectionUtils.isEmpty(categoryVsItemName.get(itemCategory))) {
itemList = new HashSet<>();
itemList.add(itemName);
categoryVsItemName.put(itemCategory, itemList);
} else {
categoryVsItemName.get(itemCategory).add(itemName);
}
}
}
}
}
在下拉菜单中分配数据
// Setting drop down values
for (int i = 0; i < headerColumns.length; i++) {
if (i == 3) {
XSSFDataValidationHelper mealdvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
XSSFDataValidationConstraint mealdvConstraint = (XSSFDataValidationConstraint) mealdvHelper
.createExplicitListConstraint(itemsCategory);
// CellRangeAddressList(int firstRow, int lastRow, int firstCol, int lastCol)
CellRangeAddressList addressListmeal = new CellRangeAddressList(1, 99, i, i);
XSSFDataValidation categoryDataValidation = (XSSFDataValidation) mealdvHelper
.createValidation(mealdvConstraint, addressListmeal);
categoryDataValidation.setShowErrorBox(true);
categoryDataValidation.setSuppressDropDownArrow(true);
categoryDataValidation.setShowPromptBox(true);
sheet.addValidationData(categoryDataValidation);
} else if (i == 4) {
XSSFDataValidationHelper rmCategorydvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
XSSFDataValidationConstraint rmCategorydvConstraint = (XSSFDataValidationConstraint) rmCategorydvHelper
.createExplicitListConstraint(itemsName);
CellRangeAddressList addressListrmCategory = new CellRangeAddressList(1, 99, i, i);
XSSFDataValidation itemNameValidation = (XSSFDataValidation) rmCategorydvHelper
.createValidation(rmCategorydvConstraint, addressListrmCategory);
itemNameValidation.setShowErrorBox(true);
itemNameValidation.setSuppressDropDownArrow(true);
itemNameValidation.setShowPromptBox(true);
sheet.addValidationData(itemNameValidation);
}
}
解决方法:
创建依赖下拉列表与apache poi一样无能为力. apache poi库用于创建Excel文件.下拉列表的依赖性必须在运行生成文件的Excels GUI中进行管理.Apachepoi仅可以创建Excel文件,这样便可以实现.
一种方法是将命名范围用于数据验证列表,然后使用INDIRECT获取其名称.因此,主列表包含从属列表的命名范围的名称.从属列表使用= INDIRECT([主列表的单元格])然后获取从主列表中选择姓名的从属列表.
如何使用apache poi创建示例:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.*;
import java.util.Map;
import java.util.HashMap;
class CreateExcelDependentDataValidationListsUsingNamedRanges {
public static void main(String[] args) throws Exception {
//some data
Map<String, String[]> categoryItems = new HashMap<String, String[]>();
categoryItems.put("Countries", new String[]{"France", "Germany", "Italy"});
categoryItems.put("Capitals", new String[]{"Paris", "Berlin", "Rome"});
categoryItems.put("Fruits", new String[]{"Apple", "Peach", "Banana", "Orange"});
Workbook workbook = new XSSFWorkbook();
//hidden sheet for list values
Sheet sheet = workbook.createSheet("ListSheet");
Row row;
Name namedRange;
String colLetter;
String reference;
int c = 0;
//put the data in
for (String key : categoryItems.keySet()) {
int r = 0;
row = sheet.getRow(r); if (row == null) row = sheet.createRow(r); r++;
row.createCell(c).setCellValue(key);
String[] items = categoryItems.get(key);
for (String item : items) {
row = sheet.getRow(r); if (row == null) row = sheet.createRow(r); r++;
row.createCell(c).setCellValue(item);
}
//create names for the item list constraints, each named from the current key
colLetter = CellReference.convertNumToColString(c);
namedRange = workbook.createName();
namedRange.setNameName(key);
reference = "ListSheet!$" + colLetter + "$2:$" + colLetter + "$" + r;
namedRange.setRefersToFormula(reference);
c++;
}
//create name for Categories list constraint
colLetter = CellReference.convertNumToColString((c-1));
namedRange = workbook.createName();
namedRange.setNameName("Categories");
reference = "ListSheet!$A$1:$" + colLetter + "$1";
namedRange.setRefersToFormula(reference);
//unselect that sheet because we will hide it later
sheet.setSelected(false);
//visible data sheet
sheet = workbook.createSheet("Sheet1");
sheet.createRow(0).createCell(0).setCellValue("Select Category");
sheet.getRow(0).createCell(1).setCellValue("Select item from that category");
sheet.setActiveCell(new CellAddress("A2"));
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
//data validations
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
//data validation for categories in A2:
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("Categories");
CellRangeAddressList addressList = new CellRangeAddressList(1, 1, 0, 0);
DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
//data validation for items of the selected category in B2:
dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT($A$2)");
addressList = new CellRangeAddressList(1, 1, 1, 1);
validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
//hide the ListSheet
workbook.setSheetHidden(0, true);
//set Sheet1 active
workbook.setActiveSheet(1);
FileOutputStream out = new FileOutputStream("CreateExcelDependentDataValidationListsUsingNamedRanges.xlsx");
workbook.write(out);
workbook.close();
out.close();
}
}
内容总结
以上是互联网集市为您收集整理的java-如何使用[Apache POI]创建从属下拉列表全部内容,希望文章能够帮你解决java-如何使用[Apache POI]创建从属下拉列表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。