@Richard , one easy way would to be use a call to axll::ReadSet on the rows/columns which contain your set elements. You can give a sufficiently large data range to axll::ReadSet and set the arguments such that it stops reading at the first empty cell. The cardinality of this set gives you the number of rows/columns you need. This works well for 1 dimensional identifiers (in a list) of 2 dimensional identifiers (in a 1x1 matrix). 

This won’t work always for higher dimensional lists because the index tuples could be sparse. The warning given by axll::ReadSet does not include the first empty cell but axll::ReadList does - when you set the argument ModeForUnknownElements to 1. Something like below.

ReadList : Cell A9351 in Requirement: Remaining rows are ignored after empty header cell.

By using the errh functions and some string manipulation, you can retrieve the cell address in Excel. See code below. spTemp is a local string parameter indexed over a local set. 

block 

empty spWarning;

axll::ReadList(

    IdentifierReference    : spTemp , 

    RowHeaderRange         : "A2:A10000" , 

    DataRange              : "C2:C10000" , 

    ModeForUnknownElements :  1, 

    MergeWithExistingData  :  0);

onerror err do

spWarning := SubString(errh::Message(err), FindString(errh::Message(err), " A"), FindString(errh::Message(err), " in"));

errh::MarkAsHandled(err);

endblock;

I search for “ A” because I was reading elements from the column A. You could also hard code the position number of A because the message “ReadList: Cell “ is going to be standard. Then I search for “ in” as the length of the cell address could be dynamic. 

This returns “A9351” in spWarning. I could just use this in all my subsequent relevant ReadList calls or I can do some work to reduce it to “A9350” which is the last row in my data. I personally wouldn’t spend that effort in getting down to A9350 as it is just one row. 

A reference: https://how-to.aimms.com/Articles/191/191-handle-errors-and-warnings.html#handle-errors-and-warnings

Hope this helps !

Thanks for your reply @mohansx. In many applications of this, we indeed encounter (often sparse) higher dimensional lists. Here we could multiply the cardinalities of the domain sets, which will definitely be enough, but probably way too much.

I could see applications of the solution you propose, but still it feels like a workaround of a “default” functionality. In many other software programs, you can simply read an excel file without having to perform these kinds of checks manually to be sure you read everything.

In any case, my question for now is answered - though it would be great if you could consider a more developer-friendly solution in future releases.

Thanks!

Somehow, we never got this question before. It does indeed sound like a very useful addition to the axll library.

I checked the documentation of the third party library that AIMMS is using to implement the axll library and there are indeed functions to retrieve this information.

We will add the following functions in one of the upcoming releases of AIMMS: 

• getLastUsedRow
• getLastUsedColumn

and just to be complete:

• getFirstUsedRow
• getFirstUsedColumn

(Update: released as of AIMMS 4.70.4!)

Thank you @Richard Stegeman, looking forward to the future releases!

Hi @Richard, will become available in next hotfix of AIMMS. Thanks for asking and suggesting the function to more easily get the dimensions of an excel sheet. 

FYI: The mentioned functions have been released this morning in the AIMMS 4.70.4 release.

Regards,

Mischa