dataSetToExcel - corrupt file when attaching file to email

Ignition
1

So I have a simple nightly email that sends a HTML report in the body of an email (works great). I am trying to attach the same data as an Excel attachment but when you open it Windows thinks the file is corrupt. I tried to change file extensions, that didn’t work at all and the process seems so straight forward I am not sure what to look for.

#Run the query 
	data = system.db.runQuery(queryReport, 'xxx')
	lengthDue = data.getRowCount()
		
	if lengthDue > 0:
		#NEW DATASET Ready for email and Excel conversions
		rawData = system.dataset.toDataSet(data)
		
		#Convert DataSet to Excel and get ready to attach to our email
		excelstr = system.dataset.dataSetToExcel(1,[rawData])
		excelbytes = excelstr.encode("UTF8")		
		filename = "Summary.xls"

		#Define Email Distribution List			
		recipients = system.db.runScalarQuery("SELECT EmailList FROM ReportDistList WHERE ReportName='Awaiting Inspection Summary'", 'xxx')
		#Put recipients into a Python list		
		EmailRecipients = [item.strip() for item in recipients.split(',')]	
		
		myEmail = ['xxx@xxx.com']		
		
		#Convert DataSet to HTML For Sending In EMAIL
		EmailHTML = system.dataset.dataSetToHTML(1, rawData, 'Awaiting Inspection Summary')
		Emailbody = '''
		''' + '<h1>Awaiting Inspection Daily Summary</h1>' + '''
		''' + EmailHTML + '<br/>This is an automated email please do not respond'
		system.net.sendEmail(smtpProfile='xxxEmail', fromAddr='xxx@xxx.com',
				subject='Awaiting Inspection Daily Summary', body=Emailbody, html=True, to=EmailRecipients, bcc=myEmail,attachmentNames=[filename],attachmentData=[excelbytes])

Thanks in advance!

2

Have you tired it without this line?

1 Like
3

I have never used those functions but...

system.dataset.toExcel returns a byte array.
and
attachmentData must be a byte array

So i doubt you should encode it
and i dont think if you need to but another array around it

1 Like
4

Yes, makes no difference. Just left it in from an example I found with similar issues.

5

If you download the excel file, is it also corrupted? Or is it only corrupt when you email it?

6

Thanks, I removed it but nothing changes.

7

Just tried it, same results. Still corrupt. The data is there, but Excel certainly doesn’t like it.

8

Maybe convert it to a csv. thats much more readable and excel an also open it

9

Try it with system.dataset.toExcel(), rather than:

According to the docs, the function you are using is depricated, that might be why its not working.
https://docs.inductiveautomation.com/display/DOC81/system.dataset.toExcel

10

Does it still open? If so, it’s in the way the xml is constructed by the function, and has always been that way, IIRC.

toExcel() overcame that.

11

Not sure if this may help or not…

If you are not formatting the spreadsheet, would a CSV export/file be a better option?

12

What is your indicator that the file is corrupt? If excel is showing data, then I don't think it is truly corrupt.

13

Thanks all, using .toExcel immediately cured the issue and allowed me to use .xlsx, a bonus.

I can’t believe I didn’t find that when doing this earlier. It’s to easy to find old info I suppose.