CSV Export of Jasper Report using Java

There is an inbuilt option of exporting reports to various forms in Jasper Server. But, sometimes when the report is huge, taking CSV export might not be possible. In such cases, export can be taken easily by writing a small piece of Java code.This blog explains how CSV export can be taken by clicking on a Text Field.

Prerequisites: Jaspersoft Studio 6.4.2,Jaspersoft server 6.4.2, Eclipse

Database: Foodmart

Consider the following simple report

Query:

select employee_id,first_name,phone_number,salary from employees

Below is the report created that contains a button which gives an excel export of the above report when clicked.

To create above report which can download CSV Export of the report using java, follow below steps:

1. 1. Create a new dynamic web project in Eclipse namely ‘CSVExport’.
   2. Create a new file in the source namely ‘DownloadFile.java’.
   3. Write below code in the file created that fetches data from database and downloads the data in CSV file format.

Code:

package helical;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.HashMap;

public class DownloadFile {
public static void main(String[] args) throws ParseException {
// TODO Auto-generated method stub
HashMap map = new HashMap();
map.put("Host", "IP Address");
map.put("Port", "5432");
map.put("DB", "foodmart");
map.put("username", "Username");
map.put("password", "password");
String fileName = "E://Mounika//My Blogs//CSVExport.csv";
String query = "select employee_id,first_name,phone_number,salary from employees";
Connection con = DownloadFile.dbConnection(map);
Boolean status = DownloadFile.writeCSV(con, fileName, query);
System.out.println("status: " + status);
}
public static Connection dbConnection(HashMap map) {
String host = (String) map.get("Host");
String port = (String) map.get("Port");
String DB = (String) map.get("DB");
String username = (String) map.get("username");
String password = (String) map.get("password");
String url = "jdbc:postgresql://" + host + ":" + port + "/" + DB;
System.out.println("url: " + url);
Connection con = null;
try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
System.out.println(e);
}
return con;
}
public static Boolean writeCSV(Connection con, String fileName, String queryReport) {
String query = queryReport;
File file = new File(fileName);
int nrows = 0;
try (FileOutputStream fop = new FileOutputStream(file)) {

// if file doesn't exists, then create it
if (!file.exists()) {
file.createNewFile();
}
Statement stmt = con.createStatement();
ResultSet ars = stmt.executeQuery(query);
con.close();
int ncols = ars.getMetaData().getColumnCount();
Writer out = new OutputStreamWriter(new BufferedOutputStream(fop));
for (int i = 1; i < (ncols + 1); i++) {
out.append(ars.getMetaData().getColumnName(i));
if (i < ncols) {
out.append(",");
} else {
out.append("\r\n");
}
}

while (ars.next()) {
nrows = ars.getRow();
for (int i = 1; i < (ncols + 1); i++) {
out.append(ars.getString(i));
if (i < ncols)
out.append(",");
else
out.append("\r\n");
}
}
out.close();
} catch (Exception e) {
System.out.println(e);
}
Boolean k = false;
if(nrows > 0){
k = true;
}else{
k = false;
}
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return k;
}
}

1. 1. Add postgres jar file(postgresql-42.1.4) in JRE System Library.
   2. Test the code in eclipse by running it as a java application.
   3. Check whether file is created in given location.
   4. Create a JSP file in WebContent folder namely ‘DownloadCSV.jsp’ and use the below code.

Code:

<%@page import="java.io.BufferedInputStream"%>

<%@page import="java.io.BufferedOutputStream"%>

<%@page import="java.io.File"%>

<%@page import="java.io.FileInputStream"%>

<%@page import="java.io.FileOutputStream"%>

<%@page import="java.io.FileNotFoundException"%>

<%@page import="java.io.IOException"%>

<%@page import="java.util.ArrayList"%>

<%@page import="java.util.List"%>

<%@page import="javax.servlet.ServletException"%>

<%@page import="javax.servlet.ServletOutputStream"%>

<%@page import="javax.servlet.http.HttpServlet"%>

<%@page import="javax.servlet.http.HttpServletRequest"%>

<%@page import="javax.servlet.http.HttpServletResponse"%>

<%@page import="java.util.zip.ZipEntry"%>

<%@page import="java.util.zip.ZipOutputStream"%>

<%@page import="java.util.HashMap"%>

<%@page import="java.sql.Connection"%>

<%@page import="helical.DownloadFile"%>

<%
String query = "select employee_id,first_name,phone_number,salary from employees";
HashMap map = new HashMap();
map.put("Host", "IP Address");
map.put("Port", "5432");
map.put("DB", "foodmart");
map.put("username", "Username");
map.put("password", "Password");

String file = "E://Mounika//My Blogs//Export.csv";

DownloadFile dr = new DownloadFile();

Connection con = dr.dbConnection(map);

Boolean status = dr.writeCSV(con, file, query);

System.out.println("status inside jsp: " + status);
if (status == true) {

File file_read = new File(file);

FileInputStream fileIn = new FileInputStream(file_read);
response.setContentType("Content-type: csv");
response.setHeader("Content-Disposition","attachment; filename="+"Export.csv");
ServletOutputStream out_data = response.getOutputStream();

BufferedInputStream fif = new BufferedInputStream(fileIn);
// Write the contents of the file

int data = 0;
while ((data = fif.read()) != -1) {
out_data.write(data);
}
fileIn.close();
out_data.flush();
out_data.close();
} else {
System.out.println("No data");
}
%>

1. 1. Create another JSP file ‘CSVDownloadmain.jsp’ which has URL to redirect to the JSP file created earlier.

Code:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<html>
<body>
<a href="http://localhost:8081/CSVExport/DownloadCSV.jsp">example</a>
</body>
</html>

1. 1. Run the main JSP file and check the functionality.

   Make data easy with Helical Insight.
   Helical Insight is world’s best open source business intelligence tool.

   Get your 30 Days Trail Version

   2. Create a new jasper report with a text field and give hyperlink to main JSP file as shown below.

1. Publish the report and run.

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download

In Case if you have any queries please get us at support@helicaltech.com

Thank You,
Mounika Pulimamidi.
Helical IT Solutions Pvt Ltd