Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa JSP-Servlet how to display data in excel sheet?
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


Nandini Mishra
how to display data in excel sheet?
2 Answer(s)      3 years and 4 months 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
Advertisements
 

 

 

DMCA.com