index.jsp
<%--Document : index
Created on : Oct 3, 2015, 11:17:49 AM
Author : Aravind Sankaran Nair
--%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body><br><br><br><br><br><br>
<center>
<form method="get" action="<%=request.getContextPath()%>/Report">
<table cellspacing="0" cellpadding="0" border="0" width="30%">
<tr bgcolor="#1E90FF">
<th>First Name</th>
<th>Last Name</th>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/qualitychecker","root","root");
Statement st=con.createStatement();
ResultSet rs = st.executeQuery("select * from user"); // this is for name
while(rs.next())
{ int rowCount=rs.getRow();
if(rowCount%2==0){ %>
<tr bgcolor="#808080">
<td><%=rs.getString("firstname")%></td>
<td><%=rs.getString("lastname")%></td>
</tr>
<%}else {%>
<tr bgcolor="#D3D3D3">
<td><%=rs.getString("firstname")%></td>
<td><%=rs.getString("lastname")%></td>
</tr>
<% }%>
<% }%>
<%
rs.close();
st.close();
con.close();
%>
</table>
<br><br>
<input type="submit" name="pdf" value="PDF">
<input type="submit" name="excel" value="EXCEL">
</form>
</center>
</body>
</html>
Report.java (Servlet)
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package controller;
import com.lowagie.text.Chunk;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.HeaderFooter;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfWriter;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
*
* @author Aravind Sankaran Nair
*/
public class Report extends HttpServlet {
/**
* Processes requests for both HTTP
* <code>GET</code> and
* <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
/* TODO output your page here. You may use following sample code. */
out.println("<!DOCTYPE html>");
out.println("<html>");
out.println("<head>");
out.println("<title>Servlet Report</title>");
out.println("</head>");
out.println("<body>");
out.println("<h1>Servlet Report at " + request.getContextPath() + "</h1>");
out.println("</body>");
out.println("</html>");
} finally {
out.close();
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
* Handles the HTTP
* <code>GET</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//processRequest(request, response);
if(request.getParameter("pdf")!=null){
System.out.println("pdf");
DocumentException ex = null;
ByteArrayOutputStream baosPDF = null;
try
{
baosPDF = generatePDFDocumentBytes(request, this.getServletContext());
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
Calendar cal = Calendar.getInstance();
String time=dateFormat.format(cal.getTime());
StringBuffer sbFilename = new StringBuffer();
sbFilename.append("PDFReport_");
sbFilename.append(time);
//sbFilename.append(System.currentTimeMillis());
sbFilename.append(".pdf");
response.setHeader("Cache-Control", "max-age=30");
response.setContentType("application/pdf");
StringBuffer sbContentDispValue = new StringBuffer();
sbContentDispValue.append("attachment");
sbContentDispValue.append("; filename=");
sbContentDispValue.append(sbFilename);
response.setHeader("Content-disposition",sbContentDispValue.toString());
response.setContentLength(baosPDF.size());
ServletOutputStream sos;
sos = response.getOutputStream();
baosPDF.writeTo(sos);
sos.flush();
}
catch (DocumentException dex)
{
response.setContentType("text/html");
PrintWriter writer = response.getWriter();
writer.println(this.getClass().getName() + " caught an exception: " + dex.getClass().getName()+ "<br>");
writer.println("<pre>");
dex.printStackTrace(writer);
writer.println("</pre>");
}
finally
{
if (baosPDF != null)
{
baosPDF.reset();
}
}
}else if(request.getParameter("excel")!=null){
System.out.println("excel");
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
Calendar cal = Calendar.getInstance();
String time=dateFormat.format(cal.getTime());
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=ExcelReport_"+time+".xls");
try{
HSSFWorkbook hwb=new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
sheet.setDefaultColumnWidth(15);
sheet.setColumnWidth(6, 50);
HSSFRow rowhead= sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("User Id");
rowhead.createCell((short) 1).setCellValue("First Name");
rowhead.createCell((short) 2).setCellValue("Last Name");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName", "databaseUserName", "databasePassword");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select * from user"); //table
int i=1;
while(rs.next()){
HSSFRow row= sheet.createRow((short)i);
row.createCell((short) 0).setCellValue(rs.getInt("uid"));
row.createCell((short) 1).setCellValue(rs.getString("firstname"));
row.createCell((short) 2).setCellValue(rs.getString("lastname"));
i++;
}
hwb.write(response.getOutputStream());
} catch ( Exception ex ) {
System.out.println(ex);
}
}
}
protected ByteArrayOutputStream generatePDFDocumentBytes(
final HttpServletRequest req,
final ServletContext ctx)
throws DocumentException
{
Document doc = new Document();
ByteArrayOutputStream baosPDF = new ByteArrayOutputStream();
PdfWriter docWriter = null;
try
{
docWriter = PdfWriter.getInstance(doc, baosPDF);
doc.addAuthor(this.getClass().getName());
doc.addCreationDate();
doc.addProducer();
doc.addCreator(this.getClass().getName());
doc.addTitle("This is a title.");
doc.addKeywords("pdf, itext, Java, open source, http");
doc.setPageSize(PageSize.LETTER);
HeaderFooter footer = new HeaderFooter(new Phrase("This is a footer."),false);
doc.setFooter(footer);
doc.open();
doc.add(new Paragraph("This document was created on "+ new java.util.Date()));
String strServerInfo = ctx.getServerInfo();
Paragraph paragraph = new Paragraph();
for(int i=0; i<10; i++){
Chunk chunk = new Chunk(" ");
paragraph.add(chunk);
}
doc.add(paragraph);
doc.add(createFirstTable());
doc.newPage();
doc.newPage();
}
catch (DocumentException dex)
{
baosPDF.reset();
throw dex;
}
finally
{
if (doc != null)
{
doc.close();
}
if (docWriter != null)
{
docWriter.close();
}
}
if (baosPDF.size() < 1)
{
throw new DocumentException(
"document has "
+ baosPDF.size()
+ " bytes");
}
return baosPDF;
}
public static PdfPTable createFirstTable() {
PdfPTable table = new PdfPTable(2);
PdfPCell cell1,cell2;
cell1 = new PdfPCell(new Paragraph("First Name"));
cell2 = new PdfPCell(new Paragraph("Last Name"));
table.addCell(cell1);
table.addCell(cell2);
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException ex) {
Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
}
Connection con;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName", "databaseUserName", "databasePassword");
Statement st;
st = con.createStatement();
ResultSet rs=st.executeQuery("Select * from user"); //table name
int i=1;
while(rs.next()){
table.addCell(rs.getString("firstname"));
table.addCell(rs.getString("lastname"));
i++;
}
} catch (SQLException ex) {
Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex);
}
return table;
}
/**
* Handles the HTTP
* <code>POST</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
*
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}// </editor-fold>
}
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<servlet>
<servlet-name>Report</servlet-name>
<servlet-class>controller.Report</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Report</servlet-name>
<url-pattern>/Report</url-pattern>
</servlet-mapping>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
Jar File Needed
mysql-connector-java-5.1.18-bin.jar //for mysql connection
com.lowagie.text-2.1.7.jar //for pdf
poi-3.9.jar //for excel
Project Structure
Output:
No comments:
Post a Comment