Spotfire是一个比较人性化的可视化软件，在药企（大外企）数据临床数据管理可视化方面应用较好
Spotfire除了自带的一些常用操作功能外，其内嵌了ironpython和TERR；以下是这段时间来整理的，关于一些功能的ironpython的实现
Spotfire-Ironpython主要社区： https://community.tibco.com/wiki/ironpython-scripting-tibco-spotfire
以下是几个有用的例子：
Reset All Filters For All Filtering Schemes
重置所有过滤控件，主要先循环filtering schemes，再循环dataTable，这样保证所有控件对应表格的过滤控件都被重置了
from Spotfire.Dxp.Data import *
from Spotfire.Dxp.Application.Filters import *
def resetAllFilteringSchemes():
# Loop through all filtering schemes
for filteringScheme in Document.FilteringSchemes:
# Loop through all data tables
for dataTable in Document.Data.Tables:
# Reset all filters
filteringScheme[dataTable].ResetAllFilters()
# Call the function
resetAllFilteringSchemes()参考：How to Reset All Filters For All Filtering Schemes in TIBCO Spotfire® Using IronPython Scripting
Set document property
对于input field，指定具体数值作为初始值；其他单选document property应该也类似
Document.Properties["FUTUREONLY"] = "myvalue"对于list box（多选框），则以列表形式设置多个初始值
Document.Properties["standard1"]=["a","b","c"]参考：Spotfire IronPython set document property
Retrieve table column values
返回某个表格中某列值（或者非空值），存入列表中（spotfire包的列表）
from Spotfire.Dxp.Data import *
#get the data table
table=Document.Data.Tables["YourTableName"]
#place generic data cursor on a specific column
cursor = DataValueCursor.CreateFormatted(table.Columns["YourColumnName"])
#list object to store retrieved values
valData = List [str]()
#iterate through table column rows to retrieve the values
for row in table.GetRows(cursor):
#rowIndex = row.Index ##un-comment if you want to fetch the row index into some defined condition
value = cursor.CurrentValue
if value <> str.Empty:
print(value)
valData.Add(value)Retrieve data marking selection
返回某个表格中marking selection行的某列信息，选定表格和指定的列，然后循环赋值为字典存储
from Spotfire.Dxp.Data import *
#get the data table
one_table = Document.Data.Tables["taget table"]
#place generic data cursor on a specific column
cursor = DataValueCursor.CreateFormatted(one_table.Columns["colID"])
# Retrieve the marking selection
markings = Document.ActiveMarkingSelectionReference.GetSelection(one_table)
# Iterate through the data table rows to retrieve the marked rows
mark = {}
for row in one_table.GetRows(markings.AsIndexSet(),cursor):
value = cursor.CurrentValue
mark[value] = 1参考：How to retrieve data marking selection using IronPython in TIBCO Spotfire
Add Progress Bar
在执行ironpython脚本的时候增加进度条和取消按钮
from Spotfire.Dxp.Framework.ApplicationModel import *
import time
ps = Application.GetService[ProgressService]()
def execute():
try:
# subtask 1 - simulates an indeterminate step
ps.CurrentProgress.ExecuteSubtask("Subtask 1");
time.sleep(5) # do some work
ps.CurrentProgress.CheckCancel()
# subtask 2 - simulates a determinate step
start = time.clock()
stepCount = 10
with ps.CurrentProgress.BeginSubtask("Subtask 2", stepCount, "Step {0} of {1}") as f:
for step in range(0, stepCount):
if (time.clock() - start) > stepCount:
break
ps.CurrentProgress.CheckCancel()
time.sleep(1) # do some work
ps.CurrentProgress.TryReportProgress()
except: # user canceled
pass
ps.ExecuteWithProgress("Progress title", "Progress description", execute)参考：How to Add Progress Bar and Cancellation Option when Executing IronPython Scripts in TIBCO Spotfire
Creat or update certain column of table
新建或者更新表格，先设定表格的列数，接着将列名写入MemoryStream，然后csvWriter.Flush()关闭csvWriter，最后通过自定义函数LoadCSV生成/更新表格，最主要的是loadCSV函数中的列属性的设定
from Spotfire.Dxp.Data import *
from System.IO import FileStream, FileMode, File, MemoryStream, SeekOrigin, StreamWriter
from Spotfire.Dxp.Data.Import import TextDataReaderSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource
def LoadCSV(dataTableName, stream):
settings = TextDataReaderSettings()
settings.Separator = ","
settings.AddColumnNameRow(0)
settings.ClearDataTypes(False)
settings.SetDataType(0, DataType.String)
settings.SetDataType(1, DataType.String)
stream.Seek(0, SeekOrigin.Begin)
fs = TextFileDataSource(stream, settings)
if Document.Data.Tables.Contains(dataTableName):
Document.Data.Tables[dataTableName].ReplaceData(fs)
else:
Document.Data.Tables.Add(dataTableName, fs)
stream = MemoryStream()
csvWriter = StreamWriter(stream)#, Encoding.UTF8)
csvWriter.WriteLine("col1,col2\r\n")
for row in range(1,101):
csvWriter.WriteLine(value+",col2\r\n")
csvWriter.Flush()
LoadCSV("New Table", stream)参考：IronPython script to Add Rows With Zero
Reset the filters of an active data table
重置active table的filters
from Spotfire.Dxp.Application import Filters as filters
from Spotfire.Dxp.Application.Filters import TableGroupCollection as TableGroups
myPanel = Document.ActivePageReference.FilterPanel
for group in myPanel.TableGroups:
# to get the required table group from the filter panel
if group.Name == Document.ActiveDataTableReference.Name:
for col in Document.ActiveDataTableReference.Columns:
# reset each and every column of an
# active data table
myFilter= group.GetFilter(col.Name)
myFilter.FilterReference.Reset()
break
# breaking the outer for loop once the desired
# table group is found..参考：How to reset the filters of an active data table in TIBCO Spotfire® using IronPython Scripting
Output Filtering Scheme in Spotfire
输出每个page的过滤方案对应的name
for pg in Document.Pages:
print pg.Title # the page name
myPanel = pg.ActiveFilteringSelectionReference
print myPanel.Name # output is the filter nameRemove mask columns
删除所有mask类的column
from Spotfire.Dxp.Data import *
TableList = ["入组基本ForRZ", "入组检验ForRZ"]
for theTable in TableList:
theTable = Document.Data.Tables[theTable]
for column in theTable.Columns:
if column.Properties["ColumnType"].ToString() == "Mask":
#column.As[TagsColumn]().Freeze()
ColumnName=column.Name
theTable.Columns.Remove(ColumnName)参考：Column Properties Descriptions
Table exist
判断table是否存在
if Document.Data.Tables.Contains(dataTableName):
print 1Iron python script for creating filters in spotfire
通过ironpython对某个table新建一个filter
# using the default Filtering Scheme and the supplied Data Table name, get the filter by its Column name
filter = Document.FilteringSchemes.DefaultFilteringSchemeReference[dt][column]
filter.TypeId = FilterTypeIdentifiers.ListBoxFilter
# cast it as a ListBox filter
lb = filter.As[ListBoxFilter]()
# reset the filter to its default state
lb.Reset() #这个代码对的现在这个应用表格是无效的，因为过滤列名一直在变化
# set the values according to the script parameter
lb.SetSelection(selectedClss)
# OPTIONAL: select (true) or deselect (false) the "(All)" option
lb.IncludeAllValues = False
# OPTIONAL: select (true) or deselect (false) the "(Empty values)" option
lb.IncludeEmpty = False参考：Iron python script for creating filters in spotfire
Reverse sort order on filters
对某个table的指定列进行反向排序
def sortDates():
dateOrder = []
dataTable = Document.Data.Tables["数据表"]
dateVals = dataTable.Columns["A"].RowValues.GetEnumerator()
for d in dateVals:
dateOrder.append(d.ValidValue)
dateOrder.sort(reverse=True)
dataTable.Columns["A"].Properties.SetCustomSortOrder(dateOrder)
print dateOrder
sortDates()参考：Reverse sort order on filters
Write back to the database from Spotfire
参考：Write back to the database from Spotfire
Outout table column name and remove it
删除table中的指定列
cols = Document.Data.Tables["MyTable"].Columns
for c in cols:
if c.Name == "ThisIsACalculatedColumn":
cols.Remove("ThisIsACalculatedColumn")Visual table
将table以visual形式展示
from Spotfire.Dxp.Application.Visuals import *
newTable = Document.Data.Tables["合并表"]
vis = vis.As[Visualization]()
vis.Data.DataTableReference = newTable
# 2. Get all available columns
vis.TableColumns.Clear() #Clear first to prevent duplicate error
for i in newTable.Columns:
vis.TableColumns.Add(i)how-create-and-use-cursors-all-columns-one-time
参考：how-create-and-use-cursors-all-columns-one-time
IronPython Script to remove rows based on criteria
以特定条件下删除table中的某行
from Spotfire.Dxp.Data import *
table = Document.Data.Tables["CriteriaResultSummaryFormal (2)"]
cursor = DataValueCursor.CreateFormatted(table.Columns["住院号"])
RowCount = table.RowCount
# 构建每行的0/1判定，1表示True，而0表示False
rowsToFilter = IndexSet(RowCount, False)
#print rowsToFilter
for row in table.GetRows(cursor):
value = cursor.CurrentValue
if value == "1545714":
# 改变rowsToFilter列表中的对应行的0/1值，默认不加参数则是将0变成1,1即需要被后续删除的行
rowsToFilter.AddIndex(row.Index)
#print rowsToFilter
table.RemoveRows(RowSelection(rowsToFilter))参考：https://community.tibco.com/questions/ironpython-script-remove-rows-based-criteria
Overwriting a SBDF file from Spotfire using Iron Python
将table输出为library的SBDF，不存在则创建，已存在则覆盖
from Spotfire.Dxp.Framework.Library import LibraryManager, LibraryItemType, LibraryItem, LibraryItemRetrievalOption
dataTable = Document.Data.Tables.TryGetValue("Test")[1]
folder = "/spot1/"
fileName = "FirstExport"
lm = Application.GetService(LibraryManager)
#"/spot1/FirstExport"
(file_found, file_item) = lm.TryGetItem(folder+fileName, LibraryItemType.SbdfDataFile, LibraryItemRetrievalOption.IncludeProperties)
if file_found:
print "File is exist, rewrite it!"
dataTable.ExportDataToLibrary(file_item, fileName)
else:
print "File is inexistent, creat it!"
(folder_found, folder_item) = lm.TryGetItem(folder, LibraryItemType.Folder, LibraryItemRetrievalOption.IncludeProperties)
if folder_found:
dataTable.ExportDataToLibrary(folder_item, fileName)参考：Overwriting a SBDF file from Spotfire using Iron Python
How to replace existing data table with an SBDF file from Library
读取library中的SBDF，再将其replace某个已存在的表
from Spotfire.Dxp.Framework.Library import LibraryManager, LibraryItemType, LibraryItem, LibraryItemRetrievalOption
from Spotfire.Dxp.Data.Import import SbdfLibraryDataSource
from System import Guid
#First get the LibraryManager service instance.
manager = Application.GetService[LibraryManager]()
#You will need the GUID of your SBDF file as saved in the Spotfire library.
MyGuid = Guid('7a154208-4774-4eeb-9f3e-a0d94943ce8c')
#Search the library with the above GUID. If found, replace the data table, else do nothing.
(found, item) = manager.TryGetItem(MyGuid)
if found:
ds = SbdfLibraryDataSource(item)
Document.Data.Tables["Data Table"].ReplaceData(ds)或者：
folder = "/spot1/"
fileName = "FirstExport"
lm = Application.GetService(LibraryManager)
(file_found, file_item) = lm.TryGetItem(folder+fileName, LibraryItemType.SbdfDataFile, LibraryItemRetrievalOption.IncludeProperties)
ds = SbdfLibraryDataSource(file_item)
Document.Data.Tables["FirstExport"].ReplaceData(ds)参考：How to access and organize the Spotfire Library in TIBCO Spotfire® using IronPython Scripting
Many Cursor, output all columns values in simple code
通过列表的方式，将table中所有column的cursor存入其中，方便调用
from Spotfire.Dxp.Data import *
from System import Array
dataTable = Document.Data.Tables["Test"]
column_names = [col.Name for col in dataTable.Columns]
CursList = []
for col in column_names:
CursList.append(DataValueCursor.CreateFormatted(dataTable.Columns[col]))
CursArray = Array[DataValueCursor](CursList)
for row in dataTable.GetRows(CursArray):
for i in range(CursList.Count):
print str(CursList[i].CurrentValue) + "\t"参考：How to get values from all the columns of the table in Spotfire using IronPython
Retrieve marking selection value in table
from Spotfire.Dxp.Data import *
dataTable = Document.Data.Tables["Test"]
markData = Document.Data.Markings["Marking"]
SelectedIndex = markData.GetSelection(dataTable).AsIndexSet()
targetValue = getSelection(dataTable, SelectedIndex, "columnName")[0]Get masking row value
获取mask指定的行对应的某个列值
dataTable = Document.Data.Tables["Test"]
rowIndexSet = Document.ActiveMarkingSelectionReference.GetSelection(dataTable).AsIndexSet()
#print rowIndexSet.IsEmpty != False
if rowIndexSet.IsEmpty == False:
target_BLH = dataTable.Columns["columnName"].RowValues.GetFormattedValue(rowIndexSet.First)
else:
target_BLH = None参考：Get Marked Row Value in Graphical Table (IronPython)
Delete all rows from a datatable
删除table中的所有行
from Spotfire.Dxp.Data import RowSelection, IndexSet
dataTable = Document.Data.Tables["DataTableName"]
dataTable.RemoveRows(RowSelection(IndexSet(dataTable.RowCount,True)))参考：How to delete all rows from a datatable in TIBCO Spotfire® using IronPython
Iterate over filtered rows
迭代依次读取已filter的table中的行
# Specify the data table used in Spotfire
dataTable = Document.Data.Tables["My Table"]
# Get a reference to the specified filtering scheme on the data table above
dataFilteringSelection = Document.Data.Filterings["My Filter Scheme"]
filteringScheme = Document.FilteringSchemes[dataFilteringSelection]
filterCollection = filteringScheme[dataTable]
# Filtered rows based on the scheme above
filteredRows = filterCollection.FilteredRows
# Specify the column in the data table to get the values
myColCursor = DataValueCursor.CreateFormatted(dataTable.Columns["My Col"])
# Iterate over the filtered rows
for row in dataTable.GetRows(filteredRows,myColCursor):
# cursorValue will now contain the value for the column at the current row position
cursorValue = myColCursor.CurrentValue参考：IronPython script to iterate over filtered rows in TIBCO Spotfire
How to get or set a specific filter using IronPython script
参考：How to get or set a specific filter using IronPython script in TIBCO Spotfire®
Get active page and filterPanel
获取active page以及table，这里的active是指正在使用的或者点击的page以及table
from Spotfire.Dxp.Application.Filters import *
#Get the active page and filterPanel
page = Application.Document.ActivePageReference
print page.Title # the page name
filterPanel = page.FilterPanel
print filterPanel.Name # output is the filter name
#Get reference for FilteringScheme used for your filter
for fs in Document.FilteringSchemes:
if fs.FilteringSelectionReference.Name == "Filtering scheme":
filterPanel.FilteringSchemeReference = fs本文出自于http://www.bioinfo-scrounger.com转载请注明出处