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_<MachineName> 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
