How to get an excel spreadsheet into anylogic database, without importing it i.e. using code on the main startup

Question

Previously I have imported my excel spreadsheet the common way. Now I want to get my information automatically from writingg the code in main. I have written the following so far to get a single cell, but I want to find out how to get the information from many rows and many columns.

insertInto(db_table).columns(db_table.db_column).values(data.getCellNumericValue("Sheet1", 5, 4)).execute();

Answer 1

simply use for-loops across all sheets (getNumberOfSheets()), all columns (getLastCellNum(row)) and all rows (getLastRowNum()).

API calls detailed in AnyLogic help for you (at the bottom) :)

Answer 2

If you want to automatically upload data tables via code, instead of relying on the AnyLogic check box, you could use the following (example from previous project):

public void importTableDataOnStartup(java.sql.Connection internalDatabaseConnection) throws Exception {
    try (DatabaseDescriptorRegistry r = new DatabaseDescriptorRegistry()) {
        java.sql.Connection cachedSourceConnection;
        cachedSourceConnection = r.getConnection(DatabaseDescriptorFactory.createFileDescriptor( Utilities.findExistingFile("..\\ExcelData\\" + ebFileName.getText() + ".xlsx"), null, ""));
        UtilitiesDatabase.copyDatabaseTable(cachedSourceConnection, internalDatabaseConnection, "\"PhysicianTypes\"", "physician_types");
        UtilitiesDatabase.copyDatabaseTable(cachedSourceConnection, internalDatabaseConnection, "\"PatientFlow\"", "patient_flow");
        UtilitiesDatabase.copyDatabaseTable(cachedSourceConnection, internalDatabaseConnection, "\"Referrals\"", "referrals");
        UtilitiesDatabase.copyDatabaseTable(cachedSourceConnection, internalDatabaseConnection, "\"Incident Patient Rate\"", "incident_patient_rate");
        UtilitiesDatabase.copyDatabaseTable(cachedSourceConnection, internalDatabaseConnection, "\"PatientDeathRates\"", "patient_death_rates");
        traceln("Database import is complete.");
    }
}

In the above code, the user can select an Excel file name via an edit box (ebFileName.getText()). There are a handful of database tables already setup in the model, and this updates them.

I had to do this because the tables were so large, it took 30 seconds on startup every time just to update the tables. In reality, my customer really didn't need to change these tables, but wanted a mechanism to do so "just in case". We added a button on the experiment window for them to just upload on startup as desired.

I requested AnyLogic add a table.upload() type command to make this easier. They said it is on their development list for a future release, but I have not seen it on their release notes yet.