Spreadsheets in ACP1 - Old
Spreadsheet functions in Analytica work nearly the same in Analytica Cloud Player (ACP). This page goes over the following example model as well as general knowledge on how to read and write to spreadsheets in ACP. See Read and Write Spreadsheets to see the parallel example model for desktop Analytica.
use_async_calls: yes {Now the default} included in the top level of the model.
In the example model, the Read Spreadsheet button prompts a user to upload a workbook and copies the contents of that workbook into the Saved Data node. The Download Spreadsheet with Results button copies the results of the Gallons per Year node into the original workbook file, and saves that as a new workbook file named saved_example.
Reading Spreadsheets
Opening spreadsheets in ACP is similar to opening spreadsheets in desktop Analytica; the main difference is that in ACP, the selected spreadsheet needs to be uploaded to ACP. Spreadsheets can be uploaded to ACP directly from the ACP front-end the same way a model would be uploaded -- by pressing the Upload button and selecting the desired spreadsheet. Similarly to desktop Analytica, If no spreadsheet is found when required, an ACP model will prompt the user to select one. Unlike desktop Analytica, ACP will actually upload the selected spreadsheet to the same ACP directory the model is in. If there is an existing spreadsheet with the same name in the same directory, ACP will ask if the user would like to overwrite the existing file.
use_async_calls: yes {Now the default} included in the top level of the model.To read a spreadsheet in Analytica, the user must first open a workbook using SpreadsheetOpen(). Then the user can use SpreadsheetRange() to determine what to import into Analytica from the opened workbook.
In the example model, the variable nodes Spreadsheet Workbook and Spreadsheet Range contain SpreadsheetOpen() and SpreadsheetRange(), respectively. The definition of Spreadsheet Workbook is:
SpreadsheetOpen('example.xlsx', True)
The definition of Spreadsheet Range is:
SpreadsheetRange(Spreadsheet_Workbook, 'Raw Data!', howToIndex: 12)
Spreadsheet Open opens a workbook file. It looks for a file named example.xlsx in the same directory as the model, but even if such a file exists, evaluating this node prompts the user to select a file. The user can select a file with any name. For more details on different parameter options, see SpreadsheetOpen().
Spreadsheet Range looks for the sheet named Raw Data in the workbook opened in Spreadsheet Open and imports all of the data contained in that sheet. It creates implicit indexes named Row and Column out of the first row and first column of the data, and uses those to index the data. For more details on different parameter options, see SpreadsheetRange().
Opening Multiple Spreadsheets per Session
The example model prompts the user to select a spreadsheet every time the Read Spreadsheet button is pressed, regardless of whether a spreadsheet has already been selected in that session.
Generally speaking, when a node contains SpreadsheetOpen(), even if the «showDialog» parameter is set to true it only prompts the user to select a spreadsheet until a spreadsheet has been selected. Once a spreadsheet has been selected, ACP will not ask again. If the user would like to open more than one spreadsheet for the same variable in the same session, the link between ACP and the opened spreadsheet must be cut. This can be done by changing the definition of Spreadsheet Open.
In the example model, this is accomplished in the OnClick of the Read Spreadsheet button as follows:
Spreadsheet_Workbook := 0;DEFINITION OF Spreadsheet_Workbook := "SpreadsheetOpen('example.xlsx', True)";
Both lines must be used in order to reset the definition -- if only the second line of code was used, because it is exactly the same as the existing definition ACP will not reset the workbook link and will continue to use the existing workbook. The first line cuts the workbook link and the second line resets the definition to a call to SpreadsheetOpen().
Saving Spreadsheet Data to a New Variable
It is best practice to copy data from a spreadsheet into a new variable so that the data can be manipulated and the model can run even if the spreadsheet is not available. In the example model, this is done in the OnClick code of the Read Spreadsheet button as follows.
Car_Type := CopyIndex(Spreadsheet_Range.Row);Car_Info := CopyIndex(Spreadsheet_Range.Column);Saved_Data := Spreadsheet_Range[@.Row = @Car_Type, @.Column = @Car_Info];
The first two lines copy the implicit indexes Row and Column into existing Index nodes Car Type and Car Info, respectively. The last line copies the data from Spreadsheet Range into the Saved Data variable, and reindexes the data from the implicit indexes to the new global indexes.
Writing to Spreadsheets
Once a workbook has been opened in ACP, the user can write data to the workbook using SpreadsheetSetCell(), SpreadsheetSetRange(), and SpreadsheetSetInfo().
use_async_calls: yes { Now the default so should not be necessary } included in the top level of the model.In the example model, this is done in the OnClick of the Download Spreadsheet with Results button:
SpreadsheetSetCell(Spreadsheet_Workbook, "Gallons per Year", 1, @Car_Type, Car_Type);SpreadsheetSetCell(Spreadsheet_Workbook, "Gallons per Year", 2, @Car_Type, Gallons_per_Year);SpreadsheetSave(Spreadsheet_Workbook, 'saved_example');
The first two lines write the contents of the Car Type index to the first column in the sheet named Gallons per Year and the variable Gallons per Year to the second column in the sheet named Gallons per Year. For more details on different parameter options, see SpreadsheetSetCell().
The last line saves the updated spreadsheet into a new file named saved_example. This file is not saved in the ACP directory for future sessions, but can be downloaded during the current session. If instead new filename was specified, ACP would save over the existing file, which can be accessed in future attempts. For more details on different parameter options, see SpreadsheetSave().
If no workbook is already open and the Download Spreadsheet with Results button, ACP first prompts the user to select a workbook before completing the calculations. However, it will only execute the OnClick of the Download Spreadsheet with Results button if a workbook is opened this way and ignore any OnClick code in the Read Spreadsheet button (i.e., it will not copy the data from the opened spreadsheet to Saved Data and therefore will be saving the existing results to the spreadsheet, not results from the newly uploaded spreadsheet).
Downloading Spreadsheets
Users often like to be able to download a spreadsheet with results from an ACP model.
If a user is an Author or Administrator in their ACP account, they can download a spreadsheet (or any file) from the ACP directory view: Simply select the radio button for the spreadsheet file you want from your ACP account's model directory, and then click the Download button.
In a model, any button can be set as a download button by adding download_spreadsheet to its CloudPlayerStyles attribute. This will download the last uploaded spreadsheet in the same session, if any exists. The user can also specify a specific spreadsheet to download by adding download_spreadsheet: filename.xlsx to its CloudPlayerStyles attribute. This will download the file named filename.xlsx in the same ACP directory as the model. If no such file exists, ACP will still start a download for filename.xlsx but the file will be 0 bytes and will contain nothing.
In the example model, downloading is achieved by placing the following in the CloudPlayerStyles attribute of the Download Spreadsheet with Results button:
download_spreadsheet:saved_example.xlsx
This causes the button to trigger a download of the file named "saved_example.xlsx".
Enable comment auto-refresher