Custom XLS Sheet - Widget for Portals


#1

Hi, mates!

I’m working on a custom widget that exports data from Portals on .csv and .xlsx files. It’s working really fine, I can do all the selection, export data but I’d like to ask if you guys can help me to change the way I get and show it on the sheet:

Today, it goes like this:

name | value | date | unit
Channel 1 | 20.5 | 5/3/2017 10:41 AM | ºC
Channel 1 | 20.5 | 5/3/2017 10:42 AM | ºC
Channel 1 | 20.5 | 5/3/2017 10:43 AM | ºC
Channel 2 | 20.5 | 5/3/2017 10:44 AM | ºC
Channel 2 | 20.5 | 5/3/2017 10:45 AM | ºC

I want to show like this:

Channel 1 | Channel 2 | date | unit
20.5 | 20.5 | 5/3/2017 10:41 AM | ºC
20.5 | 20.5 | 5/3/2017 10:42 AM | ºC
20.5 | 20.5 | 5/3/2017 10:43 AM | ºC
20.5 | 20.5 | 5/3/2017 10:44 AM | ºC
20.5 | 20.5 | 5/3/2017 10:45 AM | ºC

My .js function that edits this is:

    processData:function(){
        this.dataToExport=[];
        var dataToExport=this.dataToExport;
        var i,
          j,
          k,
          date,
          dateFormat,
          value,
          unit,
          data;
        // collect output data
        k = 0;
        for (i = 0; i < resources.length; i++) {
          if (resources[i].isEnabled()) {
            for (j = 0; j < resources[i].data.length; j++) {
              date = new Date(resources[i].data[j][0] * 1000);
              dateFormat = '';
              dateFormat += date.toLocaleTimeString() + ' ';
              dateFormat += date.toDateString();
              value = resources[i].data[j][1];
              try {
                if (typeof JSON.parse(value) === 'object') {
                  value = $('<span/>')
                    .attr('title', value)
                    .text('More data');
                }
              } catch (e) {}
              unit = JSON.parse(resources[i].info.description.meta).datasource.unit;
              dataToExport[k] = {
                date: dateFormat,
                value: value,
                name: resources[i].info.description.name,
                unit: unit
              };
              k++;
            }
          }
        }
    },
    dataToExport:[],
    exportData:function(fileExtension){
        alasql("SELECT * INTO " + fileExtension + "('exportedData." + fileExtension + "',{headers:true}) FROM ?", [this.dataToExport]);
    }

Any help would be extremely great, I’d really appreciate that!

Thanks in advance for all!

Tiago Siqueira | NOVUS IoT Dev


#2

Hey @tiago.siqueira - Thanks for posting!

I’ll need a little time to look at your code and see what I can do to help. I’ll try to write back with something useful as-soon-as-possible.

-Martin


#3

Hey @tiago.siqueira

Do you expect data written to channel 1 and 2 to always happened at the same time?

-Martin


#4

Hi, @Martin!

First of all, thank you very much for your help!

No, there’s no problem if channel 1 and channel 2 don’t have data written at the same time, the sheet can have blank spaces, the main functionality that I aim is to order it on different rows and not on one same row.

I believe that I need to filter and order the timestamps of the datasources, like a list on C++ and apply TList, for example (don’t know if there’s a command like that on JS). It should create a table with number of lines equal to the size of this list and number of columns equal to channel quantity + 1 (timestamp). On each line I put a timestamp, I have to look for an equal timestamp and insert the value of the channel related to this.

Thanks in advance, Martin!
-Tiago


#5

I don’t know if JS has something like that built in either.

I started thinking about this problem through the only JS module I have really used. The only useful thing I could think of is the _where function of underscore. It would allow you to select out all instances in the array where one property is the same(like the timestamp).

This felt kludgy, but workable. Though I am sure that someone has solved this problem in an elegant way before.

Is there something in https://github.com/agershun/alasql that would allow you to iterate over the collection of datapoints and add row based on the timestamp and expand the columns when needed?

I am still thinking about this problem, and I have reached out to some of my colleagues to ask their thoughts too.


#6

Hi, Martin!

I couldn’t find anything on alasql’s github that allow us to manipulate rows over datapoints collection…

Thank you very much for all your efforts, we really appreciate it! I’ll keep thinking about it and any news I’ll update it here.


#7

Hi, Martin!

Just to give you a feedback, I’ve reached on a solution for this!

Here’s the solution that I developed:

   processData: function() {
      var data = {};
      var headers = ["date"];

      for (var i = 0; i < resources.length; i++) {
          if (resources[i].isEnabled()) {
              headers[i+1] = resources[i].info.description.name;

              for (var j = 0; j < resources[i].data.length; j++) {
                  var ts = resources[i].data[j][0];
                  var value = resources[i].data[j][1];

                  try {
                      if (typeof JSON.parse(value) === 'object') {
                          value = $('<span/>')
                              .attr('title', value)
                              .text('More data');
                      }
                  } catch (e) {}

                  if(!data[ts])
                    data[ts] = Array.apply(null, Array(resources.length+1)).map(function(){return "";});
                  data[ts][i+1] = value;
              }
          }
      }

      data = Object.keys(data)
      .sort(function(a, b){return a-b})
      .map(function(ts){
        var row = data[ts];
        row[0] = dateTag(ts * 1000);
        return row;
      });

      data.unshift(headers);
      this.dataToExport = data;
    },
    dataToExport: [],
    exportData: function(fileExtension) {
        alasql("SELECT * INTO " + fileExtension + "('exportedData." + fileExtension + "',{headers:false}) FROM ?", [this.dataToExport]);
    }

Thank you very much for all your effort and help with this case!

Regards,
Tiago


#8

@tiago.siqueira That is awesome to hear that you resolved this problem, thank you for posting your feedback to the forum and for being so positive.

In reality you did this all yourself, I am sorry that I not able to provide any direct help.

Hopefully if you find that you have questions again you won’t hesitate to make a post, I will be able to help with greater impact.