servlet to export information in excel file

We can use servlet to export information in excel format. The code for this purpose may be as below:

String folder = "/excels/";

Three parameters have been sent using request as per requirement.fileNameToShow contains the value for the name of the file to save in client side

String fileNameToShow=request.getParameter("fileName");
String dbName=request.getParameter("dbName");
String query=request.getParameter("query");
String file_name = folder;

System.out.println("Report Name: " + file_name);
try {


ServletOutputStream servletOutputStream = response.getOutputStream();
byte[] bytes = null;


try {
ExcelHelper exlHelper = new ExcelHelper();
bytes = exlHelper.createExcell(getServletConfig().getServletContext().getRealPath(file_name), query, dbName);
response.setHeader("Content-Disposition", "inline;filename="+fileNameToShow+".xls");
response.setContentType("application/vnd.ms-excel");
response.setContentLength(bytes.length);
servletOutputStream.write(bytes, 0, bytes.length);

servletOutputStream.flush();
servletOutputStream.close();
mailMessage = mailMessage + ". REPORT produced successfully";
} catch (Exception e) {
// display stack trace in the browser
logger.error(e);
StringWriter stringWriter = new StringWriter();
PrintWriter printWriter = new PrintWriter(stringWriter);
e.printStackTrace(printWriter);
response.setContentType("text/plain");
response.getOutputStream().print("Could not display report temporarily...");
//response.getOutputStream().print(stringWriter.toString());
mailMessage = mailMessage + ".ERROR catched when creating report.";
}
} catch (Exception e) {
System.out.println(e.getMessage());
logger.error(e);
} finally {
logger.info(mailMessage);
}

Expecting your kind suggestion for moderation of the code.

to create excel file using java

Apache POI API has been used for this purpose. This is used for manipulating various microsoft files.

This is a function of a class which generate an excel file after executing query in the database. Executing query in the database is not concerned here. This code insists on the creating file using POI. The function return the file in byte array( i do it as one of my requirement).

public byte[] createExcell(String fileLoc, String query, String dbName) {

Comments:
fileLoc: provide the location where to keep the file after generation.
query: database query on which you want to create excel
dbname: if you work with multiple database in your project

try {
String file_name = fileLoc +"/"+ (new Date()).getTime() + ".xls";
FileOutputStream fileOut = new FileOutputStream(file_name);
File file = new File(file_name);
if (file.exists()) {
file.delete();
}

delete file if a file name generated before with same name.

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("Candidate List");
byte[] bytes;

GatewayPersonalInfo gwPersonalInfo = new GatewayPersonalInfo();
List lstPerson;
lstPerson = gwPersonalInfo.getAllPersonalInfo(query, dbName);

Above three lines has been used to import data from databases in list.

HSSFRow rowNew;
HSSFCell cellA, cellB, cellC, cellD, cellE;
rowNew = worksheet.createRow(0);
cellA = rowNew.createCell(0);
cellA.setCellValue("Sl. No");
cellB = rowNew.createCell(1);
cellB.setCellValue("App Serial No");
cellC = rowNew.createCell(2);
cellC.setCellValue("Full name");
cellD = rowNew.createCell(3);
cellD.setCellValue("Contact No.");
cellE = rowNew.createCell(4);
cellE.setCellValue("Email ID");

Just to create header in the file

if (lstPerson != null) {
if (!lstPerson.isEmpty()) {
Iterator itr = lstPerson.iterator();
PersonalInfo person;
int rowno = 1;
while (itr.hasNext()) {

person = (PersonalInfo) itr.next();
rowNew = worksheet.createRow(rowno);
cellA = rowNew.createCell(0);
cellA.setCellValue(rowno);
cellB = rowNew.createCell(1);
cellB.setCellValue(person.getSerialNo());
cellC = rowNew.createCell(2);
cellC.setCellValue(person.getFullName());
cellD = rowNew.createCell(3);
cellD.setCellValue(person.getContactNo());
cellE = rowNew.createCell(4);
cellE.setCellValue(person.getEmail());
System.out.println(rowno + " row added.");
rowno = rowno + 1;
}
}
}
workbook.write(fileOut);

fileOut.flush();
fileOut.close();

FileInputStream fin = new FileInputStream(file);
bytes = new byte[(int) file.length()];
fin.read(bytes);
file.delete();

After taking in the byte array, file is being deleted. But it depends on your requirements. You can keep the file.

return bytes;

} catch (Exception ex) {
System.out.println(ex);
logger.error(ex);
} finally {
}
return null;
}

Kindly provide your suggestion for further modification.

Servlet to export jasper report in excel

Its very important issue to export jasper report to excel format. This can be done through a servlet in JSP application. The servlet code may be as bellow:

In tis case I have used some code as part of many times edit. It can be summarize.


This servlet is able to handle different no. of parameters and different reports.

We just need to call this servlet as below:

