About the first option with the macro in a template:
From the implementation side:
Adding a macro in the template document.
The template document is an Excel file you create yourself and upload into your Mendix app as a file document. In this Excel document, add the needed maccro. This can be done easily by recording a macro in Excel itself, or adding your own Visual Basic code within that Excel template
Duplicate template document
Before calling the GenerateExcelDoc Java action, make sure to get this template (with this macro) from the database, copy this template filedocument with the community commons java action 'Duplicate File Document’ and feed this duplicated template document as an inputparameter to the GenerateExcelDoc java action.
Use a template within the Excel Exporter module.
Make sure the Excel Exporter module doesn't generate the Excel file itself, put uses an Excel file with the macro which you have created. You would have to alter the Excel Exporter module, specifically java action ‘GenerateExcelDoc’ for this. With the Java Apache POI library, load the workbook already available within your template document. Something like this would work:
InputStream stream = Core.getFileDocumentContent(getContext(), fileObj.getMendixObject()); //use workbook in template file XSSFWorkbook wb = new XSSFWorkbook(stream);
After this, make sure the Excel Exporter module generates the worksheets, coming from the configuration of this module in the front end, in this already existing workbook.
From the end user side:
The user clicks on a button. This will download the Excel file with the macro in it, but based on the Excel Exporter module. The user opens the Excel file and with some hot key combination triggers this macro.
You probably mean coloring based on the actual values of the objects you are exporting? You do need some custom coding hence Java knowledge for this, but it can be done in two ways by extending the Excel Export / XLSReport module from the Marketplace:
Easiest + more flexible: Keep logic in Excel with a Macro:
Harder + less flexible:
Alter the Excel Exporter module to format the cells based on the actual Mendix attribute values by using the Apache POI library. You need to deep dive in how the Excel Exporter /XLSReport module works, which needs proper Java skill.