Hi,

I don’t see why your for idea wouldn’t work.

You can also use axll:WriteCompositeTable instead of axll::WriteTable if you don’t require specifics cells for the contents. 

Hey @Noob9000, hope that worked, but I also wanted to share another library that you can use to export Excel, it is called DEX https://documentation.aimms.com/dataexchange/index.html!

No unfortunately that loop doesn't work because it doesn't like creating a string from a variable name and doesn't seem to recognize the index of the set as the actual variable (probably a syntax thing I can't figure out).
I've considered the Data Exchange library, but didn't quite understand it. Maybe I'll ask my more techy colleagues.

Hi @Noob9000 

I've attached an example how to accomplish this with DEX with the least effort. Actually not that complicated.

Thanks @MarcelRoelofs - it looks like that will probably work. Unfortunately I can't run it because my AIMMS throws an error when trying to install the DEX library. But hopefully that can be resolved.

Thanks for the help!

For future generations, I got part-way there using axll, but still ran into a problem of sending an element index to an external function call:

(After creating set MySet with index my_set_index that contains the export parameters.)

axll::CreateNewWorkBook(WorkbookName);

for my_set_index do
    axll::CreateSheet(FormatString("%e", my_set_index));
    axll::WriteTable(my_set_index, "A2:A3", "B1:C1", "", 1, 1);
endfor;

axll::CloseWorkBook(WorkbookFilename : WorkbookName );

It's the WriteTable line that fails. For the bold, I've tried:

my_set_index

Val(my_set_index)

MySet(my_set_index)

Element(MySet, my_set_index)

But they all throw various errors.
Seems like a simple syntax error, but I can't find anywhere in the documentation how to use an element index as an array parameter in an external function call. (If that's even what I'm trying to do.)

@Noob9000 Maybe you're trying to load the newest DEX version in an AIMMS version <= 4.87? The latest DEX versions only work with AIMMS >= 4.88. We're moved on to a new build system and it became too cumbersome to port all new components back to the old build system used by AIMMS <= 4.87. 

Yes updating my AIMMS fixed it.

Looking at your code example, it looks like it also requires an Excel file that is the mapping. If that's right, can you send an example or documentation on how to create that file?

Hi Noob9000,

No, the model does not require an excel file as mapping. Mapping files are XML. The code example already will generate the mappings for you if you just run MainExecution.

It is the command
dex::GenerateDatasetMappings;
that generates all possible mappings based on the annotations of the identifiers.

After you ran MainExecution, you can look in the folder Mappings/Generated/. In that folder you find the mapping file StuffToExport-Excel.xml that is used in the command
dex::WriteToFile("MyTabbedWorkbook.xlsx", "Generated/StuffToExport-Excel");

Also you'll see that the xlsx file has been generated containing the data from the identifiers.

See also: https://documentation.aimms.com/dataexchange/standard.html#generating-the-dataset-mapping-files-from-annotations

Hi @Noob9000 

The mapping is generated based on the dex::Dataset and dex::TableName annotations in the identifier declarations, using the method dex::GenerateDatasetMappings that's being called in MainExecution.

Thanks for the clarification - I didn't see the extra fields on the Parameters tabs for a while, but now it works.

One error that I keep getting when I try to make adjustments is this:
AddMapping Generated/DatabaseName-VariableName-JSON-Sparse: Array node ‘rows’ should have a single child node or an included mapping

AddMapping Generated/DatabaseName-VariableName-JSON-Sparse: JSON root node should have a single child

I can fix it by deleting the Mapping and Schema folders AND restarting AIMMS. Is there something easy I'm missing?

Also, if variables have the same indices, they could potentially be in the same table as columns, right? I tried using ColumnName from the documentation, but it threw errors. How would you put q and r from the example in the same table?

Also:
AddMapping Generate/MappingName-Excel: Unable to parse mapping file Mappings/Generated/MappingName-Excel.xml at location 22: No document element found

This is what happens when I remove the TableName field and try to use ColumnName instead.

Nevermind - I figured out putting it on one table. I had to specify the Table, TableName, and ColumnName fields for the parameters.

@Noob9000 

You can also put e.g. dataset and tablename annotations on sections or named declaration sections. They are then inherited by all identifiers underneath them. 

That would allow you to define a ‘table’ as a (named declaration) section with identifiers with the same inidces in there. You can define the column name for indices either by separating the index declaration 

from the set declaration, and then setting the dex::columnname annotation for the index, or by pre-filling the dex::ColumnName string parameter with the column you want for the index.

Hello! I have another question about DEX.

We've decided it's best to have 1 output per tab in Excel. But instead of specifying all the tab names, it would be handy if they're just Tab1-Tab-20 or named after the variables.
I found “dex::AutoTablePrefix,” but this puts all variables with the same indices on ONE tab. Is there a way of easily splitting it out?

Thanks!