提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
本文主要用java poi 实现excel 的联想式下拉列表,前提条件代码中textlist 是需要事先排好序
话不多说,直接上代码
代码
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class TestMain {
public static void main(String[] args) throws IOException, InvalidFormatException {
Workbook wb = WorkbookFactory.create(new FileInputStream("D:\\test.xlsx"));
Sheet sheet = wb.getSheetAt(0);
TestMain.setXSSFValidationWithHiddenFuzzyQuery(wb,sheet, new String[]{"1", "2", "3","33","4","44"}, "提示", 1, 10, 0, 0);
wb.write(new FileOutputStream("D:\\testq.xlsx"));
}
/**
* 设置某些列的值只能输入预制的数据,显示下拉框(兼容超出一定数量的下拉框).
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param promptContent 提示内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
*/
public static void setXSSFValidationWithHiddenFuzzyQuery(Workbook wb,Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
{
String hideSheetName = "combo_" + firstCol + "_" + endCol + sheet.getSheetName();
Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据
for (int i = 0; i < textlist.length; i++)
{
hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
}
// 创建名称,可被其他单元格引用
Name name = wb.createName();
String namesName = hideSheetName + "_data";
name.setNameName(namesName);
name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
for(int col=firstCol;col<=endCol;col++){
String colName = getColumnName(col+1);
for(int i=firstRow;i<=endRow;i++){
String currentCell = colName+(i+1);
DataValidationHelper helper = sheet.getDataValidationHelper();
String function = "=OFFSET("+namesName+",MATCH(\"*\"&"+currentCell+"&\"*\","+namesName+",0)-1,0,COUNTIF("+namesName+",\"*\"&"+currentCell+"&\"*\"),1)";
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createFormulaListConstraint(function);
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(i, i, col, col);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
dataValidation.setShowErrorBox(false);
if (promptContent!=null&&!promptContent.equals(""))
{
// 如果设置了提示信息则鼠标放上去提示
dataValidation.createPromptBox("", promptContent);
dataValidation.setShowPromptBox(true);
}
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation)
{
dataValidation.setSuppressDropDownArrow(true);
}
else
{
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
}
// 设置hiddenSheet隐藏
// wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
}
/**
* 根据列值获取字母
* @param columnNum
* @return
*/
public static String getColumnName(int columnNum) {
int first;
int last;
String result = "";
if (columnNum > 256)
columnNum = 256;
first = columnNum / 27;
last = columnNum - (first * 26);
if (first > 0)
result = String.valueOf((char) (first + 64));
if (last > 0)
result = result + String.valueOf((char) (last + 64));
return result;
}
}
存在问题
本实现方法依然存在问题,若数组中既存在查询字段开头的内容又存在在中间的内容,就会丢失其中一部分内容,例如 数组为【“中文”,“中中文”,“学习”,“学中文”】,当搜索“中”时,“学中文”会被丢失;欢迎大家指导解决此问题,谢谢