[Feature-13610]Export to Excel in Perspective Ignition 8

ceszamora · April 16, 2019, 3:23pm

hello,

If I have a Table in on a View, Could I export all data to an EXCEL file? Could I use system.dataset.exportExcel?

thank you all

cmallonee · April 16, 2019, 3:37pm

Unfortunately, no, you may not. system.dataset.exportExcel() is scoped for the Vision Client. For any exporting, Vision is your only option at this time.

You are able to write to files (create a new file) from Perspective, but the file will be placed on the Gateway machine - not your local environment (unless they happen to be the same).

pturmel · April 16, 2019, 5:37pm

The WebDev module would be an option for this, implementing a download link for a dynamically generated file of any type.

apereira · April 16, 2019, 7:54pm

We are currently developing a solution using Excel in our project, so that the user is able to preview and download reports both in excel and PDF. So far it’s stable and we were able to create the xlsx report that looks almost the same as the PDF.
We are using a 3rd party python library, xlsxwriter, and the standard xml library to interpret the report content as XML ( system.report.executeReport, fileType=“xml”). When the file is generated, you can read as byte array and then use system.perspective.download.

Unfortunately it’s not a built-in feature, so it’s fairly limited.

Here’s an example:
BSM Report.xls (25.5 KB)

silverbacknet · April 16, 2019, 11:44pm

It might not be built-in, but xlsxwriter is insanely powerful, much more so than exportExcel(), and not hard to harness. I'd be interested in whatever glue code you used to get it up and running, because I think it would be a great addition to my project as well.

apereira · April 17, 2019, 2:29pm

Here is a snippet:

def datasetListFromXML(bytesArray):
import xml.etree.ElementTree as ET
root = ET.fromstring(bytesArray.tostring())
for g0child in root.findall('page'):
for g1child in g0child.findall('shape'): #table
for row_n,g2child in enumerate(g1child.findall('shape')): #row
for colndx,g3child in enumerate(g2child.findall('text')): #column
...
return tbl

def XMLtoXLSX(rptName, bytesArray, params):
import xlsxwriter
dataset_list = datasetListFromXML(bytesArray)

workbook = xlsxwriter.Workbook(fileName)
worksheet = workbook.add_worksheet('Report')

content_format = workbook.add_format({'border': 1, 'align' : 'left'})

row = 3
for ds in dataset_list:
    for col,val in enumerate(ds[0]):
        worksheet.write(row, col, val, content_format)
        row += 1

# Page layout 
worksheet.set_page_view()
worksheet.set_paper(paper_size)
worksheet.set_landscape()

workbook.close()
bArray = system.file.readFileAsBytes(fileName)
return bArray
def downReport():
bytesArray = system.report.executeReport(path=rptName, project=prjName, parameters=input, fileType=fType)
XLbArray = XMLtoXLSX(bytesArray)
system.perspective.download(rptName+'.'+fmt.lower(),XLbArray)

I hope that helps.

Steve_Laubach · April 17, 2019, 8:40pm

Do you have a rough ETA on when exporting to excel is planned to come to Perspective?

I’m trying to gauge whether it makes sense for me to write a back end solution that writes csv files on the gateway or if I should wait for the Perspective toolset to be more fleshed out.

cmallonee · April 18, 2019, 2:13pm

I’m unaware of any current plans to provide exports through Perspective, although - as @pturmel mentioned - there are alternative avenues by coupling Perspective with the WebDev Module. Alternatively, it would probably be pretty easy for us to provide a props.download property for the Link component which inserts a download tag for the associated anchor… I’ll put in a feature request for that as it’s a good “quick-and-dirty” option, but even the feature request will probably take quite some time to see the light of day.

TLDR; 8.0.2 at the absolute earliest, 8.0.3 is more likely.

Steve_Laubach · April 18, 2019, 3:05pm

Thanks for your response. I have a customer asking me. They were trying to determine whether we custom write a csv export or wait for the feature to exist natively.

We’re going to custom write at least for the pilot system. They are hoping for this feature. Thanks for the rough ETA and adding the feature request.

Tumeksi · October 22, 2019, 12:23pm

Hi,
What is the situation on this?
Many of our customers are asking for this functionality.

cmallonee · October 22, 2019, 2:28pm

No progress has been made insofar as modifying the Link to trigger downloads, but as others have mentioned this is possible when using the Web Dev module in conjunction with Perspective.

