Second Menu
Showing posts with label Excel Creation using java. Show all posts
Showing posts with label Excel Creation using java. Show all posts
MySQL Export to CSV
0
Excel Creation Using Jsp With Mysql Database Connection
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');
Subscribe to:
Posts (Atom)
About Me
Live Traffic
Labels
- Android (2)
- Autocomplete using jquery and Mysql Database (1)
- Create Table in Java Swing (1)
- email using java code (1)
- Excel Creation using java (2)
- How to create hibernate in netbeans (1)
- How to create stored procedure in java (1)
- JASPER REPORT USING JAVA (1)
- jquery (2)
- JSTL Tags (1)
- Listview with AlertBox in Android (1)
- multiple jquery version in jsp page (1)
- MYSQL QUERY (2)
- Oracle Database Connection for Java (1)
- Sample table creation in PDF (1)
- Simple WebApplication Using Spring (1)
- Swing Look and Feel (1)
- Way2SMS Java API 2012 (1)
Popular Posts
-
STEP 1: Download ireport plugin for Netbeans http://plugins.netbeans.org/plugin/4425/ireport STEP 2: Install plugin in Netbe...
-
This Tutorial showz that how you can use the www.way2sms.com for sending message us...
-
Required Jar: Download Itext 1.3 jar Servlet Code: import javax.servlet.*; import javax.servlet.http.*; import java.util.*; imp...
-
import java.io.IOException; import java.io.PrintWriter; import java.util.Properties; import javax.activation.DataHandler; ...
-
Requirement: Required javascript and css file Download Here Required jsp files see below code update.jsp <html> <head...
-
Download the database and jar file here download 1 .Create Java Project in Netbeans 2.Create JFrame form 3.Drag...
-
PART 1 Oracle Download Install Oracle database and do steps below in image After select Go To Database Home Page a login...
-
Search the database and export the relevant data into excel format SearchUser.jsp <html> <head> ...
-
DML SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSE...
-
Export data from MySQL into a CSV file,Its simple by using this query. SELECT * INTO OUTFILE '/tmp/name.csv' FIELDS TERMINATED...
Powered by Blogger.
Translate
Widgets
Followers
About Me
GANESH RENGARAJAN,
JAVA DEVELOPER,
CHENNAI,TAMILNADU,
INDIA