customized coloring in Excel Export.

0
How do I export the excel with Customized coloring according to my requirement.(I have tried the Styles available in the Mendix UI. But that are not sufficient).
asked
2 answers
3

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: 

  1. Use a template within the Excel Exporter module.
  2. Add a macro in this template. Can easily be done by recording a macro in Excel
  3. After the file has been downloaded from Mendix, let the user run the macro with some key combination
  4. Macro adjusts the comditional coloring of the Excel

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.

 

Good luck!

 

answered
1

Hi Valentina, 

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.

 

answered