Solved

how to import data from a table to aan excel file

Posted on 2006-06-28
7
545 Views
Last Modified: 2008-01-09
I have a JSP file where I show member information in a  table. Now what I need is a button which when pressed transfers all the data from the table into an excel file. Is there a way I can do that

Any help will be greatly appreciated
0
Comment
Question by:huzefaq
7 Comments
 
LVL 3

Accepted Solution

by:
w0lver earned 500 total points
ID: 17005404
The what I do this is to create a .CSV file:
In the JSP, add a JavaScript function:
<script language="JavaScript">
            function beginDownload() {
                <% contentKey = orderReport.saveDownloadContent(out_param, sessionBean);%>
                window.location = 'orders<%= contentKey %>.csv?contentKey=<%= contentKey %>&sessionType=sessionBeanRip';
            }
  </script>
Then I create a variable for the delimiter and create a header:
  <%    
    String delimiter = ",";
    // create the header
    String out_param = "Order id" + delimiter + "Customer" + delimiter + "Total Sales" + delimiter + "Total Comission\n";
  %>

Then you do your query...  loop through it and add the results to the string like this:
                    <%
                     out_param += element.get("orderId") + delimiter +
                               element.get("company") + delimiter +
                               Utils.formatDecimal(element.get("tsales")) + delimiter +
                               Utils.formatDecimal(element.get("tcomm")) + "\n";
                    %>
Then I add a form with a button:
        <form name="report" method="post" action="" method="get">
            <br>
            <table width="700" border="0" cellspacing="0" cellpadding="2" align="center">
                <tr align="center"><td align="center"><INPUT TYPE="button" NAME="button" Value="Download CSV" onClick="beginDownload()"></td></tr>
            </table>
        </form>

Here's the saveDownloadContent class from orderReport:
  public String saveDownloadContent(String _content, SessionBean sessionBean) {
    // need to create an unique file name, here I use the combo of the vendor number and a timedate stamp
    String contentKey = vendorNr + Utils.getInstance().getTimeStamp("yyyyMMddhhmmssSSS");
    sessionBean.putCsvFile(contentKey, _content);
    return contentKey;
  }

Here's the get, put and delete classes in the sessionBean for the file:
    public String getCsvFile(String csvKey) {
        Object csvFile = csvBuffer.get(csvKey);
        if (csvFile == null)
            return "";
        else
            return csvFile.toString();
    }
    public void putCsvFile(String fileKey, String fileContent) {
        csvBuffer.put(fileKey, fileContent);
    }
    public void delCsvFile(String fileKey) {
        csvBuffer.remove(fileKey);
    }

Hope that all makes sense...
0
 

Author Comment

by:huzefaq
ID: 17013790
is there an open source API I can use
0
 

Expert Comment

by:alexmarshall
ID: 17017791
Alternatively, if you already have the information in a JavaBean and all the JSP is doing is displaying it, you can write a servlet to output the data straight to an .xls file using JasperReports.  I've started using it, and once you have an idea of what you're doing, it's great.  Big learning curve though, and I wouldn't recommend starting to learn it unless you've got a considerable amount of time to spend.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:huzefaq
ID: 17019653
Do you guys know anything about display tag. Does anyybody has used that. I would really appreciate if somebody could give more information about it

thanks

0
 
LVL 4

Expert Comment

by:Meritor
ID: 17073756
I suggest you two ways for writing data to xls.

1) Just write a simple HTML string and that to browser setting its content type to application/vnd.ms-excel and save that file with .xls extention. The Microsoft Excel is samrt enough which can automatically convert html to its own format.
 
2) The other way is : I am using a third pary Excel API. and I am sure it will definately help you out.
Java Excel API
http://www.andykhan.com/jexcelapi/index.html
please visit the above url for downloading that api
If you have any qrueries about using that you can direcly ask me.

Regards,
Meritor
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 17079830
There is one Open Source API that I recommend highly
and that is DisplayTag (
http://displaytag.sourceforge.net/11/  )
This is a powerfull and easy to use Tag library with a lot of features.

Regards,
  Tomas Helgi
0
 

Author Comment

by:huzefaq
ID: 17103731
in display tag can you show data bt not in a table format
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
Exception creating bean of class 5 200
JSONSerializer.toJSON in JSP generating error 8 80
java operators 3 120
login form jsp example 2 35
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 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