Pressing keys programatically

Why reinvent the wheel when you can send keystrokes? for example, you can filter out marked rows or add a new visualization by sending keystrokes programatically

import clr
clr.AddReference("System.Windows.Forms")

from System.Windows.Forms import SendKeys, Control, Keys

#Filter out Marked Rows same as 
SendKeys.Send("(^+M)") #Ctrl+M

#Create a Box Plot 
SendKeys.Send("^+{5}") #same as Ctrl+Shift+5



List of shortcuts

Ctrl+A Mark filtered rows. 

Ctrl+B Bookmarks.

Ctrl+C Copy the marked records to the clipboard as text.

Ctrl+D Duplicate active page.

Ctrl+E Unmark Resets the set of marked records. No records will be marked after you have executed this operation. 

Ctrl+F Find.

Ctrl+I Inverts the set of marked records. Marks all unmarked records and unmarks the set of marked records.

Ctrl+L or Alt+L Toggles legends of a visualization on or off.

Ctrl+M Filter to, Sets the selection to the marked records. Creates a temporary filter so that only the marked records are visible.  

Ctrl+N New Page.

Ctrl+O Open an existing file or a file to import.

Ctrl+P Print the active visualization. 

Ctrl+R Reset all filters.

Ctrl+S Save the present visualization in the same format as when opened.

Ctrl+T Create a new text area. 

Ctrl+U Duplicate visualization.

Ctrl+V Paste the clipboard contents into Spotfire. 

Ctrl+Y Redo. 

Ctrl+Z Undo.

Ctrl+W or Alt+F4 Close.

Ctrl+Shift+M Filter out 

Ctrl+1 Create a new table.  

Ctrl+2 Create a new cross table. 

Ctrl+3 Create a new bar chart. 

Ctrl+4 Create a new line chart. 

Ctrl+5 Create a new combination chart.  

Ctrl+6 Create a new pie chart. 

Ctrl+7 Create a new scatter plot. 

Ctrl+8 Create a new 3D scatter plot.

Ctrl+9 Create a new map chart.

Ctrl+0 Create a new graphical table. 

Ctrl+Shift+1 Create a new tree map. 

Ctrl+Shift+2 Create a new heat map. 

Ctrl+Shift+3 Create a new parallel Coordinate plot. 

Ctrl+Shift+4 Create a new summary table. 

Ctrl+Shift+5 Create a new box plot. 

F1 Help.

F12 Save file.

Alt+Enter or Ctrl+Enter Opens the Properties dialogue. 

Delete Deletes the set of marked records. 

Maximizing visualization

#Maximizes the active visualization

Application.Document.ActivePageReference.ApplyLayout(Layout.TileMode.Maximize)

Write back to the database from Spotfire

from Spotfire.Dxp.Data.Import import DatabaseDataSource
from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings
from Spotfire.Dxp.Application.Visuals import TablePlot
from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import RowSelection
from Spotfire.Dxp.Data import DataValueCursor
from Spotfire.Dxp.Data import DataSelection
from Spotfire.Dxp.Data import DataPropertyClass

rowCount = Document.ActiveDataTableReference.RowCount
rowsToInclude = IndexSet(rowCount,True)

#Get a cursor to the two columns we want to use. cursor1 is for the key column and cursor2 is for the column selected by the user input
cursor1 = DataValueCursor.Create[int](Document.ActiveDataTableReference.Columns["ProductID"])
cursor2 = DataValueCursor.CreateFormatted(Document.ActiveDataTableReference.Columns[whichCol])

#The following section will add a column to the database table using the name specified by the user. This assumes a column with this name does not already exist.
sqlCommand = "ALTER TABLE Products ADD " + colName + " varchar(50);"
dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",
"Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",sqlCommand)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp",ds)
Document.Data.Tables.Remove(newDataTable)

#The following section will update the specified column in the database using the key column in the where clause
sqlStr=""
for  row in  Document.ActiveDataTableReference.GetRows(rowsToInclude,cursor1,cursor2):
   value1 = cursor1.CurrentValue
   value2 = cursor2.CurrentValue
   sqlStr = sqlStr +  "UPDATE Products SET " + colName + "='" + value2 + "' WHERE (ProductID=" + str(value1)  + ");"

sqlCommand = "UPDATE Products "  + sqlStr + ";"
dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",
"Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",sqlCommand)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp",ds)
Document.Data.Tables.Remove(newDataTable)

Reference

Export Image from Visualization

from System.Drawing import Bitmap, Graphics, Rectangle, Point
from System.IO import Path
from Spotfire.Dxp.Application.Visuals import VisualContent

#1. Prepare canvas as a blank bitmap
w=640
h=480
bm = Bitmap(w,h)
g = Graphics.FromImage(bm)
r = Rectangle(Point(0,0), bm.Size)

#2. Paint visualization into canvas
#vis is a script parameter
vis.As[VisualContent]().Render(g, r)

#3. Save your canvas 
#tempFolder = Path.GetTempPath()
tempFilename = Path.GetTempFileName() + ".bmp"
bm.Save(tempFilename)
print "image saved as " + tempFilename

Add JSON or XML Data from a Web Service

