javapoi实现execl三级联动
List NameValue();
List<String> newNameValueList=new ArrayList<String>(); //新字典值的list
for (int i=0;i<nameValueList.size();i++){
String (i).toString();
placeAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l"); newNameValueList.add(nameValue);
}
//字典值和问题原因
List NameValueBackProblemList();
Map<String, List<String>> maps = new HashMap<String, List<String>>();
for (int j=0 ;j<nameValueProblemList.size();j++){
List<String> sets=new ArrayList<String>();
Object[] ob = (Object[])(j);
String nameValue = ob[0].toString();//字典值
placeAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l"); String problem=ob[1].toString();//问题原因
//特殊字符在execl中⽆法实现联动，所以去掉或替换掉
placeAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l");
if (ainsKey(nameValue)){
(nameValue);
sets.add(problem);
}else{
sets.add(problem);
}
maps.put(nameValue,sets);
}
List BackProblemSolve();
for (int j=0 ;j<backProblemSolveList.size();j++){
List<String> sets=new ArrayList<String>();
Object[] ob = (Object[])(j);
String problem=ob[1].toString();//问题原因
//特殊字符在execl中⽆法实现联动，所以去掉或替换掉
placeAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l"); String solve=ob[2].toString();//解决⽅法
if (ainsKey(problem)){
(problem);
sets.add(solve);
}else{
sets.add(solve);
}
maps.put(problem,sets);
}
String[] newNameValueArr = Array(new String[newNameValueList.size()]);
//创建⼀个专门⽤来存放问题解决⽅案信息的隐藏sheet页
Sheet hideInfoSheet = wb.createSheet("excelhidesheetname");
//设置隐藏页标志
wb.SheetIndex("excelhidesheetname"), true);
int rowId = 0;
Row proviRow = ateRow(rowId++);
for (int i = 0; i < newNameValueList.size(); i++) {
Cell proviCell = ateCell(i + 1);
proviCell.(i));
}
creatExcelNameList(wb, "excelhidesheetname", 1, newNameValueArr.length, false);
// 将具体的数据写⼊到每⼀⾏中，⾏开头为⽗级区域，后⾯是⼦区域。
Iterator<String> keyIterator = maps.keySet().iterator();
while (keyIterator.hasNext()) {
String key = ();
List<String> son = (key);
Row row = ateRow(rowId++);
for (int i = 0; i < son.size(); i++) {
Cell cell = ateCell(i + 1);
cell.(i));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = wb.createName();
name.setNameName(key);
String formula = "excelhidesheetname!" + range;
name.setRefersToFormula(formula);
}
//增加下拉框并增加数据验证
setDataValidation(wb,dataList.size());
/**
*
* @param offset 偏移量，如果给0，表⽰从A列开始，1，就是从B列
* @param rowId 第⼏⾏
* @param colCount ⼀共多少列
* @return 如果给⼊参 1,1,10. 表⽰从B1-K1。最终返回 $B$1:$K$1
* @author
*/
public String getRange(int offset, int rowId, int colCount) {
char start = (char)('A' + offset);
if (colCount <= 25) {
char end = (char)(start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
if ((colCount - 25) % 26 == 0) {// 边界值
endSuffix = (char)('A' + 25);
} else {
endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
}
} else {// 51以上
if ((colCount - 25) % 26 == 0) {
endSuffix = (char)('A' + 25);
endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1);
} else {
endSuffix = (char)('A' + (colCount - 25) % 26 - 1);
endPrefix = (char)(endPrefix + (colCount - 25) / 26);
}
replaceall()
}
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
}
}
/**
* 创建⼀个名称
* @param workbook
*/
private static void creatExcelNameList(XSSFWorkbook workbook,String nameCode,int order,int size,boolean cascadeFlag){ Name name;
name = ateName();
name.setNameName(nameCode);
name.setRefersToFormula("excelhidesheetname"+"!"+creatExcelNameList(order,size,cascadeFlag));
}
/**
* 名称数据⾏列计算表达式
* @param
*/
private static String creatExcelNameList(int order,int size,boolean cascadeFlag){ char start = 'A';
if(cascadeFlag){
start = 'B';
if(size<=25){
char end = (char)(start+size-1);
return "$"+start+"$"+order+":$"+end+"$"+order;
}else{
char endPrefix = 'A';
char endSuffix = 'A';
if((size-25)/26==0||size==51){//26-51之间，包括边界（仅两次字母表计算）
if((size-25)%26==0){//边界值
endSuffix = (char)('A'+25);
}else{
endSuffix = (char)('A'+(size-25)%26-1);
}
}else{//51以上
if((size-25)%26==0){
endSuffix = (char)('A'+25);
endPrefix = (char)(endPrefix + (size-25)/26 - 1);
}else{
endSuffix = (char)('A'+(size-25)%26-1);
endPrefix = (char)(endPrefix + (size-25)/26);
}
}
return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;
}
}else{
if(size<=26){
char end = (char)(start+size-1);
return "$"+start+"$"+order+":$"+end+"$"+order;
}else{
char endPrefix = 'A';
char endSuffix = 'A';
if(size%26==0){
endSuffix = (char)('A'+25);
if(size>52&&size/26>0){
endPrefix = (char)(endPrefix + size/26-2);
}
}else{
endSuffix = (char)('A'+size%26-1);
if(size>52&&size/26>0){
endPrefix = (char)(endPrefix + size/26-1);
}
}
return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;
}
}
}
/**
* 添加数据验证选项
* @param wb
* @param datasize
*/
public static void setDataValidation(Workbook wb,int datasize){
int sheetIndex = wb.getNumberOfSheets();
if(sheetIndex>0){
for(int i=0;i<sheetIndex;i++){
XSSFSheet sheet = (SheetAt(i);
if(!"excelhidesheetname".SheetName())){
DataValidation data_validation_list = null;
for(int a=2;a<datasize+3;a++){
//字典值所在列
data_validation_list = getDataValidationByFormula("excelhidesheetname",a,9,sheet);
sheet.addValidationData(data_validation_list);
//问题选项添加验证数据 10代表问题所在列
data_validation_list = getDataValidationByFormula("INDIRECT(I"+a+")",a,11,sheet);
sheet.addValidationData(data_validation_list);
/
/解决办法选项添加验证数据 J对应问题的所在列10
data_validation_list = getDataValidationByFormula("INDIRECT(K"+a+")",a,13,sheet);
sheet.addValidationData(data_validation_list);
}
}
}
}
}
/**
* 使⽤已定义的数据源⽅式设置⼀个数据验证
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @return
*/
private static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex,XSSFSheet sheet){
//加载下拉列表内容
DataValidationConstraint constraint = new XSSFDataValidationHelper((XSSFSheet)sheet).createFormulaListConstraint(formulaString); //设置数据有效性加载在哪个单元格上。
//四个参数分别是：起始⾏、终⽌⾏、起始列、终⽌列
int firstRow = naturalRowIndex-1;
int lastRow = naturalRowIndex-1;
int firstCol = naturalColumnIndex-1;
int lastCol = naturalColumnIndex-1;
CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);
//数据有效性对象
DataValidation data_validation_list = new XSSFDataValidationHelper((XSSFSheet)sheet).createValidation(constraint,regions);
data_validation_list.setShowErrorBox(true);//如果不设置为true，提⽰信息不能显⽰
data_validation_list.setShowPromptBox(true);//如果不设置为true，提⽰信息不能显⽰
//设置输⼊信息提⽰信息
data_atePromptBox("下拉选择提⽰","请使⽤下拉⽅式选择合适的值！");
//设置输⼊错误提⽰信息
data_ateErrorBox("选择错误提⽰","你输⼊的值未在备选列表中，请下拉选择合适的值！");
return data_validation_list;
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。