Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to import data from a table to aan excel file

Posted on 2006-06-28
7
Medium Priority
?
550 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 2000 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
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: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 26

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

876 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