Strip HTML from viewDataset for Export To Excel

Ignition
1

Using version 8.0.2

Is there anyway to strip HTML from the headings for a table when exporting to Excel?

My table has a bunch of HTML to get the heading to align correctly. The current script is:

filename = 'test.xls'

table = event.source.parent.getComponent('Power Table')

filePath = system.dataset.exportExcel(filename, 1, table.viewDataset)
if filePath is not None:
	system.net.openURL("file:///"+filePath.replace('\\','/'))

Previously I was using the script below to perform the same function, but the file won’t open correctly when someone in Belgium runs the script. I would love a solution for this too.

filename = 'test'
table = event.source.parent.getComponent('Power Table')

csv = system.dataset.toCSV(table.viewDataset, 1)
csv = csv.replace('<html>', '').replace('<center>', '').replace('<br>', ' ')

filePath = system.file.saveFile(filename, 'csv', 'CSV File')
if filePath is not None:
	system.file.writeFile(filePath, csv)
	system.net.openURL("file:///"+filePath.replace('\\','/'))
1 Like
2

You should be able to do it using regex

3

What was the issue? That may be a bug we've already fixed - it sounds vaguely familiar, and if you can describe what was wrong I can cross-reference.

EDIT: The bug I was thinking of was resolved by the addition of system.dataset.formatDates - if it's an issue with formatting in the output, maybe just pass the output dataset through formatDates before exporting it.

4

The power table has a bunch of headings that are like <html><center>First Line<br>Second Line

In the script where I create a CSV, I am able to remove the <html>, <center>, and <br> before saving the file. When a user in Belgium uses this function, the system.net.openURL does indeed open the file in excel, but it looks as if it was opened in notepad. My guess is that since Europeans use commas in numbers as we use periods, that their version of excel does not interpret a CSV very well.

In the script where we export to Excel, the header row in Excel shows the <html>, etc. making it very hard to read.

Either of the following will help:

  1. Is there a way to modify the CSV script (script 2 above) such that it works in both the US and Europe?

  2. A method to strip the html tags out of the header before performing the export to Excel (script 1 above).

5

Instead of using exportCSV directly, you could use the system.dataset.toCSV function - the last argument (localized) should make the CSV export behave in a useful manner in an international context.

If you’re using toCSV, then you’ll also need to add the system.file.saveFile function, since toCSV just silently outputs a string, rather than prompting the user.

6

I added “localized=1” to the toCSV function call and was very hopeful that this would fix the problem. My client in Belgium attempted to export, but the resulting file won’t open correctly in Excel. It works fine for clients in the US. Is there anyway to test the different results (US vs Europe) from a computer in the US?

7

Changing the client locale through system.util.setLocale should work to generate different files.

8

Could there be a bug in Ignition 8.0.3 EU file looks like the delimiter is three characters, “,”.

Looks like the EU version is getting extra quote marks and using commas instead of semi colons. Following is the script being used to generate the file:

filename = 'smart_plan_%s_%s' % (system.db.dateFormat(event.source.parent.date_beg, 'yyyyMM'), system.db.dateFormat(system.tag.read("[System]Client/System/CurrentDateTime").value, 'yyyyMMdd'))

table = event.source.parent.getComponent('Power Table')

csv = system.dataset.toCSV (table.viewDataset, showHeaders=1, forExport=0, localized=1)

csv = csv.replace('<html>', '').replace('<center>', '').replace('<br>', ' ')

filePath = system.file.saveFile(filename, 'csv', 'CSV File')
if filePath is not None:
	system.file.writeFile(filePath, csv)
	system.net.openURL("file:///"+filePath.replace('\\','/'))