As of version 5.0, Dynamics NAV introduced the option to export forms to MS Office (Word or Excel). Data presented on the form are exported in an xml, formatted using style sheets then sent to Word\/Excel\/Other programs<\/p>\n
A Style Sheet tool for Dynamics NAV has later been released\u00a0to help partners create custom style sheets for export to Word in an easy and user friendly way. A possibillity for extending functionality to export to Excel\u00a0might be\u00a0considered\u00a0for future versions.<\/p>\n
Meanwhile, customizing style sheets for excel, though requireing basic knowledge in xml\u00a0and style sheets, can be simplified with some coding handling basic formatting\u00a0of style sheets.<\/p>\n
The FOB attached contains an example of how to create such a tool,\u00a0allowing some\u00a0basic customizations to excel style sheets.<\/p>\n
Style Tool for Excel (xlt)<\/strong><\/p>\n The Style Toos for EXceL (xlt) is designed to make it easier to customize style sheets for exporting to Excel. Style sheets have endless possibilities to format your Excel documents. This tool makes some of the most common possibilities simpler, but does not cover all possible changes, so it may not eliminate the need for editing style sheets in external tools, but may help you with some simple customizations.<\/p>\n Note, this tool was created\u00a0and tested on \u00a05.01 update 1 client, and assumes using default NavisionFormToExcel style sheet (update 1) as\u00a0a starting point\u00a0for customizations.<\/p>\n To use it with 5.0 client, you might need to modify the tool.<\/p>\n This is what you can achieve with xlt:<\/strong> How to use xlt:<\/strong> Before you can make changes to a style sheet, you need to import it by clickin on Style -> Import, and choose an existing style sheet for export to Excel (such as NavisionFormToExcel), either from the database or from a file.<\/p>\n On the Destination tab, specify the table and form IDs that the style sheet will be used for.<\/p>\n Example:<\/strong> Excel Styles: As an example, consider the following scenario: we want to modify the existing label (caption) style and also create a new style (red fonts) that we apply to records in Net Change column with negative value. You can apply same kind of change directly in the Style tab (instead of opening Excel), provided you are familiar with properties and values as defined in Excel. If modifying styles directly in Style tab, remember to click ‘Update (F11)’ to apply changes to style sheet.<\/p>\n Note that<\/p>\n 1) moving away from the style by closing the form or moving to the next record will not save any changes you made manually (and not through excel), and<\/p>\n 2) the tool does not validate if changes you do manually are valid.<\/p>\n Now we want to create a new style and apply it to Net Change column of records with net change < 0. Conditional formatting:<\/strong> To follow our example, we will now specify the condition for using the style created in last step, RED style. Totals<\/strong> XLStyleSheetDataMgt.UpdateDataXML(DataXML,StylesheetID);<\/p>\n where XLStyleSheetDataMgt is a new local variable, type Codeunit, subtype 73001 (“XLT Style Sheet Data Mgt”)<\/p>\n This will add totals to the data that you export to Excel, and it will add elements to the style sheet to handle these totals. Note: This will not work for RTC because RTC does not make calls to codeunit 403.<\/p>\n Export style sheet:<\/strong> Open form 18 (Chart of Accounts), in toolbar menu, click on send-to button. Select Microsoft Excel and select ‘export Chart of Accounts’ style sheet., click on OK. Other functionality:<\/strong> These postings are provided “AS IS” with no warranties and confer no rights. You assume all risk for your use.<\/p>\n All feedback regarding issues or suggestion to this tool are very welcome!<\/p>\n","protected":false},"excerpt":{"rendered":" As of version 5.0, Dynamics NAV introduced the option to export forms to MS Office (Word or Excel). Data presented on the form are exported in an xml, formatted using style sheets then sent to Word\/Excel\/Other programs A Style Sheet tool for Dynamics NAV has later been released\u00a0to help partners create custom style sheets for<\/p>\n","protected":false},"author":4528,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ms_queue_id":[],"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","footnotes":""},"post_tag":[],"product":[],"content-type":[4690],"topic":[],"audience-type":[],"audience":[],"ms-author":[],"coauthors":[4273],"class_list":["post-42784","post","type-post","status-publish","format-standard","hentry","content-type-news-and-product-updates","review-flag-1593580425-950","review-flag-2-1593580434-938","review-flag-5-1593580450-212","review-flag-new-1593580245-522"],"yoast_head":"\n
\nDefine a (font) style to be used on document, for example Bold, color, underline, itallic, etc.
\nDefine a style for certain condition, for example “Net Change” < 0, “Customer No.” = 10000, etc
\nAdd totals.<\/p>\n
\nAll functionality in the tool can be accessed from form 73000 “XLT Style Card”. Run the form and insert a new record to create a new custom style sheet, and specify Code and Description.<\/p>\n
\nRun form 73000 and press F3. Fill out Code:ChartOfAccounts, Description: export Chart of Accounts
\nImport the standard NavisionFormToExcel style sheet (by default placed in <client folder>\\Stylehseets), by clicking on Style-Import and selecting NavisionFormToExcel style sheet.
\nIn New XSLT file location field, specify the location of your customized style sheet (example: c:\\temp\\NewXsltFIleName.xslt).
\nOn Destination tab, specify TableID=15, select ‘Style sheet for this form only’ and select form 16, Chart of Accounts.<\/p>\n
\n<\/strong>You are now ready to customize your style sheet. To create new style, click Design -> Excel Styles. This shows you the styles that were imported with the standard style sheet. From here you can modify existing, or create new ones. To create a new style, press F3, and enter a Style ID.
\nThen click on “Edit in Excel (F9)” to define this new style. It will open up Microsoft Excel, and here you can apply any formatting you wish, TO THE TOP LEFT CELL. The style you apply to this cell and it’s contents will be imported and applied to all the cells your style applies to. Once you are done applying your style, close and save Excel, and then go back to NAV and confirm to import the style.<\/p>\n
\nTo modify existing label style, click on Design-Excel Styles, press Page Up\/Down to browse between records, find Style ID: Label and Press F9 (Edit in excel). In opened excel ark, go to top left cell, in Excel menu select Home-Font and select a different font (for example, Arial Black, size 14). Change also the color to blue. Close the excel and save the file. In NAV client, click on YEs on prompted question (Please apply format in Excel sheet…..). In the Style tab, check that new style is applied\u00a0 (you should see the record with Option=Font, Property=ss:FontName and Value=Arial Black).<\/p>\n
\nPress F3 to create a new style, in Style ID field type RED. Click on F9 (Edit in Excel) and in opened excel sheet click on top left cell. In Excel menu, select Home-Font and select red font color. Close the Style Sheet, answer yes to save changes. In NAV Client answer yes to prompted question (Please apply format in Excel Sheet…). In General tab, You should now see properties defined for StyleID=RED<\/p>\n
\nAfter designing the style(s), then go back to the XLT Style card, and then click Design -> “Conditional Formatting” to apply this style to certain conditions. It is not recommended that you change any of the existing layouts here, but you can create a new one by clicking “Insert (F3)”. This opens a mini-wizard where you can specify which columns and values this will apply to.<\/p>\n
\nClose the Excel Styles form and in Xslt style card click on Design-Conditional Formatting. Click on F3 (or click on Insert) to create new condition (it is not recommended to modify any of the existnig, defualt ones). Wizard form opens. We will now create a condition to apply style red to NET Change column with values < 0. Select TableID=15 and Field 32 (Net Change). In Condition field, select'<‘ anad Value=0. Finally, In Choose style select RED. Click on Finish. You should now see your condition defined in General tab. Condition= (@name=’Net Change’) and (@value<‘0’).
\nClick on Update to update the style sheet with new condition.<\/p>\n
\nYou can specify fields that you want to create totals for, by clicking on Design -> “Total Fields”, and select one or more (numerical!) fields. You also need to add one line of C\/AL code in codeunit 403 “Application Launch Management”. The following line should be added to Codeunit 403, at the top of function LanuchApp:<\/p>\n
\nWhen you have created one or more style(s) and applied them to certain conditions, then you generate a new style sheet from the “XLT Style Card”\u00a0 by clicking Style -> Export, and then select to export to file or database. If exporting to database, then a new style sheet will be saved in database, using the Description you have specified in the “XLT Style Card”.<\/p>\n
\nChart of Accoutns shoud lbe exported to excelm, with labels in Arial Black font, size 14. Net change column where value < 0 should be marked RED and totals should be present for Net Change column.<\/p>\n
\n– From “XLT Style Card”, tab “Standard Styles”, specify a style to apply to totals.
\n– Click Style -> Reset to re-load the stylesheet, which will erase any changes you have done since you imported it.<\/p>\n