Dynamics NAV 5.0 and Style Sheet issues
This post is starting of a summary of style sheet issues reported in Dynamics NAV 5.0 when using send-to Excel/Word functionality, and attempt to provide workaround suggestions for as many of these issues as possible.
Most issues are related to regional settings and use of decimal separator and or/date format. In 5.0, when xml file with data is created by application, only values displayed on form are passed, along with the decimal separator sign used. Style sheets are determining the field type based on the format of the field value. As different date formats are used in different places, along with different decimal separator/thousand delimiter combinations, this can lead to number of issues. In newer versions, 'data type' attribute is passed along with decimal separator and field value, to avoid ambiguous formatting issues.
Keep in mind that matrix forms and forms like Customer Sales History are not supported by style sheets at all.
The list below contains three of the issues reported on 5.0, some corrected in 5.0 SP1 version, and a suggestion for possible workaround.
Other reported issues will be handled in coming blogs.
1. When running forms that contain several subforms (example form 521, Application Worksheet), and selecting Send to Excel, the following error is reported in excel:
Problems came up in the following area during load: Workbook Setting...
Table box names created in excel should be added a counter value to the name to avoid this issue. To do this:
Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.
Browse to the following section and replace the marked line :
<xsl:template match="Control[@type='TableBox']">
<Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:attribute name="ss:Name">
<xsl:variable name="TableBoxCaption">
<xsl:value-of select="//Object/@caption"/>
</xsl:variable>
<xsl:value-of select="substring($TableBoxCaption,1,30)"/> <-- REPLACE THIS LINE -->
</xsl:attribute>
with the following line:
<xsl:value-of select="substring($TableBoxCaption,1,28)"/><xsl:value-of select="position()"/>
2. Error when exporting date fields to excel.
If a form contains date fields and date format is dd.mm.yy or dd.mm.yyyy , following error occurs when running send-to excel, with Decimal Separator equals ‘.’:
Problems came up in the following area during load: Table...
If value of decimal separator is '.', style sheet interprets all fields containing numeric values (only) and '.' as decimals. Dates are then interpreted as invalid decimal values. One way of solving this in 5.0 is to check for number of dots, and number of places between the dots, 2 implying the value is a date. To do this:
Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.
Browse to the following section and replace the section:
<xsl:when test="$DecimalSeparator = '.'">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(@value,',','')"/>
</xsl:when>
<xsl:when test="$DecimalSeparator = ','">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>
</xsl:when>
With
<xsl:when test="$DecimalSeparator = '.'">
<xsl:choose>
<xsl:when test="string-length(substring-after(substring-after(@value,'.'),'.'))=2">
<xsl:attribute name="ss:Type">String</xsl:attribute> <xsl:value-of select="@value"/>
</xsl:when>
<xsl:otherwise>
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(@value,',','')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:when test="$DecimalSeparator = ','">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>
</xsl:when>
3. Date fields exported as decimals when Decimal Separator equals ‘,’.
The same method of workaround could be applied here as for the previous issue. Checking for number of dots and number of digits between the dots and reading value 2 would normally imply that the value is a date.
1. Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.
2. Browse to the following section and replace the section:
<xsl:when test="$DecimalSeparator = '.'">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(@value,',','')"/>
</xsl:when>
<xsl:when test="$DecimalSeparator = ','">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>
</xsl:when>
With
<xsl:when test="$DecimalSeparator = '.'">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(@value,',','')"/>
</xsl:when>
<xsl:when test="$DecimalSeparator = ','">
<xsl:choose>
<xsl:when test="string-length(substring-after(substring-after(@value,'.'),'.'))=2">
<xsl:attribute name="ss:Type">String</xsl:attribute> <xsl:value-of select="@value"/>
</xsl:when>
<xsl:otherwise>
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(@value,'.',''),',','.')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
Jasminka Vukovic
Microsoft Dynamics NO
Microsoft Customer Service and Support (CSS) EMEA
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.