Replacing a data table with an excel worksheet using IronPython.
I'm trying to write a script to replace data tables with worksheets from an excel file (sheets will have fixed names and dive locations). The script I'm using as a template is:
# Replace tables from file import clr from System.Windows.Forms import OpenFileDialog from Spotfire.Dxp.Data import * clr.AddReference("System.Windows.Forms") #get filename from file chooser d1 = OpenFileDialog() d1.InitialDirectory='C:\Users\data\test.xlsm' d1.ShowDialog() #setup data source from selected file myDataManager = Document.Data ds=myDataManager.CreateFileDataSource(d1.FileName) #replace a couple of data tables table1 = Document.Data.Tables["Table_1"] table1.ReplaceData(ds)
This code is based off a similar previous question (https://community.tibco.com/questions/how-replace-data-table-using-ironp...).
When I run this code I firstly recieve the following error ImportError: No module named Forms. Secondly, Is it possible to adjust this code to replace a data table with a sheet from an excel file?
Any information would be greatly appriciated, thanks!
(Spotfire version 126.96.36.199)
I am stuck with a problem where I get erroneous data in the first two rows of the excel sheet so spotfire is not detecting the namerow. Is there any method to specify the name rows and remove unwanted rows from the excel source fire while replacing a data table with it? like as in Textdatareadersettings
readerSet = TextDataReaderSettings()
#Here we set the delimiter used, which is Tab in most cases.
readerSet.Separator = ","
ds = TextFileDataSource(d1.FileName, readerSet )
This above code sets row 2 as header and removes rows 0 and 1 when read from a text file, but it does not work for excel.
Any help is appreciated