Spreadsheets in ACP1 - Old
The functions to open, read, write, and save spreadsheets work almost the same in Analytica Cloud Player (ACP) as in Analytica on your own computer. They access spreadsheet files on the users ACP project directory. ACP also offers the option of uploading a spreadsheet from the end user's computer to the ACP directory on the web server, and downloading from the ACP directory back to the computer.
Upload and download spreadsheets in the ACP Models view
A simple way to upload and download spreadsheets is from the ACP Models view that lists the files in your ACP Project, in the same way you can upload or download Analytica models. This view also lists any spreadsheets or other files in the Project. You can upload a spreadsheet (or any file) simply by clicking the Upload button on bottom left. Similarly you can download a file by clicking the button in the Select column for that file, and clicking the Download button. (You need to have Author or Manager access to see the Upload and Download buttons.)
Upload and download spreadsheets from an ACP model
It's often more convenient for end users to be able to upload spreadsheets from their computer and download them again directly from an ACP model. In ACP, SpreadsheetOpen(filename) tries to find a file matching filename in the ACP Project directory. If it doesn't it shows the user a Windows file browser prompt to find a file on the user's own computer with that filename as the default. After the user selects a file, it uploads it to the ACP directory and opens it as a spreadsheet. You can force it always to open a file browser dialog by specifying SpreadsheetOpen(filename, ShowDialog: True).
Downloading a spreadsheet is slightly less obvious, since SpreadsheetSave(workbook) doesn't have a way to open a dialog to download a file (yet). Instead, create a button with title "Download spreadsheet", say. Add download_spreadsheet to its CloudPlayerStyles attribute. It will offer download the last opened spreadsheet in this session, if any. When the end user clicks the button, it will open a file browser dialog to select a directory to accept the file. Or you can specify a named spreadsheet 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.
use_async_calls: yes That is the default, so usually you don't need to worry about this.Example ACP Model for reading and writing spreadsheets
Here's an example to illustrate these methods:
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
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.
To read a spreadsheet in Analytica, you must first call SpreadsheetOpen(), which returns a link to the workbook. You can use SpreadsheetRange() to determine what to import into Analytica from the opened workbook.
In the example model, the definition of Spreadsheet_Workbook is:
SpreadsheetOpen('example.xlsx', ShowDialog: True)
It looks for a file named example.xlsx in the same directory as the model. Parameter ShowDialog: True means it will prompt you to select a file, and set the name, even if file example.xlsx already exists. For more details, see SpreadsheetOpen().
The definition of Spreadsheet_Range is:
SpreadsheetRange(Spreadsheet_Workbook, 'Raw Data!', howToIndex: 12)
When evaluated, it looks for the sheet Raw Data in Spreadsheet_Workbook. It imports all of the data contained in that sheet into Spreadsheet_Range. It creates implicit indexes Row and Column from the first row and first column of the data, and uses those to index the data. For details on other 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 you have opened a workbook in ACP, you 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).
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