Solved

how to import data from a table to aan excel file

Posted on 2006-06-28
7
548 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
[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
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
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: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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
With the rising number of cyber attacks in recent years, keeping your personal data safe has become more important than ever. The tips outlined in this article will help you keep your identitfy safe.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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