Replacing a data table with an excel worksheet using IronPython.

[Paul MInogue 2]

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:Usersdatatest.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.spotfire.com/questions/how-replace-data-table-using-ironp...).

When I run this code I firstly recieve the following errorImportError: 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)

[Shandilya Peddi]

Hellominoguep,

 

You will have to import the System.Windows.Forms after you add the CLR reference. So you will have to change the imports as below,

----------------------------------

import clr

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

from System.Windows.Forms import OpenFileDialog

from Spotfire.Dxp.Data import *

----------------------

[Shandilya Peddi]

Hellominoguep,

Below is an sample script that you can use to replace data tables from excel ,

##########################################################################

from Spotfire.Dxp.Data import DataTableSaveSettings

import clr

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

from Microsoft.Office.Interop import Excel

 

# Assuming data tables in this analysis have the same name as the workbook spreadsheets.

dataTablesToUpdateFromExcel = ["sheet1", "sheet2", "sheet3", "sheet4"]

# Local path and name of the excel file.

filename= "C:\TestData\TestData.xlsx"

# Microsoft Excel application

excelApp = Excel.ApplicationClass()

excelApp.Visible = False

workbook = excelApp.Workbooks.Open(filename)

# Perform the data import

for entry in dataTablesToUpdateFromExcel:

# Rotate the spreadsheets to the beginning of the file

workbook.worksheets(entry).move(workbook.worksheets(1))

workbook.save()

# Find out if a data table already exist in the DXP file

status, table = Document.Data.Tables.TryGetValue(entry)

# Create data source

ds = Document.Data.CreateFileDataSource(filename)

ds.IsPromptingAllowed=False

# Replace data table with new data if found

if status:

table.ReplaceData(ds)

# Add a new data table, if corresponding table is not found

elif not status:

# Add table

myNewTable = Document.Data.Tables.Add(entry,ds)

# Keep the data embedded or linked

# Embedded

#settings = DataTableSaveSettings (myNewTable,False, False);

# Linked

settings = DataTableSaveSettings (myNewTable,True, False);

Document.Data.SaveSettings.DataTableSettings.Add(settings);

# Exit MS Excel

excelApp.Workbooks.close()

excelApp.quit()

############################################################################

 

Disclaimer:

The script code in this article is only a sample to be used as a reference. It is not intended to be used "As Is" in a Production environment.Always test in a Development environment.Make modifications to the script per your implementation specifications that suit best your business requirements. Refer to the API reference(s) cited in this article for usage of the classes and methods used in the script.

[Paul MInogue 2]

Thanks a million, will give this a try!

[Aditya Singh 3]

I am using the above script but not able to replace all datatables, it's replacing all data tables with same sheet from excel file. Any help will be appreciated.

 

My tables are different sheets of same excel file.

[Bhargava Reddy Chinakonda Chenchu]

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