iT邦幫忙

1

使用iReport+JasperReport產生PDF及Excel報表(2)

前言

在上篇文章中,已經用iReport製作好報表的template,這篇文章就要介紹在Servlet中使用JasperReport的jar檔產生報表。


import所需jar檔

首先我們需要為我們之前建立的JasperReportExample專案import需要用到的jar檔如下,我們可以去Maven的官網下載jar檔,下列的版本僅供各位參考,不同的java版本會要不同的jar版本:
bcprov-jdk15on-1.46.jar
commons-beanutils-1.9.3.jar
commons-collections-3.2.1.jar
commons-digester-2.1.jar
commons-logging-1.2.jar
iText-2.1.7.js2.jar
jasperreports-5.6.1.jar
kaiu.jar (在第一篇中用iReport產出的字型jar檔)
mysql-connector-java-5.1.38.jar
poi-3.10.1.jar


程式實作

Step 1.接著就是進入程式的部分,首先我們先製作前端的JSP頁面,我們需要有兩個DatePicker欄位讓使用者選擇要查詢的生日起迄日,還有一個報表類型的Select讓使用者可以選擇要產生PDF或是Excel的報表,我們就在專案的/JasperReportExample/WebContent/html/pages路徑下新增一個index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script src="<%=request.getContextPath()%>/html/js/jquery-3.3.1.min.js" type="text/javascript"></script>
<script src="<%=request.getContextPath()%>/html/js/jquery-ui.min.js" type="text/javascript"></script>
<link rel="stylesheet" href="<%=request.getContextPath()%>/html/css/jquery-ui.min.css">
<script>
	$( function() {
		$( "#startDate" ).datepicker({dateFormat : "yy-mm-dd"});
		$( "#endDate" ).datepicker({dateFormat : "yy-mm-dd"});
	} );
 </script>
<title>ReportDownload</title>
</head>
<body>
	<form method="Post" action="/JasperReportExample/ReportDownload">
		<p>起始時間:<input type="text" id="startDate" name="startDate" autocomplete="off"></p>
		<p>結束時間:<input type="text" id="endDate" name="endDate" autocomplete="off"></p>
		<p>報表類型:
			<select name="reportType">
				<option value="PDF">PDF</option>
				<option value="Excel">Excel</option>
			</select>
		</p>
		<input type="submit" value="下載">
	</form>
</body>
</html>

Step 2.接著我們要在路徑/JasperReportExample/src/com/example/controller為這個JSP新增一個Home.java的controller,讓request進來的時候可以導到我們的index.jsp去。

package com.example.controller;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/Home")
public class Home extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.getRequestDispatcher("/html/pages/index.jsp").forward(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

Step 3.完成後我們就可以先run在tomcat上,並在瀏覽器打上網址http://localhost:8080/JasperReportExample/Home ,就可以看到我們剛剛做的JSP畫面如下。
https://ithelp.ithome.com.tw/upload/images/20190327/20114725N3Xskcj4s4.png

Step 4.接著我們要在路徑/JasperReportExample/src/com/example/dao做一個PersonDao.java,讓我們可以去DB查詢Person的table,我們這邊用的DB是MySQL,並事先為這個表格塞入一些假資料。

package com.example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.example.model.Person;

public class PersonDao {
	
	public List<Person> getPersonList(String startDate, String endDate){
		Connection conn = null;
		PreparedStatement ps = null;
		List<Person> personList = new ArrayList<Person>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/new_schema", "yourDBAccount", "yourDBPassword");
			String sql =" select * from Person where (Birthday Between ? AND ?) order by Id";
			ps = conn.prepareStatement(sql);
            ps.setString(1, startDate);
            ps.setString(2, endDate);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Person person = new Person();
                person.setId(rs.getInt("Id"));
                person.setName(rs.getString("Name"));
                person.setAge(rs.getString("Age"));
                person.setPhone(rs.getString("Phone"));
                person.setEmail(rs.getString("Email"));
                person.setBirthday(rs.getString("Birthday").substring(0, 10));
                personList.add(person);
            }
		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				ps.close();
	            conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
        return personList;
	}
}

