Solved

Export to Excel or CSV from a JSP application

Posted on 2007-04-04
8
8,840 Views
Last Modified: 2008-01-09
Hi there

I have a simple JSP application that displays reports from data from a SQL database. I want to give the user the option of exporting the results to an excel or csv file by pressing a button. Is there any simple way of doing this?

The sql query is stored in a string variable 'sql' if that would help things a bit.

Cheers
0
Comment
Question by:AndriesKeun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:Manish
ID: 18852166
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 18864292
You can use an Excel library to export to Excel:

http://api.openoffice.org
0
 

Author Comment

by:AndriesKeun
ID: 18889510
Is there an easy way to find the desktop path of the user using the application?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AndriesKeun
ID: 18904289
I did this, however now i realise that it only saves the file to the pc where the application is running from, and not the user that is using the appplication. Any idea how i can change it?

<%@ page language="java" import="java.sql.*,java.sql.Time,java.util.Date,java.text.SimpleDateFormat" errorPage="error.jsp" %>
<%@ include file="CheckSession.jsp" %>
<%@ include file="conn.jsp" %>
<%@ page import="java.io.*"  %>

<%
SimpleDateFormat sdf_new = new SimpleDateFormat("dd-MM-yyyy");
String date = sdf_new.format(new Date());

String userDir = System.getProperty("user.home");
out.println(userDir);

double r;
int ir = -1;
           
while (ir % 2 != 0) {
    r = Math.random();
    ir = (int) ((9999 - 1000 + r) * r + 1000);
}

String query = (String)session.getAttribute("tempSql");
String nameOfTextFile = "c:\\CallReport_";

nameOfTextFile += date;
nameOfTextFile += "_";
nameOfTextFile += ir;
nameOfTextFile += ".csv";

PrintWriter pw = new PrintWriter(new FileOutputStream(nameOfTextFile));
String header = "Incoming/Outgoing,Call Date,Call Time,Call Length,Incoming Extension,Receiving Extension,Receiving/Calling Name,Number Called/Received";
pw.println(header);

try {
      Driver DriverRecordset = (Driver)Class.forName(_driver).newInstance();
      con = DriverManager.getConnection(_url, _uid,_pwd);
       stmt = con.createStatement();
      rs = stmt.executeQuery(query);

      while (rs.next()){      

            String line = rs.getString("c.inout") + "," + rs.getString("c.calldate") + "," + rs.getString("c.calltime")  + "," + rs.getString("c.calllength") + "," + rs.getString("c.incoming")  + "," + rs.getString("c.receiving")  + "," + rs.getString("e.Name") + "," + rs.getString("c.number");
            pw.println(line);
            }
      
      con.close();
}

catch (SQLException sqle) {
       out.println("SQLException" + sqle.toString());
}

pw.close();

%>

<table>
      <tr><td>The report has been saved on your desktop. Click 'Ok' to close this window.</td></tr>
      <tr><td><input type=button value="Ok" onClick="logout()"></td></tr>
</table>

<script language="Javascript">

function logout(){

      self.close();

}

</script>
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 18928421
This scriplet <% %> code runs on the server side - so the file is saved there. Instead of C:\ you can save the file within the context root of the web application and give a link <a href/> to the file so that the user can download it.
0
 

Author Comment

by:AndriesKeun
ID: 18930179
Clever, didnt think of that. Will try it, thanks.
0
 

Author Comment

by:AndriesKeun
ID: 18930349
Only one problem... because its a csv it opens up in the browser and does not download it, how do i go about doing that?
0
 
LVL 30

Accepted Solution

by:
Mayank S earned 500 total points
ID: 18985103
Try:

res.setContentType ( "application/download" ) ;
res.setHeader ( "Content-Disposition", "inline;filename=myFile.txt" ) ;
res.setContentLength ( ( int ) file.length () ) ;

Or try using a Download servlet: http://jspwiki.org/wiki/MakingADownloadServlet 

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A hard and fast method for reducing Active Directory Administrators members.
This article is a collection of issues that people face from time to time and possible solutions to those issues. I hope you enjoy reading it.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question