Skip to main content

Hi,

Many of our projects include export functionality to Excel; which is a good way to allow the end-user to continue to work on the data and/or use it as a report out. Proper formatting of the excel file is in these cases high on the priority list of the users. One can think of: setting fonts, right number of decimals, putting data in a pivot (correctly organized). Basically, saving the user the manual work to redo these formatting actions after each export to excel.

In the win-ui we applied the following trick to get the excel file formatted as desired:

  1. Create an excel macro to perform all the required actions
  2. Include an excel file with the macro in the aimms project
  3. When exporting, use a copy of that excel file, put the data in and run the macro via the aimms function Spreadsheet::RunMacro

Since we are developing web-ui apps, which are cloud deployed, this trick is not available any more. The model runs in the cloud without the ability to running an excel macro. Because the AIMMS Excel Library (axll) does not contain a function like Spreadsheet::RunMacro 

What are the possibilities to control the formatting in excel files created from a webui app?

 

Thank you!

  • The axll library works without an Excel executable, so it cannot run macros
  • The spreadsheet library uses an Excel installation/executable but it is deprecated (as of last year IIRC) and can only be used on Windows OS - AIMMS Cloud is on linux and a typical Windows server wouldn’t have an Excel installation as it is not a server application.

You can

  1. Configure to have your macro run when the workbook is opened, so when the user downloads this Excel file from an AIMMS app and opens it - the macro will be executed and the formatting will be applied. Microsoft docs - automatically run a macro after opening a workbook
  2. You can apply the formatting in the Excel file template that you include in the AIMMS project, and set AIMMS docs - axll::KeepExistingCellFormatting to 1 before executing any write procedures. This should preserve the formatting you created in the workbook.

Thanks Mohan for your 2 suggestions.

The first is the most applicable in our case, because not know the dimensions of the tables upfront makes it difficult (if not impossible) to format the excel beforehand.

 

 

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