craigb · October 22, 2019, 2:46pm

	dataset = self.parent.parent.getChild("Table Container").getChild("Your_Table").props.data
	data = system.dataset.toCSV(dataset, 1, 0, 0)
	filename = 'Orders.csv'
	system.perspective.download(filename, data)

craigb · October 22, 2019, 2:49pm

	table = self.parent.parent.getChild("Table Container").getChild("Table").props.data
	excelstr = system.dataset.dataSetToExcel(True,[table])
	excelbytes = system.dataset.dataSetToHTML(1, table, "")
	OrderNo = self.view.params.OrderID
	fileName = "Purchase Request Number %s.xls" % OrderNo
	system.perspective.download(fileName, excelbytes)

Phil_B · November 1, 2019, 7:48pm

Hello @ceszamora,

I just had this same issue come up and here is what i came up with. this is a script i put on a button.

	value = self.parent.parent.getChild("Table").props.data
	headers = value[0].keys()
	datasetData = []
	for row in range(len(value)):	
		rowData = []
		for col in range(len(headers)):
			rowData.append(value[row][headers[col]])
		datasetData.append(rowData)
	finalDataset = system.dataset.toDataSet(headers,datasetData)
	XLSFormat = system.dataset.dataSetToExcel(True,[finalDataset])
	system.perspective.download("test.xls",XLSFormat)

You will need change the path to your table’s data prop and also the filename. I tested it on my system but i don’t have excel installed, i just have an excel file reader and it opened with no issues.

I found that i needed to convert json to a dataset in order to make my instance work.

Let me know if you find a better way.

weixionglichina · November 4, 2019, 7:57am

Just a little bit change , and I export with default table @Phil_B

data = self.parent.getChild("Table").props.data
headers = data[0].keys()
system.perspective.print(headers)

datasetData = []
for row in range(len(data)):	
	rowData = []
	for col in range(len(headers)):
		cell = data[row][headers[col]]
		if hasattr(cell, 'value'):
			rowData.append(cell.value)
		else:
			rowData.append(cell)
	#system.perspective.print(rowData)
	datasetData.append(rowData)

#system.perspective.print(datasetData)	
finalDataset = system.dataset.toDataSet(headers,datasetData)
XLSFormat = system.dataset.dataSetToExcel(True,[finalDataset])
system.perspective.download("test.xls",XLSFormat)

djones · July 15, 2020, 5:43pm

This worked great!

johnmichael.savio · August 24, 2020, 11:27am

Hi,

Has there been any development regarding exporting data from perspective in the current versions (8.0.15)

Thanks,
John

ali · January 19, 2021, 3:54pm

Hi all,

I got onto this forum thread looking for a way to download a table from perspective just like you all. I was able to figure it out on Ignition 8.1.0 (Perspective Module 2.1.0) by doing the following:

Message Handler Script on Table component (1 indent on all lines) (Message Type was ‘download-test-data’) (Listen Scopes, selected ‘View’) :
fileName = ‘testData.xls’
excelbytes = system.dataset.dataSetToHTML(1, self.props.data, “”)
system.perspective.download(fileName, excelbytes)

OR in case you wanted a csv instead of xls file

#csv = system.dataset.toCSV(self.props.data)
#fileName = ‘testData.csv’
#system.perspective.download(fileName, csv)
On Click Event on Button component (1 indent on all lines):
messageType = ‘download-test-data’
system.perspective.sendMessage(messageType, scope = ‘view’)

Thanks for the discussion guys!

mcgheeiv · February 7, 2021, 3:01pm

My table has hidden columns and custom headings… I found the following works well:

columns = self.getSibling("Table").props.columns
header = [col.header.title for col in columns if col.visible]

data = self.getSibling("Table").props.data
data = [[data.getValueAt(i,j) for j in range(data.columnCount) if columns[j].visible] for i in range(data.rowCount)]

data = system.dataset.toDataSet(header, data)
data = system.dataset.toExcel(True, [data])

system.perspective.download('test.xls',data)

jd1 · March 9, 2021, 9:47pm

I am using this script as it was posted here, but I get the following error I try and open it in excel. " The file format and extension of ‘test.xls’ don’t match. The file could be corrupted or unsafe. Unless you trust its source, don’t open it. DO you want to open it anyway?" If I click yes the file will open. I should also add the save dialog box that opens when the script runs doesn’t list a file type and so I am adding the “.xls” to the end of the file name. Any ideas on what might be causing this issue?

[Feature-13610]Export to Excel in Perspective Ignition 8

OR in case you wanted a csv instead of xls file