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

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

  • Comments 0

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 (jvukovic )

Microsoft Dynamics NO


Microsoft Customer Service and Support (CSS) EMEA

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post