如何用java代码在excel中创建可搜索下拉列表
我想创建下拉列表,该列表必须作为用户类型进行搜索,必须选择所有相关值
我的Java代码是:-
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("testing");
// This data needs to be written (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();
// String fileType = ImportItemFileType.Medicine.name();
String fileName = fileType.getType();
Object[] columnName = new Object[1];
columnName[0] = "AutoSearch";
data.put("1", columnName);
// Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 0;
int cellnum = 0;
for (String key : keyset) {
Row row = sheet.createRow(rownum++);
Object[] objArr = data.get(key);
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
// System.out.println("cell num---------------------------------------------------"+
// cellnum);
if (obj instanceof String)
cell.setCellValue((String) obj);
else if (obj instanceof Integer)
cell.setCellValue((Integer) obj);
}
}
// department drop down
List<String> list = new ArrayList<>();
list.add("unm");
list.add("hcg");
list.add("star");
list.add("changa");
list.add(0, "");
String[] departmentArray = new String[list.size()];
departmentArray = list.toArray(new String[list.size()]);
XSSFSheet hiddenDep = workbook.createSheet("hiddenDepartment");
for (int i = 0, length = departmentArray.length; i < length; i++) {
String name = departmentArray[i];
System.out.println("name "+name);
XSSFRow row = hiddenDep.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellValue(name);
}
DataValidation dataValidationDep = null;
DataValidationConstraint constraintDep = null;
DataValidationHelper validationHelperDep = null;
validationHelperDep = new XSSFDataValidationHelper(sheet);
CellRangeAddressList addressListDep = new CellRangeAddressList(1, 10, 0, 0);
constraintDep = validationHelperDep
.createFormulaListConstraint("hiddenDepartment!$A$1:$A$"
+ departmentArray.length);
dataValidationDep = validationHelperDep.createValidation(constraintDep, addressListDep);
dataValidationDep.setSuppressDropDownArrow(true);
workbook.setSheetHidden(1, true);
dataValidationDep.setShowErrorBox(true);
sheet.addValidationData(dataValidationDep);
for (int j1 = 0; j1 <= cellnum; j1++) {
if(j1 == 0){
sheet.setColumnWidth(0, 2000);
}else{
sheet.autoSizeColumn(j1);
}
}
String filePath = "";
try {
filePath = MY_PATH + fileName + ".xlsx";
FileOutputStream out = new FileOutputStream(new File(filePath));
workbook.write(out);
out.close();
System.out
.println("howtodoinjava_demo.xlsx written successfully on disk.");
} catch (Exception e) {
e.printStackTrace();
}
在这里,我创建了“hiddenDepartment”作为隐藏表,用于显示下拉列表,将应用于A1单元格到A10单元格
问题是当我的下拉列表有超过50个值时,我想让它作为用户类型进行搜索,只需要显示相应的数据enter image description here
你可以在img中看到下拉列表
共 (0) 个答案