Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks

In this post Luca Spinelli and Ivan Aguilar from ClearPeaks indroduce us to the new export to Excel solution for Xcelsius using the Apache Tomcat (jsp) server.

If you have been searching for an export to Excel solution for Xcelsius using the Apache Tomcat (jsp) server, look no further.

ClearPeaks has developed a customized solution that any BI Developer can quickly and easily add to an Xcelsius dashboard. Export2Excel* will allow your end users to filter results by selecting part of their dashboard using the XML connection of Xcelsius. Essentially, this customized option adds flexibility to an Xcelsius dashboard for those users who want to look at data outside of the predefined dashboard components.

How to Configure Export2Excel

Follow the steps below to configure the Export2Excel solution in your Xcelsius dashboard:

1. Download the WAR file (Find the link at the end of this post)

2. Copy the ExportToExcelv2.war into your Tomcat webapps folder

3. Open the Data Manager

4. Add XML connection

5. Set the XML Data URL as http://<servername>:<port>/ExportToExcelv2/index.jsp

6. Check the “Enable Send” function. This will contain the range of cells that you choose to export.

7. Check the “Enable Load” function. Be sure that the Name is URLDummy (as highlighted in red in the screenshot below), and that the Range contains just one cell. This cell is the URL of the exported Excel file.

Note: Add a refresh connection and URL button on the Xcelsius dashboard to open this link

clip_image002

Demo:

As you will see in the image below, ClearPeaks has merged the functionalities of a refresh connection button and a URL button. As a result of this customized functionality, users can open an Excel file and begin their own analysis and comparisons if desired.

clip_image0021

download

If you want to learn more about ClearPeaks and our BI solutions, please contact us at info@clearpeaks.com or visit us at www.clearpeaks.com.

* Copyright (C) 2009 ClearPeaks. This free solution can be used, published or redistributed with appropriate credit to ClearPeaks. Please see the terms and conditions of the General Public License within the “About ClearPeaks Export2Excel” tab of the file.

