Solved

how to import data from a table to aan excel file

Posted on 2006-06-28
7
542 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
Comment Utility
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
Comment Utility
is there an open source API I can use
0
 

Expert Comment

by:alexmarshall
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:huzefaq
Comment Utility
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
Comment Utility
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 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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
Comment Utility
in display tag can you show data bt not in a table format
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now