Dynamics NAV 5.0 and Style Sheet issues, decimals exported as text

When using Send-to Excel option from NAV, decimals are exported as texts if Digit grouping symbol (Control Panel - Regional and Language Options) is a ' ' (whitespace). NAV uses ANSI 160 code character for space, which is not a space in ascii code table (normally used by windows applications). Consequently, decimals larger then 1000 (thus containing a space as digit grouping symbol) will be interpreted as text by excel.

This has been corrected in a new style sheet released for this issue.

The issue is also corrected in 5.0 SP1 update 1 (using style hseets provided with SP1).

To correct this issue, open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder. Browse to the following line :  

  <xsl:variable name="DecimalSeparator" select="Object/DecimalSeparator"/>

and add a line

  <xsl:variable name="DecimalSeparator" select="Object/DecimalSeparator"/>
<xsl:variable name="nbsp">&#160;</xsl:variable> <!-- LINE ADDED !-->

Then locate and modify the following sections (modifications marked in code):

 <xsl:template match="Control[@type='TextBox']">
<Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:choose>
<xsl:when test="contains(@value,'..')">
<xsl:attribute name="ss:StyleID">TextBox</xsl:attribute>
</xsl:when>
<xsl:when test="@value = translate(@value,',.','')">
<xsl:attribute name="ss:StyleID">TextBox</xsl:attribute>
</xsl:when>

<!-- BEGIN modifications, the following section was modified !-->

          <xsl:when test="string(number(translate(translate(@value,$nbsp,''),',.','11')))!='NaN'">  

               <xsl:attribute name="ss:StyleID">TextBoxNumber</xsl:attribute>
</xsl:when>                                                                                                           

<!-- END modifications !-->        

          <xsl:otherwise>
<xsl:attribute name="ss:StyleID">TextBox</xsl:attribute>
</xsl:otherwise>

       </xsl:choose>
<Data>
<xsl:choose>

  <!-- BEGIN modifications, the following section was modified !-->

                        <xsl:when test="string(number(translate(translate(@value,$nbsp,''),',.','11')))!='NaN'">
<xsl:choose>
<xsl:when test="contains(@value,'..')">
<xsl:attribute name="ss:Type">String</xsl:attribute>
<xsl:value-of select="@value"/>
</xsl:when>

                                    <xsl:when test="translate(@value,$nbsp,'') = translate(translate(@value,$nbsp,''),',.','')">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(@value,$nbsp,'')"/>
</xsl:when>

                                    <xsl:when test="$DecimalSeparator = '.'">
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(@value,$nbsp,''),',','')"/>
</xsl:when>                                    
<xsl:when test="$DecimalSeparator = ','">
                                          <xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(translate(@value,$nbsp,''),'.',''),',','.')"/>
</xsl:when>

                                    <xsl:otherwise>
<xsl:attribute name="ss:Type">Number</xsl:attribute>
<xsl:value-of select="translate(translate(translate(@value,$nbsp,''),'.',''),',','.')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>

                         <xsl:otherwise>
<xsl:attribute name="ss:Type">String</xsl:attribute>
<xsl:value-of select="@value"/>
</xsl:otherwise>
</xsl:choose>
</Data>

 <!-- END modifications !-->

 .....

 

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.