XML example

import clr
clr.AddReference('System.Data')
import System
from System import DateTime
from System.Data import DataSet, DataTable, XmlReadMode
from System.IO import StringReader, StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Net import HttpWebRequest
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings

# get stock quotes data in XML format from Yahoo Finance API
tickerSymbol = Document.Properties["TickerSymbol"]
startDate = DateTime.Today.AddMonths(-3).ToString("yyyy-MM-dd")
endDate = DateTime.Today.ToString("yyyy-MM-dd")
uri = "http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.historicaldata where symbol = %22" + tickerSymbol + "%22 and startDate = %22" + startDate + "%22 and endDate = %22" + endDate + "%22&format=xml&diagnostics=true&env=store://datatables.org/alltableswithkeys"
webRequest = HttpWebRequest.Create(uri)
response = webRequest.GetResponse()
streamReader = StreamReader(response.GetResponseStream())
xmlData = streamReader.ReadToEnd()
stringReader = StringReader(xmlData)

# read desired data fields from XML into a DataSet
dataSet = DataSet()
dataTable = DataTable("quote")
dataTable.Columns.Add("Date", System.String)
dataTable.Columns.Add("Close", System.String)
dataSet.Tables.Add(dataTable)
dataSet.ReadXml(stringReader, XmlReadMode.IgnoreSchema)

# build a string representing the data in tab-delimited text format
textData = "Symbol\tDate\tClose\r\n"
for row in dataTable.Rows:
textData += tickerSymbol + "\t" + "\t".join(row.ItemArray) + "\r\n"

# make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

# set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = "\t"
readerSettings.AddColumnNameRow(0)
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetDataType(1, DataType.Date)
readerSettings.SetDataType(2, DataType.Currency)

# create a data source to read in the stream
textDataSource = TextFileDataSource(stream, readerSettings)

# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains("Stock Data"):
Document.Data.Tables["Stock Data"].ReplaceData(textDataSource)
else:
newTable = Document.Data.Tables.Add("Stock Data", textDataSource)
tableSettings = DataTableSaveSettings (newTable, False, False)
Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)


JSON example

import clr
clr.AddReference('System.Data')
clr.AddReference('System.Web.Extensions')
import System
from System import DateTime
from System.Data import DataSet, DataTable
from System.IO import StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Net import HttpWebRequest
from System.Web.Script.Serialization import JavaScriptSerializer
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings

# get stock quotes data in JSON format from Yahoo Finance API
tickerSymbol = Document.Properties["TickerSymbol"]
startDate = DateTime.Today.AddMonths(-3).ToString("yyyy-MM-dd")
endDate = DateTime.Today.ToString("yyyy-MM-dd")
uri = "http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.historicaldata where symbol = %22" + tickerSymbol + "%22 and startDate = %22" + startDate + "%22 and endDate = %22" + endDate + "%22&format=json&diagnostics=true&env=store://datatables.org/alltableswithkeys"
webRequest = HttpWebRequest.Create(uri)
response = webRequest.GetResponse()
streamReader = StreamReader(response.GetResponseStream())
jsonData = streamReader.ReadToEnd()
js = JavaScriptSerializer()
dataDict = js.Deserialize(jsonData,object)

# build a string representing the data in tab-delimited text format
textData = "Symbol\tDate\tClose\r\n" 
for quote in dataDict["query"]["results"]["quote"]:
     textData += tickerSymbol + "\t" + quote["Date"] + "\t" + quote["Close"] + "\r\n"

# make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

# set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = "\t"
readerSettings.AddColumnNameRow(0)
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetDataType(1, DataType.Date)
readerSettings.SetDataType(2, DataType.Currency)

# create a data source to read in the stream
textDataSource = TextFileDataSource(stream, readerSettings)

# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains("Stock Data"):
     Document.Data.Tables["Stock Data"].ReplaceData(textDataSource)
else:
     newTable = Document.Data.Tables.Add("Stock Data", textDataSource)
     tableSettings = DataTableSaveSettings (newTable, False, False)
     Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)

Source: http://spotfire.tibco.com/tips/2014/03/03/streaming-xml-and-json-data-from-the-web-directly-into-spotfire-clients-using-script-controls/

Connect to a DB Programatically via ODBC (PIOLEDB)

from Spotfire.Dxp.Data.Import import DatabaseDataSource
from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings

Category=Document.Properties["Category"]
State=Document.Properties["State"]

dbSettings=DatabaseDataSourceSettings("System.Data.Odbc","DSN=DSN Name","Dynamic SQL Query")

ds=DatabaseDataSource(dbSettings)

Document.Data.Tables["Test1"].ReplaceData(ds)


##Example using PIOLEDB
CSTR = (
   "Provider=PIOLEDBENT.1;"
   "Initial Catalog=DPDSDashboard;"
   "Data Source=piserver.americas.ent.yourcompany.net;"
   "Integrated Security=SSPI;Persist Security Info=False"
)
PROVIDER ="System.Data.OleDb"

