Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

Exporting Excel reports to csv


  • 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


  • 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


  • 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


  • @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
        
    }```

  • 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


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

    Fox


  • 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


  • 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


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

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


  • 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


  • @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.