jxls的excel报表效果 ,遇到什么问题给我流言吧,我做过这方面
还是来点代码吧 ,是关键代码
HttpServletRequest request = getRequest();HttpServletResponse response = getResponse();SimpleDateFormat tempDate = new SimpleDateFormat("yyyyMMddhhmmssSSS");String datetime = tempDate.format(new java.util.Date());response.setContentType("application/x-msdownload");response.setHeader("content-disposition", "attachment; filename=" + datetime + ".xls");List<EntrustBatchs> orglist = new ArrayList<EntrustBatchs>();Map<String, Object> map = new HashMap<String, Object>();SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");map.put("year", year);map.put("month", month);map.put("bankname", orgName);map.put("tjdate", sdf.format(System.currentTimeMillis()));map.put("result1", orglist);try {String path = (getClass().getClassLoader().getResource("").toURI()).getPath();FileInputStream is = new FileInputStream(path +"template"+File.separator+"BatchEntrustReportTemplete.xls");
// InputStream is = new FileInputStream("d:/test/cc.xls");// 关联模板XLSTransformer transformer = new XLSTransformer();HSSFWorkbook workBook = (HSSFWorkbook) transformer.transformXLS(is, map);HSSFSheet sheet = workBook.getSheetAt(0);// 合并单元格int fork = 3;int li = orglist.size(), lj = 0, lk = 0;int b = fork;b = fork;// 合并第二列b = fork;for (int i = 0; i < li; i++) {lj = orglist.get(i).getEntrustBatchTimes().size();for (int j = 0; j < lj; j++) {lk = orglist.get(i).getEntrustBatchTimes().get(j).getEntrustReport().size();int e = lk + b;sheet.addMergedRegion(new CellRangeAddress(b, e - 1, 1, 1));sheet.addMergedRegion(new CellRangeAddress(b, e - 1, 2, 2));b = e;b = b + 1;// 每行外部新增一行在此添加}b = b+orglist.get(i).getEntrustBatchDate().getEntrustReport().size()+1;// 每个分组外新增在此添加}// 合并第一列b = fork;for (int i = 0; i < li; i++) {lj = orglist.get(i).getEntrustBatchTimes().size();int lkh = 0;for (int m = 0; m < lj; m++) {lkh = lkh+ orglist.get(i).getEntrustBatchTimes().get(m).getEntrustReport().size()+1;}int e = lkh + b;sheet.addMergedRegion(new CellRangeAddress(b, e - 1, 0, 0));b = e;e = e + orglist.get(i).getEntrustBatchDate().getEntrustReport().size()+1;sheet.addMergedRegion(new CellRangeAddress(b, e - 1, 0, 0));b = e;}OutputStream os = response.getOutputStream();// OutputStream os = new FileOutputStream("d:/test/cc_report.xls");workBook.write(os);
xls模板 :因为这里不能上传文件,我就直接粘贴代码和图片了
项目 委托日期 手别 姓名 工号 委案情况 撤案减数 回款情况 回款率 组内排名金额 个数 金额 个数 金额 个数
<jx:forEach items="${result1}" var="b">
<jx:forEach items="${b.entrustBatchTimes}" var="b1">
<jx:forEach items="${b1.entrustReport}" var="b3">
批次明细 ${b3.commisnDate} ${b3.timesStr} ${b3.fullname} ${b3.username} ${b3.commisnmoneySum} ${b3.commisnCount} ${b3.caCommisnMoneySum} ${b3.caCommisnCount} ${b3.hkCommisnMoneySum} ${b3.hkCommisnCount} $[J7/(F7-H7)] ${b3.znSort}
</jx:forEach> 在银行内排名(委外机构排名) 1/4 合计 $[SUM(F7)] $[SUM(G7)] $[SUM(H7)] $[SUM(I7)] $[SUM(J7)] $[SUM(K7)] $[J9/(F9-H9)]
</jx:forEach>
<jx:forEach items="${b.entrustBatchDate.entrustReport}" var="b4">
批次合计 ${b.entrustBatchDate.commisnDate} ${b.entrustBatchDate.timesSetStr} ${b4.fullname} ${b4.username} ${b4.commisnmoneySum} ${b4.commisnCount} ${b4.caCommisnMoneySum} ${b4.caCommisnCount} ${b4.hkCommisnMoneySum} ${b4.hkCommisnCount} $[J12/(F12-H12)]
</jx:forEach> 在银行内排名 1/4 合计 $[SUM(F12)] $[SUM(G12)] $[SUM(G12)] $[SUM(I12)] $[SUM(J12)] $[SUM(K12)] $[J14/(F14-H14)]
这是模板参考
下面是生成效果:
对于生成excel的财务报表特别好,jxls已经出2.0了比之前更好更快了。 很多报表工具生成pdf,xls很多类型但是都不强大不好用,什么report之类的 。经过筛选 jxls是最成熟 还在更新与维护,文档也很多。更多资料就自己百度了。