java poi实现execl三级联动

List nameValueList=csBackImportProblemRelDao.getNameValue();

List<String> newNameValueList=new ArrayList<String>(); //新字典值的list
for (int i=0;i<nameValueList.size();i++){
String nameValue=nameValueList.get(i).toString();
nameValue=nameValue.replaceAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l");
newNameValueList.add(nameValue);
}
//字典值和问题原因
List nameValueProblemList=csBackImportProblemRelDao.getNameValueBackProblemList();
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[])nameValueProblemList.get(j);
String nameValue = ob[0].toString();//字典值
nameValue=nameValue.replaceAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l");
String problem=ob[1].toString();//问题原因
//特殊字符在execl中无法实现联动，所以去掉或替换掉
problem=problem.replaceAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l");
if (maps.containsKey(nameValue)){
sets=maps.get(nameValue);
sets.add(problem);
}else{
sets.add(problem);
}
maps.put(nameValue,sets);
}
List backProblemSolveList=csBackImportProblemRelDao.getBackProblemSolve();
for (int j=0 ;j<backProblemSolveList.size();j++){
List<String> sets=new ArrayList<String>();
Object[] ob = (Object[])backProblemSolveList.get(j);
String problem=ob[1].toString();//问题原因
//特殊字符在execl中无法实现联动，所以去掉或替换掉
problem=problem.replaceAll("，","、").replaceAll("\\*","x").replaceAll("（","“").replaceAll("）","”").replaceAll("/","l");
String solve=ob[2].toString();//解决方法
if (maps.containsKey(problem)){
sets=maps.get(problem);
sets.add(solve);
}else{
sets.add(solve);
}
maps.put(problem,sets);
}
String[] newNameValueArr = newNameValueList.toArray(new String[newNameValueList.size()]);
//创建一个专门用来存放问题解决方案信息的隐藏sheet页
Sheet hideInfoSheet = wb.createSheet("excelhidesheetname");
//设置隐藏页标志
wb.setSheetHidden(wb.getSheetIndex("excelhidesheetname"), true);
int rowId = 0;
Row proviRow = hideInfoSheet.createRow(rowId++);
proviRow.createCell(0).setCellValue("无字典值");
for (int i = 0; i < newNameValueList.size(); i++) {
Cell proviCell = proviRow.createCell(i + 1);
proviCell.setCellValue(newNameValueList.get(i));
}
creatExcelNameList(wb, "excelhidesheetname", 1, newNameValueArr.length, false);
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<String> keyIterator = maps.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = maps.get(key);
Row row = hideInfoSheet.createRow(rowId++);
row.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(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);
}
}
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 = workbook.createName();
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 = (XSSFSheet)wb.getSheetAt(i);
if(!"excelhidesheetname".equals(sheet.getSheetName())){
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_validation_list.createPromptBox("下拉选择提示","请使用下拉方式选择合适的值！");
//设置输入错误提示信息
data_validation_list.createErrorBox("选择错误提示","你输入的值未在备选列表中，请下拉选择合适的值！");
return data_validation_list;
}