Complex object data structure to xlsx

Question

I have a javascript object that contains in itself other objects:

{
    "10": {},
    "12": {
        "20": {
            "value": 1,
            "id": 1,
        },
        "100": {
            "value": 12,
            "id": 1,
        }
    },
    "14": {
        "100": {
            "value": 14,
            "id": 2,
        }
    },
    "16": {},
    "18": {},
    "20": {
        "100": {
            "value": 23,
            "id": 1,
        },
        "150": {
            "value": 56,
            "id": 3,
        }
    },
    "22": {},
    "24": {},
    "26": {
        "50": {
...

I want to export this as an xlsx file, but I have some issues doing so.

I have mainly resorted at using js-xlsx that is not very helpful regarding it's documentation, and alasql.

Creating such a file for a simpler datastructure is quite easy. But I have a few issues when trying to create it with my own structure.

Firstly trying to do it like this:

alasql('SELECT * INTO XLSX("test.xlsx",{headers:true}) FROM ?',[$scope.TestData]);

fails printing [Object] instead of the actual values.

Trying to create the cells myself I end up with an empty file.

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
    var row1 = 0;
    for (var R in data) {
        if (data.hasOwnProperty(R)) {
            var row2 = 0;
            for (var C in data[R]) {
                if (data[R].hasOwnProperty(C)) {
                var col = 0;
                    for (var K in data[R][C]) {
                        if (data[R][C].hasOwnProperty(C)) {
                            var RR = row1 + row2;
                            var CC = col;
                            if(range.s.r > RR) range.s.r = RR;
                            if(range.s.c > CC) range.s.c = C;
                            if(range.e.r < RR) range.e.r = RR;
                            if(range.e.c < CC) range.e.c = CC;
                            var cell = {v: data[R][C][K] };
                            if(cell.v == null) continue;
                            var cell_ref = XLSX.utils.encode_cell({c:CC,r:RR});

                            if(typeof cell.v === 'number') cell.t = 'n';
                            else if(typeof cell.v === 'boolean') cell.t = 'b';
                            else if(cell.v instanceof Date) {
                                cell.t = 'n'; cell.z = XLSX.SSF._table[14];
                                cell.v = datenum(cell.v);
                            }
                            else cell.t = 's';

                            ws[cell_ref] = cell;

                            col = col + 1;
                        }
                    }
                    row2 = row2 + 1;
                }
            }
            row1 = row1 + 1;
        }
    }
    if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

function Workbook() {
    if(!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

var wb = new Workbook(), ws = sheet_from_array_of_arrays($scope.TestData);

/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});

function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "test.xlsx")

Is there a way for me to create an xlsx file with the object I presented above?

Could you specify a desired output (I mean list of columns in the result sheet)? — agershun, Jan 08 '16 at 16:43
@agershun I was thinking that the best way of showing this sort of data would be showing each internal object, for example 20 and 100 for object 12, as separate rows that either all have 12 in the first cell or all are under a cell containing 12, something like many mini matrices. — dearn44, Jan 09 '16 at 08:53
By the way alasql is pretty neat, especially the fact that it can operate inside simple javascript object. I dont really know how to use it though. — dearn44, Jan 09 '16 at 09:02

agershun · Answer 1 · 2016-01-09T01:03:18.390

0

You can use SEARCH operator for parsing of nested JSON objects:

var data = {
"10": {},
"12": {
    "20": {
        "value": 1,
        "id": 1,
    },
    "100": {
        "value": 12,
        "id": 1,
    }
},
"14": {
    "100": {
        "value": 14,
        "id": 2,
    }
},
"16": {},
"18": {},
"20": {
    "100": {
        "value": 23,
        "id": 1,
    },
    "150": {
        "value": 56,
        "id": 3,
    }
}
};


  var res = alasql('SEARCH OF(@a) OF(@c) \
    RETURN(@a AS a,@c AS c, _->[value] AS [value], _->id AS id) \
    INTO XLSX("test406.xlsx",{headers:true}) \
    FROM ?',[data]);

Here:

SEARCH ... INTO XLSX FROM ? - special search operator
OF(@v) - list of keys of the object (key value will be stored into variable
RETURN(...) - result object
_ - current search value

edited Jan 09 '16 at 01:03

answered Jan 08 '16 at 17:19

agershun

4,077
38
41

This exact example gives me a long error message: `Error: Parse error on line 1: SEARCH OF(@a) OF(@c) RE -------^ Expecting 'LITERAL', 'BRALITERAL', 'EOF' ...` – dearn44 Jan 09 '16 at 09:19
Can you download last version. – agershun Jan 09 '16 at 15:06
Indeed I was using the development branch. Downloading the library from the webpage though gives this error: `x.srch[l.srchid.toUpperCase(...)] is not a function` Have a look here https://plnkr.co/edit/AtGd49CQU5S4FDEHHOWD?p=preview – dearn44 Jan 10 '16 at 11:55

Complex object data structure to xlsx

1 Answers1