JSP using java

View Answers

September 9, 2009 at 2:50 PM

Hi Friend,

We are providing you a code that will retrieve the values from the database and display the data into the excel file.

1)RetrieveData.java:

package form;

import java.sql.*;
import java.util.*;

public class Retrievedata {
List dataList = new ArrayList();
Connection con;

public void connect() throws Exception{
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root";);
}
catch (ClassNotFoundException e) {
System.out.println(e);
}
}

public void disconnect() throws SQLException {
try {
if ( con != null ) {
con.close();
}
}
catch (SQLException ex) {
System.out.println(ex);
}
}

public List viewDatabase () throws SQLException, Exception {
ResultSet rs = null;
try {
String queryString = "Select * From student;";
Statement stmt = con.createStatement();
rs = stmt.executeQuery(queryString);
while (rs.next ()){
dataList.add(rs.getInt("rollNo"));
dataList.add(rs.getString("name"));
dataList.add(rs.getInt("marks"));
dataList.add(rs.getString("grade"));
}
}
catch (Exception e) { }
return dataList;
}
}

September 9, 2009 at 2:54 PM

continue...........

2)excel.jsp:

<%@page import="java.io.*"%>
<%@page import="java.util.*"%>
<%@page import="java.sql.*"%>
<%@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"%>
<jsp:useBean id="test" class="form.Retrievedata" />

<html>
<body>
<%
try{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Excel Sheet");
HSSFRow rowhead = sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("Roll No");
rowhead.createCell((short) 1).setCellValue("Name");
rowhead.createCell((short) 2).setCellValue("Marks");
rowhead.createCell((short) 3).setCellValue("Grade");
test.connect ();
int index=1;

Iterator itr;
List data= test.viewDatabase ();
for (itr=data.iterator(); itr.hasNext(); ){
HSSFRow row = sheet.createRow((short)index);
row.createCell((short)0).setCellValue(String.valueOf(itr.next()));
row.createCell((short)1).setCellValue(String.valueOf(itr.next()));
row.createCell((short)2).setCellValue(String.valueOf(itr.next()));
row.createCell((short)3).setCellValue(String.valueOf(itr.next()));
index++;
}
File file=new File("c:\\excelFile.xls");
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
out.println("Data is saved in excel file.");

String filePath=file.getAbsolutePath();
Runtime rt = Runtime.getRuntime();
rt.exec("cmd.exe /C start C:\\excelFile.xls");
}
catch(Exception e){}
%>

Hope that it will be helpful for you.
Thanks