SQL = (
 " SELECT * "
 " FROM [XXX].[A].[E] T1"
 " CROSS APPLY [XXX].[D].[T](T1.E, DATE('t+6h+59m'), DATE('t+6h+59m'), '1d')"
 " WHERE T1.Path LIKE N'\PROD\EF\HV\HV\%' AND T1.level=6"
)

from Spotfire.Dxp.Data.Import import DatabaseDataSource
from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings

dbSettings=DatabaseDataSourceSettings(PROVIDER,CSTR,SQL)

ds=DatabaseDataSource(dbSettings)
Document.Data.Tables["pi test"].ReplaceData(ds)

Add columns dynamically to a Data Table

In this example, we use a Property Control to add columns to a Table Plot Visualization. For this exercise, the Underlying data table must have 6 columns called 'col1,col2,..,col6'

Ingredients:
 * Data Table Visualization
 * Text Area
 *  Property Control

Preparation:

1) Add a Data Table Visualization and a Text Area. Edit the Text Area and add a List box (multiple select) control. Create a new property called 'groups' and add Fixed values for each item. The display name can be something like 'Group 1' and the value, a comma separated list of columns i.e. 'col2,col3,col4'.

2) Click on the Script button and add 1 tbs of the following script. If there is no script button, you would need to add a button to trigger the script:


#This script takes myDataTable Visualization as a script parameter
#It adds columns to a Data Table based on a property. The property has a comma separated list of columns for the data table to show.

from Spotfire.Dxp.Application.Visuals import TablePlot, VisualContent

from Spotfire.Dxp.Data import DataPropertyClass

#get underlying data table

dt=myDataTable.As[VisualContent]()

cols = dt.Data.DataTableReference.Columns

#remove all columns

dt.TableColumns.Clear()

#get document property

selection = Document.Data.Properties.GetProperty(DataPropertyClass.Document, "groups").Value

#parse columns from selection

for property in selection:

   for col in property.split(","):

      #add columns from document property (ignore duplicates)

      try:

         dt.TableColumns.Add(cols[str(col)])

      except:

         print col + " is already in table"

1. Add a Visualization Script parameter called 'myDataTable'

Create a Visualization Programatically

Bar Chart Example

#Creates a BarChart visualization from the default table
from Spotfire.Dxp.Application.Visuals import BarChart

#gets a reference to the default data table
#dataTable = Document.Data.Tables["yourTable"]
dataTable = Document.Data.Tables.DefaultTableReference

#Creates a BarChart 
chart = Application.Document.ActivePageReference.Visuals.AddNew[BarChart]()

#Configure the BarChart 
chart.Data.DataTableReference = myDataTable
chart.Title = "Test Bar Chart1"
chart.XAxis.Expression = "<"+str(dataTable.Columns[1])+">"
chart.YAxis.Expression = "Count(" + str(dataTable.Columns[0]) + ")"
chart.Legend.Visible = False

Table Plot Example

#Creates a TablePlot visualization from the default table
from Spotfire.Dxp.Application.Visuals import TablePlot

#gets a reference to the (default) data table
#dataTable = Document.Data.Tables["yourTable"]
dataTableReference = Document.Data.Tables.DefaultTableReference

#Creates a TablePlot
tablePlot = Application.Document.ActivePageReference.Visuals.AddNew[TablePlot]()

#Configure the TablePlot
tablePlot.Data.DataTableReference = dataTableReference
tablePlot.Title = "${DataTable.DisplayName}"

Duplicate a Visualization
myPage.Visuals.AddDuplicate(vis)  #vis is a script parameter

Exporting to Excel (from client only)

# Script that exports a file from client (not webplayer)

import System

from System.IO import FileStream, FileMode

from Spotfire.Dxp.Application.Visuals import TablePlot

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

import clr

clr.AddReference("System.Windows.Forms")

from System.Windows.Forms import SaveFileDialog

# GETS THE FILE PATH FROM THE USER THROUGH A FILE DIALOG

SaveFile = SaveFileDialog()

SaveFile.Filter = "Spotfire Text Data Format (*.xlsx)|*.xls"

SaveFile.ShowDialog()

for vis in Application.Document.ActivePageReference.Visuals: 

if  vis.Title == "ExportKPI" : 

vizTable = vis.As[TablePlot]()

if SaveFile.FileName=="":

    # user does not select a file (cancel button)

    saveFilename = ""

else:

    saveFilename = SaveFile.FileName

    print "saveFilename=", saveFilename

    # Export Table data to the file

    try:

        stream = FileStream(saveFilename, FileMode.Create)

        vizTable.ExportData(DataWriterTypeIdentifiers.ExcelXlsxDataWriter, stream)

        #vizTable.As[TablePlot]().ExportData(DataWriterTypeIdentifiers.StdfDataWriter, stream)

        print "stream.Length = ", stream.Length

    finally:

        stream.Dispose()

Disable sorting on a cross table (no script)

You can suppress the sorting if you do add a second hierarchy level to the "y"-Axis of the Crosstable. Try adding your "y"-Axis a second time. With this you are creating a hierarchy and the cells are sorted in their subsection, which is only 1 item - so it will not be sorted ;-) I know this is a little trick, but it helps sometimes... You can actually just add a " " (Space) as the first hierarchy level.

Thanks Christof!