Nandini Mishra
how to display data in excel sheet?
2 Answer(s)      4 years ago
Posted in : JSP-Servlet

According to the user Id,some links will be generated from the database,and if we click a link,data would be retrieved from the database and data will be displayed in a new page.in this page there will be a button called excel download,and if we click the button data will be displayed in excel sheet.can you people please help me how to create the button and to display the data in excel sheet?

Advertisement
View Answers

May 31, 2011 at 3:16 PM


1)id.jsp:

<%@page import="java.sql.*"%>
<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("select * from user");
while(rs.next()){
    %>
    <a href="retrievedata.jsp?id=<%=rs.getString("id")%>">User <%=rs.getString("id")%></a><br>
    <%
}
    %>

2)retrievedata.jsp:

<%@page import="java.sql.*"%>
<form method="post" action="excelFile.jsp">
<table>
<%
String id=request.getParameter("id");
Class.forName("com.mysql.jdbc.Driver").newInstance();  
 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");  
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select * from user where id='"+id+"'");
if(rs.next()){
    %>
<tr><td>Name</td><td><input type="text" name="name" value="<%=rs.getString("name")%>"></td></tr>
<tr><td>Address</td><td><input type="text" name="address" value="<%=rs.getString("address")%>"></td></tr>
<%
}
%>
</table>
<input type="submit" value="Export To Excel">
</form>

3)excelFile.jsp:

<%@page import="  java.io.*"%>  
<%@page import="  org.apache.poi.hssf.usermodel.*"%>  
<%
String name=request.getParameter("name");
String address=request.getParameter("address");
String filename="c:/data.xls" ;
try{

HSSFWorkbook hwb=new HSSFWorkbook();
HSSFSheet sheet =  hwb.createSheet("sheet");

HSSFRow row=   sheet.createRow((short)0);
row.createCell((short) 0).setCellValue(name);
row.createCell((short) 1).setCellValue(address);

FileOutputStream fileOut =  new FileOutputStream(filename);
hwb.write(fileOut);
fileOut.close();
out.println("Your excel file has been generated!");
} 
catch(Exception ex ){
System.out.println(ex);
}
File f = new File(filename);
    String fn=f.getName();
    response.setContentType("application/excel");
    response.setHeader("Content-Disposition", "attachment;  filename=\""+fn+"\"");

    String n = f.getName().substring(f.getName().lastIndexOf("/") + 1,f.getName().length());
    InputStream in = new FileInputStream(f);
        ServletOutputStream outs = response.getOutputStream();
        int bit = 256;
        int i = 0;
            try{
                    while ((bit) >= 0) {
                        bit = in.read();
                        outs.write(bit);
                    }
                        } catch (IOException ioe) {
                        ioe.printStackTrace(System.out);
                    }
                        outs.flush();
                    outs.close();
                    in.close(); 
                    %>


December 3, 2011 at 5:08 PM


I want to open that data.xls file which is saved.

I mean When I am going to click Export to Excel it will save and automatically open in Excel not in Browser.

Please Help Me










Related Tutorials/Questions & Answers:
Advertisements