Replacing a data table with an excel worksheet using IronPython.

Hi,

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 7.0.1.16)

 

1 Comment

Hello,

 

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 = ","

readerSet.AddIgnoreRow(0)

readerSet.AddIgnoreRow(1)

readerSet.AddColumnNameRow(2)

#ds=myDataManager.CreateFileDataSource(d1.FileName)

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 

Thank you,

Bhargava

chenchu.chinakonda - Sep 11, 2020 - 10:46pm
+ Add a Comment

(2) Answers

Login