望有大神指点迷津。
JSP代码如下：
<%@page import="net.sf.jasperreports.engine.xml.JRDatasetFactory"%>
<%@page import="net.sf.jasperreports.engine.util.JRDataUtils"%>
<%@page import="net.sf.jasperreports.engine.query.JRJdbcQueryExecuterFactory"%>
<%@page import="net.sf.jasperreports.engine.query.JRJdbcQueryExecuter"%>
<%@page import="net.sf.jasperreports.engine.data.JRBeanCollectionDataSource"%>
<%@page import="net.sf.jasperreports.engine.util.JRLoader"%>
<%@page import="com.rc.web.utils.JasperUtils"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="javax.jms.Session"%>
<%@page import="org.drools.lang.dsl.DSLMapParser.mapping_file_return"%>
<%@page import="com.qam.framework.context.ContextService"%>
<%@ page isELIgnored="false" language="java" contentType="text/html; charset=UTF-8"%>
<%@ page import="net.sf.jasperreports.engine.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.sql.*"%>
<html:dialog id="GNDH_DanWeiYSZXZB" />
<%
String danweimc = request.getParameter("dw");//取预算单位名称
String yusuansxfl = request.getParameter("xmfl");//取预算事项分类
String jiezhirq = request.getParameter("jiezhirq");//取截止日期
//报表编译之后生成的.jasper文件的存放位置
Map<String,Object> parameters = new HashMap<String,Object>();
parameters.put("danweimc",danweimc);
parameters.put("yusuansxfl",yusuansxfl);
parameters.put("jiezhirq",jiezhirq);
Connection conn = ContextService.lookupDefaultDataBaseAccess().getConnection();
try{
JasperRunManager.runReportToPdfFile(config.getServletContext().getRealPath("/DanWeiNBYSZXZB.jasper"),parameters,conn);
JasperPrint jasperPrint = JasperFillManager.fillReport(config.getServletContext().getRealPath("/DanWeiNBYSZXZB.jasper"),parameters,conn);
File file = new File(config.getServletContext().getRealPath("/DanWeiNBYSZXZB.jasper"));
InputStream is = config.getServletContext().getResourceAsStream("/DanWeiNBYSZXZB.jasper");
String sql = " SELECT T2.*, "
+ " (SELECT SUM (TOTAL) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " TOTALHZ, "
+ " (SELECT SUM (NIANCHUPIFU) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " NIANCHUPIFUHZ, "
+ " (SELECT SUM (ZHUIJIATIAOZHENG) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " ZHUIJIATIAOZHENGHZ, "
+ " (SELECT SUM (ZAITUZHICHU) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " ZAITUZHICHUHZ, "
+ " (SELECT SUM (ZHICHU) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " ZHICHUHZ, "
+ " (SELECT SUM (ZHICHULV) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " ZHICHULVHZ, "
+ " (SELECT SUM (WEIZHICHU) "
+ " FROM VIEW_DANWEIYSSXZB T3 "
+ " CONNECT BY PRIOR T3.BIANHAO = T3.SHANGJIBH "
+ " START WITH T3.BianHao = T2.BIANHAO) "
+ " WEIZHICHUHZ, "
+ " CASE "
+ " WHEN FUNC_ISLEAFNODE ( "
+ " '(SELECT * FROM VIEW_DANWEIYSSXZB WHERE JieShouZZ = ''GLZZ201209060003'' AND NIANDU = 2012)', "
+ " 'ShangJiBH', "
+ " BianHao "
+ " ) > 0 "
+ " THEN "
+ " 'false' "
+ " ELSE "
+ " 'true' "
+ " END "
+ " ISLEAF "
+ " FROM (VIEW_DANWEIYSSXZB) T2 "
+ " CONNECT BY PRIOR T2.BIANHAO = T2.SHANGJIBH "
+ " START WITH T2.SHANGJIBH IS NULL "
+ " ORDER SIBLINGS BY T2.BIANHAO ";
Statement prep = conn.createStatement();
ResultSet rs = prep.executeQuery(sql);
System.out.print(">>>>>>>>>>>>>>" + rs.next());
JRResultSetDataSource ds = new JRResultSetDataSource(rs);
JasperUtils.export(is,"excel",null,ds,response,request);
}catch(Exception e){
System.out.println(e);
}finally{
conn.close();
}
double randomNum = Math.random();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String today = sdf.format(new java.util.Date());
%>
JAVA工具类代码:
/**
* 修改Excel模板样式
*/
public static void prepareReport(JasperReport jasperReport) {
try {
Class myClass = Class
.forName("net.sf.jasperreports.engine.base.JRBaseReport");
Field margin = myClass.getDeclaredField("leftMargin");
margin.setAccessible(true);
margin.setInt(jasperReport, 0);
margin = myClass.getDeclaredField("topMargin");
margin.setAccessible(true);
margin.setInt(jasperReport, 0);
margin = myClass.getDeclaredField("bottomMargin");
margin.setAccessible(true);
margin.setInt(jasperReport, 0);
Field pageHeight = myClass.getDeclaredField("pageHeight");
pageHeight.setAccessible(true);
pageHeight.setInt(jasperReport, Integer.MAX_VALUE);
} catch (Exception exception) {
}
}
/**
* 导出excel
*/
public static void exportExcel(JasperPrint jasperPrint,
HttpServletResponse response, HttpServletRequest request)
throws IOException {
response.setContentType("application/vnd.ms-excel");
String fileName = new String("未命名.xls".getBytes("gbk"), "ISO8859_1");
response.setHeader("Content-disposition", "attachment; filename="
+ fileName);
response.setCharacterEncoding("utf-8");
ServletOutputStream ouputStream = response.getOutputStream();
JRXlsExporter exporter = new JRXlsExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, ouputStream);
exporter.setParameter(
JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS,
Boolean.TRUE);
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,
Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND,
Boolean.FALSE);
try {
exporter.exportReport();
} catch (JRException e) {
e.printStackTrace();
}
ouputStream.flush();
ouputStream.close();
}
/**
* 导出pdf
*/
public static void exportPDF(JasperPrint jasperPrint,
HttpServletResponse response, HttpServletRequest request)
throws IOException {
response.setContentType("application/pdf");
String filename = new String("未命名.pdf".getBytes("GBK"), "ISO8859_1");
response.setHeader("Content-Disposition", "attachment;filename=/"
+ filename + "");
ServletOutputStream ouputStream = response.getOutputStream();
try {
JasperExportManager.exportReportToPdfStream(jasperPrint,
ouputStream);
} catch (JRException e) {
e.printStackTrace();
}
ouputStream.flush();
ouputStream.close();
}
/**
* 按照类型导出不同格式文件
*
* @param datas
* 数据
* @param type
* 文件类型
* @param is
* jasper文件的来源
* @param request
* @param response
*/
public static void export(InputStream is, String type, Map parameters,
JRDataSource ds, HttpServletResponse response,
HttpServletRequest request) throws IOException {
JasperReport jasperReport = null;
JasperPrint jasperPrint = null;
try {
jasperReport = (JasperReport) JRLoader.loadObject(is);
jasperPrint = JasperFillManager.fillReport(jasperReport,
parameters, ds);
} catch (JRException e) {
e.printStackTrace();
}
if (EXCEL_TYPE.equals(type)) {
exportExcel(jasperPrint, response, request);
prepareReport(jasperReport);
} else if (PDF_TYPE.equals(type)) {
exportPDF(jasperPrint, response, request);
} else if (HTML_TYPE.equals(type)) {
exportHTML(jasperPrint, response, request);
} else if (WORD_TYPE.equals(type)) {
try {
exportWord(jasperPrint, response, request);
} catch (JRException e) {
e.printStackTrace();
}
} else if (PRINT_TYPE.equals(type)) {
exportPrint(jasperPrint, response, request);
}
}
1 个解决方案
#1
楼主要不然换一款吧，我现在用的这款叫finereport，导出的格式就很多，它导出excel后还可以再导入excel，这样就很方便了。而且我要做的报表基本都能用它做出来，效果很好。
#1
楼主要不然换一款吧，我现在用的这款叫finereport，导出的格式就很多，它导出excel后还可以再导入excel，这样就很方便了。而且我要做的报表基本都能用它做出来，效果很好。