Export data out of Xcelsius dashboards

Accessing data in Xcelsius is pretty easy. You can have static data loaded from Excel, or dynamic data loaded through Web Services, Excel XML Maps, QaaWS (Query as a Web Service), Crystal Reports, Web Intelligence Reports or queries using Live Office.

There are however only a few ways to export data from Xcelsius. Either we could use Flash variables or use the XML Data Button to export data out of the compiled .swf file.

Here I present a simple method to export data from an Xcelsius .swf to a csv/Excel file. This method utilizes the XML Data Button’s capability to send and receive XML data.

The source code is provided at the end of this post as a download. Copy the code from the PDF and save as export2csv.aspx on the web server.

Note that this is an ASP.Net (.aspx extension) file, which works with an IIS Web Server. If you wish to use this code, please remember to change the links to point to your webserver and the appropriate directories.
For being able to write the exported data to a directory, you may need to grant Write access to the IUSR_ account to allow anonymous access.

After you’ve placed the export2csv.aspx file on your webserver, perform the following steps in your XLF file.
1. Add an XML data button [ Xcelsius 4.5 ]/Connection Refresh button [ Xcelsius 2008 ] and check the “Enable Send” box. Make sure that in the MIME Type dropdown, “application/x-www-form-urlencoded” is selected.

2. Type out the URL of the export2csv.aspx file in the XML Data URL and add the ranges of data that you need to export. The screenshot above shows 2 ranges being exported.
3. Check the “Enable Load” box and add a single cell range (Sheet1!$J$1 in the example) to receive the URL of the CSV file that will be returned by the export2csv.aspx file.
Note that the XML variable name returned by the export2csv.aspx is exportdataurl, so you need to name the range the same. If you wish to change the name, you’ll need to update the same in the export2csv.aspx file.


4. Add a URL link button and set its URL to the cell defined in the previous step (Sheet1!$J$1)
5. Export the .swf file and try out exporting the data by clicking on the Connection Refresh Button and then opening the file by clicking on the URL link Button.

Now you can easily export data from any .swf that you’ve created in Xcelsius.

How does this work?
1. The XML Data Button exports the ranges you’ve selected in an XML file to our export2csv.aspx file.
2. Our export2csv.aspx file parses and saves this XML in a .csv format on the web server.
3. The path of the .csv file is returned to the .SWF file through the “Enable Load” range.
4. The URL Link component retrieves the URL from this cell and opens the .CSV file when clicked.

Source code:

