Step 1: alasql => 'bower install --save alasql filesaver jzip'
Step 2: exclude it from the post-head script includes (if using grunt)
wiredep: {
app: {
src: ['<%= YourApp.app %>/index.html'],
exclude: [
'bower_components/angular/angular.js',
'bower_components/file-saver/FileSaver.js',
'bower_components/js-xls/dist/xls.js',
'bower_components/js-xlsx/dist/xlsx.js',
'bower_components/es6-promise/promise.js',
'bower_components/alasql/dist/alasql.min.js',
'bower_components/jzip/jzip.js'
],
ignorePath: /\.\.\//
},
Step 3: move the appropriate script calls BEFORE the qlik reauirejs
<script src="bower_components/file-saver/FileSaver.js"></script>
<script src="bower_components/jzip/jzip.js"></script>
<script src="bower_components/js-xls/dist/xls.js"></script>
<script src="bower_components/js-xlsx/dist/xlsx.js"></script>
<script src="bower_components/es6-promise/promise.js"></script>
<script src="bower_components/alasql/dist/alasql.min.js"></script>
<!-- qlik -->
<script src="http://YourHost:YourPort/resources/assets/external/requirejs/require.js"></script>
<link rel="stylesheet" href="http://YourHost:YourPort/resources/autogenerated/qlik-styles.css">
<!-- qlik -->
<base href="/">
</head>
<body class="ng-cloak">
Step 4: add this to a service factory in your app. or call it directly as a function (whichever you prefer -- my factory is called 'engine', method 'createQube' expecting a list of dimension fields and measures in the sample structure found at the end of the file):
createQube: function(dimFields, measureDef, showTotals, cBack){
console.info('cube by field requested...');
var Qube = function(qDimensions, qMeasures){
var self = this;
self = {
qInitialDataFetch: [{
qHeight: 200,
qWidth: 20
}],
qDimensions: qDimensions,
qMeasures: qMeasures,
qSuppressZero: false,
qSuppressMissing: false,
qMode: "S",
InterColumnSortOrder: [],
qStateName: "$"
};
return self;
};
var Dim = function(fieldName, fieldLabel, dimGrouping, nullSuppressed){
var self = this;
self = {
qNullSuppression: nullSuppressed === undefined? false : nullSuppressed,
qDef: {
qGrouping: dimGrouping === undefined ? 'N' : dimGrouping,
qFieldDefs: [
fieldName
],
qFieldLabels: [
fieldLabel
]
}
} ;
return self;
};
var Calc = function(measureExp, measureLabel, expGrouping, expDescription, tagArray){
var self = this;
self = {qDef: {
qLabel: measureLabel,
qDescription: expDescription,
qTags: tagArray,
qGrouping: expGrouping === undefined ? 'N' : expGrouping,
qDef: measureExp
}
};
return self;
};
var Dims = [];
var Calcs = [];
angular.forEach(dimFields, function(key){
this.push(Dim(key.value, key.label));
},Dims);
// console.debug(Dims);
angular.forEach(measureDef, function(key){
this.push(Calc(key.value, key.label));
},Calcs);
function bindQube(dimensions, measures) {
return Qube(dimensions, measures);
}
// console.log(Calcs);
// var mColumns = angular.extend({},{}, Dims,Calcs);
// var mDims = $.merge(cDims, Dims);
// var mCalcs = $.merge(cCalcs, Calcs);
// var mColumns = $.merge(mDims,mCalcs);
function qCubeCall(reply, app) {
var header = [];
var dimsRows = reply.qHyperCube.qDimensionInfo;
var expRows = reply.qHyperCube.qMeasureInfo;
// var headerRow = {};
angular.forEach(dimsRows, function (row) {
// header[header.length] = row.qFallbackTitle;
this.push( row.qFallbackTitle);
console.info(row.qFallbackTitle);
}, header);
angular.forEach(expRows, function (row) {
// header[header.length] = row.qFallbackTitle;
this.push( row.qFallbackTitle);
console.info(row.qFallbackTitle);
}, header);
// header.shift();
console.info(header);
// console.info(reply.qHyperCube.qDimensionInfo);
// console.info(reply.qHyperCube.qMeasureInfo);
// console.info(reply.qHyperCube.qDataPages[0].qMatrix);
var result = reply.qHyperCube.qDataPages[0].qMatrix;
var exportTable = [];
angular.forEach(result, function(row){
var column= [];
angular.forEach(row, function (cell) {
column.push(cell.qText);
});
exportTable.push(column);
});
console.info(exportTable);
var data = exportTable;
data[0] = header;
data[(data.length - 1)][0] = ['Total'];
// // console.log(data);
var res = alasql('SELECT * INTO XLSX("list.xlsx",{headers:false}) FROM ?',[data]);
console.log(res);
// var res2 = alasql.('SELECT SUM([1]) INTO XLSX("list.xlsx",{headers:false}) FROM ?',[data]);
// console.log(res2);
// saveAs(new Blob([s2ab(res)],{type:""}), "test.xlsx");
}
// console.debug(mColumns);
senseApp.createCube(bindQube(Dims, Calcs), qCubeCall).then(function(reply){
// console.info(reply);
// console.info(reply.handle);
// senseApp.exportData(reply);
// var qTable = qlik.table(reply.id);
// qTable.exportData({download: true});
/*
var xHandle = reply.handle;
var requestExport = function(handle){
var self = this;
self = {
jsonrpc: "2.0",
id: 3,
method: "ExportData",
handle: handle,
params: [
"OOXML",
"/qHyperCubeDef"
]
};
return self;
};
senseApp.global.session.rpc(requestExport(xHandle)).then(function (data) {
console.info(data.result.qUrl);
});*/
// return reply;
});
}
Step 5: call the service method from any scope as you see fit -- something like this:
$scope.xData = function(){
engine.createQube($scope.cDimensions, $scope.cMeasures, false, 'main');
};
/* Sample Data Structure Reference: */
$scope.dimensions = {};
$scope.dimensions = [
{ label: 'Publisher', value: 'Publisher'},
{ label: 'Placement', value: 'Placement'}
];
$scope.measuress = {};
$scope.measures = [
{ label: 'Weighted Gross', value: '=Sum(gross_imp)*.5', id: 'measure1'},
{ label: 'Gross Impressions', value: '=Sum(gross_imp)', id: 'measure2'},
{ label: 'Total Validated Impression Universe', value: '=$(Total Validated Impression Universe)', id: 'measure3'},
{ label: 'Validated In-Target Imps', value: '=$(Validated In-Target Imps)', id: 'measure4'}
];