Forum:

Need help while exporting CSV file using JSP

pushpa naga

Greenhorn

Posts: 10

posted 7 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,

while exporting csv file from jsp im getting empty csv file.

Any help is much appreciated.

<%@ page language="java" contentType="text/html; charset=UTF-8"   pageEncoding="UTF-8" %>
<%@ page import="java.io.*"%>

<%@page import="java.sql.*"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<%!
String getData(){
	
	
	String JDBC_DRIVER = "oracle.jdbc.OracleDriver";  
	String DB_URL = "oracle:thin:@10.102.239.141:1521/VCSTAGE";
	
	String USER = "vcstaging";
	String PASS = "vcstaging";

Connection conn = null;

Statement stmt = null;
	StringBuffer sbf = new StringBuffer();
try{
    //STEP 2: Register JDBC driver
    Class.forName("oracle.jdbc.OracleDriver");

//STEP 3: Open a connection
    System.out.println("Connecting to database...");
    conn = DriverManager.getConnection(DB_URL,USER,PASS);

//STEP 4: Execute a query
    System.out.println("Creating statement...");
    stmt = conn.createStatement();
    String sql;
    sql = "SELECT APPROXIMATE_LONGITUDE, ALTITUDE FROM AUDIT_SITE_DATA";
    
    ResultSet rs = stmt.executeQuery(sql);
    
    ResultSetMetaData rsm = rs.getMetaData();
    
    int count = rsm.getColumnCount();
    int n = 1;
    
    
    System.out.println("Column count="+count);
    System.out.println(rsm.getColumnName(1));
    
    
while(n<count){
  	sbf.append(rsm.getColumnName(n)+",");
  	n++;
    }
sbf.append(rsm.getColumnName(n)+"\n");
    //STEP 5: Extract data from result set
    while(rs.next()){
       //Retrieve by column name
       
       int i = 1;
       
       while(i<count){
      	 sbf.append(rs.getString(i)+",");
      	 i++;
       }
       sbf.append(rs.getString(i)+"\n");
   
    }
    
    //STEP 6: Clean-up environment
    rs.close();
    stmt.close();
    conn.close();
 }catch(SQLException se){
    //Handle errors for JDBC
    se.printStackTrace();
 }catch(Exception e){
    
    e.printStackTrace();
 }finally{
    
    try{
       if(stmt!=null)
          stmt.close();
    }catch(SQLException se2){
    }
    try{
       if(conn!=null)
          conn.close();
    }catch(SQLException se){
       se.printStackTrace();
    }//end finally try
 }//end try

return sbf.toString();
}
%>

{  
    int length   = 0;
    ServletOutputStream outStream = response.getOutputStream();
    PrintStream printStream = new PrintStream(outStream);
    response.setContentType("application/vnd.ms-excel");
  
    response.setHeader("Content-Disposition", "attachment; filename=\"" + "Auditsitedata.csv" + "\"");
    
    printStream.print(getData());
    
   
     outStream.flush();
     outStream.close();
   
}
%>
</body>
</html>

William Brogden

Author and all-around good cowpoke

Posts: 13078

posted 7 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You should not even be attempting to do stuff like this in JSP. JSP is for generating HTML pages, not computation and manipulation of output streams.

Save yourself lots of trouble and look into servlet code designed to handle file transfer.

Bill

Paul Ngom

Ranch Hand

Posts: 355

posted 7 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Pushpa,
Are you getting the correct values in sbf?

I am always surprised at the added knowledge i can get from others when i give my opinion on a topic.

Dave Tolls

Rancher

Posts: 4801

posted 7 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Are you sure you're not getting an IllegalStateException?
Because I'm pretty sure using getOutputStream inside a JSP page will result in that (at least it used to) as the JSP has already called getWriter to write its own stuff out as pretty much the first thing it does.

Bear Bibeault

Sheriff