CODEWITHSUNDEEP
javascriptpythonjsonwebsocketqliksense
Mohammad Anas
Mohammad Anas

Reputation: 52

Export visualization data using Qlik Engine JSON API

Our organization uses Qlik Sense Enterprise and we are looking to automate the download process of the data used for visualizations (format can be excel or csv) instead of the manual process which leads to the following (cropped screenshot shown):

From qlik sense portal

For our use case, lets assume there is only one app which has one sheet inside and that sheet has 3 visualizations.

I have written a python script which currently has been connected to the localhost and I am able to retrieve the app_id, sheet_id and the id of the 3 charts present using Qlik Engine JSON API. The code works in the following manner:

  1. Fetch the doc_list (app_list)
  2. Select the app, since we have only one app we choose index as 0
  3. Create a session object (I saw Qlik Engine on Dev Hub exhibiting this behavior which is why I executed this step)
  4. Get the layout of the app
  5. Select the sheet, since we have one sheet we choose index as 0
  6. Iterate through the visualizations and print their names

I have provided the code below for your reference as well as the pastebin link can be accessed here

import requests
import websocket, ssl
import json,  csv
import os, time
from pprint import pprint 
 
#Connecting to the server. The lines below will be replaced by the certificates and headers for enterprise usage later
ws = websocket.WebSocket()
ws.connect("ws://localhost:4848/app/")
 
#For getting the doc (app) list
doclist_req = {
    "handle": -1,
    "method": "GetDocList",
    "params": [],
    "outKey": -1,
    "id": 1
}
 
ws.send(json.dumps(doclist_req))
result = ws.recv()
ws.send(json.dumps(doclist_req))
result = ws.recv()
result_json = json.loads(result)
print(result_json)
print()
 
#For opening the doc (app)
app_req = {
    "jsonrpc": "2.0",
    "method": "OpenDoc",
    "handle": -1,
    "params": [
        #Can iterate if multiple apps are there
        #Since only one app was present we used the index 0
        result_json['result']['qDocList'][0]['qDocId']
    ],
    "outKey": -1,
    "id": 2
}
 
#The first call seems to be for establishing the connection and second to actually send the request body
ws.send(json.dumps(app_req))
result = ws.recv()
ws.send(json.dumps(app_req))
result = ws.recv()
result_json = json.loads(result)
print(result_json)
print()
app_req_handle = result_json['result']['qReturn']['qHandle']
 
#For creating the session object necessary for fetching dimensions, fields, etc.
session_req = {
    "jsonrpc": "2.0",
    "method": "CreateSessionObject",
    "handle": app_req_handle,
    "params": [
        {
            "qInfo": {
                "qType": "SheetList"
            },
            "qAppObjectListDef": {
                "qType": "sheet",
                "qData": {
                    "title": "/qMetaDef/title",
                    "description": "/qMetaDef/description",
                    "thumbnail": "/thumbnail",
                    "cells": "/cells",
                    "rank": "/rank",
                    "columns": "/columns",
                    "rows": "/rows"
                }
            }
        }
    ],
    "outKey": -1,
    "id": 3
}
 
ws.send(json.dumps(session_req))
result = ws.recv()
ws.send(json.dumps(session_req))
result = ws.recv()
result_json = json.loads(result)
print(result_json)
print()
session_req_handle = result_json['result']['qReturn']['qHandle']
 
#For fetching the layout of the sheets
layout_req = {
    "jsonrpc": "2.0",
    "method": "GetLayout",
    "handle": session_req_handle,
    "params": [],
    "outKey": -1,
    "id": 4
}
 
ws.send(json.dumps(layout_req))
result = ws.recv()
ws.send(json.dumps(layout_req))
result = ws.recv()
result_json = json.loads(result)
print(result_json)
print()
 
#Since only one sheet was present we used the index 0
list_of_charts = result_json['result']['qLayout']['qAppObjectList']['qItems'][0]['qData']['cells']
for chart in list_of_charts:
    print(chart['name'])
print()
 
ws.close()

I have explored a lot of pages on the Qlik community as well as SO and ExportData seems to be the way to go but I am not able to write the correct JSON request for it. For reference, the output I get in my terminal on executing the python script is given below. I am quite new to this and I would be very thankful for any help from all of you.

{'jsonrpc': '2.0', 'id': 1, 'result': {'qDocList': [{'qDocName': 'Test_2.qvf', 'qConnectedUsers': 0, 'qFileTime': 44188.190983796296, 'qFileSize': 851968, 'qDocId': 'C:\Users\mohdm\Documents\Qlik\Sense\Apps\Test_2.qvf', 'qMeta': {'hassectionaccess': False, 'encrypted': False}, 'qLastReloadTime': '2020-12-22T19:12:22.245Z', 'qTitle': 'Test_2', 'qThumbnail': {}}]}}

{'jsonrpc': '2.0', 'id': 2, 'result': {'qReturn': {'qType': 'Doc', 'qHandle': 1, 'qGenericId': 'C:\Users\mohdm\Documents\Qlik\Sense\Apps\Test_2.qvf'}}, 'change': 1}

{'jsonrpc': '2.0', 'id': 3, 'result': {'qReturn': {'qType': 'GenericObject', 'qHandle': 2, 'qGenericType': 'SheetList', 'qGenericId': '4b344780-a350-48db-8b65-27bb5a2c62b2'}}, 'change': 1}

