Solved

Export to Excel or CSV from a JSP application

Posted on 2007-04-04
8
8,842 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
Industry Leaders: 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

707 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