Related Posts:

  • http://twitter.com/kalyanverma/status/2500448227 kalyanverma

    Customize your Xcelsius dashboard with the Export2Excel solution from ClearPeaks http://tinyurl.com/nob5fg

  • http://twitter.com/rossgoodman/status/2503843363 Ross Goodman

    Reading : Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks: In this post Lu.. http://tinyurl.com/nob5fg

  • ACaussin

    It’s great and really easy to use !
    However, do you think an Excel’s export could keep colors and forms ?

  • http://www.clearpeaks.com Luca Spinelli

    Hi,
    thanks for your thumbs up, I’m glad to know that our solution is being helpful!!!

    The problem of formatting the text comes when Xcelsius sends out the xml.

    Xcelsius unfortunately sends out just the string that contains the data you want to export without information about the color and the format of the cells. The format of the xml is like .

    Anyway one time we set an auto-format for the cells(i.e dates, percentages and blank cells instead of “null”) from the java code and the performance went down during the export!

    I will keep in mind this topic and try to figure out if we can set an export that contains the format of the cells without loosing performance.

    Let me know if you have any other questions!
    Best Regards,
    Luca

  • http://twitter.com/xcelsiusgurus/status/2762952895 The Xcelsius Gurus

    Reading: "Customize your Xcelsius dashboard with Export2Excel solution" – http://alturl.com/6gdo

  • http://twitter.com/exceldashboards/status/2850349789 Excel Dashboards

    Customize your Xcelsius dashboard with Export2Excel solution from … http://bit.ly/PrPST

  • Daniele

    Hi,
    it’s possible to set the name of file generated on server?
    Best Regards,
    Daniele

  • http://www.clearpeaks.com Luca Spinelli

    Hi Daniele,
    there are two available options:
    1 – (easy and quick): you will just change the name of the file once you download it
    2 -(easy but with java customization) you open the jsp page of the export2excel, and where you find temp.xls you can set the name that you prefer. Also, in the web folder you will find a real excel file called temp.xls, make sure that it will have the same name you set in the java code.

    Thanks for using our product!
    Best Regards,
    Luca

  • http://twitter.com/exceldashboards/status/4726794670 Excel Dashboards

    Blogs: Customize your Xcelsius dashboard with Export2Excel solution from … http://bit.ly/qMvLG

  • http://www.reportex.co.uk Charles

    Hi Luca

    I’ve followed the instructions and have the appropriate crossdoamin policy file in my Tomcat ROOT folder but I still get the dreaded an Error #2032 when I hit refresh in preview or as a swf file.

    Any ideas??

    Many thanks
    Charles

    • fred

      Hi Charles,
      I’m confronted to the same issue. Did you solved it ?

  • http://twitter.com/kalyanverma/status/4891687559 kalyanverma

    Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw

  • http://www.clearpeaks.com Luca Spinelli

    Hi Charles,
    I would check the following things:
    1) if the name of the url is correct;
    2) if your firewalls are up and running;

    This issue is not related to the crossdomain.xml The error #2032 is a stream error and often happens because the flash file (Xcelsius in this case) cannot reach the connection.

    Hope this helps!
    Cheers,
    Luca

    • Luther

      We ran into the stream error and we noticed that it was happening because there are a few “&” in the data. We removed the “&” from the data and it started working fine. I think we will see the same behaviour for any XML specific characters like “!”, “”, “&” etc.

  • Bill

    Hi thats a great post thanks luca
    i have tried all the steps but i am confused about Enable Load’s Range it should be one cell with URL to targeted Excel file where we need to dump data am i right?

    (In other words URL of Excel file which is saved in any folder of my infoview) Can some body please help me with this

  • http://twitter.com/xcelsiusgurus/status/4911198036 The Xcelsius Gurus

    RT @kalyanverma: Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw

  • http://twitter.com/mybpc/status/4911280974 myBPC

    RT XcelsiusGurus RT @kalyanverma: Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw

  • http://twitter.com/mybpc/status/4911285296 myBPC

    RT @XcelsiusGurus RT @kalyanverma: Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw

  • Bill

    Hay now i am able to open the excel file through a url button which is pointed to temp.xls. But it is showing me a blank excel file some how it is not taking data from the cells in xlf file can somebody help me with this

  • http://www.clearpeaks.com Luca Spinelli

    Bill,
    the load range is only one cell, but the send range is the entire range you need to export.

    If you have issue during your export, try to make a snaphot in excel of your dashboard and check the following:
    1) if there is data in the range you specified as “send”
    2) if there are special caracters (try to not use special caracters)

    Let me know what happens and we (you and me!) will solve the issue together!

    Hope this helps,
    Luca

    my email: luca.spinelli@clearpeaks.com

  • http://www.reportex.co.uk Charles

    Hi Luca

    Yes the name of the URL is correct and there is no Firewall involved as Xcelsius and the server arte on the same box.

    If I paste the URL (http://:8080/ExportToExcelv2/index.jsp)into a browser I get an HTTP Status 500 Exception Error.

    Any further ideas?

    Many thanks
    Charles

  • http://www.clearpeaks.com Luca Spinelli

    Hi Charles,
    if you paste the url in a link it gives you this error because the page tries to get an xml that doesn’t exist…(because xcelsius is not sending out anything!).

    I couldn’t see the name of the machine from your link but can you try with the url ->localhost:8080/ExportToExcelv2/index.jsp ???

    Can you also try to use your IP:8080 ???

    Let me know if there are improvements.
    Feel free to contact me also by email.

    Cheers,
    Luca

  • Nimesh

    Hi Luca,
    Thanks for sharing this utility, it worked.

    Here is thing does it work the same way with QAAWS also, because if i have hardcoded data in excel then its working fine but if i have QAAWS it shows blank excel file.

    Can you please giude if i am missing something.

    Thanks,
    Nimesh.

  • http://www.clearpeaks.com Luca Spinelli

    Hi Nimesh,
    what happens if you make a snaphot from the preview of your dashboard?
    Can you see the data in the range you want to export?

    Let me know your comments.
    Best Regards,

    • Nimesh

      Hi Luca,
      Thanks for the reply.

      I took the snapshot from preview and i am able to see the data in excel.

      I hav multiple worksheet and for out put i have select only one worksheet.

      Thanks,
      Nimesh.

  • http://Www.clearpeaks.com Luca Spinelli

    Hi Nimesh,
    I will try to reproduce your issue in my environment! In the meantime, please make sure that you don’t have special caracters in you export data and that all the settings are fine (I know you checked them but a double check is always healthy!)

    I will keep you posted!
    Best Regards,
    Luca

    • Nimesh

      Hi Luca,

      I don’t have any special character in worksheet but i all the cells which i need to display in the worksheet it has if condition.

      Thanks,
      Nimesh.

  • fanqi

    It’s great and easy to use!
    And It Expanded my ideas,thank you!
    But I think that it is necessary to add “request.setCharacterEncoding(“UTF-8″);” at the front of “BufferedReader urlLine = request.getReader();” in “index.jsp”。If the String that contains Chinese code,messy code will appear.

  • http://Www.clearpeaks.com Luca Spinelli

    Hi fanqi,
    thanks for you feedback and suggestions! I will modify it and release a new version. Keep me posted on your expanded ideas ;-) !! And if you prefer, you can contact me directly at Luca.spinelli@clearpeaks.com

    Have a great week end!
    Luca

  • Bill

    Hi how you guys doing. i have done that in xcelsius 2008 but now i am trying it in xceslius 4.5 with xml connector but it is not working any one have any idea why it is not working in 4.5 ??????

  • Mike

    Hi Luca,

    I have couple of questions regarding this solution:

    1. How does this handle in a clustered web server environment?
    2. How will this solution work in the concurrency. Say User A, and User B, click at the same time, with different data written on the file.

    Thanks for your help.

  • Dan

    hi Luca,

    I have a weird behavior. On trying to export the excel data multiple times to the application, sometimes I see that I get an old xml file instead of the new one written on the server. Can you explain this behavior?

    Thanks in advance for your help

  • http://www.clearpeaks.com Luca Spinelli

    Hi Dan,
    this is quite weird. I think this is something that keeps on staying in your tomcat temp folder… I will investigate on that and let you know!

    @Mike: Could you please do this test and let me know if you have problem with the multitask?

    Cheers,
    Luca

  • Dan

    hi,

    How does this application handle concurrent users? Since this application writes an xls file on the web server and when there are concurrent requests like request1 and request2 or 1 and 2 follows immediately the request2 overwrites the data from request1 and request1 gets the exported xls file from request2. Can you guys let me know how to handle this problem? or is Xcelsius capable of handling httpresponse instead of just sending the link to the temporary xls file?

    thanks in advance.

  • http://www.clearpeaks.com Luca Spinelli

    Hi guys,
    apologies for being late with my answer. At the moment this solution doesn’t allow multitasks.
    We are going to release a new version that will allow the use of accents like öüÿï and arabic, japanese and chinese.

    We are already thinking about this new requirement by the way. If you already have some ideas about how to make it multitasking it will be appreciated.

    We release it as open source even because it would be great whether together we can make it more powerful and useful!

    Best Regards,

  • Bill

    Hi i deployed this solution and it was working fine on internet explorer 6 but it does not open any thing in IE7 and IE8 if you will click on export to excel it will show you it is opening another window but suddenly it will be disappeared. Any suggestions????????????

  • http://www.clearpeaks.com Luca Spinelli

    Hi Bill,
    could you please check the settings of your browser? Probably some of the actions needed to popup the export message are not allowed…

    Let me know your comments.
    Best Regards,
    Luca

  • Rama Krishna

    Hi Luca

    I’ve followed the instructions described in the post, but I’m getting an Error #2032 when I hit ‘Refresh’ button.

    My servers are on the same domain and as mentioned in your replies above there aren’t any special characters in the data, that I’m trying to export. It is 2 rows of data with Country_Name and Country_ID as shown below

    C_ID Country
    —- ——-
    100 UK
    101 USA

    Regards
    Rama Krishna

  • iIshani

    Hi Luca
    We have created a Xcelsius dashboard interfacing with BW. We need to publish the Xcelsius dashboad off-line.. meaning we need to export the dashboard to PDF format and need to publish the same dashboard in PDF format to end users who are not having access to BW. How do we do this ? Finally when we publish the PDF file to end users , system should not ask for user id and pwd of bw….. pls help

  • http://www.clearpeaks.com Luca Spinelli

    Hi Guys,
    quick notice for all of you. At the moment we are having some issue with the Export To Excel used within Xcelsius 2008 SP3. It always shown a #2032 error. We are investigating on it. If in the meantime you have some idea please contact me directly on my email (luca.spinelli@clearpeaks.com).

    Hope to give you good news very soon.

    Cheers,
    Luca

  • Scott Lassiter

    Prior to SP3 all XML data was sent on one line:

    some data

    Now in SP3, the XML data is sent across multiple lines:

    some data

    In the JSP file, change the code for reading the XML data from

    String exported = (String) urlLine.readLine();

    to

    String exported = (String) urlLine.readLine();
    while (urlLine.ready()) { exported += (String) urlLine.readLine(); }

    and the problem with SP3 is resolved.

  • Rama Krishna

    Hi Scott,

    Thanks for suggesting the code change.
    The problem was resolved, when I made the mentioned change in the JSP file.

    I am using XCelsius 2008 SP3.

    Thanks
    Rama Krishna Kota

  • http://www.clearpeaks.com Luca Spinelli

    Hi Scott,
    once more thanks for your precious suggestion. It works perfectly!!!! Make the feature stable and powerful is the objective of this free code. Many Thanks for your collaboration!

    Best Regards,
    Luca

    • Scott Lassiter

      I am glad to help. Thank you for providing this free application!

  • Scott Lassiter

    This application can also be deployed on SAP NetWeaver 7.0 running Java 1.4.2. You will need to add xml-apis.jar to the lib directory of the WAR file and then wrap the WAR in an EAR for deployment. I have not yet deployed this application on SAP NetWeaver 7.1 running Java 5.

  • Srikanth

    Thanks Luca and Scott,
    I will try to change my JSP file,
    I am having problem #2032, hope it will work,

    how will it work if want to export multiple sets
    Like one set from Sheet1 and other from a different sheet.
    this is the first time I am using this Utility, and I am trying on SP3

  • keats

    Hi Luca & Scott

    Great export feature, works fine with SP3 and the code modif suggested above.

    One question: How have you implemented the combined feature of Conn Refresh and URL button ?

    thanks

  • http://www.clearpeaks.com Luca Spinelli

    Hi Keats,
    yes it is possible to combine the two features.
    You can show only the refresh button and set the “load”and “idle” option for the connection.(i.e. you will send load and idle to the cell X).
    Then you can set the “Trigger” cell feature for the URL Button (where you set the export link!) specifying that when X is idle it has to work. then you can hide the url behind the refresh connection and that’s it!!!!

    I hope I was clear enough otherwise fell free to contact me at any time!

    Cheers,
    Luca

  • http://www.clearpeaks.com Luca Spinelli

    Just two remarks (I was writing very fast and I did not explain it very well…)

    Hide means “send to back” in this case.

    Let me know your comments.
    Cheers,
    Luca

  • keats

    Perfect!

    thanks indeed. It works with multiple ranges as well

  • Pingback: Export2Excel v3 for Xcelsius 2008 SP3 (Tomcat version) « ClearPeaks Blog

  • http://www.clearpeaks.com Luca Spinelli

    Guys,
    as you can see from the post above we released the new version of the ExportToExcel and it is available on our website. This is to make easier the use of this feature without the need of modifying the code for all of you.

    Thanks again to Scott and the user fanqi for their precious contribution.

    Cheers,
    Luca

  • Yday

    Hi Luca,

    Thanks a lot for the program. We tried deploying it in the server, however, when i try to execute the URL interactively, it is giving a jsp compile error. Seems like some jars are missing. Generated servlet error:
    C:\Program Files\Business Objects\Tomcat\work\Catalina\localhost\ExportToExcel3\org\apache\jsp\ExportToExcel3\index_jsp.java:8: package org.apache.poi.hssf.usermodel does not exist
    import org.apache.poi.hssf.usermodel.*;

    I dont have much exposure to JAVA. Can you please let me know if I need to do anything specific.

  • Andy

    I am loving the concept of this product, but I have a query…on my URL button, if I reference a cell which has the file url in it, I get a warning from Excel stating “The file you are trying to open ‘temp.xls’ is in a different format than specified by the file extension…etc etc. If I then open the file, it is empty. If I hard code the same url into the url button the excel file opens and the values I selected appear in it…anyone know what I’m doing wrong ? I’m using SP3 and ExportToExcel v2 (with the jsp code change)

  • Ann Merin

    Hi,
    i require ur help in resolving my issue with export to excel. My url is containing special characeters like £ and &. while encoding the url some additional characters getting loaded and excel is returning blak page. how to resolve this.