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

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

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

1 Like

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

4 Likes

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)

6 Likes

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.

1 Like

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.

6 Likes

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.

1 Like

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.

2 Likes

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.

3 Likes

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

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.

1 Like
	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)
6 Likes
	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)
3 Likes

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.

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)
3 Likes

This worked great!

Hi,

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

Thanks,
John

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:

  1. 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)

  2. 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!

3 Likes

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)
4 Likes

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?