<% '----------------------------------- 'Name: export2csv.aspx 'Author: Maloy Manna 'Date: 04.26.2008 'Purpose: To export data out of Xcelsius .swf in csv format 'Input: Xcelsius data manager Send ranges in XML format 'Output: CSV output file URI 'Last Modified: 'Modification: '----------------------------------- 'Variable declarations Dim sXMLString as String Dim sFormitem as String Dim sFileName as String Dim sRPath as String Dim sPath as String Dim sXML as String Dim iRangeLength As Integer Dim iRowLength As Integer Dim iRangeCounter As Integer Dim iCounter As Integer Dim oFile as File Dim oWrite as StreamWriter Dim oXML as Object Dim oRNodeList as Object Dim oRNode as Object Dim oNodeList as Object Dim oNode as Object 'Get XML that was sent from SWF file For Each sFormItem In Request.Form sXMLString = sXMLString & sFormItem & " = " & Request.Form(sFormItem) Next 'Load XML into object oXML = Server.CreateObject("MSXML2.DOMDocument") oXML.async = False oXML.validateOnParse = False oXML.loadXML(sXMLString) 'Create output file with timestamp in the name sFileName = "Exported_Data_" & DatePart("m",Now()) & DatePart("d",Now()) & DatePart("yyyy",Now()) & DatePart("h",Now()) & DatePart("n",Now()) & DatePart("s",Now()) & ".csv" oWrite= oFile.CreateText("C:\Inetpub\wwwroot\" & sFileName) 'Use XPath to get the data 'Find the number of ranges or variables to be exported sRPath ="/data/variable" oRNodeList = oXML.documentElement.selectNodes(sRPath) iRangeLength = oRNodeList.Length For iRangeCounter = 0 to iRangeLength - 1 oRNode = oXML.documentElement.selectSingleNode("/data/variable["+CType(iRangeCounter,String)+"]/row") 'Write out the data set for the range sPath = "/data/variable["+CType(iRangeCounter,String)+"]/row" 'Find the number of rows for the range oNodeList = oXML.documentElement.selectNodes(sPath) iRowLength = oNodeList.Length For iCounter =0 to iRowLength - 1 oNode = oXML.documentElement.selectSingleNode("/data/variable["+CType(iRangeCounter,String)+"]/row["+CType(iCounter,String)+"]/column") oWrite.Write(oNode.text) While Not(oNode.nextSibling Is Nothing) oWrite.Write("," +oNode.nextSibling.text) oNode = oNode.nextSibling End While oWrite.WriteLine Next 'Write a blank line before the next range starts oWrite.WriteLine Next oWrite.Close() 'Build XML data to return sXML = "" 'exportdataurl sXML = sXML & "" sXML = sXML & "" sXML = sXML & "" & "http://localhost/" & sFileName & "" sXML = sXML & "" sXML = sXML & "" 'close out the XML data string sXML = sXML & "" 'Set the Response type Response.Buffer = True Response.ContentType = "application/vnd.csv" Response.Write(sXML) %>

Download code as PDF: Code for export2csv.aspx

Update on 06 September 2009:

Several of you have asked me for the JSP version of the export code. Unfortunately, I don’t have the expertise to do this in JSP, but fortunately Luca Spinelli and Ivan Aguilar from ClearPeaks have created the JSP version. The .war file is available for free if you  send an email to info@clearpeaks.com (I’m unable to include the .war file it here due to lack of compressed file extension support in WordPress). Please get in touch with ClearPeaks, if you want the source code.

– Maloy

Advertisements

43 responses to “Export data out of Xcelsius dashboards

  1. Does something like this work with apache/tomcat? Can I also export data to a file on c:\?
    Thanks!!
    Joe

    Like

  2. Hi all,
    I’m also searching something like this code to export xcelsius file in excel using apache/tomcat. Please contact me if someone knows the way of.

    Thanks and regards.
    Luca

    Like

  3. Hi all, happy to say you that I ‘ve done it!!! export to excel using apache/tomcat (in other words jsp) is possible and works fine 🙂 !!!

    Regards,
    Luca

    Like

  4. Hi – Unfortunatley I cant get this to work – Xcelsius throws an Error #2032….. Both the SWFand ASPX are on the webserver.

    I have copied the ASPX code exactly as per the PDF…Any ideas?

    Like

  5. Hi Luca,

    Can you please share the code for export to excel using apache/tomcat?

    Regards,
    Vimal

    Like

  6. Hi Luca,

    I cant get this to work – Xcelsius throws an Error #2032.
    I put .aspx file in the webserver and the .aspx file contains the same thing in the PDF. I also placed the crossdomain.xml file in the webserver.

    Could you please help on this one.

    Thanks,
    Mandava…

    Like

  7. hi, some one make it works with IIS? I have the same problem with Chris, I think the problem is in the code. I would appreciate

    Like

  8. Hi, unfortunately i have the same problem with Jessy and Chris, if I checked “enable send” then it will throw #Error 2032 (Stream Error).. I also had changed crossdomain.xml in my IIS’s root folder but it ain’t help much.

    My flash player is version 10, is it the problem?

    Regards,
    Sandy

    Like

  9. I am having the same problem with error #2032. Weird thing is I have made movies using this method that have successfully exported csv files and are still working. However, new movies that I make are getting the error when I implement the same method of exporting. Movies are running from the same directory on the same server so I can’t see how this is a crossdomain.xml issue. This problem is driving me nuts.

    Like

  10. Hi.

    I followed all the steps and at the end I receive the #2032 error. I tried placing the crossdomain.xml in the website root folder but I still get the #2032 error. Is there a solution for this? Or how should I implement the export using the flash variables ?
    Many Thanks!

    Like

  11. Please shares the solution if anybody has solve it.. Thanks.

    Like

  12. Great news Babjee,

    Can you explain it to us? What Flash player, os, webserver do you use? and why some of us failed to do and the others can..

    Like

  13. Guys,
    the error #2032 is not related to the crossdomain.xml! If you are facing with this issue it means that Xcelsius cannot reach the URL. The possible reasons are 2:
    1) the url is wrong
    2) in your excel there are special caracters not allowed by XML (i.e. “&”…)

    Hope this helps,
    Luca

    Like

  14. Guys, by the way if you can’t get the code please contact us at info@clearpeaks.com

    Cheers,
    Luca

    Like

  15. #2032 is not happening because of crossdomain issues. It’s more related connection issues or delay in response etc.
    There are low price add-on components available in SAP market place which you can plug and play. Some of the vendors provides good support for deployment issues.

    Like

  16. Hi every body,
    it worked fine in 2o08. My requirment is export data from xceslius 4.5 when i tried same thing with xml connector it is not working can some boday please help me

    Like

  17. 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.

    Like

  18. Same error as everybody else, reconfigured IIS, BO, Xcelsius, Windows server and Security. Still nothing.

    Like

  19. Hi Dan,

    Are you able to find the way to save the xcelsius different scenrios in excel without overwriting the old ones.

    Like

  20. Hello. With a few modifications, I have this working in Xcelsius 2008. I previously also got this to work in 4.5. I am happy to answer anyone’s questions about how to get the functionality working.

    Like

    • Hi David,
      I am reieving the 2048 cannot access external data
      add a cross-domain policy.

      Any help would be appreciated

      Thanks

      Chris

      Like

  21. David,

    Can you share how you got this to work? Specifically, how you resolved the error #2032?

    Like

    • I am also facing this #2032 error With Xcelsius 2008… Could someone be so kind as to share the solutions he found to solve the problem and post them on the blog ? It will help lots of us.

      Thank you.

      Like

  22. Hi All,

    Can somebody provide me the modified code for the same? I dont understand what is to be done. Please help me

    Like

  23. David,

    Could you send me the info on how to get this to work for 2008.

    Like

  24. Hi can any body suggest me whether we can export the swf data to csv with out creating the file on the webserver. Actually i want the file to be directly downloaded to the client’s desktop or allow the client to save it some where in his local machine. Please help its urgent…

    Like

  25. Hi all
    I have tried the same but i am getting an error like ” XML datastructure must start and ecd with same entity”.

    Can any one help me to resolve this?

    Thanks in advance
    Ann

    Like

  26. Hi All,
    Can any one please provide the updated code, results in same #2032 Error.
    Thanks,
    Adil

    Like

  27. Hi All,

    I have deployed the .WAR file in tomact but getting below error, can you please help.

    org.apache.jasper.JasperException: Exception in JSP: /index.jsp:132

    129: // Write tempo xls file
    130: String realPath = request.getRealPath(“/”);
    131: FileOutputStream fileOut = new FileOutputStream(realPath + “temp.xls”);
    132: wb.write(fileOut);
    133: fileOut.close();
    134:
    135: // Building xml to return url to file

    Like

  28. I really wanted to get this working and I’m not a big code techie. I called the .aspx from the browser so I could see detail error info. The majority of my errors were from just simple pdf cut-and-paste issues of the code spanning multiple lines. Like the 2 lines: Node = oXML.documentElement.selectSingleNode(“/data/variable[“+CType

    (iRangeCounter,String)+”]/row[“+CType(iCounter,String)+”]/column”)

    are suppose to be 1 line. Then I just had to figure out which security role to give read/writer permission and I was off and running. Hope this helps others.

    Like

  29. Hello Everyone,

    It seems the .NET version is not working properly.
    Can anyone tell me about the why this line gives error
    oRNodeList = oXML.documentElement.selectNodes(sRPath)
    What needs to be done to fix this issue.

    Like

  30. Hello Everyone,

    Can anyone please send me the code for IIS and . Net if it is working for you

    Like

  31. luca – can you please help the blog members on how you got it worked out,we would appreciate your help,i am still getting the same 2032 error

    Like

  32. I was also getting frustrated with the 2032 error, so after some investigation I found the fix is to divorce the aspx file from the normal Visual studio header info and just use the raw file even if you edit it in Notepad. It’s the Body, Div etc tags that will not allow it to work.

    Here is my fully working version. It has a couple of mods to the file writing code and file naming.

    Hope it helps…

    <%
    '———————————–
    'Name: export2csv.aspx
    'Author: Maloy Manna
    'Date: 04.26.2008
    'Purpose: To export data out of Xcelsius .swf in csv format
    'Input: Xcelsius data manager Send ranges in XML format
    'Output: CSV output file URI
    'Last Modified:
    'Modification:
    '———————————–
    'Variable declarations
    Dim sXMLString As String = ""
    Dim sFormitem As String
    Dim sFileName As String
    Dim sRPath As String
    Dim sPath As String
    Dim sXML As String
    Dim iRangeLength As Integer
    Dim iRowLength As Integer
    Dim iRangeCounter As Integer
    Dim iCounter As Integer
    Dim oWrite As StreamWriter
    Dim oXML As Object
    Dim oRNodeList As Object
    Dim oRNode As Object
    Dim oNodeList As Object
    Dim oNode As Object
    'Get XML that was sent from SWF file
    For Each sFormitem In Request.Form
    sXMLString = sXMLString & sFormitem & " = " & Request.Form(sFormitem)
    Next
    'Load XML into object
    oXML = Server.CreateObject("MSXML2.DOMDocument")
    oXML.async = False
    oXML.validateOnParse = False
    oXML.loadXML(sXMLString)
    'Create output file with timestamp in the name
    'sFileName = "Exported_Data_" & DatePart("m", Now()) & DatePart("d", Now()) & DatePart("yyyy", Now()) & DatePart("h", Now()) & DatePart("n", Now()) & DatePart("s", Now()) & ".csv"
    sFileName = "Exported_Data.csv"
    oWrite = File.CreateText("D:\wwwroot\revdashboard\" & sFileName)
    'Use XPath to get the data
    'Find the number of ranges or variables to be exported
    sRPath = "/data/variable"

    oRNodeList = oXML.documentElement.selectNodes(sRPath)
    iRangeLength = oRNodeList.Length
    For iRangeCounter = 0 To iRangeLength – 1
    oRNode = oXML.documentElement.selectSingleNode("/data/variable[" + CType(iRangeCounter, String) + "]/row")
    'Write out the data set for the range
    sPath = "/data/variable[" + CType(iRangeCounter, String) + "]/row"
    'Find the number of rows for the range
    oNodeList = oXML.documentElement.selectNodes(sPath)
    iRowLength = oNodeList.Length
    For iCounter = 0 To iRowLength – 1
    oNode = oXML.documentElement.selectSingleNode("/data/variable[" + CType(iRangeCounter, String) + "]/row[" + CType(iCounter, String) + "]/column")
    oWrite.Write(oNode.text)
    While Not (oNode.nextSibling Is Nothing)
    oWrite.Write("," + oNode.nextSibling.text)
    oNode = oNode.nextSibling
    End While
    oWrite.WriteLine()
    Next
    'Write a blank line before the next range starts
    oWrite.WriteLine()
    Next
    oWrite.Close()
    'Build XML data to return
    sXML = "”
    ‘exportdataurl
    sXML = sXML & “”
    sXML = sXML & “”
    sXML = sXML & “” & “http://yourserver:8087/” & sFileName & “”
    sXML = sXML & “”
    sXML = sXML & “”
    ‘close out the XML data string
    sXML = sXML & “”
    ‘Set the Response type
    Response.Buffer = True
    Response.ContentType = “application/vnd.csv”
    Response.Write(sXML)
    %>

    Like

  33. I have just noticed that the text I posted here is missing the XML tags within the quotes so something on this forum removes them. If you want to see what is supposed to be in the aspx file from ‘Build XML data to return, then this site will show you… http://forums.asp.net/t/1532888.aspx/1

    Like

  34. Pingback: An error has occurred. For more information, contact the file creator or your system administrator. Error: Error #2032 Connection Type: XML Data | SES

Share your views

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s