Nav developer: Dynamics NAV and export to Excel: Export dates as true excel dates

[Blog bot]

When running Send-to Excel functionality and exporting data to MS Excel using style sheets, all data exported form NAV is formatted as either numbers or strings.


That also includes dates, that are exported as strings and not formatted as true dates in excel. As of update 1 for NAV 5.0 SP1, data.xml generated when running export to excel contains data type attribute for each row with data printed. Using 'Date' attribute, one can add a fromatting rule in the style sheet, to specify formatting of data of type Date.


Following example illustrates this. Please note, the style sheet below is created and tested on W1 NAV using US regional settings. Though tests have been run on few other combinations of regional settings and date format, you might need to adjust the rule to local date format. The scenario also assumes years in range 00..39 are in fact 2000..2039, while years in range 39.. will be formatted as 1939..


Open the default style sheet for export to Excel using notepad. The default style sheet is NavisionExportToExcel.xslt, and is placed in Stylesheet folder of the Client folder.





Go to line 61 of the default style sheet, or locate the section below using Search/find, and add the lines marked in the code below.


....


<Styless:ID="CheckBox">


<Alignmentss:Horizontal="Left"ss:Vertical="Bottom"/>


<Fontss:FontName="Verdana"x:Family="Swiss"/>


</Style>


<Styless:ID="rowheading">


<Fontx:Family="Swiss"ss:Bold="1"/>


<Interiorss:Color="#C0C0C0"ss:Pattern="Solid"/>


</Style>


<Styless:ID="Dateformat">





<NumberFormatss:Format="mm/dd/yy;@"/>


</Style>


....


****************************************************


Go to line 235 of default style sheet, or locate the section below using Search/find, and add the lines marked in the code below.


....


<xsl:templatematch="Control[@type='TextBox']">


<Cellxmlns="urn:schemas-microsoft-comffice:spreadsheet">


<xsl:choose>


<xsl:whentest="(@datatype = 'Date')">


<xsl:attributename="ss:StyleID">Dateformat</xsl:attribute>


</xsl:when>


<xsl:whentest="(@datatype != 'Integer')and(@datatype != 'Decimal')and(@datatype != 'BigInteger')">


<xsl:attributename="ss:StyleID">TextBox</xsl:attribute>


</xsl:when>


</xsl:choose>


<Data>


<xsl:choose>





<xsl:whentest="(@datatype = 'Date')and (substring(@value,7,2) &gt; 39)">


<xsl:attributename="ss:Type">DateTime</xsl:attribute>


<xsl:value-ofselect="19"/><xsl:value-ofselect="substring(@value,7,2)"/>-<xsl:value-ofselect="substring(@value,1,2)"/>-<xsl:value-ofselect="substring(@value,4,2)"/>T00:00:00.000</xsl:when>


<xsl:whentest="(@datatype = 'Date')and (substring(@value,7,2) &lt;= 39)">


<xsl:attributename="ss:Type">DateTime</xsl:attribute>


<xsl:value-ofselect="20"/><xsl:value-ofselect="substring(@value,7,2)"/>-<xsl:value-ofselect="substring(@value,1,2)"/>-<xsl:value-ofselect="substring(@value,4,2)"/>T00:00:00.000</xsl:when>


<xsl:whentest="(@datatype = 'Integer')or(@datatype = 'Decimal')or(@datatype = 'BigInteger')">


<xsl:attributename="ss:Type">Number</xsl:attribute>


<xsl:value-ofselect="@value"/>


</xsl:when>


<xsltherwise>


<xsl:attributename="ss:Type">String</xsl:attribute>


<xsl:value-ofselect="@value"/>


</xsltherwise>


</xsl:choose>


.....


These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.


All feedback providing scenarios where this change wouldn't work, or scenarios that would require any other formatting of date type fileds, is most welcome





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.



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