Read and Write Spreadsheets

This page goes over the following example model on how to read and write to spreadsheets in desktop Analytica.

• Download Example Model & Spreadsheet

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 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. The definition of Spreadsheet Workbook is:

SpreadsheetOpen('example.xlsx', True)

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 inSpreadsheet Open] and imports all of the data contained in that sheet. It creates implicit indexes named Row and 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 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 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 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 called 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, "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 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).

See Also

• Example model and spreadsheet
• Reading Excel Worksheets.ana
• Excel spreadsheets read and write
• Functions To Read Excel Worksheets
• Functions to Write Data to Excel Worksheets
• Working with Excel Worksheets using the Excel 2013 Object Model.