Using IronPython to export markedd data to Excel file results in an empty file

[Georgi Koemdzhiev]

I am trying to write an IronPython code that exports the marked items from a MapChart (i.e. select a few data points from the map) to an Excel (xls) file. The code below downloads the file onto the desired location but it is empty (i.e. only the headers are stored in the excel file

 

That is the current code I have:

from System.IO import File

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

 

writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter)

table = Document.Data.Tables['WellsData']

filtered = Document.ActiveFilteringSelectionReference.GetSelection(table).AsIndexSet()

stream = File.OpenWrite("C:UsersuserDesktopdropdownexport.xls")

names =[]

for col in table.Columns:

names.append(col.Name)

writer.Write(stream, table, filtered, names)

stream.Close()

[Shandilya Peddi]

Are you looking to export Marked rows or Filtered RowsIn your question you mentioned Marked Rows but in the code you are using below to get FilteredRows

filtered = Document.ActiveFilteringSelectionReference.GetSelection(table).AsIndexSet()If you are looking for Marked Rows then this "ActiveFilteringSelectionReference" should be "ActiveMarkingSelectionReference"

[Georgi Koemdzhiev]

Hi, Shandilya. Yes, I wanted to use the marked rows. Replacing "ActiveFilteringSelectionReference" with "ActiveMarkingSelectionReference" did the trick. Thanks

[Peter Bij]

Hello Shandilya Peddi,

Instead of exporting the Marked or Filtered rows, I want to export all rows of data. But my data is refreshed as an on-demand table, and is an external data

But my Excel file keeps remaining empty (besides the header of the columns), even after puting this line of code in the script:

allRows = Document.Data.AllRows.GetSelection(table).AsIndexSet()

 

Can you help solving this

[Tyger Guzman 2]

Looks like you are only writing the column names to the file and not the all rows. 

[Peter Bij]

Same problem here

[Peter Bij]

Yes indeed. You'r right about that.

 

But how should I change that to table with headers AND data

 

Any suggestion

[Tyger Guzman 2]

Instead of reading the DataTable I would suggest having a table visual that is controled from the map markings then pull the data from the table visual.

 

Setting myVis parameter to the Table Visual

import System

from System.IO import FileStream, FileMode

from Spotfire.Dxp.Application.Visuals import TablePlot

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

import clr

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

saveFilename = "C:/Users/User/Desktop/dropdownexport.xls"

from Spotfire.Dxp.Application.Visuals import VisualContent,TablePlotColumnSortMode

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

from System.IO import File, StreamWriter

writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter)

stream = StreamWriter(saveFilename)

te = myVis.As[VisualContent]()

te.ExportText(stream)

stream.Close()

[Tyger Guzman 2]

Let me write a solution using only the datatable and not a table visual. I'll post shortly 

[Peter Bij]

Hello All,

 

I just realize that the ActiveVisual is not the table-visual, but the textbox with the spript button.

 

So I need to change my coding probably in such a way that the referred visual is the table visual. Problem is only that I don't know the code / correct syntax for this.

 

Maybe this will help i solving the problem

[Tyger Guzman 2]

Here is a long method using python to save to a xls file and also the shorter method :

 

from System.Collections.Generic import List

from System.IO import File

from Spotfire.Dxp.Data import *

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

 

class my_dictionary(dict):

# __init__ function

def __init__(self):

self = dict()

# Function to add key:value

def add(self, key, value):

self[key] = value

 

# Create a cursor for the table column to get the values from.

# Add a reference to the data table in the script.

dataTable = Document.Data.Tables["Table Name"]

cursor = DataValueCursor.CreateFormatted(dataTable.Columns["Column Name"])

 

# Retrieve the marking selection

markings = Document.ActiveMarkingSelectionReference.GetSelection(dataTable)

 

# Create a List object to store the retrieved data marking selection

markedata = List [str]();

myDict = {}

dict_obj = my_dictionary()

column_dict = my_dictionary()

 

for column in dataTable.Columns:

column_dict.add(column.Name,'')

print(str(column_dict))

 

for row in dataTable.GetRows(markings.AsIndexSet(),cursor):

dict_obj.add(row.Index, {'Column 1':'' ,'Column 2' : '' ,'Column 3' : '' ,'Column 4' :'' , 'COUNTRY_NAME' : '' })

 

for x in dataTable.Columns:

cursor = DataValueCursor.CreateFormatted(dataTable.Columns[x.Name])

# Iterate through the data table rows to retrieve the marked rows

for row in dataTable.GetRows(markings.AsIndexSet(),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:

MyItems = {x.Name: value}

dict_obj[row.Index].update(MyItems)

 

filename="C:/Users/User/Desktop/dropdownexport2.xls"

f=open(filename,"w")

a= 0

for z in dict_obj.keys():

if a == 0 :

lines = str( dict_obj[z].keys()).strip('[]').replace("'",'').replace(',','t')

f.write(lines+'n')

a = a+1

for z in dict_obj.keys():

lines = str( dict_obj[z].values()).strip('[]').replace("'",'').replace(',','t')

f.write(lines+'n')

f.close() The easier version would be to use:

 

from System.IO import File

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

#get list of filtered rows

dataTable = Document.Data.Tables['TableName']

 

writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter)

filtered = Document.Data.Markings['Marking Name'].GetSelection(dataTable).AsIndexSet()

stream = File.OpenWrite("C:/Users/user/Desktop/dropdownexport5.xls")

names =[]

for col in dataTable.Columns:

names.append(col.Name)

writer.Write(stream, dataTable, filtered, names)

stream.Close()

print('done')

[Tyger Guzman 2]

To reference the proper markings use  

 

filtered = Document.Data.Markings['Marking Name'].GetSelection(dataTable).AsIndexSet()

 

 

 

specifying the marking rather than choosing the active reference. 

[Tyger Guzman 2]

The script would only work with Imported data , you can still have it on-demand but must be imported not External. 

[Georgi Koemdzhiev]

Thank you for your answer! Very nice! Can I ask if you have tested that code for In-database data table 

[Paul Lung]

This works like a charm :)   So grateful for your help.

 

Cheers

[Phantom Gaming]

hey tyger, your script works fine. But what if my visual data is changed and I want to append that data into the existing created file any idea |