{'jsonrpc': '2.0', 'id': 4, 'result': {'qLayout': {'qInfo': {'qId': '4b344780-a350-48db-8b65-27bb5a2c62b2', 'qType': 'SheetList'}, 'qMeta': {'privileges': ['read', 'update', 'delete', 'exportdata']}, 'qSelectionInfo': {}, 'qAppObjectList': {'qItems': [{'qInfo': {'qId': '8a0f6a01-ef89-4d65-821f-371c26208dcf', 'qType': 'sheet'}, 'qMeta': {'privileges': ['read', 'update', 'delete', 'exportdata'], 'title': 'Sheet_1', 'description': ''}, 'qData': {'rank': None, 'thumbnail': {'qStaticContentUrl': {}}, 'columns': 24, 'rows': 12, 'cells': [{'name': 'kfxNpV', 'type': 'auto-chart', 'col': 0, 'row': 0, 'colspan': 15, 'rowspan': 6, 'bounds': {'y': 0, 'x': 0, 'width': 62.5, 'height': 50}}, {'name': 'qHzmARQ', 'type': 'qlik-barplus-chart', 'col': 0, 'row': 6, 'colspan': 21, 'rowspan': 6, 'bounds': {'y': 50, 'x': 0, 'width': 87.5, 'height': 50}}, {'name': 'BXBQmw', 'type': 'auto-chart', 'col': 15, 'row': 0, 'colspan': 9, 'rowspan': 6, 'bounds': {'y': 0, 'x': 62.5, 'width': 37.5, 'height': 50}}], 'title': 'Sheet_1', 'description': ''}}]}}}}

kfxNpV
qHzmARQ
BXBQmw

I had originally posted this question at Qlik community as well but it didn't get a response.

Upvotes: 3

Views: 3684

Answers (2)

Øystein Kolsrud
Øystein Kolsrud

Reputation: 389

I'm not sure about Python, but I find that usually the hard part is getting the authentication right. Libraries like the ones that exist for JavaScript typically simplifies this a lot. Personally I tend to use C# for these kind of gadgets where the .NET SDK provides operators for doing the plumbing work for you. This is an example of what it could look like using these two nuget packages:

This code downloads xlsx files for all visualizations on the first sheet of the app.

var location = Location.FromUri(uri);
location.AsNtlmUserViaProxy();
var restClient = new RestClient(uri);
restClient.AsNtlmUserViaProxy();

using (var app = location.App(new AppIdentifier {AppId = appId}))
{
  var theSheet = app.GetSheets().First();
  var objs = theSheet.GetChildInfos().Select(info => app.GetGenericObject(info.Id));
  foreach (var o in objs)
  {
    var exportResult = o.ExportData(NxExportFileType.EXPORT_OOXML);
    var data = restClient.GetBytes(exportResult.Url);
    using (var writer = new BinaryWriter(new FileStream(o.Id + ".xlsx", FileMode.OpenOrCreate)))
    {
      writer.Write(data);
    }
  }
}

Upvotes: 1

Stefan Stoychev
Stefan Stoychev

Reputation: 5022

For this cases I'm usually "observing" Qlik's communication from the browser.

(in Chrome)

  • open the app
  • open the browser dev tools (press F12)
  • navigate to "Network" (1)
  • navigate to "WS" (2)
  • press the required socket session (3)
  • press "Messages" (4)
  • check whats being send/received in the socket

(if you cant see the sockets in the Network tab just refresh the page)

enter image description here

Below is a Javascript/Node code that is exporting data for one object. (Im hardcoding the object ID in my case)

const fs = require('fs');
const axios = require('axios');

const enigma = require('enigma.js');
const WebSocket = require('ws');
const schema = require('enigma.js/schemas/12.20.0.json');

const session = enigma.create({
    schema,
    url: 'ws://localhost:9076/app/engineData',
    createSocket: url => new WebSocket(url)
});

(async function () {
    // open new session
    let global = await session.open();

    // open the app
    let doc = await global.openDoc("C:\\Users\\USERNAME\\Documents\\Qlik\\Sense\\Apps\\Consumer_Sales.qvf");

    // get the required object
    let qObj = await doc.getObject("MEAjCJ");

    // OOXML - export the data in Excel (xlsx) format
    let data = await qObj.exportData("OOXML");

    // generate the full download link
    let downloadLink = `http://localhost:4848${data.qUrl}`;

    // download and save the file
    await axios.get(downloadLink, { responseType: "stream" })
        .then(response => {
            response.data.pipe(fs.createWriteStream("export.xlsx"));
        });
})()

Official documentation for the ExportData method is here

Update - Python code snippet

The loop through the sheet objects can look like this:

id = 5

list_of_charts = result_json['result']['qLayout']['qAppObjectList']['qItems'][0]['qData']['cells']
for chart in list_of_charts:

    obj_req = {
        "jsonrpc": "2.0",
        "method": "GetObject",
        "handle": app_req_handle,
        "params": [ chart["name"] ],
        "outKey": -1,
        "id": id
    }

    ws.send(json.dumps(obj_req))
    result = ws.recv()
    ws.send(json.dumps(obj_req))
    result = ws.recv()
    result_json = json.loads(result)
    # print(result_json)

    obj_req_handle = result_json['result']['qReturn']['qHandle']

    export_req = {
        "jsonrpc": "2.0",
        "method": "ExportData",
        "handle": obj_req_handle,
        "params": [ "OOXML" ],
        "outKey": -1,
        "id": 6
    }

    ws.send(json.dumps(export_req))
    result = ws.recv()
    ws.send(json.dumps(export_req))
    result = ws.recv()
    result_json = json.loads(result)

    downloadURL = "http://localhost:4848" + result_json["result"]["qUrl"]
    r = requests.get(downloadURL, allow_redirects=True)
    open('export_python_' + chart["name"] + '.xlsx', 'wb').write(r.content)

    id += 1

Upvotes: 2

Related Questions