XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Web Development

XLCubed Web Edition allows application developers to embed XLCubed reports in their own web pages.

Several reports can be embedded in a single page and linked together using parameters as required, allowing you to build custom pages out of existing published workbooks.

This page describes the web API, and the html and javascript required to embed reports.

Direct report linking

It is possible to link to a published report directly using the following URL syntax:

http://Server/XLCubedWeb/WebForm/ShowReport.aspx?rep=folder/report.xml

This can also include any Web Parameters to allow specific criteria to be passed.

Embedding Workbooks

Standards Mode

XLCubed does not support "Quirks Mode" in Internet Explorer. To ensure that IE renders your page in standards mode you will need to put a suitable doctype at the top of your page.

Sample doctypes can be found on wikipedia.

Required Includes

Embedded XLCubed reports reference various javascript libraries which must be included in your page.

You should change the references to "MyServerName" to the name of the XLCubed web site, eg: "WebServer01/XLCubedWeb"

Required Css: 

<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubedWeb.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubed.Workbook.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery-ui-1.8.custom.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery.treeview.css" />

Required Javascript: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.treeview.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.treeview.async.js"></script>
    
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.cookie.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.n-contextmenu.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.scrollTo-1.4.2-min.js"></script>
    
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery.xlcubedextensions.js"></script>
<script type="text/javascript" src="http://MyServerName/js/microsoft/microsoftajax.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.Web.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.Workbook.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.Toolbars.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.Repository.js"></script>

<script type="text/javascript" src="http://MyServerName/js/XLCubed.DialogManager.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.Controls.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.Dialogs.js"></script>
        
<script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
Updates for version 6.5

As of version 6.5 many resources have been consolidated, and you can instead include the following.

Required Css: 

<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery-ui-1.8.custom.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery.treeview.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubed.min.css" />

Required Javascript: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
<script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>
Updates for version 7.0

As of version 7.0 the version of jQuery has been updated to 1.7.2, so the includes are as follows.

Required Css: 

<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery-ui-1.8.custom.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/redmond/jquery.treeview.css" />
<link rel="stylesheet" type="text/css" href="http://MyServerName/Css/XLCubed.min.css" />

Required Javascript: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.7.2.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
<script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>


Updates for version 8.0

The jQuery version has been incremented, so now the following files should be included instead of the previous version of jQuery. jQuery-ui is still required. 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-1.2.1.min.js"></script>
Updates for version 9.2

The jQuery-ui version has been incremented, so now the update file should be included instead of the previous version of jQuery-ui. Additionally an extra file is required for interactive charts, so the fill include list is: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-1.2.1.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.12.1.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
<script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
<script type="text/javascript" src="http://MyServerName/js/SmallMultiples/ChartJS/Chart.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>
Updates for version 10.0.001 to 10.0.152

The jQuery version has been updated, so the version number has changed and a new migrate script is required. The new include list is: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-3.4.1.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-3.1.0.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-xlcubed.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.12.1.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
<script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
<script type="text/javascript" src="http://MyServerName/js/SmallMultiples/ChartJS/Chart.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>


Updates for version 10.0.153

The jQuery version has been updated, so the version number has changed and a new migrate script is required. The new include list is: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-3.6.1.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-3.4.0.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-migrate-xlcubed.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.13.2.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
<script type="text/javascript" src="http://MyServerName/WebServices/WorkbookService.svc/js"></script>
<script type="text/javascript" src="http://MyServerName/js/SmallMultiples/ChartJS/Chart.js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>

Page Markup

XLCubed reports are loaded into a div element, which requires markup in the following style 

<div id="workbookdiv" style="position:relative;height:300px;width:600px;border:1px solid silver;"></div>

The id attribute is mandatory, and must be unique in the page.

The div must have the position of its style set, either inline as above or via CSS. It must be relative or absolute.

Javascript

Loading

Initialisation of the workbook is done via javascript, the simplest example is as follows: 

<script>
function loadXLCubed(){
    //Initialise XLCubed
    var xlcubedOptions = { BaseUrl:"http://MyServerName" };
    XLCubed.Settings.Init(xlcubedOptions);

    //create the XLCubed workbook
    var div = document.getElementById("workbookdiv");
    var book = new XLCubed.Workbook(div);

    //load a report into the workbook
    book.Load("MyFolder/MyReport.xml");
}
</script>

Here MyServerName must again be replaced with the actual XLCubed web site.

