jxl操作复杂excel表格

上一篇，我写了jxl的理论的东西比较多， 是为了让我自己学习，更好让我去完成项目中的一個模块， 就是在ext的gridpanel显示出的数据显示出來， 因为导出的数据是两个gridpanel的数据所以这就算比较复杂的导出了， 单单使用ext自带的excel导出，就难以做到啦。

简单的说明之后， 我还是把代码贴吧。一整个controller方法

	@SuppressWarnings("unchecked")
	@RequestMapping("/pages/deliveryPoint/deliveryPointSearch/exportExcel.page")
    public String exportExcel(HttpServletRequest request, HttpServletResponse response){
		 Map condition = new HashMap();

		 String regieOrgCode = request.getParameter("regieOrgCode_export");
		 String regieDeptCode = request.getParameter("regieDeptCode_export");

		 condition.put("custName", request.getParameter("custName_export"));
		 condition.put("regieOrgCode", regieOrgCode);
		 condition.put("regieDeptCode", regieDeptCode);
		 condition.put("grantDate1", request.getParameter("grantDate1_export"));
		 condition.put("grantDate2", request.getParameter("grantDate2_export"));


		 String regieOrgName = "       烟草专卖局"; 
		 if(StringUtils.isNotBlank(regieOrgCode)){
			 RmRegieOrg   obj = rmRegieOrgService.getRmRegieOrg(regieOrgCode);
			 regieOrgName = obj.getRegieOrgName();
		 }

		 String regieDeptName = "       专管所"; 
		 if(StringUtils.isNotBlank(regieDeptCode)){
			 RmRegieDept   obj = rmRegieDeptService.getRmRegieDept(regieDeptCode);
			 regieDeptName = obj.getRegieDeptName();
		 }


		 SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 

         List<Map> custMap = rmDeliveryPointApplyDetailService.findCustMapForExport(condition);


		 response.setHeader("Content-disposition", "attachment;filename=text.xls");
		 response.setContentType("application/msexcel");
		  try {
		   OutputStream os = response.getOutputStream();
		   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);

		   jxl.write.WritableSheet ws = wwb.createSheet("定点取货点寄货户实时更新汇总表", 0);

		   WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 15,WritableFont.BOLD);
		   WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		   titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
		   titleFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		   titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);

		   WritableFont titleFont1 = new WritableFont(WritableFont.createFont("宋体"), 11,WritableFont.NO_BOLD);
		   WritableCellFormat titltFormat1 = new WritableCellFormat(titleFont1);
		   titltFormat1.setAlignment(jxl.format.Alignment.LEFT);
		   titltFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		   titltFormat1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);

		   WritableFont cellFont = new WritableFont(WritableFont.createFont("宋体"), 9,WritableFont.NO_BOLD);
		   WritableCellFormat cellFormat = new WritableCellFormat(cellFont);
		   cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
		   cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		   cellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		   cellFormat.setWrap(true);

		   WritableFont headFont = new WritableFont(WritableFont.createFont("宋体"), 9,WritableFont.BOLD);
		   WritableCellFormat headFormat = new WritableCellFormat(headFont);
		   headFormat.setAlignment(jxl.format.Alignment.CENTRE);
		   headFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		   headFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		   headFormat.setWrap(true);




		   int rowIndex = 0 ; //Excel 行索引
		   jxl.write.Label labelC = new jxl.write.Label(0, 0, "定点取货点寄货户实时更新汇总表",titleFormat);
		   ws.addCell(labelC);
		   ws.mergeCells(0, rowIndex, 8, rowIndex);
		   ws.setRowView(rowIndex, 800);

		    rowIndex = rowIndex + 1; // 下一行
			labelC = new jxl.write.Label(0, rowIndex, regieOrgName+"  "+regieDeptName, titltFormat1);
			ws.addCell(labelC);
			ws.mergeCells(0, rowIndex, 8, rowIndex);
			ws.setRowView(rowIndex, 600);


		   for(int i=0;i<custMap.size();i++){

			   rowIndex = rowIndex + 1; // 下一行
				labelC = new jxl.write.Label(0, rowIndex, "取货点姓名", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(1, rowIndex, "许可证号码", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(2, rowIndex, "经营地址", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(3, rowIndex, "序号", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(4, rowIndex, "寄货户姓名", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(5, rowIndex, "许可证号码", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(6, rowIndex, "经营地址", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(7, rowIndex, "设立时间", headFormat);
				ws.addCell(labelC);

				labelC = new jxl.write.Label(8, rowIndex, "取消时间", headFormat);
				ws.addCell(labelC);

				ws.setColumnView(0, 12);
				ws.setColumnView(1, 8);
				ws.setColumnView(2, 12);
				ws.setColumnView(3, 3);
				ws.setColumnView(4, 12);
				ws.setColumnView(5, 8);
				ws.setColumnView(6, 12);
				ws.setColumnView(7, 10);
				ws.setColumnView(8, 10);

				ws.setRowView(rowIndex, 800);
				rowIndex = rowIndex + 1; // 下一行

	        	 Map map = custMap.get(i);
	        	 String custName = map.get("custName").toString();
	        	 String custLicenceCode= map.get("custLicenceCode").toString();
	        	 String custAddress= map.get("custAddress").toString();

	        	 labelC = new jxl.write.Label(0, rowIndex, custName, cellFormat);
	 			 ws.addCell(labelC);

	 			 labelC = new jxl.write.Label(1, rowIndex, custLicenceCode, cellFormat);
	 			 ws.addCell(labelC);

	 			 labelC = new jxl.write.Label(2, rowIndex, custAddress, cellFormat);
	 			 ws.addCell(labelC);

	        	 String custCode = map.containsKey("custCode")? map.get("custCode").toString():"";
	        	 List<Map> list = deliveryPointSearchService.getDeliveryPointAnchoredCustList(custCode);

	        	 for(int j=0;j<list.size();j++){

	        		 Map mapTemp = list.get(j);
	        		 labelC = new jxl.write.Label(3, rowIndex+j, String.valueOf(j+1), cellFormat);
		 			 ws.addCell(labelC);

		 			 String anchoredCustName = mapTemp.get("anchoredCustName").toString();
		 			 String anchoredLicenceCode = mapTemp.get("anchoredLicenceCode").toString();
		 			 String anchoredCustAddress = mapTemp.get("anchoredCustAddress").toString();
		 			 Date setTime = (Date)mapTemp.get("setTime");

		 			String setTimeString = "";
					if (setTime != null) {
						setTimeString = df.format(setTime);
					}
		 			 Date cancelTime = mapTemp.containsKey("cancelTime")?(Date)mapTemp.get("cancelTime"):null;
		 			String cancelTimeString = "";
					if (cancelTime != null) {
						cancelTimeString = df.format(cancelTime);
					}
		 			 labelC = new jxl.write.Label(4, rowIndex+j, anchoredCustName, cellFormat);
		 			 ws.addCell(labelC);

		 			labelC = new jxl.write.Label(5, rowIndex+j, anchoredLicenceCode, cellFormat);
		 			 ws.addCell(labelC);

		 			labelC = new jxl.write.Label(6, rowIndex+j, anchoredCustAddress, cellFormat);
		 			 ws.addCell(labelC);

		 			labelC = new jxl.write.Label(7, rowIndex+j, setTimeString, cellFormat);
		 			 ws.addCell(labelC);

		 			labelC = new jxl.write.Label(8, rowIndex+j, cancelTimeString, cellFormat);
		 			 ws.addCell(labelC);

		 			 ws.setRowView(rowIndex+j, 800);
	        	 }

	        	 if(list.size()>0){
	        		 int temp = list.size()-1;
		        	 ws.mergeCells(0, rowIndex, 0, rowIndex+temp);
		        	 ws.mergeCells(1, rowIndex, 1, rowIndex+temp);
		        	 ws.mergeCells(2, rowIndex, 2, rowIndex+temp);
		        	 rowIndex = rowIndex+temp;
	        	 }

	        }

		    wwb.write();
			 //关闭Excel工作薄对象
			wwb.close();
			os.close();

		  }catch (WriteException e) {
		   e.printStackTrace();
		  } catch (IOException e) {
		   e.printStackTrace();
		  }
	     return null;
	}

效果图：
[img]http://dl.iteye.com/upload/attachment/353111/7dd1f414-9bae-343f-9a7f-c191f800a2b1.jpg[/img]