Read and Write Spreadsheets
This page goes over the following example model on how to read and write to spreadsheets in desktop Analytica. See Spreadsheets in ACP to see the parallel example model for Analytica Cloud Player ACP.
In the example model, the Read Spreadsheet button prompts a user to select a workbook and copies the contents of that workbook into the
Saved Data node. The Save Results to Spreadsheet button copies the results of the
Miles per Year node into the original workbook file, and saves that as a new workbook file named
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.
The definition of SpreadsheetRange 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 Rangelooks 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
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, Analytica 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 Analytica and the opened spreadsheet must be cut. This can be done by changing the definition of
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 Analytica 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 </code>Column</code> into existing Index nodes
Car Type and
Car Info, respectively. The last line copies the data from </code>Spreadsheet Range</code> into the
Saved Data variable, and reindexes the data from the implicit indexes to the new global indexes.
Writing to Spreadsheets
In the example model, this is done in the OnClick of the Save Results to Spreadsheet button:
SpreadsheetSetCell(Spreadsheet_Workbook, "Gallons per Year", 1, @Car_Type, Car_Type);
SpreadsheetSetCell(Spreadsheet_Workbook, "Gallons per Year", 2, @Car_Type, Gallons_per_Year);
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 automatically saved in the same directory as the opened workbook. For more details on different parameter options, see SpreadsheetSave().
If no workbook is already open and the Save Results to Spreadsheet button, Analytica first prompts the user to select a workbook before completing the calculations. However, it will only execute the OnClick of the Save Results to Spreadsheet 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).