The string passed to the Load() function is the path in the repository to an existing report published from Excel.

Optionally you can pass parameters to the new XLCubed.Workbook() call to customise the appearance of the report 

var opts = {	HorizontalScrollBar:false,
		VerticalScrollBar:true,
		SheetTabs:true,
		ContextMenus:false };

var book = new XLCubed.Workbook(div, opts);

If you wish to load a different report, then simply call 

book.Load()

again on the same book variable, do not create a second book in the same div.

Impersonation

XLCubed can impersonate a named user if required. To do this the following API can be called. 

//Set up the user we want to run queries as
var logonOptions = {	Username:"MyUsername",
			Domain:"MyDomain",
			Password:"MyPassword"};
		
XLCubed.Settings.Logon(logonOptions);

The Logon() function must be called after XLCubed.Settings.Init() and before book.Load()

Parameters

If a report is published with web parameters, these can be setup through the API.

You can load a report with parameters set as follows: 

//create book as above...

var params = {	Date:"January 2010",
		Geography:"Europe" };
		
book.Load("MyFolder/MyReport.xml", params);

After a book has been loaded you can update the parameters as follows: 

//book variable from elsewhere, the report is already loaded

var params = {	Date:"March 2010",
		Geography:"America" };
		
book.ApplyParameters(params);

You can apply multiple values at once by passing an array: 

//book variable from elsewhere, the report is already loaded

var params = {	Geography:["America", "Europe"] };
		
book.ApplyParameters(params);

As of Version 7.6 you can also retrieve the current values of the parameters: 

//book variable from elsewhere, the report is already loaded

var params = book.GetParameters();
		
for(var param in params){
	alert(param + " = " + params[param]);
}
Toolbar

You can create a toolbar for your report using the following

Markup: 

<div id="toolbar"></div>

Script: 

//book already created
var toolDiv = document.getElementById("toolbar");
var toolbar = new XLCubed.WorkbookToolbar(toolDiv, book);
Events

An XLCubed workbook will fire events which you can attach to to customise your page and link reports together.

Binding to events uses the following syntax 

//book already created
book.Bind(eventName, eventHandlerFunction)

The eventHandlerFunction takes two arguments, an event object and a data object containing information about the event that fired. 

function loadXLCubed(){
    //book already created
    book.Bind("load", loadHandler);
}


function loadHandler(e, data){
    //we can get a reference to the book from here
    var b = data.workbook;
    alert("loaded");
}

The events fired are

Event Name Fired When Data Properties
load Workbook load completed workbook
render Workbook is updated and rendered workbook
exception An error occured workbook

message //text of error

cancel //set to true to stop display of message to user

expire Users session timed out workbook
dimensionslicerchange User updates a slicer workbook

name //slicer name if setup

items // array of unique names selected

xl3linkclick User follows an XL3Link() formula workbook

linkAddress

linkTo

linkType

values // javascript object, mapping cells to values

memberclick User clicks a member in a grid or formula workbook

cell

cellType ("gridmember", or "memberlookup")

hierarchy (unique name)

getMember (callback function to get member metadata, receives an object) 

data.getMember(function(metadata){ });
cubecellclick (New in Version 9) User clicks data in a grid or formula workbook

cell

cellType ("gridcell", or "lookup")

location cell position {x:number, y:number}

gridlocation grid only, cell position in the grid data

getCubeCell (callback function to get cell metadata, receives an object) 

data.getCubeCell(function(cellInfo){ });
parameterschanged Cell parameter values changed workbook

parameters

Methods

An XLCubed workbook has a public API which you can call once a book has been loaded.

Method Description
Load(path) Loads the requested report, closes any existing report.
RecalculateAll() Causes all grids, tables and formulae to be refreshed.
ApplyParameters(params) params is a JS literal object. Updates the values in the workbook and refreshes the display.
GetParameters() Returns a JS literal object of the current parameter values.
Bind(eventName, eventHandlerFunction) Attaches the handler to the specified event.
SaveToExcel(keeplive) Saves the workbook and downloads it. The keeplive parameter controls whether the book is fixed or saved as a live XLCubed report. Possible values as "NOTHING" and "FORMULAANDGRID".
GetCellValues(address, callback) Gets the values in the specified range, and returns them as an array. Callback is called with the array, whose first dimension is the row, and second is the column. (i.e. An array of arrays) New in Version 9
SubmitChanges() Commits any changes made by the user and recalculates the workbook.
Object Reference
  • cellInfo

