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


Export data from MySQL into a CSV file,Its simple by using this query.

SELECT * INTO OUTFILE '/tmp/name.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM [tablename]

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>&nbsp;</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>&nbsp;</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');

 
java4practices © 2013 | Designed by Ganesh Rengarajan