Share via

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

  • Article

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.

Comments

  • Anonymous
    July 01, 2008
    PingBack from http://blog.a-foton.ru/2008/07/dynamics-nav-50-and-style-sheet-issues-decimals-exported-as-text/

  • Anonymous
    August 24, 2008
    What version does this apply to? I've been looking for this section in NAV 5.0 SP1, but I'm unable to locate it, and we still have the same issue in this version!  [:(]

  • Anonymous
    September 03, 2008
    Style sheets in NAV SP1 have been changed decide the data format based on data type passed by application. However, currently, application is still not updated to actually pass the data type, so it is still only passing the data value. In short, you really need to use 5.0 style sheets in 5.0 SP1 client.

  • Anonymous
    September 03, 2008
    Addition to last comment, update 1 for 5.0 SP1 contains this correction on clients. With SP1 update 1, clients pass on the actual data type, so when running 5.0 SP1 update 1, use SP1 style sheets. This correction should then not be needed, export to excel should work as expected.

  • Anonymous
    October 14, 2008
    According to my information then update 1 for SP1, then this did only include fixes for job! Do you have a KB article no. for update 1?

  • Anonymous
    October 14, 2008
    So what you say is that I need to get this update to get it to display decimal fields correctly in Excel?

  • Anonymous
    November 10, 2008
    Just upgrade to NAV5 SP1 update 1. But I stillhave the same problems! :( Any suggestions?

  • Anonymous
    November 10, 2008
    Sorry about this, looks like the updated style sheets never made it to the update release. They will be released at some point, meanwhile, use SP1 style sheets with the correction described in post below: Please follow the link below to http://blogs.msdn.com/nav_developer/archive/2008/11/11/dynamics-nav-5-0-sp1-and-export-to-excel.aspx Hope it helps, but let me know if you get any problems with this correction