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.


 


Comments (8)

  1. epernst says:

    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!  [:(]

  2. MSDN Archive says:

    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.

  3. MSDN Archive says:

    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.

  4. epernst says:

    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?

  5. epernst says:

    So what you say is that I need to get this update to get it to display decimal fields correctly in Excel?

  6. epernst says:

    Just upgrade to NAV5 SP1 update 1. But I stillhave the same problems! 🙁

    Any suggestions?

  7. MSDN Archive says:

    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

Skip to main content