POI 汇总、总结帖
最近工作需求使用POI，使用场景如下：
1、查询数据库获取数据
2、按照客户要求格式导出成excel
?
碰到的几个问题：
1、POI的基本操作
2、客户的有格式要求，还比较复杂
?
问题一参见：
POI操作Excel文档－基础篇?http://llyzq.iteye.com/admin/blogs/1354542
POI操作Excel文档－中级篇?http://llyzq.iteye.com/admin/blogs/1354545
Apache POI ?http://llyzq.iteye.com/admin/blogs/729521
?
问题二的解决思路：
1、将客户提供的excel作为样式模板
2、读取该excel创建HSSFWorkbook对象
3、填充数据
4、对于需要纵向扩充的表格数据，使用sheet.shiftRows方法实现，同时复杂样式和合并单元格
?
/** * 复制一行的单元格样式 */ private void copyCellStyleOfRow(HSSFSheet sheet,HSSFRow fromRow,HSSFRow toRow,int count){ for (int i = 0; i < count; i++) { if(toRow.getCell((short)i) != null) toRow.getCell((short)i).setCellStyle(fromRow.getCell((short)i).getCellStyle()); else{ toRow.createCell((short)i).setCellStyle(fromRow.getCell((short)i).getCellStyle()); } for (int j = 0; j < sheet.getNumMergedRegions(); j++) { Region r = sheet.getMergedRegionAt(j); //判断是否包含在合并表格中 if (r.contains(fromRow.getRowNum(), (short)i)) { sheet.addMergedRegion(new Region(toRow.getRowNum(), r.getColumnFrom(), toRow.getRowNum(), r.getColumnTo())); } }} }?
其他主要代码：
?
protected ActionForward doPrev(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { ITenpayQuetyService tenpayQueryServiceImpl=(ITenpayQuetyService)ApplicationContextUtil.getInstance().getBean("tenpayQueryServiceImpl"); try { String begDate = request.getParameter("begDate").replaceAll("-", ""); String endDate = request.getParameter("endDate").replaceAll("-", ""); Map params = new HashMap(); params.put("begDate", begDate); params.put("endDate", endDate); params.put("broke", "TENPAY"); //读取excel模板 String urlpath = request.getRealPath(""); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(urlpath + "\\template\" + HsDictionary.getInstance().getSysParameter("SALE", "SYSTEMPLATE")+"\\custom\\tenpaydemo.xls")); HSSFSheet s = wb.getSheetAt(0); DecimalFormat format = new DecimalFormat("###,##0.00"); //--------------汇总报表 //统计周期 String tjzq = request.getParameter("begDate").replaceAll("-", ".")+" -- " +request.getParameter("endDate").replaceAll("-", "."); int hs = 0;//户数 int cggmkhs = 0;//成功购买客户数 double zb = 100;//占比 int csgmkhs = 0;//尝试购买客户数 int cssgbs = 0;//尝试申购笔数 int cgsgbs = 0;//成功申购笔数 double cgsgje = 0;//成功申购金额 int csrgbs = 0;//尝试认购笔数 int cgrgbs = 0;//成功认购笔数 double cgrgje = 0;//成功认购金额 int dtcskkbs = 0;//定投尝试扣款笔数 int dtcgkkbs = 0;//定投成功扣款笔数 double dtcgkkje = 0;//定投成功扣款金额 HsRowSet rowSet = tenpayQueryServiceImpl.getTenpayNewOpenSuccessTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0) cggmkhs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); rowSet = tenpayQueryServiceImpl.getTenpaySuccessOpenStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0) hs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); rowSet = tenpayQueryServiceImpl.getTenpayNewOpenTryTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0) csgmkhs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); if(hs != 0) zb = cggmkhs/hs; //申购 params.put("businflag", "022"); rowSet = tenpayQueryServiceImpl.getTenpayTryTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0) cssgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); rowSet = tenpayQueryServiceImpl.getTenpaySuccessTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0){ cgsgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); cgsgje = Double.valueOf(((String)rowSet.getInterfaceProperty(0,"confirmbala"))).doubleValue(); } //认购 params.put("businflag", "020"); rowSet = tenpayQueryServiceImpl.getTenpayTryTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0) csrgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); rowSet = tenpayQueryServiceImpl.getTenpaySuccessTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0){ cgrgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); cgrgje = Double.valueOf(((String)rowSet.getInterfaceProperty(0,"confirmbala"))).doubleValue(); } //定投 params.put("businflag", "039"); rowSet = tenpayQueryServiceImpl.getTenpayTryTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0) dtcskkbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); rowSet = tenpayQueryServiceImpl.getTenpaySuccessTradeStatic(this.getPkgId(request), params); if(rowSet.getReturnRecords() > 0){ dtcgkkbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue(); dtcgkkje = Double.valueOf(((String)rowSet.getInterfaceProperty(0,"confirmbala"))).doubleValue(); } //填充excel HSSFRow row = s.getRow(6); row.getCell((short)0).setCellValue(new HSSFRichTextString(tjzq)); row.getCell((short)1).setCellValue(new HSSFRichTextString(String.valueOf(hs))); row.getCell((short)2).setCellValue(new HSSFRichTextString(String.valueOf(cggmkhs))); row.getCell((short)3).setCellValue(new HSSFRichTextString(String.valueOf(zb)+"%")); row.getCell((short)4).setCellValue(new HSSFRichTextString(String.valueOf(csgmkhs))); row.getCell((short)5).setCellValue(new HSSFRichTextString(String.valueOf(cssgbs))); row.getCell((short)6).setCellValue(new HSSFRichTextString(String.valueOf(cgsgbs))); row.getCell((short)7).setCellValue(new HSSFRichTextString(format.format(cgsgje))); row.getCell((short)8).setCellValue(new HSSFRichTextString(String.valueOf(csrgbs))); row.getCell((short)9).setCellValue(new HSSFRichTextString(String.valueOf(cgrgbs))); row.getCell((short)10).setCellValue(new HSSFRichTextString(format.format(cgrgje))); row.getCell((short)11).setCellValue(new HSSFRichTextString(String.valueOf(dtcskkbs))); row.getCell((short)12).setCellValue(new HSSFRichTextString(String.valueOf(dtcgkkbs))); row.getCell((short)13).setCellValue(new HSSFRichTextString(format.format(dtcgkkje))); //------------新开户报表--------------- HsRowSet newOpenList = tenpayQueryServiceImpl.getTenpayNewOpenList(this.getPkgId(request),params); request.setAttribute("begDate",begDate); request.setAttribute("endDate",endDate); request.setAttribute("newOpenList",newOpenList); //填充excel row = s.getRow(10); for (int i = 0; i < newOpenList.getReturnRecords(); i++) { row.getCell((short)0).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"date"))); row.getCell((short)1).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"partneraccoid"))); row.getCell((short)5).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"time"))); row.getCell((short)6).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"type"))); if(i != newOpenList.getReturnRecords()-1){ s.shiftRows(row.getRowNum()+1, row.getRowNum()+8, 1); this.copyCellStyleOfRow(s,row, s.getRow(row.getRowNum()+1), 7); row=s.getRow(row.getRowNum()+1); } } row=s.getRow(row.getRowNum()+1); row.getCell((short)0).setCellValue(new HSSFRichTextString("共"+newOpenList.getReturnRecords()+"条记录")); //---------------交易详细报表------------------------ HsRowSet dealDetailList = tenpayQueryServiceImpl.getTenpayDealDetailList(this.getPkgId(request),params); //填充excel row=s.getRow(row.getRowNum()+4); for (int i = 0; i < dealDetailList.getReturnRecords(); i++) { row.getCell((short)0).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"partneraccoid"))); row.getCell((short)1).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"date"))); row.getCell((short)3).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"time"))); row.getCell((short)5).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"fundcode"))); row.getCell((short)7).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"fundtype"))); row.getCell((short)8).setCellValue(new HSSFRichTextString(HsDictionary.getInstance().getCaption("业务名称", dealDetailList.getInterfaceProperty(i,"businflag")))); row.getCell((short)9).setCellValue(new HSSFRichTextString(format.format(Double.valueOf(dealDetailList.getInterfaceProperty(i,"confirmbala"))))); row.getCell((short)10).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"status"))); if(i != dealDetailList.getReturnRecords() -1){ s.shiftRows(row.getRowNum()+1, row.getRowNum()+2, 1); this.copyCellStyleOfRow(s,row, s.getRow(row.getRowNum()+1), 11); row=s.getRow(row.getRowNum()+1); } } row=s.getRow(row.getRowNum()+1); row.getCell((short)0).setCellValue(new HSSFRichTextString("共"+dealDetailList.getReturnRecords()+"条记录")); //-----------生成报表excel文件------------- ByteArrayOutputStream fos = new ByteArrayOutputStream(); wb.write(fos); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "inline; filename=income_"" + begDate + "-" + endDate +".xls ""); //确保IE识别本次为下载文件，解决https IE下载无法保存的问题 response.setHeader("Content-Transfer-Encoding","binary"); response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); response.setHeader("Pragma", "public"); ByteArrayInputStream inputStream = new ByteArrayInputStream(fos.toByteArray()); PrintWriter out = response.getWriter(); int i; while ( (i = inputStream.read()) != -1) { out.write(i); } out.flush(); inputStream.close(); out.close(); fos.close(); } catch (Exception ex) { SysLogUtils.error("下载对账数据发生错误：", ex); throw new BusinessException("ETS-1BT27","下载对账数据发生错误!"); } return null; }