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是最成熟 还在更新与维护,文档也很多。更多资料就自己百度了。