Customizing style sheets for MS Excel
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 to 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 might be considered for future versions.
Meanwhile, customizing style sheets for excel, though requireing basic knowledge in xml and style sheets, can be simplified with some coding handling basic formatting of style sheets.
The FOB attached contains an example of how to create such a tool, allowing some basic customizations to excel style sheets.
Style Tool for Excel (xlt)
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.
Note, this tool was created and tested on 5.01 update 1 client, and assumes using default NavisionFormToExcel style sheet (update 1) as a starting point for customizations.
To use it with 5.0 client, you might need to modify the tool.
This is what you can achieve with xlt:
Define a (font) style to be used on document, for example Bold, color, underline, itallic, etc.
Define a style for certain condition, for example "Net Change" < 0, "Customer No." = 10000, etc
Add totals.
How to use xlt:
All 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.
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.
On the Destination tab, specify the table and form IDs that the style sheet will be used for.
Example:
Run form 73000 and press F3. Fill out Code:ChartOfAccounts, Description: export Chart of Accounts
Import the standard NavisionFormToExcel style sheet (by default placed in <client folder>\Stylehseets), by clicking on Style-Import and selecting NavisionFormToExcel style sheet.
In New XSLT file location field, specify the location of your customized style sheet (example: c:\temp\NewXsltFIleName.xslt).
On Destination tab, specify TableID=15, select 'Style sheet for this form only' and select form 16, Chart of Accounts.
Excel Styles:
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.
Then 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.
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.
To 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 (you should see the record with Option=Font, Property=ss:FontName and Value=Arial Black).
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.
Note that
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
2) the tool does not validate if changes you do manually are valid.
Now we want to create a new style and apply it to Net Change column of records with net change < 0.
Press 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
Conditional formatting:
After 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.
To follow our example, we will now specify the condition for using the style created in last step, RED style.
Close 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').
Click on Update to update the style sheet with new condition.
Totals
You 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:
XLStyleSheetDataMgt.UpdateDataXML(DataXML,StylesheetID);
where XLStyleSheetDataMgt is a new local variable, type Codeunit, subtype 73001 ("XLT Style Sheet Data Mgt")
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 (see blog here: https://blogs.msdn.com/nav_developer/archive/2008/12/16/dynamics-nav-2009-and-ms-office-integration-send-to-excel-and-word.aspx).
Export style sheet:
When 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" 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".
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.
Chart 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.
Other functionality:
- From "XLT Style Card", tab "Standard Styles", specify a style to apply to totals.
- Click Style -> Reset to re-load the stylesheet, which will erase any changes you have done since you imported it.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
All feedback regarding issues or suggestion to this tool are very welcome!
Jasminka Vukovic, Lars Lohndorf-Larsen
Microsoft Dynamics NO
Microsoft Customer Service and Support (CSS) EMEA
Comments
Anonymous
February 17, 2009
PingBack from http://www.anith.com/?p=11040Anonymous
April 22, 2009
A while a go we posted a blog decribing how to make some basic customizations to style sheets used forAnonymous
April 22, 2009
A while a go we posted a blog decribing how to make some basic customizations to style sheets used forAnonymous
April 23, 2009
A while a go we posted a blog decribing how to make some basic customizations to style sheets used for