poi实现execl2003 两级联动

@Override
public String writeDataToEXCEL(HSSFWorkbook  wb,  List data, String string,
  String fileName, long bum, FileOutputStream out, Sheet sheet1,CsBackImportProblemRel obj) throws BOException {
try {
Row row = sheet1.createRow(0);

List<CsBackProblem> problemList= csBackImportProblemRelDao.getProblem();
List<String>  btProblemList=new ArrayList<String>(); //
List<Long> idProblemList=new ArrayList<Long>();
for (int i=0;i<problemList.size();i++){
btProblemList.add(problemList.get(i).getProblem().toString());
idProblemList.add(problemList.get(i).getCreateUserId());
}
//隐藏sheet页第一行数据
String[] problemArr = btProblemList.toArray(new String[problemList.size()]);
List list=csBackImportProblemRelDao.getSul();
Map<Long,List> maps=new TreeMap<Long, List>();
for (int j=0 ;j<list.size();j++){
List<String> sets=new ArrayList<String>();
Object[] ob = (Object[])list.get(j);
Long obId = Long.parseLong(ob[0].toString());
String problem=ob[1].toString();//问题原因
String solve=ob[2].toString();//解决方法
if (maps.containsKey(obId)){
                                      sets=maps.get(obId);
                                       sets.add(solve);
}else{
sets.add(problem);
sets.add(solve);
}
maps.put(obId,sets);
}
//对map进行按键排序
List<Map.Entry<Long,List>> sortlist = new ArrayList<Map.Entry<Long,List>>(maps.entrySet());
Collections.sort(sortlist,new Comparator<Map.Entry<Long,List>>() {
//升序排序
public int compare(Map.Entry<Long,List> o1, Map.Entry<Long,List> o2) {
return o1.getKey().compareTo(o2.getKey());
}
});


/**
            //创建一个专门用来存放地区信息的隐藏sheet页
//因此也不能在现实页之前创建，否则无法隐藏。
Sheet hideSheet = wb.createSheet("site");
wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);


int rowId = 0;
// 设置第一行，存省的信息
Row proviRow = hideSheet.createRow(rowId++);
proviRow.createCell(0).setCellValue("问题列表");
for(int i = 0; i < btProblemList.size(); i ++){
Cell proviCell = proviRow.createCell(i + 1);
proviCell.setCellValue(btProblemList.get(i));
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<Long> keyIterator = maps.keySet().iterator();
while(keyIterator.hasNext()){
Long key = keyIterator.next();
List<String> solve = maps.get(key);
Row row0 = hideSheet.createRow(rowId++);
row0.createCell(0).setCellValue(key);
for(int i = 0; i < solve.size(); i ++){
Cell cell = row0.createCell(i + 1);
cell.setCellValue(solve.get(i));
}


// 添加名称管理器
String range = getRange(1, rowId, solve.size());
Name name = wb.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
} **/
            //创建一个专门用来存放问题解决方案信息的隐藏sheet页
Sheet hideInfoSheet = wb.createSheet("excelhidesheetname");
//设置隐藏页标志
// wb.setSheetHidden(wb.getSheetIndex("excelhidesheetname"), true);
//第一行设置问题信息
Row problemNameRow = hideInfoSheet.createRow(0);
creatRow(problemNameRow, problemArr);
creatExcelNameList(wb, "excelhidesheetname", 1, problemArr.length, false);
int  k=0;
for (List solve:maps.values()) {
//以下行设置解决方案
Row solveNameRow = hideInfoSheet.createRow(k + 1);
List<String> solveList = solve;
String[] solveArr = solveList.toArray(new String[solve.size()]);
creatRow(solveNameRow, solveArr);
//动态大小设置问题对应的解决方案
creatExcelNameList(wb, problemArr[k].toString(), k + 2, solveArr.length, true);
solveList.clear();
solveArr = null;
++k;
}


setDataValidation(wb);


} catch (Exception e) {
throw new BOException(e);
}
return null;
}




/**
* 创建一列数据
* @param currentRow
* @param textList
*/
private static void creatRow(Row currentRow,String[] textList){
if(textList!=null&&textList.length>0){
int i = 0;
for(String cellValue : textList){
Cell userNameLableCell = currentRow.createCell(i++);
userNameLableCell.setCellValue(cellValue);
}
}
}


/**
* 创建一个名称
* @param workbook
*/
private static void creatExcelNameList(HSSFWorkbook 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 workbook
*/
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 sheet
*/
public static void setDataValidation(Workbook wb){
int sheetIndex = wb.getNumberOfSheets();
if(sheetIndex>0){
for(int i=0;i<sheetIndex;i++){
Sheet sheet = wb.getSheetAt(i);
if(!"excelhidesheetname".equals(sheet.getSheetName())){
DataValidation data_validation_list = null;
//问题选项添加验证数据
for(int a=2;a<3002;a++){
data_validation_list = getDataValidationByFormula("excelhidesheetname",a,2);
sheet.addValidationData(data_validation_list);
//城市选项添加验证数据
data_validation_list = getDataValidationByFormula("INDIRECT($B$"+a+")",a,3);
sheet.addValidationData(data_validation_list);
}
}
}
}
}


/**
* 使用已定义的数据源方式设置一个数据验证
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @return
*/
private static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex){
//加载下拉列表内容
DVConstraint constraint = DVConstraint.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 HSSFDataValidation(regions,constraint);
//设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示","请使用下拉方式选择合适的值！");
//设置输入错误提示信息
data_validation_list.createErrorBox("选择错误提示","你输入的值未在备选列表中，请下拉选择合适的值！");
return data_validation_list;
}