https://ithelp.ithome.com.tw/upload/images/20190327/20114725jSCMxQMwit.png

Step 5.最後在路徑/JasperReportExample/src/com/example/controller新增一個ReportDownload.java的controller,並在這邊接收前端JSP傳過來的參數,然後用剛剛做的dao去DB查資料,然後再製作報表。

package com.example.controller;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.example.dao.PersonDao;
import com.example.model.Person;

import net.sf.jasperreports.engine.JRAbstractExporter;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JREmptyDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.export.JRPdfExporter;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.util.JRLoader;

@WebServlet("/ReportDownload")
public class ReportDownload extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		// 前端傳進來的參數
		String startDate = request.getParameter("startDate");
		String endDate = request.getParameter("endDate");
		String reportType = request.getParameter("reportType");
		
		File file = null;
		JasperReport jasperReport = null;
		JRDataSource dataSource = null;
		JRAbstractExporter exporter = null;
		byte[] bytes = null;
		
		try {
            // 用iReport產出來的jasper檔案路徑
			String path = "/Users/greg/Desktop/JasperReportExample.jasper";
			file = new File(path);
			jasperReport = (JasperReport) JRLoader.loadObject(file);
		} catch(JRException e) {
			System.err.println(e);
		}
		
		// 資料庫查詢資料
		PersonDao dao = new PersonDao();
		List<Person> personList = dao.getPersonList(startDate, endDate+" 23:59:59");

		int size = personList.size();
		if(size > 0) {
			dataSource = new JRBeanCollectionDataSource(personList);
		}else {
			dataSource = new JREmptyDataSource();
		}
		
		Map parameters=new HashMap();
		parameters.put ("searchDate", startDate + "~" + endDate);
			
		if("PDF".equals(reportType)) {
			exporter = new JRPdfExporter();
		}else if ("Excel".equals(reportType)) {
			exporter = new JRXlsExporter();
		}
		
		try {
			ByteArrayOutputStream oStream = new ByteArrayOutputStream();
			JasperPrint jasperPrint = JasperFillManager.fillReport("/Users/greg/Desktop/JasperReportExample.jasper", parameters, dataSource);
			exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
			exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, oStream);
			exporter.exportReport();
			bytes = oStream.toByteArray();
			oStream.close();
			
		} catch (JRException e) {
			e.printStackTrace();
			System.err.println(e);
		}
		
        if("PDF".equals(reportType)) {
        	response.setHeader("Content-Disposition","attachment; filename=JasperReportExample.pdf");
            response.setContentType("application/pdf");
            response.setContentLength(bytes.length);
		}else if("Excel".equals(reportType)) {
			response.setHeader("Content-Disposition","attachment; filename=JasperReportExample.xls");
			response.setContentType("application/vnd.ms-excel");
			response.setContentLength(bytes.length);
		}
        
        ServletOutputStream ouputStream = response.getOutputStream();
		ouputStream.write(bytes, 0, bytes.length);
		ouputStream.flush();
		ouputStream.close();
        
	}

}

Step 6.到目前我們的程式部份都完成囉,專案的架構如下
https://ithelp.ithome.com.tw/upload/images/20190327/201147256RhzVMzyZV.png

Step 7.完成後我們就可以在剛剛的JSP網址選擇日期跟報表類型進行下載,因為這只是個小Demo,所以並沒有對前端的值進行檢查,各位有興趣的話可以自己在前端用JQuery或在後端新增檢查的邏輯。
https://ithelp.ithome.com.tw/upload/images/20190327/20114725Pl5yAeMYkI.png

產出來的PDF報表:
https://ithelp.ithome.com.tw/upload/images/20190327/20114725VnYVV1qNou.png

產出來的Excel報表:
https://ithelp.ithome.com.tw/upload/images/20190327/20114725kTskYVkgVl.png


結論

這個小專案到這邊就差不多完成囉,其實用iReport製作template還有很多功能可以操作,大家有興趣的話可以製作更複雜的template,製作屬於自己的客製化報表,若是對source code有興趣的話可以參考我的GitHub
對這篇主題有任何指教的部分也請各位在下方留言,互相交流~


尚未有邦友留言

立即登入留言