how to display data in excel sheet?

Ads
 

how to display data in excel sheet?

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?

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

Ads









Related Tutorials/Questions & Answers:
how to display data in excel sheet?
how to display the data from excel to webpage
Advertisements
how to display the excel file content in the jsp
how to display the output of the newly created excel file
how to display the excel file in the web browser.
how to count no.of sheets in excel through POI jar as well as through Java?
Creating Excel sheets - Java Beginners
How to write more than 65536 rows in single excel file but more than 1 sheets?
How to get data from Excel sheet - Struts
Hi.. how to write more than one sheets in a excel file... pls anybody help me....
how to present the excel to the web browser in jsp
How to Create New Excel Sheet Using JSP
How to Read Excel file Using Java
How to display the data column on jsp
how to display data from database in jsp
How to export data from jsp to excel sheet by using java
How to export data from html to excel sheet by using java
How to Display Data in a tree structure on the GUI
How to export data from html file to excel sheet by using java
How to Get The Data from Excel sheet into out jsp page???
How to export data from html file to excel sheet by using java
Fetching data from excel file on other website and display in interactive graph form
How to import data from sql server table into an excel file by creating the rows dynamically in the excel according to the dataabase??
How to Retrieve Data from the database and write into excel file using Java
How to read excel data and store it in database - Java Beginners
how read excel data into database using struts2 with hibernate
how to display data in List Or grid or in table in Jsp
how to read data from excel file through browse and insert into oracle database using jsp or oracle???
how to display data from jsp file into database
How to display data from database in a TableView
How to save data to excel with a 2.1 work sheet format without changing it?

Ads