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

[Blog bot]

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.


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"> </xsl:variable>



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


<xsl:template match="Control[@type='TextBox']">
<Cell xmlns="urn:schemas-microsoft-comffice: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>





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


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





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


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





<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>


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


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





.....





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.






Читать дальше