Solved

how to import data from a table to aan excel file

Posted on 2006-06-28
7
546 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
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!

 

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

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

Suggested Solutions

Title # Comments Views Activity
tomcat users xml 7 139
Reading data  from excel sheet and displaying 1 92
Is it possible to "sandbox" html content within a JSP? 4 110
glassfish admin console 1 21
Enabling the Skype for Business Meeting Scheduler in Hybrid OWA
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…

749 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