Search the database and export the relevant
data into excel format
SearchUser.jsp
<html>
<head>
</head>
<body>
<br><br><br><br>
<form
name="searchForm" method="post"
action="excelData.jsp">
<table
align="center" bgcolor="LIGHTBLUE">
<tr>
<td
colspan=3 align="center"><b><span style="font-size:20px;">Create
a Excel file with Search Table</span></b></td>
</tr>
<tr>
<td
colspan=3> </td>
</tr>
<tr>
<td><b>Search</b></td>
<td><input
type="text" name="searchtxt"
value=""></td>
<td><input
type="submit" name="Submit"
value="Search"></td>
</tr>
<tr>
<td
colspan=3> </td>
</tr>
</table>
</form>
</body>
</html>
excelData.jsp:
<%@ page
import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page
import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page
import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page
import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%!
Connection conn = null;
String url =
"jdbc:mysql://localhost:3306/";
String dbName = "db5";
String driver =
"com.mysql.jdbc.Driver";
String username =
"root";
String userPassword =
"root";
%>
<br><br>
<%
int index=3;
int sno=0;
String name="";
java.util.Date date = new java.util.Date();
String filename = "c:\\f"+date.getTime()+".xls" ;
String searchText="";
if(request.getParameter("searchtxt")!=null)
{
searchText=
request.getParameter("searchtxt").toString();
System.out.println(""+searchText);
}
try{
HSSFWorkbook hwb = new
HSSFWorkbook();
HSSFSheet sheet =
hwb.createSheet("new sheet");
HSSFRow rowhead =
sheet.createRow((short)2);
rowhead.createCell((short)
0).setCellValue("SNo");
rowhead.createCell((short)
1).setCellValue("First Name");
rowhead.createCell((short)
2).setCellValue("Last Name");
rowhead.createCell((short)
3).setCellValue("Username");
rowhead.createCell((short)
4).setCellValue("E-mail");
rowhead.createCell((short)
5).setCellValue("Country");
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/db5","root","root");
Statement stmt = conn.createStatement();
String strQuery = "select * from register where firstname like
'%"+searchText+"%' or lastname like
'%"+searchText+"%'";
ResultSet rs = stmt.executeQuery(strQuery);
while(rs.next())
{
System.out.println(sno++);
HSSFRow row =
sheet.createRow((short)index);
row.createCell((short)
0).setCellValue(sno);
row.createCell((short)
1).setCellValue(rs.getString(1));
row.createCell((short)
2).setCellValue(rs.getString(2));
row.createCell((short)
3).setCellValue(rs.getString(3));
row.createCell((short)
4).setCellValue(rs.getString(4));
row.createCell((short)
5).setCellValue(rs.getString(5));
index++;
}
FileOutputStream fileOut = new FileOutputStream(filename);
hwb.write(fileOut);
fileOut.close();
out.println("<b>Your
excel file has been generated</b>");
} catch ( Exception ex ) {
}
%>
DataBase:
#
# Table structure for table 'register'
#
CREATE TABLE ‘register’ (
‘SNo‘ int(10) default
NULL,
‘firstname‘ varchar(50)
default NULL,
‘lastname‘ varchar(50)
default NULL,
‘Username‘ varchar(50)
default NULL,
‘E-mail‘ varchar(50)
default NULL,
‘Country‘ varchar(50)
default NULL
);
#
# Dumping data for table 'register'
#
REPLACE INTO "register" ("SNo",
"firstname", "lastname", "Username",
"E-mail", "Country")
VALUES(1,'dsd','gd','fd','fd','fddf');
REPLACE INTO "register" ("SNo",
"firstname", "lastname", "Username",
"E-mail", "Country")
VALUES(2,'daxs','ddd','ll;','l;l','ll');
what do if we wan to generate in clent pc