·
2 min read

Style Sheet Export to Excel vs IndentationControls

The IndentationControls property is widely used to generate indented Tree View pages, and you can learn about it here: https://docs.microsoft.com/en-us/dynamics-nav/indentationcontrols-property.

In some scenarios, where you need more flexibility and dynamically populate the tree view, you would set the page property ShowAsTree to FALSE and disable the Excel Add-In to fall back to the original Style Sheet Excel export.

At the bottom of this blog post, you’ll find a link to an attachment – it’s a simple page loaded just as proof of concept, based on the CRONUS demonstration company in Dynamics NAV 2017.

When this constellation is enabled (IndentationControls + ShowAsTree = FALSE + Excel Add-in disabled), then the generated XML file is created using the Windows UI concept of a viewport which roughly collapses the columns of the three into a single column. If this happens, then an error might be thrown when you export to Excel, as shown in the following screenshot:

And the Excel Error Log would report the following:

XML ERROR in Worksheet Setting
 REASON: Bad Value
 FILE: C:\Users\myname\AppData\Local\Temp\Modifica - Tree Buffer5.xml
 GROUP: Worksheet
 TAG: Table
 ATTRIB: ExpandedColumnCount
 VALUE: 2

The easiest way to resolve this problem is to simply change ShowAsTree = TRUE, but in some development scenarios this might not be possible.

So here is another suitable workaround: Edit the existing style sheet that will allow the export to succeed for that specific page. The only limitation in the proposed XSLT is related to the fact that columns will be indented and the very fist one would export an integer as indentation value.

NOTE: In this example the header value has been hardcoded for an easy understanding.

In a more real scenario, this could be exported, edited, and uploaded again also through code, if and when necessary.
Below you will find a step by step scenario how to reproduce the problem and apply the workaround.

  1. Import, save and compile Table, Codeunit and Page 51000 Tree Buffer (TreeBuffer.txt)
  2. Run Page 51000 Tree Buffer
  3. Press CTRL+E (or Export to Excel)
     Excel error
  4. Open the Dynamics NAV Windows client, search for “Manage Style Sheet”, and then open the related link
  5. Set the Show field to Style sheet for a specific page
  6. Select Page 51000 Tree Buffer
  7. Click “Import” action and upload the XSLT (PAG51000_Stylesheet.xslt)
  8. Run page 51000 Tree Buffer
  9. Press CTRL+E (or click Export to Excel)

Et voila: A magnificent Excel file is served.

Demo Page 51000 and XSLT

These postings are provided “AS IS” with no warranties and confer no rights. You assume all risk for your use.

Duilio Tacconi (dtacconi)
Microsoft Dynamics Italy
Microsoft Customer Service and Support (CSS) EMEA

navteam

Related posts