With Canvas, you can upload an Excel file into your browser, validate the data and then send the data into TM1 using the REST API. A sample showcasing has been introduced in Canvas 2.0:

• http://localhost:8080/samples/#/sample/sample-sheetjs

This article explains in details how this sample work.

Enable SheetJS library

The first step is to enable the optional libray sheet JS, the steps are described in the following article:

• Configuring SheetJS

Upload Excel File

To upload an Excel file into your browser, you need to create an upload button that will handle and help us process the Excel file, within the client’s browser. To do that you just need to add and to utilize the js-xls directive as follows:

When you upload an Excel file, the read function is triggered and if there is an error, the error function is triggered.

You then need to define the read function. This function takes the processed workbook by SheetJS as an argument, initializes the variables. We will then call our parseWorksheet function which will help us extract the data from the Excel file and show it into our Canvas page:

The parseWorksheet function loops through all rows and associate the cell values, to an object we will name as record:

For example: for every row, we know that the product name is in Excel sheet’s column A. To then access the name for each row (A1, A2, A3, etc.):

var product = sht["A" +(r + 1)].v;

SheetJS creates an object and make the cells accessible via properties. A cell in Excel has different properties such as: the formula used, the actual display value, etc. But where we are interested in is in the property named ‘v’, which contains the value. 

We will then associate all values for a row to a record. Each record will have one Boolean property indicating if it was selected, the product name on that row, one value for each month on that row and an error message.:

var record = {      row: r - startRow + 1,      selected: true,      product: product,      m01: m01,m02: m02,m03: m03,m04: m04,m05: m05,m06: m06,      m07: m07,m08: m08,m09: m09,m10: m10,m11: m11,m12: m12,      error: error};

Each record will then gets pushed into an array stored within the lists.records object:

$scope.lists.records.push(record);

Near the end of this function, the lists.records should contain a list of record objects. Lastly, the validate function is then called to check these records for any errors or missing information.

The validate function loops through all the records we have created and check if the product is in the Product dimension. If it is not, it updates the error message and set its Boolean property to indicate selection, to false (this will make the record be not selected on the front end):

To display the records in the view, we use the ng-repeat directive in a table and iterate through the list of records we have prepared previously:

Send data into TM1 via REST API

Now that we have displayed the data in the browser, the last step is to allow the users to send this data to the right intersection of the TM1 cube:

We create first an array of CellPut request objects with the following properties:

• [{value:’Test’, instance:’dev’, cube:’System Info’, cubeElements:[‘Server Time’, ‘String’]}]

We need one request per month:

Once the array is populated, we just need to use one of the available Canvas scripting service for sending data into TM1 – $tm1Ui’s service cellsetPut:

This service should then send a batch of cells into TM1 for update. In the case above, this should batch and send the cells within the row all at the same time into TM1 for updating.

If you prefer to send data into TM1 line per cell, use cellPut instead of cellsetPut:

• cellPut(value, instance, cube, element1, element2, elementN)