Cross Table to HTML

Here is an example of how to extract the data from a Cross Table visualization to format the way you want it with HTML. The downside is that the output is not dynamic or interactive when filtering data, so and the script needs to be run to refresh the data.

# The way this scrip works is
# 1 Export Cross Table data into a temp file
# 2 Read temp file and create html
# 3 Write html into a Text Area
# Note: viz and textArea are script parameters

from System.IO import Path, StreamWriter
from Spotfire.Dxp.Application.Visuals import CrossTablePlot

#1 Export Cross Table data into a temp file
#1.a Temp file for storing the cross table data
tempFolder = Path.GetTempPath()
tempFilename = Path.GetTempFileName()

#1.b Export CrossTable data to the temp file
writer = StreamWriter(tempFilename)
viz.As[CrossTablePlot]().ExportText(writer)

#2 Read temp file and create html
#2.a open the temp file    
f = open(tempFilename)

#2.b Build the HTML
htmlReport = " <TABLE style='border:1px solid #C0C0C0;' cellpadding='2' cellspacing='0'>\n"
htmlReport += "<TR style='background-color: #C0C0C0;border:1px solid #C0C0C0;'>"
htmlReport += "<td style='font-weight:bold;'>2014 YTD Key Metrics</td><td colspan='3' style='width:175px;text-align:center;font-weight:bold;'>Recordable Injuries</td><td colspan='3' style='width:175px;text-align:center;font-weight:bold;'>High Potential(Hi-Po)</td><td colspan='2' style='width:175px;text-align:center;font-weight:bold;'>LOPC</td><td colspan='2' style='width:175px;text-align:center;font-weight:bold;'>Process Safety Events</td>"
htmlReport += "</TR>\n"

htmlReport += "<tr style='background-color: #D9D9D9;'>"
htmlReport += "<td></td><td style='font-weight:bold;text-align:center;'>Week</td><td style='font-weight:bold;text-align:center;'>MTD</td><td style='font-weight:bold;text-align:center;'>YTD</td><td style='font-weight:bold;text-align:center;'>Week</td><td style='font-weight:bold;text-align:center;'>MTD</td><td style='font-weight:bold;text-align:center;'>YTD</td><td style='font-weight:bold;text-align:center;'>Volume</td><td style='font-weight:bold;text-align:center;'>Count</td><td style='font-weight:bold;'>T1</td><td style='font-weight:bold;text-align:center;'>T2</td>"
htmlReport += "</TR>\n"

#rowCount variable is used to eliminate the first row from crosstable.  
rowCount=0
#firstColumn variable is used to eliminate the first column from crosstable.
firstColumn=""
#To identify the Subtotal row. 
isSubTotalRow=False

#reading line by line from the temp table
for line in f:
rowCount=rowCount+1
if rowCount>1:
htmlReport += "<TR>"
count=1
for col in line.split('\t'):
if count == 1:
if firstColumn != col:
htmlReport += "<tr style='background-color: #D9D9D9;'>"
htmlReport += "<td colspan='11' style='font-weight:bold;text-decoration:underline;border:1px solid #C0C0C0;'>" + col + ":"+"</td>"
htmlReport += "</TR>\n"
firstColumn=col
if count ==2:
#Highlight the background color for Sub total row.
if col == "Subtotal":
col = "Total " + firstColumn
isSubTotalRow=True
htmlReport += "<TD style='background-color: #C0C0C0;border:1px solid black;'>" +col+"</TD>"
else:
htmlReport += "<TD style='border:1px solid #C0C0C0;'>&nbsp;&nbsp;&nbsp;" +col+"</TD>"
elif count >2:
if col=="0":
col="-"
if isSubTotalRow==True:
htmlReport +="<TD style='background-color: #C0C0C0;border:1px solid black;'>"+col+"</TD>"
else:
htmlReport += "<TD style='border:1px solid #C0C0C0;'>"+col+"</TD>"
count=count+1
isSubTotalRow=False
htmlReport += "</TR>\n"
f.close()
htmlReport += "</TABLE>\n"

#3 Write html into a Text Area

from Spotfire.Dxp.Application.Visuals import HtmlTextArea

textArea.As[HtmlTextArea]().HtmlContent = htmlReport