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 ACP. Media:SpreadsheetOpenSampleDTA.ana Media:SpreadsheetOpenSampleDTA.ana

In the example model, the Read Spreadsheet button will prompt a user to select a workbook and copy the contents of that workbook into the Saved Data node. The Save Results to Spreadsheet button will copy the results of the Miles per Year node into the original workbook file, and save that as a new workbook file named "saved_example".

Reading Spreadsheets

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.

Definition of Spreadsheet Workbook: SpreadsheetOpen('example.xlsx',True) Definition of Spreadsheet Range: SpreadsheetRange(Spreadsheet_Workbook,'Raw Data!',howToIndex:12)

Spreadsheet Open opens a workbook file. It will look for the file named "example.xlsx", and even if it finds that file in the same directory as the model, it will prompt 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 will look for the sheet named "Raw Data" in the workbook opened in Spreadsheet Open and import all of the data contained in that sheet. It will create implicit indexes named Row and Column out of the first row and first column of the data, and use those to index the data. For more details on different parameter options, see SpreadsheetRange().

Opening Multiple Spreadsheets per Session

Analytica will only prompt the user to select a spreadsheet until a spreadsheet has been selected that session. 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 Spreadsheet Open.

In the example model, this is done in the OnClick of the Read Spreadsheet button as follows:

DEFINITION OF 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 would not reset the workbook link and would continue to use the existing workbook.

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 implixit 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 Analytica, the user can write data to the workbook using SpreadsheetSetCell(), SpreadsheetSetRange(), and SpreadsheetSetInfo().

Note

If any changes are made to a workbook from Analytica, SpreadsheetSave() must be used for those changes to be saved

In the example model, this is done in the OnClick of the Save Results to Spreadsheet button:

SpreadsheetSetCell( Spreadsheet_Workbook, "Miles per Year", 1, @Car_Type, Car_Type);
SpreadsheetSetCell( Spreadsheet_Workbook, "Miles per Year", 2, @Car_Type, Miles_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 "Miles per Year" and the variable Miles per Year to the second column in the sheet named "Miles 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 will first prompt the user to select a workbook before completing the calculations. However, Analytica 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).

See Also

• Spreadsheets in ACP
• Functions To Read Excel Worksheets
• Functions to Write Data to Excel Worksheets