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
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.
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.
Customize your Xcelsius dashboard with the Export2Excel solution from ClearPeaks http://tinyurl.com/nob5fg
Reading : Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks: In this post Lu.. http://tinyurl.com/nob5fg
It’s great and really easy to use !
However, do you think an Excel’s export could keep colors and forms ?
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
Reading: "Customize your Xcelsius dashboard with Export2Excel solution" – http://alturl.com/6gdo
Customize your Xcelsius dashboard with Export2Excel solution from … http://bit.ly/PrPST
Hi,
it’s possible to set the name of file generated on server?
Best Regards,
Daniele
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
Blogs: Customize your Xcelsius dashboard with Export2Excel solution from … http://bit.ly/qMvLG
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
Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw
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
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.
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
RT @kalyanverma: Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw
RT XcelsiusGurus RT @kalyanverma: Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw
RT @XcelsiusGurus RT @kalyanverma: Customize your Xcelsius dashboard with Export2Excel solution from ClearPeaks http://bit.ly/2rHadw
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
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
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
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
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.
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,
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.
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
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.
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.
Hi fanqi,
!! And if you prefer, you can contact me directly at Luca.spinelli@clearpeaks.com
thanks for you feedback and suggestions! I will modify it and release a new version. Keep me posted on your expanded ideas
Have a great week end!
Luca
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 ??????
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.
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
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
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.
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,
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????????????
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
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
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
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