Exporting Excel reports to csv

iperry

Heya,

Can Excel reports be exported as csv instead of xlsx? It doesn't appear so but wanted to be sure that I haven't missed something.

If not, is there any other facility to generate "reports" of data points to csv? I have a client requirement to create a report for a sensor node every hour and email it to them. Each report would contain 6 records, as data is logged every 10 mins. Ideally the report should contain the records for multiple sensor nodes.

The other option is to generate the xlsx reports and have an automated process which picks up these reports from the server directory and converts them to csv. It would be ideal to avoid this option as it just adds a layer of complexity and maintenance.

Thanks
Ian

MattFox

Does the CSV need to be added as an attachment?
If not, why not just create a scheduled alarm and attach an email handler to it; populate the contents with your CSV data, and fire it?
If you need to make it CSV attachment though. There is no reason why you cannot make the necessary headers and put your CSV in amongst it.
EDIT: Sorry Ian, it appears I did it all with a scripting datasource not an event based system. If that's still of any use to you I don't mind sharing.

Fox

iperry

Hi @MattFox

The existing Excel scheduled report function would work fine if it was able to be generated as a csv instead of a xlsx. The csv report would be emailed as an attachment to the client.

I guess the next question would be: what is the best way to generate a csv report containing multiple data points for a specific time period?

Seeing the script you used would be interesting. At least gives me some insight as to how to create my own queries.

Thanks
Ian

MattFox

@iperry Fortunately Phil answered a question I had requesting that here in the forums:
https://forum.infiniteautomation.com/topic/3741/get-roll-up-in-scripted-data-source/3
Here is what I based my email code off of:
https://forum.infiniteautomation.com/topic/3147/email-last-day-s-events

and for the RQL query: (I grossly simplified mine)

var allPoints = DataPointQuery.query("and(like(deviceName,My Device*),eq(name,probe1))&sort(deviceName,name)&limit(10000)");

Then simply iterate through all of the points:

for (var k = 0; k < allPoints.length; k+=1) {
    //do magic here
    
}```

iperry

Hi @MattFox

Thanks for the starting points. As I pick through the requirements for the csv and email, it looks like the scripting data source is the more likely option. Will let you know how things go.

Ian

MattFox

@iperry Glad to be of assistance, you know I'll be here!

Fox

iperry

Hi @MattFox

So far so good. Using the examples you provided, I am able to query the data points I want for the last hour of samples and save the data to a csv file on the server.

The email example works fine but I'm stuck trying to attach the csv file to the email. I haven't been able to find any javadocs or api docs for the Serotonin classes and my experiments haven't worked yet. Hopefully I haven't been blind. heh

Do you have any examples of attaching a file?

@phildunlap are there any javadocs?

Thanks
Ian

MattFox

Nicely done Ian!
To my recollection I cannot remember trying to attach anything. I know that it's a header based thing that will either go where that null is or we should be able combine the csv attachment amongst the email body. Sorry I'm not not near my pc atm to test. Think I might give it a go when I get home...

This will be hugely beneficial for other mango users.
Fox

MattFox

Nailed It!
I looked through the source code on git public. Here is your solution:

get the full path to your attachment.

var toAttach = "/opt/mango/overrides/web/modules/mangoUI/web/ui/manifest.json";    
    var emailAttachment = new com.serotonin.web.mail.EmailAttachment.FileAttachment("manifest.json",toAttach);

First parameter is the name of the file that will appear in the email. This means if your attachment hasa really bizarre name or is timestamped to oblivion you can make it "Easier to read".

NEXT:
When you generate the email content object, simply call the in class method for attaching a file:

emailContent.addAttachment(EmailAttachment ea )

//simply generate and append
var emailContent = new com.serotonin.web.mail.EmailContent(null, writer.toString(), com.serotonin.m2m2.Common.UTF8);
emailContent.addAttachment(emailAttachment);

THEN!

com.serotonin.m2m2.rt.maint.work.EmailWorkItem.queueEmail(recipientsArray, "Email with attached file", emailContent, null); //FIRE!

Et voila!

Fox

EDIT:

@phildunlap are there any javadocs?

Here is the link @phildunlap provided we practically posted this within a few minutes of each other :D
https://github.com/infiniteautomation/ma-core-public/blob/main/Core/src/com/serotonin/web/mail

iperry

Awesome! Thanks Matt :)

The part I was missing was using the subclass FileAttachment. doh

All the parts work now. Once I get the code cleaned up, I will post it here.

Need to make sure I bookmark the javadocs. I think I recall seeing them before but forgot all about them. heh

Ian

MattFox

@iperry said in Exporting Excel reports to csv:

The part I was missing was using the subclass FileAttachment. doh

Yep, having the EmailAttachment class as an abstract class means it cannot be instantiated. Gotta use the public child classes.