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;}