Open navigation

Customising the Excel export layout

Modified on Tue, 25 Mar at 2:54 PM

It is possible to affect the layout of the Excel sheet that Aico uses when exporting data from an Aico document to Excel using the Export to Excel button.

This is done by uploading an Excel sheet with the required design to the Aico document template. Note that once a template has the Excel sheet added, then ALL exports to Excel will pick up this layout.

By default, when a user exports the document data to Excel, the resulting Excel will contain all visible headers and rows in a WYSIWYG format; however, the layout can be changed by attaching an Excel file behind the Aico document template. 

Follow these steps to set this up.

  1. Open an existing Aico document which uses the document template for which you want to change the Excel export layout.
  2. Click the Export to Excel button (1) to download the basic layout. 
  3. Open the excel and edit the layout.
  4. It is possible to use many standard Excel functions, including embedding images and conditional formatting. A few examples are set out below:
  5. Note that it is not strictly necessary to remove the data from the Excel template as this is automatically cleared each time a User exports data from a new document. However, you must not remove the header and row mappings.
  6. Save it with a user-friendly name, e.g. "JOURNALSAP Export Template". 
  7. Open the document template in System manager. Go to the Excel export template file name parameter and choose your newly saved file. 
  8. Remember to save the template using the Save button just above the Validations and Modification tasks area.
  9. Every Excel export made from this template will now use this Excel layout.
  10. In 23.1, additional functionality was added to better format the rows of the Excel and accommodate the fact that you can have header mappings below the row mappings, which can get overwritten when you have more rows than you have anticipated in the Excel export template. In short, the export picks up the formatting of the first row (underneath the row mappings) and inserts lines using that same formatting. This also moves down any headers that you may have mapped below the rows.
    In the example below, the Excel export template has got simple borders around the first row under the row mappings (1) and 2 header mappings for the company code and Template 3 rows under the row mappings (2). We then export a journal that has 3 rows, and we see each one picks up the simple borders and the 2 headers are moved down.
    Note: It is very important that you only put your formatting on the first row underneath the row mappings. If you add it to more than the first row, then this will just appear as an empty row with the formatting at the bottom of your exported rows.
    We then export a journal that has 3 rows, and we see each one picks up the simple borders and the 2 headers are moved down. 
     
     

     
     


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article