Export to Excel or CSV from a JSP application

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
AndriesKeunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mayank SAssociate Director - Product EngineeringCommented:
You can use an Excel library to export to Excel:

http://api.openoffice.org
0
AndriesKeunAuthor Commented:
Is there an easy way to find the desktop path of the user using the application?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

AndriesKeunAuthor Commented:
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
Mayank SAssociate Director - Product EngineeringCommented:
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
AndriesKeunAuthor Commented:
Clever, didnt think of that. Will try it, thanks.
0
AndriesKeunAuthor Commented:
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
Mayank SAssociate Director - Product EngineeringCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.