This object has the cell value, and information on the tuple coordinates. A tuple axis has the hierarchies on the axis, and the members in an array.

For normal axes the name will be "axis" + idx, where idx is the axis position. E.g. 0 = Columns, 1 = Rows. 

{
    "value":object,

    "coordinates":[
       {"axis":"where",
        "hierarchies":[
            {"uniqueName":"hier1",
             "members":["mem1"]},
            {"uniqeName":"hier2",
             "members":["mem2a", "mem2b"]}
       ]},
       {"axis":"axis0",
        "hierarchies":[
            {"uniqeName":"hier3",
             "members":["mem3"]}
       ]} ...
    ]
}

Cross domain considerations

If the XLCubed web site is hosted in a different domain to the containing web site then access to some elements is restricted by default. This typically shows up as an "Access denied" message after the report has loaded.

To allow correct access to the hosted site you should add the following line to your web.config. 

<add key="LinkElementCrossOrigin" value="use-credentials"/>

and add the following line after the call to XLCubed.Settings.Init(); 

XLCubed.Settings.LinkElementCrossOrigin = "use-credentials";

Embedding Repository

Required Includes

You can query the repository to get a list of the folders and files available on the server. To do this you must reference a couple of javascript files.

You should change the references to "MyServerName" to the name of the XLCubed web site, eg: "WebServer01/XLCubedWeb"

Required Css: 

None

Required Javascript: 

<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-1.7.2.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/JQuery/jquery-ui-1.8.custom.min.js"></script>
<script type="text/javascript" src="http://MyServerName/js/Microsoft/MicrosoftAjax.js"></script>        
<script type="text/javascript" src="http://MyServerName/WebServices/RepositoryService.svc/js"></script>
<script type="text/javascript" src="http://MyServerName/js/XLCubed.min.js"></script>

Javascript

Loading

Initialisation of the repository is done via javascript, the simplest example is as follows: 

<script>
function loadXLCubed(){
    //Initialise XLCubed
    var xlcubedOptions = { BaseUrl:"http://MyServerName" };
    XLCubed.Settings.Init(xlcubedOptions);

    //create a repository object
    var oRep = new XLCubed.Repository();
    //and query the server.
    //This is asynchronous, so pass in a callback to handle the results
    oRep.GetRepository(
	function(oTopFolder)
	{
		alert(oTopFolder.folders.length);
	});
}
</script>

Here MyServerName must again be replaced with the actual XLCubed web site.

The oTopFolder object is an instance of the folder class, which itself contains file.

You can then iterate over the contents of the top level folder to create your own UI, or do anything else you'd like.

Object Reference
Folder
Property Content Type
path String
name String
folders Folder[]
files Files[]
File
Property Content Type
path String
name String
description String
publishedby String
datepublished Date

Parameters

This was added in Version 9

You can retrieve the names of the report parameters in javascript, for example 

<script>
function loadXLCubed(){
    //Initialise XLCubed
    var xlcubedOptions = { BaseUrl:"http://MyServerName" };
    XLCubed.Settings.Init(xlcubedOptions);
    //fill this in with a real path of a report
    var reportPath = "???";

    //create a repository object
    var oRep = new XLCubed.Repository();
    //and query the server.
    //This is asynchronous, so pass in a callback to handle the results
    oRep.GetReportParameters(reportPath,
	function(parameterNames)
	{
		alert(parameterNames.length);
	});
}
</script>

Customising HTML

From Version 7.1 it is possible to configure the HTML output of XLCubed web reports. This is configured on the XLCubed format sheet in Excel.

The area is in column Y, and is hidden by default so if you want to use this feature you'll have to unhide the relevant columns.

Column Content
Object Type "Grid" or "Table"
Name Name of the object to customise. If blank will be used as the default.
Area Report area to customise, see below.
CssClass Value for the class attribute of the cell.

Area Names

Grid

Possible values are:

  • FiltMemLabel
  • FiltMem
  • ColMemLabel
  • ColPropLabel
  • ColMem
  • ColProp
  • RowMemLabel
  • RowPropLabel
  • RowMem
  • RowProp
  • Data
Table

Possible values are:

  • WhereLabel
  • WhereMem
  • Header
  • Data
Retrieved from ‘https://help.xlcubed.com/index.php?title=Web_Development&oldid=10889’