CODEWITHSUNDEEP
databaseexport-to-excelanylogic
Eric
Eric

Reputation: 1

Export Database from Anylogic to Excel On Event Trigger

I would like to export an AnyLogic database to Excel when a certain event triggers. However, the only export options I can see are manually when the model is paused or automatically when the model closes.

Can anyone help with a Java snippet or point to a reference, please?

Subsidiary Questions (if the above is possible): Can you, from Anylogic, 1. Create new Worksheets in Excel to write data to? 2. Relabel a Worksheet so that the name could include, say, a timestamp?

Upvotes: 0

Views: 1552

Answers (2)

Stuart Rossiter
Stuart Rossiter

Reputation: 2517

I would like to export an AnyLogic database to Excel when a certain event triggers.

Yes, you can programmatically export tables from the AnyLogic database (i.e., do what the manual 'Export tables to Excel' or the automated 'Export tables at the end of model execution' does). But, yes, there is no real documentation of this and working out the logic needed from the API reference in the help is not easy (especially as there is currently an error in the detail for the exportToExternalDB method, though AnyLogic should be fixing that documentation soon).

Code example is as below. Beyond your requirements, the main reason to do this is that it allows you to dynamically determine the output filename (or dynamically determine what tables are output).

NB: This requires the Excel file to exist with the required worksheets and column-name header rows already there. But you can manually export empty tables once to generate this (and, if you wanted to change the output filename, you could add code prior to the export which copies that exported skeleton file to your required-name file, using standard Java file-handling code).

There is also a way to programmatically create the Excel file and the required 'skeleton' contents, using more low-level Java and the Apache POI library which AnyLogic uses under the covers to connect to Excel. That can also be used to solve your subsidiary questions (see later).

Database outExcel = new Database(this, "ExcelOutput", "outputTest.xlsx");
outExcel.connect();
ModelDatabase modelDB = getEngine().getModelDatabase();
Connection connection = outExcel.getConnection();

// Do the actual per-table export; repeat per table to output
// This requires the Excel file to have the required sheets and header rows
// therein

modelDB.exportToExternalDB("output_sample",   // Table name
                           connection,        // External connection
                           "output_sample",   // Target worksheet name
                           false,             // Clear table prior to copy
                           true);             // Auto-commit

outExcel.disconnect();

Can you, from Anylogic, 1. Create new Worksheets in Excel to write data to? 2. Relabel a Worksheet so that the name could include, say, a timestamp?

Sample code as below (where your output filename was stored in a String variable fileName). Note this is 'AnyLogic-independent' Java using Apache POI; there is nothing in this code which uses any AnyLogic classes. Because AnyLogic already includes Apache POI as a library internally, you don't need to add anything as a model dependency.

try (FileOutputStream fileOut = new FileOutputStream(fileName)) {
    Workbook wb = new XSSFWorkbook();
    // Create a worksheet for the table
    Sheet sheet = wb.createSheet("output_table"); 
          
    // Create a header row with the required column names in. Row indices are 0 based
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("col1");
    row.createCell(1).setCellValue("col2");
    row.createCell(2).setCellValue("col3");

    // Write the output to a file
    wb.write(fileOut);      
} catch (Exception e) {
    [Handle exceptions in some way]
}

You'll need some required import statements in the agent/experiment this code is within (in the 'Imports section' in the Properties):

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;

Upvotes: 1

Felipe
Felipe

Reputation: 9431

Eric, you can export data to an Excel file at any given time during simulation runtime... for instance, the following code writes "the value" in the first sheet, in the first row and the first column:

excelFile.readFile();
excelFile.setCellValue("the value",1,1, 1);//value,sheetName or number, row, column
excelFile.writeFile();

There are no limitations in when to use this. You can find all the Excel API in the help documentation to discover all the ways in which you can read and write data.

But you can't control the name of the worksheet or create new ones through the API... as far as I know

Upvotes: 0

Related Questions