how to display data in excel sheet?

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









Related Tutorials/Questions & Answers:
how to display data in excel sheet?
how to display the excel file content in the jsp
Advertisements
how to display the data from excel to webpage
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 Create New Excel Sheet Using JSP
How to get data from Excel sheet - Struts
how to present the excel to the web browser in jsp
Hi.. how to write more than one sheets in a excel file... pls anybody help me....
How to Read Excel file Using Java
How to display the data column on jsp
how to display data from database in jsp
Fetching data from excel file on other website and display in interactive graph form
How to Display Data in a tree structure on the GUI
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 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
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
read excel data from jsp - JSP-Servlet
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?
Exporting to excel using display tag?
read data from Excel sheet
How to display data in form using aryylist in struts - Java Beginners
Display data
Export html data to excel
save data in excel
save data in excel
How to read every cell of an excel sheet using Apache POI and insert those data into a DB?
how to display data into textboxes which is retrieved from the database
how to display data into textboxes which is retrieved from the database
how to display data into textboxes which is retrieved from the database
How to draw to graph in Ms Excel from the data which is sent from an java application....?
How to export data from database to excel sheet by using java in standalone project
How to display data in jsp from dao using java beans?
How to read and display data from a .properties file from a jsp page
Hi how to transfer table data from html page to excel sheet by using javascript .
jsp with excel sheet data uploading into database
Drawing a Shape in Excel Sheet

Ads