link = "/JRExcelExport?rname=candidateList&pname=posiCode=" + posiCode + "@qValue1=" + qValue1 + "@qValue2=" + qValue2 ;
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(link);
dispatcher.forward(request, response);

The value of rname is the name of the report. And all the parameters would be attached after the pname

package dbbl.paul.recruitment.servlets;

import dbbl.paul.recruitment.dal.dbconnector.ConnectionHandler;
import java.io.*;
import java.sql.Connection;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.*;

import net.sf.jasperreports.engine.export.JExcelApiExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import org.apache.log4j.Logger;


public class JRExcelExport extends HttpServlet {

final static Logger logger = Logger.getLogger(ReportManager.class);

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
logger.trace("Trying to Generate report:processRequest");

String mailMessage = "";
String folder = "/reports/";
JasperPrint jasperPrint = null;

String rpt_name = request.getParameter("rname");
String report_name = rpt_name + ".jasper";
String file_name = folder + report_name;

mailMessage = "RPTNAME=" + rpt_name;
System.out.println("Report Name: " + file_name);
try {
HashMap hm = new HashMap();
String param_name = request.getParameter("pname");
System.out.println(param_name);

if (param_name != null) {

if (!param_name.equals("")) {
String split_param[] = param_name.split("@");
String tmp_param[];
for (int i = 0; i < split_param.length; i++) { System.out.println(split_param); tmp_param = split_param[i].split("="); hm.put(tmp_param[0], tmp_param[1].replaceAll("NULL", "")); } } mailMessage = mailMessage + ". PARAMETERS collected successfully"; } ServletOutputStream servletOutputStream = response.getOutputStream(); File reportFile = new File(getServletConfig().getServletContext().getRealPath(file_name)); Connection con = null; try { con = ConnectionHandler.getConnection_OnlineJob(); jasperPrint = JasperFillManager.fillReport(reportFile.getPath(), hm, con); generateXLSOutput(rpt_name, jasperPrint, response); } catch (JRException e) { // display stack trace in the browser logger.error(e); StringWriter stringWriter = new StringWriter(); PrintWriter printWriter = new PrintWriter(stringWriter); e.printStackTrace(printWriter); response.setContentType("text/plain"); response.getOutputStream().print("Could not display report temporarily..."); //response.getOutputStream().print(stringWriter.toString()); mailMessage = mailMessage + ".ERROR catched when creating report."; } } catch (Exception e) { System.out.println(e.getMessage()); logger.error(e); } finally { logger.info(mailMessage); } } private void generateXLSOutput(String reportname, JasperPrint jasperPrint, HttpServletResponse resp) { String reportfilename = reportname + ".xls"; try { JExcelApiExporter exporterXLS = new JExcelApiExporter(); exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint); exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE); exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE); exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE); exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, resp.getOutputStream()); resp.setHeader("Content-Disposition", "inline;filename=" + reportfilename); resp.setContentType("application/vnd.ms-excel"); exporterXLS.exportReport(); } catch (Exception ex) { logger.trace(ex); } } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override public String getServletInfo() { return "Short description"; }//
}

Starting of J2ME application

# J2ME

—Java 2 Platform, Micro Edition, is the Java platform meant to run on small devices. Currently, these devices are typically cell phones or PDAs, but J2ME is also used on other embedded systems. A configuration, a profile, and optional packages are what compose a J2ME platform.

# CLDC

—Connected Limited Device Configuration is a J2ME configuration that is currently most often used on mobile phones. It contains a run time environment and a core API that are appropriate for the limited processor speed and memory size of mobile devices.

# MIDP

—Mobile Information Device Profile is the set of APIs that provides higher-level functionality required by mobile applications, such as displayable components (“screens”) and network communication.

#MIDlet

—A class required by all MIDP applications. It acts as the interface between the application and the device on which it is running. A MIDlet is similar to a main class in a J2SE project.

# Preverification

—When building an application that runs with CLDC, all compiled classes must be preverified. Preverification is a process that adds annotations used by the CLDC JVM to a class file’s bytecode. The preverification process also ensures that the class contains only code that will run on its CLDC version.

# Device fragmentation

—Term used for the variations between mobile platforms that prevent a single application from automatically running optimally on all phones. These differences can be physical (screen size, screen color depth, available memory, and so on) or software related (available APIs, CLDC/MIDP version, and so on).

# Preprocessor

—Though preprocessor is not a J2ME-specific term, NetBeans Mobility Pack ships with a preprocessor that is used as part of its device fragmentation solution. The preprocessor is an Ant task that runs before files are compiled. It looks for special Java comment tags within the file and adds or removes line comments based on these tags.

# Obfuscation

process that makes class files difficult to reverse engineer. This is usually accomplished by, at least, replacing names of packages, classes, methods, and fields with short identifiers. This has the result of decreasing the size of your application and, therefore, is an important aspect of the mobile application build process.

more