How to Link to Excel Files

  • 28 March 2019
  • 8 replies
  • 845 views

Userlevel 2
The AimmsXLLibrary was introduced in AIMMS 4.20. You can deploy your AIMMS project to more platforms with less restrictions, because this library can communicate with Excel files in server environments where Excel is not installed. For instance, while running AIMMS through a WebUI on PRO or on Linux.

The AimmsXLLibrary doesn’t require Excel to be installed in order to read from or write to an Excel (.xls/.xlsx) file.

You first need to add the system library `AIMMSXLLibrary` to your model, and then you can use the predefined `axll::` functions.

Read more...

8 replies

Dear,

The last sentence of the provided link says:

Similarly, you can use the
code:
AIMMSXLLibrary
to write to Excel. You can see the comments in the library for further reference.

I am looking into ways how I can write the multi-dimensional results of my model to Excel, but I haven't been succesful. Which comments is that sentence referring to? Because I think I have not seen it yet, but I can't seem to find it.

Kind regards,

Koen
Userlevel 4
Badge +1
Hi @KoenV ,

Please follow https://how-to.aimms.com/C_Developer/Sub_Connectivity/sub_excel_csv/index.html !
Hope this article helps
Hello Arthur,

Yes I have! I actually have that link open as we speak. However, this is not something I have done before, and from what I am actually reading on that page, a lot is about reading data, instead of writing it. I have used the XLL library several times in my project to read data with the functions ReadSet and ReadTable. Now I am actually looking to do the reverse of a ReadTable function. Is there possibly another web-page or guide that shows how this is done?

Kind regards,

Koen
Userlevel 4
Badge +1
Indeed ! it's true that none of those article are talking about writing.
Thanks for your feedback, we will update it.

You may in the meantime check in the library. Comments in the library itself are a rudimentary documentation:



Dear Arthur,

I have been trying to use the comments in the library to make sure that I can generate my own Excel file with results. However, I seem to be unable to do so. If I use the example code from the library, then I get errors such as "the range A4:C14 is not expected here". I have attached my model in this comment, with the procedure "WriteTable". Could you point out what I am doing wrong, or perhaps provide a template that I could fill in along the specifications of my model? FYI: with the code in the model, I tried to get results of the Variable Transport(w2,c,p,t).

Kind regards,

Koen
Userlevel 4
Badge +1
Dear Koen,

You unfortunately forgot the library prefix "axll::". Please replace

code:
WriteTable( TransportToCustomer(w2, c, p, t), "A4:C14", "D1:G1", "D4:G14", 1, 1)


by

code:
axll::WriteTable( TransportToCustomer(w2, c, p, t), "A4:C14", "D1:G1", "D4:G14", 1, 1);


Please mind also to replace ResultWorkbookName in the following:

code:
if axll::WorkBookIsOpen(ResultWorkbookName) then
axll::SelectWorkBook(ResultWorkbookName);
else
axll::OpenWorkBook(ResultWorkbookName);
endif;


It's working fine now ! :)

please see attached
Dear Arthur,

Thank you very much! The Excel file generation works flawlessly now. :)

I hope you don't mind, but I do have a follow-up question regarding my model. If I manually check the costs from the Excel files (see files "Results" and "ResultsStock", the manual checks are in there), then these costs do not correspond with the proposed solution of AIMMS.

I have the suspicion that this is caused by either my StockBalancingConstraint and/or my objective cost function TotalCosts.

The code for the StockBalancingConstraint is:

code:
if ord(t) = 1 then 
StockAtWarehouse(w2,p,t) = StartingStock(p,w2) + sum[f, ProductionAllocation(f,p,t) * ProductionRouting(f,w2)] - sum[c, TransportToCustomer(w2, c, p, t)]


else


StockAtWarehouse(w2,p,t) = StockAtWarehouse(w2,p,t-1) + sum[f, ProductionAllocation(f,p,t) * ProductionRouting(f, w2)] - sum[c, TransportToCustomer(w2, c, p, t)]


endif



For this constraint I think that the sum(f, ProductionAllocation) may be wrong, because I want the full production of a certain factory, so that would be the sum of p. However, when I replace the f by p, I get the error remark "the index p already has a scope".


The code for my objective function TotalCosts is:

code:
sum[(w2, p, t), StockCostsPerPeriod(w2) * StockAtWarehouse(w2,p,t)] + sum[(p,w2,t,c), TransportCosts(c,w2) * TransportToCustomer(w2,c,p,t)]



Is there perhaps something wrong with the order in which I present the indices in each of the summation formulae? Or could you possibly point me out to anything that may be wrong in these declarations? In terms of syntax they don't cause any errors, but I think that there is perhaps still a logical mistake in here that I cannot see.

Kind regards,

Koen
Userlevel 4
Badge +1

Hi@KoenV ,

Quick answer:
Indeed, because p is used in your StockBalance Constraint index domain, you can't use this particular index in a sum. You will need to declare another index, from the same set, and replace it in the sum. AIMMS will use it to run this nested sum .

Explanation:
The index domain of your constraint (or any AIMMS identifier) could be seen as a big "for" statement, running over each index of the index domain. Each index in your index domain will get a particular element value, and then the constraint will be generated.
Obviously, when you will generate the constraint for w2 = 'w2_1' p = 'p1' and t='t1' for example, you don't want to do a trivial sum on p = 'p1', but a full sum on all the p's! The way to rigorously specify that to AIMMS is by declaring another index (over the same set as p, in our case the set named Product). Let's give it a name, index 'other_p' 🙂 Then your formulation will look as follows:

StockAtWarehouse(w2,p,t) =

StockAtWarehouse(w2,p,t-1) +

sum[other_p, ProductionAllocation(f,other_p,t) * ProductionRouting(f, w2)] -

sum[c, TransportToCustomer(w2, c, p, t)]


Error explanation:
AIMMS is throwing an "index has already a scope" error because when trying to compile your "sum" statement, AIMMS requires an index as first argument, and not a simple element, or "an index that already has a scope". Et voila ! Of course , this error is not specific to the sum, but also the "average" function for example, or every intrinsic function that needs an index to run on.

Note:
You still can use p in the sum numerical expression (2nd argument of the sum), but not in the first argument (binding domain of the sum). That is why AIMMS has to throw an error, and can't correct your statement, guessing that you wanted to make a local sum. Otherwise, how would AIMMS know if p is for the sum, or for the constraint generation ?..

Reply


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

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