Spotfire ironpython示例小结

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 name

参考:https://stackoverflow.com/questions/21633438/need-ironpython-code-to-output-filtering-scheme-in-spotfire

Remove 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 1

Iron 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转载请注明出处

文章目录
  1. 1. Reset All Filters For All Filtering Schemes
  2. 2. Set document property
  3. 3. Retrieve table column values
  4. 4. Retrieve data marking selection
  5. 5. Add Progress Bar
  6. 6. Creat or update certain column of table
  7. 7. Reset the filters of an active data table
  8. 8. Output Filtering Scheme in Spotfire
  9. 9. Remove mask columns
  10. 10. Table exist
  11. 11. Iron python script for creating filters in spotfire
  12. 12. Reverse sort order on filters
  13. 13. Write back to the database from Spotfire
  14. 14. Outout table column name and remove it
  15. 15. Visual table
  16. 16. how-create-and-use-cursors-all-columns-one-time
  17. 17. IronPython Script to remove rows based on criteria
  18. 18. Overwriting a SBDF file from Spotfire using Iron Python
  19. 19. How to replace existing data table with an SBDF file from Library
  20. 20. Many Cursor, output all columns values in simple code
  21. 21. Retrieve marking selection value in table
  22. 22. Get masking row value
  23. 23. Delete all rows from a datatable
  24. 24. Iterate over filtered rows
  25. 25. How to get or set a specific filter using IronPython script
  26. 26. Get active page and filterPanel
|