Exporting data to MS Excel workbook

To export data from AnyLogic database tables to MS Excel file

1. In the Projects view, click the model’s  Database item.
2. In the Properties view, expand the Export section.

   

3. Before doing the export, you need to set it up. If you have not done this yet, click the Export settings... label.
4. You will see the Export settings dialog. Using the Browse... button, select the MS Excel file where you want to write the information. The button switches the path between absolute and relative. The path to the file will be displayed in the Export to field of the DB properties’ Export section.
5. Below, in the Select tables you want to export list, select the check boxes for the database tables you want to export to MS Excel workbook.
6. We have finished defining the export settings. Click the OK button.

   

7. If you want to perform the data export, in the Database properties, click the label Export tables to Excel.

8. If you want data to be exported every time when the model execution is finished or terminated by the user, select the check box Export tables at the end of model execution.

   

If the export was successful, you can open the MS Excel file, and see that AnyLogic added new worksheet(s), one per each exported database table, and wrote the data there.

To export data from the model’s database into an external Excel file programmatically

You can command AnyLogic to export data from its internal database to an external Excel file, using any of the available code fields, for example, a function body or agent action.

To do that:

1. Use the Database class constructor to specify the location of the Excel file you need, for example:

   Database myFile = new Database(this, "A DB from Excel", "D:\Files\myDBFile.xls")

   In the Database constructor, the first argument (this) specifies the database owner (an agent or experiment), the second ("A DB from Excel") is the flavor text (an arbitrary name of the database for your convenience), and the last one is the file name.
   You don’t have to specify the absolute path all the time — the relative path will do, too. Moreover, you can use various UI elements to allow users to specify different files. Consider using File Chooser for that purpose.
2. To access the model’s database, create an instance of the ModelDatabase object:

   ModelDatabase modelDB = getEngine().getModelDatabase();

3. Finally, call the exportToExternalDB() function:

   modelDB.exportToExternalDB("Database table for exporting", myFile.getConnection(), "Sheet 1", false, false);

   • The first argument ("Database table for exporting") specifies the name of the internal database table you want to export.
   • The second argument specifies a database Connection, which is retrieved by the getConnection() function call.
   • The third argument ("Sheet 1") specifies the name of the target table within the external data source (the Excel file). Note the table (the sheet) must exist within the database file and have the specified name, and its header row must name columns in the same way they are named within the table in the AnyLogic model database.
   • The fourth argument may be used to command AnyLogic to wipe the existing contents from the target table before executing the call, though as of now, this functionality is not supported for the Excel table. So, in our case, we are specifying false, so the new records will complement the existing content of the external database rather than overwrite it.
   • false as the fifth argument identifies that the copy action would not be considered a proper database transaction (which may cause performance issues in some cases).
   If you need to export data from multiple tables, repeat the function call and specify a different source sheet in the same file — or a completely different file.
   For more information regarding this function, as well as the other internal database functions, see the following reference: Database functions.
4. That’s it — upon the execution of the code described above, the Excel sheet will be filled out with values.
   Consider adding the myFile.disconnect() function to terminate the database connection and proceed with the model as usual.

How can we improve this article?

Send