絕大多數 WEB 專案都會有報表開發的模組，對於客戶來說可以直觀瞭解各項資料的產生和使用情況，筆者最近接觸的小貸系統也有報表列印模組，也參與到了報表開發中，這也是我第一次接觸報表開發，這篇文章算是對完成之後的一番總結吧！
筆者接觸的報表開發使用的工具是 ireport，iReport 是一個能夠建立複雜報表的開源專案，使用純 Java 編寫，擁有非常豐富的圖形介面，並且它能呼叫 JasperReports 庫應用於任何一種 java 應用程式，JasperReports 支援 PDF、HTML、XLS、CSV 和 XML 檔案輸出格式，是開源報表工具中比較常用的。
這裡我就用一個 web projects 來演示如何列印一張報表，包括 PDF、excel 格式的
工具準備
- myeclipse
- 資料庫
- ireport5.6
新建 Web Project，目錄結構及所需要的jar包如下
具體可視情況而定，如果只要列印 pdf 可以去掉一些 jar 包的，這裡我因為excel、htlml、ttf 等各種格式都列印了，所以全部加上吧；另外版本一定要對應：我用的是ireport5.6,jasperreports-5.6.0.jar 、 jasper-compiler-jdt-5.5.15.jar 、iText-2.1.7.js2.jar 也要是高版本，否則會報錯，空指標之類的。
畫報表，儲存到 webroot/report/jrxml 路徑下
報表的操作很簡單，只要拖動、設定各種控制元件，就能迅速得到自己想要的報表，而通過資料的輸入可以得到各種圖表，這裡就不詳述 ireport 工具的操作使用了，我畫的報表介面如下：
設定報表編譯路徑，儲存到 webroot/report/jasper 路徑下
從頁面跳轉到 servlet
為了簡便，我就在 index.jsp 頁面寫了兩超連結，分別列印 PDF 和 EXCEL 格式的報表，如下：
<body>
<a href="TestReport?type=pdf">PDF匯出</a>
<br/>
<a href="TestReport?type=excel">excel匯出</a>
</body>
複製程式碼JDBC 資料庫連線(這裡就羅列關鍵程式碼)
連線資料庫
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/loans", "root", "root");
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
複製程式碼查詢資料庫表資料
public static List selectAll(String sql){
ResultSet rs = null;
Statement statement = null;
Connection conn = null;
List dataList = new ArrayList();
try {
conn = getConnection();
statement = conn.createStatement();
rs = statement.executeQuery(sql);
MakeLoanDetail makeLoanDetail = null;
while (rs.next()){
makeLoanDetail = new MakeLoanDetail();
//把值 set 到 javabean 物件
makeLoanDetail.setCustName(rs.getString(1));
makeLoanDetail.setCustIdNo(rs.getString(2));
makeLoanDetail.setBrdName(rs.getString(3));
makeLoanDetail.setApprAmt(rs.getDouble(4));
makeLoanDetail.setApprTerm(rs.getInt(5));
makeLoanDetail.setActvSysDt(rs.getDate(6));
makeLoanDetail.setActvUserId(rs.getString(7));
//最後把物件新增到 list 集合中去，dataList 將最終傳到報表中去
dataList.add(makeLoanDetail);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//這裡關閉 rs,statement,conn 資源
}
return dataList;
}
複製程式碼servlet 邏輯處理
public class TestReport extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Map parameterMap = new HashMap();
parameterMap.put("PRINT_DATE", new Date());//此處的鍵值對應報表 parameter 中的欄位
String reportMode = "";
String type = request.getParameter("type");
if (type.equals("pdf")) {
reportMode = "pdf";
}else if (type.equals("excel")) {
reportMode = "excel";
}
String reportName = "WhLoan_Detail_Report";//報表名字，列印出來的報表名
String reportId = "WhLoan_Detail_Report";//報表Id,對應於webroot/report/jrxml 你儲存的報表名
String sql = "SELECT CI.CUST_NAME,CI.cust_id_no,LB.BRD_NAME,APPR_AMT,APPR_TERM,ACTV_SYS_DT,ACTV_USER_ID"
+" FROM loan,cust_info CI,LOAN_BRD LB"//注意 FROM 前要有空格
+" WHERE loan.CUST_ID_CTRY = CI.CUST_ID_CTRY"//空格
+" AND LOAN.CUST_ID_NO = CI.CUST_ID_NO"//空格
+" AND LOAN.CUST_ID_TYPE = CI.CUST_ID_TYPE"
+" AND LOAN.LOAN_BRD = LB.BRD_ID";
try {
List dataList = SqlHelper.selectAll(sql);
ReportExporter.exportReport(request, response, reportId, reportMode, parameterMap, dataList, reportName);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
複製程式碼考慮到各大瀏覽器的相容性，最好將 reportName 用英文而不是中文
ReportExporter 類
ReportExporter 封裝了匯出 pdf,excel,html等全部格式的方法，直接呼叫即可
public class ReportExporter {
/**
* 獲取列印報表
*/
public static void exportReport(HttpServletRequest request, HttpServletResponse response, String reportId,
String exportMode, Map parameterMap, List dataList, String downloadFileName) throws Exception {
Connection connection = null;
try {
if (dataList == null) {
connection = SqlHelper.getConnection();
}
ServletContext servletContext = request.getSession().getServletContext();
File jasperFile = new File(servletContext.getRealPath("/report/jasper/" + reportId + ".jasper"));
if (!jasperFile.exists())
throw new IOException("Report file can't be found");
if (parameterMap == null)
parameterMap = new HashMap();
//ireport3.0用這個
// JasperReport jasperReport = (JasperReport)JRLoader.loadObject(jasperFile.getPath());
JasperReport jasperReport = (JasperReport) JRLoader.loadObject(jasperFile);
JasperPrint jasperPrint = null;
if (dataList == null) {
jasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, connection);
} else {
JRDataSource source = new JRBeanCollectionDataSource(dataList);
jasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, source);
}
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0)
downloadFileName = new String(downloadFileName.getBytes("UTF-8"), "ISO8859-1");// firefox瀏覽器
else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0)
downloadFileName = new String(downloadFileName.getBytes("gb2312"), "ISO8859-1");// IE瀏覽器
if (ReportExportMode.EXP_PDF_MODE.equalsIgnoreCase(exportMode)) {
exportPdf(response, jasperPrint, downloadFileName);
} else if (ReportExportMode.EXP_EXCEL_MODE.equalsIgnoreCase(exportMode)) {
exportExcel(response, jasperPrint, downloadFileName);
} else if ("word".equals(exportMode)) {
exportWord(response, jasperPrint, downloadFileName);
} else if ("rtf".equals(exportMode)) {
exportRTF(response, jasperPrint, downloadFileName);
} else if ("html".equals(exportMode)) {
exportHtml(response, jasperPrint, downloadFileName);
}
} finally {
if (dataList == null && connection != null)
try {
connection.close();
} catch (SQLException e) {
}
}
}
/**
* pdf匯出
*/
private static void exportPdf(HttpServletResponse response, JasperPrint jasperPrint, String downloadFileName)
throws JRException, IOException {
ServletOutputStream ouputStream = response.getOutputStream();
try {
JRPdfExporter exporter = new JRPdfExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, ouputStream);
response.setContentType("application/pdf;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFileName + ".pdf");
exporter.exportReport();
ouputStream.flush();
} finally {
try {
ouputStream.close();
} catch (Exception e) {
}
}
}
/**
* excel匯出
*/
private static void exportExcel(HttpServletResponse response, JasperPrint jasperPrint, String downloadFileName)
throws JRException, IOException {
ServletOutputStream ouputStream = response.getOutputStream();
try {
JRXlsExporter exporter = new JRXlsExporter();
exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, ouputStream);
response.setContentType("application/vnd_ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFileName + ".xls");
exporter.exportReport();
ouputStream.flush();
} finally {
try {
ouputStream.close();
} catch (Exception e) {
}
}
}
/**
* 匯出word
*/
private static void exportWord(HttpServletResponse response, JasperPrint jasperPrint, String downloadFileName)
throws JRException, IOException {
ServletOutputStream ouputStream = response.getOutputStream();
try {
JRExporter exporter = new JRRtfExporter();
exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, ouputStream);
response.setContentType("application/msword;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFileName + ".doc");
exporter.exportReport();
ouputStream.flush();
} finally {
try {
ouputStream.close();
} catch (Exception e) {
}
}
}
/**
* 匯出RTF
*/
private static void exportRTF(HttpServletResponse response, JasperPrint jasperPrint, String downloadFileName)
throws JRException, IOException {
ServletOutputStream ouputStream = response.getOutputStream();
try {
JRExporter exporter = new JRRtfExporter();
exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, ouputStream);
response.setContentType("application/rtf;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + downloadFileName + ".rtf");
exporter.exportReport();
ouputStream.flush();
} finally {
try {
ouputStream.close();
} catch (Exception e) {
}
}
}
/**
* 匯出html
*/
private static void exportHtml(HttpServletResponse response, JasperPrint jasperPrint, String downloadFileName)
throws JRException, IOException {
ServletOutputStream ouputStream = response.getOutputStream();
try {
JRHtmlExporter exporter = new JRHtmlExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, ouputStream);
exporter.setParameter(JRExporterParameter.CHARACTER_ENCODING, "UTF-8");
exporter.setParameter(JRHtmlExporterParameter.IS_USING_IMAGES_TO_ALIGN, Boolean.FALSE);
response.setContentType("text/html;charset=utf-8");
exporter.exportReport();
ouputStream.flush();
} finally {
try {
ouputStream.close();
} catch (Exception e) {
}
}
}
}
複製程式碼ReportExportMode 類
public class ReportExportMode {
public static String EXP_PDF_MODE="PDF";
public static String EXP_EXCEL_MODE="EXCEL";
public static boolean isPDF(String mode){
return EXP_PDF_MODE.equals(mode);
}
public static boolean isEXCEL(String mode){
return EXP_EXCEL_MODE.equals(mode);
}
}
複製程式碼執行程式
點選 PDF 匯出 和 EXCEL 匯出
pdf 匯出
excel匯出
