Search code examples
export-to-excelexport-to-csvspotfire

Spotfire Webplayer crosstable export with title

I Have requirement to export Crosstable data along with Header in Spotfire.

1) First i tried direct excel export with location from temp folder or direct location like C:\Export\Text.xls. these are working perfect in Spotfire client. But in webplayer it is not working. It is throwing Access issue to the folder.

2) Secondly i tried, With help of other forums i developed a code to convert Crosstable to text area through Iron python script and HTML to excel through JAVA script. This is working perfect in webplayer chrome browser. But the issue here is it is working perfectly for the small data. but i have to export around 10 MB. so it hung in both client and webplayer.

Can anyone help me to fix this.

Script 1: Cross table to HTML and then to javascript for download. This script hangs due to size of the data. When i use small data it works perfect in chrome.

from Spotfire.Dxp.Application.Visuals import TablePlot, HtmlTextArea, 
CrossTablePlot
ta = visTA.As[HtmlTextArea]()
from System.IO import Path, StreamWriter
from System.Text import StringBuilder
from System.IO import *
tempFilename = MemoryStream();
tp = visDT.As[CrossTablePlot]()
writer = StreamWriter(tempFilename)
tp.ExportText(writer)
tempFilename.Seek(0,SeekOrigin.Begin);
#Build the table
sb = StringBuilder()
#Open the temp file for reading
f = open(tempFilename)
#add some scripting magic from CDN
html = ""
#build the html table
html += " <TABLE id='myTable'>\n"
html += "<THEAD>"
html += " <TR><TH>"
html += "Performance Attribution"
html += " </TH></TR> <TR><TH>"
html += Heading2
html += " </TH></TR> <TR><TH>"
html += Heading6
html += " </TH></TR> <TR><TH>"
html += Heading3
html += " </TH></TR> <TR><TH>"
html += " </TH></TR> <TR><TH><Font Size=3><B>"
html += Heading4
html += "</TD><TD></TD><TD></TD><TD>"
html += Heading5
html += "</TD><TD></TD><TD></TD><TD>"
html += "Attribution Analysis"
html += "</TD><TD></TD></B></Font></TH></TR>"
html += " <TR><TH>"
html += " </TH><TH>".join(f.readline().split("\t")).strip()
html += " </TH></TR>"
html += "</THEAD>\n"
html += "<TBODY>\n"
for line in f:
html += "<TR><TD>"
html += "</TD><TD>".join(line.split("\t")).strip()
html += "</TD></TR>\n"
f.close()
html += "</TBODY>\n"
html += "</TABLE>\n"
ta.HtmlContent = html

Script to Export from Text Area
==============================================================
jQuery.fn.fnExcelReport = function(options)
{
 var options = jQuery.extend({
    separator: ',',
    header: [],
    headerSelector: 'th',
    columnSelector: 'td',
    delivery: 'popup', // popup, value, download
    // filename: 'powered_by_sinri.csv', // filename to download
    transform_gt_lt: true // make &gt; and &lt; to > and <
 },
options);

var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
var textRange; var j=0;
//tab = $(this); // id of table
tab = document.getElementById('myTable');
for(j = 0 ; j < tab.rows.length ; j++) 
{     
    tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
    //tab_text=tab_text+"</tr>";
}

tab_text=tab_text+"</table>";

var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE "); 

 sa = window.open('data:application/vnd.ms-excel,' + 
 encodeURIComponent(tab_text));  
return (sa);
}

$(document).ready(function () {
$('table').each(function () {
var $table = $(this);

var $button = $("<button type='button'>");
$button.text("Download");
$button.insertBefore($table);

$button.click(function () {
var csv = $table.fnExcelReport({
delivery: 'value'
  });
  });
 });
})



#Script 2: This script is working in client and not in web browsers.

from System.IO import *
from System import Environment, Threading 
username = Threading.Thread.CurrentPrincipal.Identity.Name
import clr
clr.AddReference("System.Windows.Forms")
from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers 
from System.Windows.Forms import SaveFileDialog
from System.Diagnostics import Process, ProcessStartInfo
from Spotfire.Dxp.Application.Visuals import VisualContent
vc=Visuals.As[VisualContent]()  #Visuals = Script parameter for Table/Cross 
Table visualization
memStream = MemoryStream();
writer = 
Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter);
sWriter = StreamWriter(memStream);
#Exporting the data to Memory Stream
vc.ExportText(sWriter);  #exports data in tab separated text
sReader = StreamReader(memStream);
memStream.Seek(0, SeekOrigin.Begin);
tempFolder = Path.GetTempPath()
Filenm = "Fixed_Income_Performce_Attribution.csv";
str1='\\'
print str1
newtemp = tempFolder
print newtemp
filename=newtemp+Filenm
print filename
f=open(filename,"w+")
counter=0
j=0
str1=''
f.write("Percent of Total Holdings"+'\n')
f.write(Heading2+'\n')
f.write(Heading3+'\n')
f.write(Heading6+'\n')
f.write('\n'+'\n')
while (sReader.Peek()>=0):
line=[]
counter=counter+1 #counts the number of rows in dataset
a=sReader.ReadLine()
lines=a.split("\t")
for elem in lines:
    j=j+1 # counts the number of columns in dataset
    #print elem
    if str(elem).find(",")<>-1:
        elem='"'+elem+'"'  # escaping comma already present in string
    line.append(elem)
 str1 = ','.join(str(e) for e in line)
f.write(str1+'\n')
f.close();
MemoryStream.Dispose(memStream);
sReader.Close()
Process.Start(filename)

Thanks Venkatesh

Solution

  • For your first query (downloading to C:\Export\Text.xls); when this script runs on the web player, the files will be generated on the node manager machine (since this is where the IP script is executed).

    You could output the files to a network/ shared drive, perhaps? You would need to add the network drive path to the allowed path section of Spotfire.Dxp.Worker.Host.exe.config and ensure it can be accessed by the node manager.