Solved

Export to Excel: Spreadsheet vs axll

  • 25 February 2021
  • 3 replies
  • 275 views

Badge

I want to write data in Excel. Generally “Spreadsheet::Assign...” commands work fine. However, I fail to include zeros in the output in Excel.

 

I want this:

i/t 1 2
1 0 0
2 5 7

 

I get this:

i/t 1 2
2 5 7

 

Now I found the “axll::”  library and the axll::WriteTable command, which seemed promising. Unfortunately this seems to work differently, since commands need an open workbook, which the “spreadsheet::” commands don´t. Combining both worlds doens´t work either. 

 

Can someone help? I am very confused. Thanks

icon

Best answer by mateusarakawa 25 February 2021, 21:56

View original

3 replies

Userlevel 5
Badge +2

Hi, @inkognitro.

Sparse

If this argument is 1 (its default value), the default values of the parameter will be represented as empty cells in the sheet, instead of the real default value.

Source: https://documentation.aimms.com/functionreference/data-management/spreadsheet-functions/spreadsheet_assignparameter.html

 

I think if you set this argument to 0, it will write what you want.

 

Hope this helps.

Userlevel 4
Badge +5

Some additional comments here to potentially add to the confusion, but also bring clarity at the same time: 

  • the spreadsheet functions only work when Excel is installed on the machine where you run the model. So might feel great when developing, but are pretty useless when deploying via cloud/PRO platform. For this reason we will deprecate them at some point. I would advise against using them.
  • axll library will work everywhere even on Linux, because it uses a separate library to read and write Excel spreadsheets. Did you look at all the available arguments of axll::WriteTable:(IdentifierReference, RowHeaderRange, ColumnHeaderRange, DataRange, allowRangeOverflow, WriteZeros, IncludeEmptyRows, IncludeEmptyColumns, IncludeEmptyRowsColumns). It seems like cleverly combining these will do exactly what you want. There is also a axll::FillTable function that might be of use here. 
  • If you are looking to write row-based data to Excel, you may also want to give the DataExchange library a look, It supports a mode where it can automatically create mappings to write the data of a single set of identifiers with the same domain to a various formats such as CSV, JSON, XML, Excel. Especially interesting if you want to call other services with data from your model, or call your model thru a REST API from within another application (on our roadmap). 

So, looking a little bit further ahead what you want to achieve in the end, may let you prefer one solution over the other.

Hi @inkognitro 

Were mateusarakawa and Marcel’s answeres sufficient in answer your question?

Reply


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

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