Spreadsheets in ACP1 - Old

<<Back to Analytica Cloud Platform

The functions to open, read, write, and save spreadsheets work almost the same in Analytica Cloud Platform (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 model (and spreadsheet) files in your ACP Project. You can upload or download Analytica spreadsheets just as you an upload and download model files. To upload a file, you simply click the Upload button on bottom left. To download a file, you click the radio button in the Select column for that file, and click the Download button. (Reviewers may upload Spreadsheets, and may download spreadsheets which they uploaded. Otherwise you need to have Author or Manager access to the project to use the Upload and Download buttons.)

In ACP, Press Upload

ACP recognizes both Analytica model and Excel spreadsheet files.

Upload both the Model file and the Spreadsheet files to ACP. You can download files to try it out here: Reading Excel Worksheets.ana and SolvSamp.xls.
Both files can now be seen in the Model listing

Now you can play the model which uses the spreadsheet functions as long as the spreadsheet is present in the model list. You can't open the spreadsheet in ACP directly or send it by Email invite. The Email Invite button is disabled when the spreadsheet's radio button is selected}.

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 AcpStyles attribute. When the end user clicks the button, it offers to download the last spreadsheet uploaded in this session, if any. When the end user clicks the button, it opens 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 AcpStyles attribute. This will download the file named filename.xlsx in the same ACP directory as the model. {In this case, if the file is not present then ACP will give an error}.

Similarly, you can upload a spreadsheet, without using Excel, by creating a button with the flag Upload_spreadsheet in its' AcpStyles attribute. When the user clicks the button, they are prompted to upload a spreadsheet file. This takes place after the onclick completes (for the case where an onclick has more than one line). There is currently no way to name the spreadsheet. However, to retrieve the filename, you can save the spreadsheet to the definition of a variable: In the button CPS attribute if you have the following:

upload_spreadsheet Upload_filename_variable: Var_x

After uploading the file, ACP will write the filename (e.g. "ssFile.xlsx" note current does not include full path) to the definition of Var_x

CSV files

CSV files are supported by ACP.

You need to have the Flat File library in your model to access the functions to read/write/parse these files.
To access csv file data in ACP you upload the CSV file as detailed above for a spreadsheet file.

ReadTextFile

ReadTextFile basically works the same in ACP as SpreadsheetOpen does while playing a model. For a variable which calls this function, ACP will show a file upload dialog which will allow the user to upload the text document and use the data in models played in that ACP account.

Example ACP Model for reading and writing spreadsheets

Here's an example to illustrate these methods:

Download Example Model & Spreadsheet

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, or rename the existing spreadsheet.

Note that you cannot save a spreadsheet if it is locked by Excel. So if you decide you want to rename or overwrite a file after accessing it in the same session, you will need to cut the link to the spreadsheet first, as described below: Multiple Spreadsheets in One Session.

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().

Note

If any changes are made to a workbook from ACP, SpreadsheetSave() must be called for those changes to be saved. In order to write to spreadsheets from a model, the Acp Styles attribute must have 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).

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', and use JoinText() to add it to "download_spreadsheet".

In the example model, downloading is achieved by placing the following in the AcpStyles 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.