Solved

Export Set on Separate Tabs using AXLL

N
Badge +3

Hello - I'm creating a script to export a bunch of vars/params to Excel/CSV. It needs be pretty clean/standardized so it can be read into a UI.


I tried:

write AllVariables to file export_file;

But the output was too messy. 

 - Is there a way to silence Basic/NonBasic?

 - My output is often Nodes x Time, which AIMMS seems to prefer as (n,t), but then it shows Time as columns instead of rows (which is SUPER ugly in the text file). So I have to duplicate everything as parameters with (t,n) for the export. Or am I wrong?

 

So now I'm looking at using AXLL, but it's pretty tedious.

This is what I'm currently doing:

axll::CreateSheet("VarName1");

axll::WriteTable(Var1, "A2:A5", "B1:AZ1", "", 1, 1);



axll::CreateSheet("VarName2");

axll::WriteTable(Var2, "A2:A5", "B1:AZ1", "", 1, 1);
… ad nauseam

 

There has to be a better way. Is there something like:
for i in MySet do

   axll::CreateSheet(FormatString(“%e”, i));

   axll::WriteTable(i, “A2:A5”, “B1:AZ1”, "”, 1, 1);

endfor;

 

I want to print one variable per tab in Excel - I plan on formatting all vars/params so they'll play nicely with WriteTable, then put them in a Set.

Is this possible or is there a better way?

icon

Best answer by gabiservidone 26 May 2023, 15:40

View original

15 replies

gabiservidone
Rank
Userlevel 5
Badge +6

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. 

 

Gabriela Servidone - AIMMS and Optimization Solutions Consultant
gabiservidone
Rank
Userlevel 5
Badge +6

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!

Gabriela Servidone - AIMMS and Optimization Solutions Consultant
N
Badge +3

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.

M
Rank
Userlevel 4
Badge +5

Hi @Noob9000 

 

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

 

1 Attachment
Marcel Roelofs - AIMMS Product Portfolio Architect
N
Badge +3

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!

N
Badge +3

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.)

M
Rank
Userlevel 4
Badge +5

@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. 

Marcel Roelofs - AIMMS Product Portfolio Architect
N
Badge +3

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?

S
Rank

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

 

M
Rank
Userlevel 4
Badge +5

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.

 

 

Marcel Roelofs - AIMMS Product Portfolio Architect
N
Badge +3

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?

N
Badge +3

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.

N
Badge +3

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

M
Rank
Userlevel 4
Badge +5

@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.

 

 

Marcel Roelofs - AIMMS Product Portfolio Architect
N
Badge +3

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!

Reply


Terms & Conditions

Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator