Jaa

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

  • Artikkeli

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.

To represent strings as dates in excel, .xml file generated must contain dates in XML datetime format. XML format is defined as:

yyyy-mm-ddT00.00.000Z.

The scenario also assumes years in Dynamics NAV are represented with 2 digits,  and years in range 00..29 are in fact 2000..2029, while years in range  30.. will be formatted as 1930... Dynamcis NAV date format in this example is mm/dd/yy.

In the coded below, substring(@value,1,2)=mm, substring(@value,4,2)=dd and substring(@value,7,2)=yy

Modify the code in example below according to date format in local NAV client. 

To modify the style sheet, 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.

....

<

Style ss:ID="CheckBox">

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

<Font ss:FontName="Verdana" x:Family="Swiss"/>

</Style>

<Style ss:ID="rowheading">

<Font x:Family="Swiss" ss:Bold="1"/>

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

</Style>

<

Style ss:ID="Dateformat"> <!-- THIS LINE IS ADDED !-->

<!-- this is where we define the style for date type fields!-->

<

NumberFormat ss:Format="Short Date"/> <!-- THIS LINE IS ADDED !-->

</

Style> <!-- THIS LINE IS ADDED !-->

....

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

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:template match="Control[@type='TextBox']">

<

Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet">

<xsl:choose>   

<xsl:when test="(@datatype = 'Date')"> <!-- THIS LINE IS ADDED !--><!-- Dateformat style is to be used for data type Date !-->

<xsl:attribute name="ss:StyleID">Dateformat</xsl:attribute> <!-- THIS LINE IS ADDED !-->

</xsl:when> <!-- THIS LINE IS ADDED !-->

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

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

</xsl:when>

</xsl:choose>

<

Data>

<

xsl:choose>     

<!-- The actual date formatting, make sure data sent from navision are formatted according to XML datetime format yyyy-mm-ddT00.00.000 and passed as dateTime type of variable. This makes sure Excel will consider this as date type value. Excel will from here take input data and format the output in the format selected in regional settings (or excel settings) !-->

<xsl:when test="(@datatype = 'Date')and (substring(@value,7,2) &gt; 29)"> <!-- THIS LINE IS ADDED !-->

<xsl:attribute name="ss:Type">DateTime</xsl:attribute> <!-- THIS LINE IS ADDED !-->

<xsl:value-of select="19"/><xsl:value-of select="substring(@value,7,2)"/>-<xsl:value-of select="substring(@value,1,2)"/>-<xsl:value-of select="substring(@value,4,2)"/>T00:00:00.000</xsl:when> <!-- THIS LINE IS ADDED !-->

<xsl:when test="(@datatype = 'Date')and (substring(@value,7,2) &lt;= 29)"> <!-- THIS LINE IS ADDED !-->

<xsl:attribute name="ss:Type">DateTime</xsl:attribute> <!-- THIS LINE IS ADDED !-->

<xsl:value-of select="20"/><xsl:value-of select="substring(@value,7,2)"/>-<xsl:value-of select="substring(@value,1,2)"/>-<xsl:value-of select="substring(@value,4,2)"/>T00:00:00.000</xsl:when>  <!-- THIS LINE IS ADDED !-->

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

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

<xsl:value-of select="@value"/>

</xsl:when>

<xsl:otherwise>

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

<xsl:value-of select="@value"/>

</xsl:otherwise>

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

Comments