话不多说，看效果图，直接上代码。
sheet 商户表
hideSheet ，功能完成后隐藏的Sheet，用于储存下拉框中的信息，（以一定的规则将所需数据存储在表格中）。
下面是代码
部分数据需要在导出时使用，如通过省名去找它的编码，这时就没有必要再去数据库查询，把此数据储存成全局参数，使用时只需要简单的判断即可。
四大直辖市在这里被忽略，如有需要根据实际情况去处理。
数组可使用动态数组。
@RequestMapping(params = "action=toExcel") public void toExcel(HttpServletRequest request,HttpServletResponse response) { try { String name = "商户表"; String valStr = "attachment;filename=" + new String(name.getBytes("gb2312"), "ISO8859-1") + ".xls"; OutputStream os = response.getOutputStream();// 取得输出流 response.reset();// 清空输出流 // 设定输出文件头 response.setHeader("Content-Disposition", valStr); response.setContentType("application/msexcel");// 定义输出类型 Workbook workbook = new HSSFWorkbook(); String[] fieldLabelArray = { "商户简称", "商户全称", "商户类型", "父级商户", "省", "市", "县", "电话", "联系人", "邮箱", "邮编", "地址", "经度", "纬度" }; //得到省名称 Map<String,String> params = new HashMap<String,String>(); //所有的省 if(faMerchantList.size()==0){ faMerchantList = service.findList("UU_BANK_MERCHANT.queryName", paramData); } if(merchantList.size()==0){ merchantList = bankMerchantTypeService.findList("UU_BANK_MERCHANTTYPE.query", paramData); } if(proviceLists.size()==0){ proviceLists = serviceOut.findList("UU_BANK_BANKOUTLET.selectProvices", paramData); } String[] provinceArr=new String[proviceLists.size()]; //将有子区域的父区域放到一个数组中 ArrayList areaFatherNameArr=new ArrayList(); Map<String,String[]> areaMap =new HashMap<String, String[]>(); int index=0; int cityIndex=0; for(Object object:proviceLists){ Map entry=(Map) object; provinceArr[index]=(String) entry.get("NAME"); String provinceCode = (String) entry.get("CITYCODE"); areaFatherNameArr.add(cityIndex,(String) entry.get("NAME")); cityIndex+=1; if(!provinceCode.isEmpty()){ String city_Code = provinceCode.substring( 0, 2); params.put("CITY_CODE", city_Code); params.put("CITYCODE", provinceCode); } List cityLists = serviceOut.findList("UU_BANK_BANKOUTLET.selectCitys", params); String[] areaArr= new String[cityLists.size()]; int indexs=0; for(Object city:cityLists){ Map entryCity=(Map) city; areaFatherNameArr.add(cityIndex, (String) entryCity.get("NAME")); areaArr[indexs]=(String) entryCity.get("NAME"); String cityCode = (String) entryCity.get("CITYCODE"); if(!cityCode.isEmpty()){ String city_Code = cityCode.substring( 0, 4); params.put("CITY_CODE", city_Code); params.put("CITYCODE", cityCode); } List countyLists = serviceOut.findList("UU_BANK_BANKOUTLET.selectCountys", params); String[] countyArr= new String[countyLists.size()]; int countyindex=0; for(Object county:countyLists){ Map entryCounty=(Map) county; countyArr[countyindex]=(String) entryCounty.get("NAME"); countyindex++; } areaMap.put((String) entryCity.get("NAME"), countyArr); indexs++; cityIndex++; } areaMap.put((String) entry.get("NAME"),areaArr); index++; }; //查询商户类型 int merchantArrIndex=0; String[] merchantArr=new String[merchantList.size()]; for (Object merchantType : merchantList) { Map merchant=(Map) merchantType; merchantArr[merchantArrIndex]=(String) merchant.get("NAME"); merchantArrIndex++; } //查询父级商户 int faMerchantIndex=0; String[] faMerchantArr=new String[faMerchantList.size()]; for (Object fmerchantI : faMerchantList) { Map merchantf=(Map) fmerchantI; faMerchantArr[faMerchantIndex]=(String) merchantf.get("NAME"); faMerchantIndex++; } // 创建Sheet String sheetName = name; Sheet sheet = workbook.createSheet(name); //创建sheet1 Sheet hideSheet =workbook.createSheet("area"); //是否隐藏sheet1 workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true); //生成数据 int rowId=0; Row provinceRow = hideSheet.createRow(rowId++); provinceRow.createCell(0).setCellValue("省列表"); for(int i = 0; i < provinceArr.length; i ++){ Cell provinceCell = provinceRow.createCell(i + 1); provinceCell.setCellValue(provinceArr[i]); } //将具体数据写入每一行中 for(int i = 0;i < areaFatherNameArr.size();i++){ String key = (String) areaFatherNameArr.get(i); String[] son = areaMap.get(key); Row row = hideSheet.createRow(rowId++); row.createCell(0).setCellValue(key); for(int j = 0; j < son.length; j ++){ Cell cell = row.createCell(j + 1); cell.setCellValue(son[j]); } //添加名称管理 String range = getRange(1,rowId,son.length); Name names =workbook.createName(); names.setNameName(key); String formula ="area!"+range; names.setRefersToFormula(formula); } // 商户类型 DVConstraint typeConstraint = DVConstraint.createExplicitListConstraint(merchantArr); CellRangeAddressList typeRangeAddressList = new CellRangeAddressList(1, 1000, 2, 2); DataValidation typeDataValidation = new HSSFDataValidation(typeRangeAddressList, typeConstraint); typeDataValidation.createErrorBox("error", "请选择正确的商户类型"); sheet.addValidationData(typeDataValidation); // 父级商户 DVConstraint faConstraint = DVConstraint.createExplicitListConstraint(faMerchantArr); CellRangeAddressList faRangeAddressList = new CellRangeAddressList(1, 1000, 3, 3); DataValidation faDataValidation = new HSSFDataValidation(faRangeAddressList, faConstraint); typeDataValidation.createErrorBox("error", "请选择正确的商户类型"); sheet.addValidationData(faDataValidation); // 省规则 DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(provinceArr); CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 1000, 4, 4); DataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint); provinceDataValidation.createErrorBox("error", "请选择正确的省份"); sheet.addValidationData(provinceDataValidation); // 市以规则，此处仅作一个示例 // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是 // 浙江省下的区域信息。 DVConstraint formula = DVConstraint.createFormulaListConstraint("INDIRECT($E:$E)"); CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 1000, 5, 5); DataValidation cacse = new HSSFDataValidation(rangeAddressList, formula); cacse.createErrorBox("error", "请选择正确的市"); sheet.addValidationData(cacse); // 区规则 formula = DVConstraint.createFormulaListConstraint("INDIRECT($F:$F)"); rangeAddressList = new CellRangeAddressList(1, 1000, 6, 6); cacse = new HSSFDataValidation(rangeAddressList, formula); cacse.createErrorBox("error", "请选择正确的区"); sheet.addValidationData(cacse); //设置格式 CellStyle cellStyle = workbook.createCellStyle(); //设置边框: cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //设置居中: cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 //设置字体: Font font2 = workbook.createFont(); font2.setFontName("仿宋_GB2312"); font2.setFontHeightInPoints((short) 11); cellStyle.setFont(font2);//选择需要用到的字体格式 //设置标题行格式 CellStyle cellStyleHeader = workbook.createCellStyle(); //设置背景色 cellStyleHeader.setFillForegroundColor(HSSFColor.LIME.index); cellStyleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置边框: cellStyleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //设置居中: cellStyleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 //设置字体: Font font = workbook.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 12); cellStyleHeader.setFont(font);//选择需要用到的字体格式 WritableCell cell; // 生成表头 Row headerRow = sheet.createRow(0); for (int i = 0; i < fieldLabelArray.length; i++) { sheet.setDefaultColumnStyle(i, cellStyle); sheet.setColumnWidth(i, 4000); headerRow.createCell(i).setCellValue(fieldLabelArray[i]); headerRow.getCell(i).setCellStyle(cellStyleHeader); } // 输出文件 workbook.write(os); os.close(); }catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
添加名称管理方法
private 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; } }
有更好的希望能给给我邮箱发一封，大家共同学习，共同进步，有部分内容网上找的，如有冒犯，请谅解，本人，菜鸟。
导入有时间再整理。
处理一些bug
1.超255错误
1 for (Object fmerchantI : faMerchantList) { 2 Map merchantf=(Map) fmerchantI; 3 faMerchantArr[faMerchantIndex]=(String) merchantf.get("NAME"); 4 faMerchantIndex++; 5 } 6 areaFatherNameArr.add("上级商户"); 7 areaMap.put("上级商户",faMerchantArr); 8 // 创建Sheet 9 String sheetName = name;
1 //添加名称管理 2 String range = getRange(1,rowId,son.length); 3 Name names =workbook.createName(); 4 names.setNameName(key); 5 String formula ="area!"+range; 6 names.setRefersToFormula(formula); 7 //避免超过255错误 8 if("上级商户".equals(key)){ 9 merChantName=formula; 10 }
1 // 父级商户 2 DVConstraint faConstraint = DVConstraint.createFormulaListConstraint(merChantName); 3 CellRangeAddressList faRangeAddressList = new CellRangeAddressList(1, 1000, 3, 3);
2.对市和区设置有效性，有时无效的处理，去掉原先的市区有效性，新加如下：
1 //对前1000行设置有效性 2 for(int i = 2;i < 1000;i++){ 3 setDataValidation("E" ,sheet,i,6); 4 setDataValidation("F" ,sheet,i,7); 5 } 6 //设置格式 7 CellStyle cellStyle = workbook.createCellStyle();
1 //设置有效性 2 /* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动 3 * @param sheet 4 * @param rowNum 行数 5 * @param colNum 列数 6 */ 7 public static void setDataValidation(String offset,HSSFSheet sheet, int rowNum,int colNum) { 8 HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); 9 DataValidation data_validation_list; 10 data_validation_list = getDataValidationByFormula( 11 "INDIRECT($" + offset +"$"+ (rowNum) + ")", rowNum, colNum,dvHelper); 12 sheet.addValidationData(data_validation_list); 13 } 14 15 /** 16 * 加载下拉列表内容 17 * @param formulaString 18 * @param naturalRowIndex 19 * @param naturalColumnIndex 20 * @param dvHelper 21 * @return 22 */ 23 private static DataValidation getDataValidationByFormula( 24 String formulaString, int naturalRowIndex, int naturalColumnIndex,HSSFDataValidationHelper dvHelper) { 25 // 加载下拉列表内容 26 // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据， 27 //如果A2是江苏省，那么此处就是江苏省下的市信息。 28 DVConstraint formula = DVConstraint.createFormulaListConstraint(formulaString); 29 // 设置数据有效性加载在哪个单元格上。 30 // 四个参数分别是：起始行、终止行、起始列、终止列 31 int firstRow = naturalRowIndex -1; 32 int lastRow = naturalRowIndex - 1; 33 int firstCol = naturalColumnIndex - 1; 34 int lastCol = naturalColumnIndex - 1; 35 CellRangeAddressList regions = new CellRangeAddressList(firstRow, 36 lastRow, firstCol, lastCol); 37 // 数据有效性对象 38 // 绑定 39 HSSFDataValidation data_validation_list = (HSSFDataValidation) dvHelper.createValidation(formula, regions); 40 data_validation_list.createErrorBox("error", "请选择正确的市或区"); 41 return data_validation_list; 42 }