Spreadsheets in ACP1 - Old

The Analytica Functions that open a spreadsheet, and read from it, or write to it also work in ACP. These functions open, read, write a spreadsheet in the same directory as the ACP model being run. If you call SpreadsheetOpen with parameters Dialog: True, it first prompts the user to upload a spreadsheet from his computer into this directory. You can also set a model to download a spreadsheet from ACP -- for example, with saved results.

For more information about spreadsheet functions in Desktop Analytica, see: Functions To Read Excel Worksheets and Functions to Write Data to Excel Worksheets

SpreadsheetOpen in ACP

The function SpreadsheetOpen(filename) works almost the same in ACP as it does in Analytica on the desktop. If it finds an Excel file filename.xls in the server directory from which the ACP model was loaded it opens that file and returns the Workbook object. You can upload a spreadsheet to an ACP directory from the ACP front-end in the same way you can upload a model. Just click on the Upload button.

If SpreadsheetOpen(filename) doesn't find that file, or if you include the optional parameter,

 SpreadsheetOpen(showDialog: True)

it shows a dialog box prompting the user for a spreadsheet to upload from their desktop computer.

If the user provides a spreadsheet, it loads it into the same directory and opens it to read or write from the ACP model. If the ACP directory already contains a file with that name, ACP asks if you want to overwrite the existing file with the new file to be uploaded. As in Desktop Analytica, the user doesn't have to select a spreadsheet with the same filename as the «filename» parameter, but after the model is closed ACP will not remember any filenames aside from what is used in the «filename» parameter.

Note: In order to upload spreadsheets from the model, the Cloud Player Styles attribute must have "use_async_calls: yes" included in the top level of the model.

Unlike Desktop Analytica, ACP may only prompt a user to upload a spreadsheet once after the model is opened. Is this still true? If you would like Analytica to prompt a user multiple times, the definition of the SpreadsheetOpen() variable may need to be reset. For example, if Va1 calls SpreadsheetOpen(), you can create a duplicate of Va1 called Va2 and use a button script to set the definition of Va1 as the definition Va2 like so:

Definition of Va1: SpreadsheetOpen('example.xls',True)
Definition of Va2: SpreadsheetOpen('example.xls',True)

OnClick for Reset Button
Definition of Va1 := Definition of Va2;

After pressing the Reset button, the user will be able to upload a new spreadsheet when Va1 is evaluated regardless if a spreadsheet has already been uploaded.

Download a spreadsheet from ACP

Users often like to be able to download a spreadsheet with results from an ACP model.

If you are an Author or Administrator in your ACP account, you can download a spreadsheet (or any file) from the ACP directory view: Simply select the radio button for the spreadsheet file you want, and then click the Download button. Can't you do this if you are simply a reviewer?

You can also add a button to an ACP model to let users write and download a spreadsheet with ACP results:

Create a button called say "Download results".
Add "download_spreadsheet: abcxyz.xls" to the CloudPlayerStyles Attribute of the button.
To the Onclick attribute of the Button, add a function that writes results to that spreadsheet.

When the user presses the button, it opens a dialog box prompting the user to download the spreadsheet file "abcxyz.xls". If it finds no spreadsheet with the name "abcxyz.xls", ACP will still download a file named "abcxyz.xls" but this will be 0 bytes and will contain nothing.

Tip: You can find out the filename of the spreadsheet opened using Analytica's built-in SpreadsheetInfo function with the second parameter set to 'Name'.

You can also simply put "download_spreadsheet" into the CloudPlayerStyles attribute of the button. In this case, it will prompt to download the last spreadsheet was uploaded into ACP, if any.