Edit In Excel – Part 4 (out of 4)

If you haven’t read part 3, part 2 (and part 1), you should do so before continuing here.

We have seen how to put code inside Excel, using VSTO and connect to NAV 2009 Web Services. We have seen how to add this to a table inside Excel and how to write data back to NAV through Web Services. We can delete, add and modify records in Excel and we can even do so with both Customers, Vendors and Items. We have added support for NAV filters, error handling and the only thing we are missing to have a very good experience is integrating the whole thing into NAV.

So we better do that now!

Disclaimer

Most of the people reading this are probably superior AL coders compared to me. I really only started coding in AL last year and I am still struggling to understand how things works. So, if I do something stupid in AL – just tell me in a comment how to do it smarter – thanks.

Actions in NAV 2009

What we want to do is

image

Add an action to the menu, which launches Excel, reads the same data as we have in the list place and allows the user to modify that data.

But we need to start in a different area – we need a COM object, which our action can invoke.

Creating a COM object

First of all we will create a COM object, which contains one function.

public void EditInExcel(string page, string view)

I do think there are a number of tutorials that explains how to do this, so I will run over the steps very quickly.

  1. In the same solution as the NAVTemplate, create a new project – type Class Library and name the project NAVEditInExcel
  2. Rename class1.cs to NAVEditInExcel.cs – and say Yes to the question whether you want to rename the class as well.
  3. Select Properties on the project (not the solution)
    1. On the Build tab, set the output path to ..NAVTemplatebinDebug in order to share the output path the the Excel Spreadsheet
    2. On the Build events tab, we need to register the COM object to make it visible to NAV. Add the following Post Build Event: C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm NAVEditInExcel.dll /codebase /tlb
    3. On the Signing tab, check the Sign the Assembly checkbox and select New key in the combo box, name the key and protect it with a password if you fancy.
  4. Open the AssemblyInfo.cs (under Properties in the Solution Explorer)
    1. Add using system; to the using statements
    2. Add [assembly: CLSCompliant(true)] under the line with [assembly: ComVisible(false)].
  5. Open the source for the NavEditInExcel.cs
    1. Add using System.Runtime.InteropServices; to the using statements
    2. Create an Interface and change the class to be:

[ComVisible(true)]
[Guid(“A2C51FC8-671E-4135-AD27-48EDC491E76E”), InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INAVEditInExcel
{
void EditInExcel(string page, string view);
}

[ComVisible(true)]
[Guid(“233E0C7F-2276-4142-929C-D6BA8725D7B4”), ClassInterface(ClassInterfaceType.None)]
public class NAVEditInExcel : INAVEditInExcel
{
public void EditInExcel(string page, string view)
{

        // Code goes here…
    }
}

Now you should be able to build the COM object and see it inside NAV when adding a variable of type automation.

Adding the Action in NAV

Open up the Classic Client and design the Customer List Place.

Insert an Action on the Customer List Place called Edit In Excel and edit the code for that (btw. the Image Name for the Excel icon is Excel)

In the code for that Action – create a local variable called NAVEditInExcel of type Automation and select the NAVEditInExcel.NAVEditInExcel COM object to use and add the following code:

CREATE(NAVEditInExcel, TRUE, TRUE);
NAVEditInExcel.EditInExcel(TABLENAME, GETVIEW(TRUE));

That’s it on the NAV side, but of course we didn’t make all the code necessary in the COM object yet.

If you try to launch the Action you will be met by the security dialog

image

Which you of course want to hit always allow to – else you will get this dialog every time you say Edit In Excel.

BTW – If you hit Never allow – you will never be allowed to Edit in Excel – not until you have deleted your PersonalizationStore.xml at least.

Completing the COM object

Having that hooked up we really just need to launch that damn spreadsheet with the right parameters.

We need to add 3 .NET references to the COM object:

  • System.Windows.Forms
  • Microsoft.Office.Interop.Excel
  • Microsoft.VisualStudio.Tools.Applications.ServerDocument.v9.0

and the following 3 using statements:

using Microsoft.VisualStudio.Tools.Applications;
using System.Windows.Forms;
using System.Reflection;

and last but not least, add the following EditInExcel method:

public void EditInExcel(string page, string view)
{
try
{
// Copy the original template to a new template using the page name as name!
string originalTemplate = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), “NAVTemplate.xltx”);
if (!System.IO.File.Exists(originalTemplate))
{
MessageBox.Show(string.Format(“The template: ‘{0}’ cannot be found!”, originalTemplate), “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string template = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), page + “.xltx”);
while (System.IO.File.Exists(template))
{
try
{
System.IO.File.Delete(template);
}
catch (System.IO.IOException)
{
if (MessageBox.Show(string.Format(“The template: ‘{0}’ is locked, cannot open spreadsheet”, template), “Error”, MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) != DialogResult.Retry)
{
return;
}
}
}
System.IO.File.Copy(originalTemplate, template);

        // Open the new template and set parameters
ServerDocument serverDoc = new ServerDocument(template);
CachedDataHostItem host = serverDoc.CachedData.HostItems[0];
host.CachedData[“page”].SerializeDataInstance(page);
host.CachedData[“view”].SerializeDataInstance(view);
serverDoc.Save();
serverDoc.Close();

        // Create a new spreadsheet based on the new template
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
excelApp.Workbooks.Add(template);

        // Erase template
System.IO.File.Delete(template);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, “Critical error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

This method really does 4 things:

  1. Copy the NAVTemplate.xltx to a new template called Customer.xltx (that is if the page name is customer) which is a temporary template
  2. Open the template as a ServerDocument and set the parameters
  3. Ask Excel to create a new spreadsheet based on this template
  4. Erase the template

That was easy!

Oh – there is one things I forgot to say, you need to specify in the Excel Spreadsheet that the page and view variables are cached data (meaning their value are saved with Excel) – this is done by adding an attribute to the variables:

[Cached]
public string page;

[Cached]
public string view;

Having done this, you can open the spreadsheet as a Serverdocument, get and set the value of these parameters and save the document again, pretty sweet way of communicating parameters to Excel or Word – and this will definitely come in handy later.

Adding the action other pages

Having verified that we can edit customers in Excel we can now add the same action as above to the Vendor and the Item List Places.

You can either follow the same steps as above – or you can copy the action and paste it on the other List Places.

Note that you cannot build the Visual Studio solution while you have NAV 2009 open. When NAV loads the COM object, it keeps a reference to it until you close NAV.

Last but not least – this should work from the classic client as well – if you want to add the functionality there – I haven’t tried it.

That’s it folks

That completes the Edit In Excel in Part 1 through 4

As always, there is absolutely no warranty that this code works for the purpose you need it to, but these posts show how to do some things and feel free to use pieces of this or use it as a base to build your own solution using Excel – the code is free – a referral to my blog is always a good way of acknowledgement.

I hope you can make it work, that it is useful and you can download the final solution here: http://www.freddy.dk/NAVTemplate_Final.zip

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 3 (out of 4)

If you haven’t read part 2 (and part 1), you should do so before continuing here.

In Part 1 and 2, we have seen how easy it is to add a Web Service Reference inside Excel, and use it to get Data. In Part 2 we even had the ability to modify data and send this back to NAV. The original intend was that part 3 would be all about integrating this to NAV on the Client side and part 4 would be to make this loosely coupled – but I have changed my mind on this.

Part 3 will remove the direct dependency on the Customer Web Service from most of the code – and thus allowing us to modify both Customer, Vendor or Item data in Excel with very few tweaks to the code. Also I will add support for parsing a filter string and applying this to the list. I will also add error handling of the save process.

Part 4 will then be to add the Action in NAV and hook that up to set the parameters in Excel.

I will still post the source of the original loosely coupled XMLHTTP based Edit In Excel, but I will not use it for anything.

To prepare ourselves for part 4 we need the following variables:

/// <summary>
/// Page which is going to be used for Edit In Excel
/// Customer, Vendor, Item, etc…
/// The card page for this record needs to be exposed as webservice with that name
/// </summary>
string page;

/// <summary>
/// The filters to apply (format: GETVIEW(TRUE))
/// Sample: “SORTING(No.) WHERE(Balance (LCY)=FILTER(>10,000))”
/// </summary>
string view;

These are the parameters, which we in part 4 will transfer values to Excel in – for now we will build the Spreadsheet to use those.

BTW – I changed the Project name from CustomerTemplate to NAVTemplate (actually I created a new project and copied over some of the files and changed the namespace).

Then I have moved the service connection initialization away from Load – and into Sheet_Startup, the new Sheet1_Startup code looks like this

private void Sheet1_Startup(object sender, System.EventArgs e)
{
switch (this.page)
{
case “Customer”:
this.service = new CustomerRef.Customer_Service();
break;
case “Vendor”:
this.service = new VendorRef.Vendor_Service();
break;
case “Item”:
this.service = new ItemRef.Item_Service();
break;
default:
MessageBox.Show(string.Format(“Page {0} is not setup for Edit In Excel. Please contact your system administrator”, this.page), “Microsoft Dynamics NAV”, MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
}
if (this.service != null)
{
this.service.UseDefaultCredentials = true;
Load();
}
}

and I have added references to all 3 services.

This is the only place I have a switch on the page – the rest of the code is made to work with all – but wait… – how is that possible?

Service Connection classes code generated from Visual Studio doesn’t implement any common interface and we cannot change the code generated proxy classes (or rather – we don’t want to). We can, however, add something to the existing service. Looking at the code generated proxy class we will notice that the Customer_Service class is defined as a partial class – meaning that we can actually write another part of the class just by creating a new class (with the keyword partial)

Looking through my code I really need the Customer_Service to implement an interface like this:

public interface INAVService
{
bool UseDefaultCredentials {get; set; }
System.Net.ICredentials Credentials {get; set; }

    object[] ReadMultiple();
void Update(object obj);
void Create(object obj);
bool Delete(string key);

    Type GetFieldsType();
Type GetObjectType();

    void ClearFilters();
void AddFilter(string field, string criteria);
}

Some of these methods are already implemented by all Service Proxy classes and I use this to allow my code to look at the Service Connection via this interface only and the service variable I have in the sheet is actually type INAVService, flip side of this idea is, that for every new Page I want to add – I need to create a class like this:

public partial class Customer_Service : INAVService
{
List<Customer_Filter> filters;

    #region INAVService Members

    public object[] ReadMultiple()
{
return this.ReadMultiple(this.filters.ToArray(), null, 0);
}

    public void Update(object obj)
{
Customer customer = (Customer)obj;
this.Update(ref customer);
}

    public void Create(object obj)
{
Customer customer = (Customer)obj;
this.Create(ref customer);
}

    public Type GetObjectType()
{
return typeof(Customer);
}

    public Type GetFieldsType()
{
return typeof(Customer_Fields);
}

    public void ClearFilters()
{
this.filters = new List<Customer_Filter>();
}

    public void AddFilter(string field, string criteria)
{
Customer_Filter filter = new Customer_Filter();
filter.Field = (Customer_Fields)Enum.Parse(typeof(Customer_Fields), field, true);
filter.Criteria = criteria;
this.filters.Add(filter);
}

    #endregion
}

Not really nice – but it beats having a series of switch statements scattered around in the source files.

So, whenever we want to add a record object type, which we want to be able to Edit In Excel – we add a source file like this (search and replace Customer with <newtype>), we add an extra outcome in the switch statement above and we expose the page to Web Services in NAV 2009.

BTW – In my solution, I have added the classes to the solution in a folder called Services.

Applying Filters and Load

The Load method now looks like this:

/// <summary>
/// Load Records from NAV via Web Services
/// </summary>
private void Load()
{
PopulateFieldsCollection(this.service.GetObjectType(), this.service.GetFieldsType());
SetFilters(this.view);
this.objects = this.service.ReadMultiple();
PopulateDataTable();
AddDataToExcel();
}

Note that we ask the Service connection class for the Object Type, the Fields Enum Type and we call the ReadMultiple on the Service Connection (all through the interface we just implemented).

After generating fields collection and the DataTable we call SetFilters – which in effect just parses the view variable (sample: “SORTING(No.) WHERE(Balance (LCY)=FILTER(>10,000))” – without the quotes) and calls AddFilter a number of times (in the sample only once) on the Service Connection Interface.

I added a NAVFilterHelper static class with 3 static helper methods – GetBlock, WSName and VSName.

GetBlock parses the string for a block (a keyword followed by a parentheses with stuff in it) – SORTING(No.) is one and the WHERE clause is another. The FILTER  clause is another block inside the WHERE block.

WSName takes a name like “Balance (LCY)” and puts it through name mangling to get the Visual Studio generated identifier name (this is the name used in Enum – Balance_LCY)

VSName takes the enum identifier and removes special characters to get the Property name of the record object (there are no special characters in Balance_LCY)

Confused? – well look at this:

test  &&//(())==??++–**test – is a perfectly good (maybe stupid) field name in NAV

test__x0026__x0026___x003D__x003D__x003F__x003F__x002B__x002B___x002A__x002A_test is the same identifier in the xx_Fields enum (from the WSDL)

test___test is the same identifier as property in Visual Studio (the code generated proxy class)

and yes – you can generate fields, which will cause Web Services to fail. In fact, CTP4 (the US version) has an Option field in Customer and Vendor (Check Seperator), where the options causes Customer and Vendor to fail when exposed to Web Services. This special case is fixed for RTM – and the WSName in my sample contains the same name mangling as NAV 2009 RTM, but you can still create field names, which will end up having identical names in VS – and then your WebService proxy won’t work.

WSName and VSName works for my usage – they might not work for all purposes.

There is really nothing fancy about the SetFilters code, but it works for the purpose:

/// <summary>
/// Parse the view and apply these filters to the Service Connection
/// </summary>
/// <param name=”view”>View to parse (from AL: GETVIEW(TRUE))</param>
private void SetFilters(string view)
{
this.service.ClearFilters();
if (string.IsNullOrEmpty(view))
return;
string sorting = NAVFilterHelper.GetBlock(“SORTING”, ref view);
string where = NAVFilterHelper.GetBlock(“WHERE”, ref view);
do
{
int e = where.IndexOf(“=FILTER”);
if (e < 0)
break;
string field = NAVFilterHelper.WSName(where.Substring(0, e));
string criteria = NAVFilterHelper.GetBlock(“FILTER”, ref where);
this.service.AddFilter(field, criteria);

        if (where.StartsWith(“,”))
where.Remove(0, 1);
}
while (true);
}

Yes, yes – as you of course immediately spotted – this code doesn’t work if you have a field with =FILTER in the field name – so don’t!

The PopulateDataTable and AddDataToExcel methods haven’t changed.

Save

One thing we didn’t get done in part 2 was error handling. If anybody tried to modify a Location Code to an illegal Location Code and save it back to Excel – you will have noticed that Excel just ignored your request.

Reason for this is, that Excel swallows the Exception, and just ignores it.

So – I have changed the Save() method to:

/// <summary>
/// Save Changes to NAV via Web Service
/// </summary>
internal void Save()
{
if (DoSave())
{
Reload();
}
}

and then created the DoSave() – with most of the content from Save() – but refactored inside one loop with error handling (Abort, Retry, Ignore).

/// <summary>
/// Delete, Add and Update Records
/// </summary>
internal bool DoSave()
{
// Run through records marked for delete, create or modify
DataView dv = new DataView(this.dataTable, “”, “”, DataViewRowState.Deleted | DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
foreach (DataRowView drv in dv)
{
bool retry;
do
{
retry = false;
try
{
if (drv.Row.RowState == DataRowState.Deleted)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
if (!service.Delete((string)drv[0]))
{
throw new Exception(string.Format(“Unable to delete record”));
}
}
}
else if (drv.Row.RowState == DataRowState.Added)
{
object obj = Activator.CreateInstance(this.service.GetObjectType());
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(obj, drv.Row[nfi.field]);
}
}
this.service.Create(obj);
}
else
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(obj, drv[nfi.field]);
}
}
this.service.Update(obj);
}
}
}
catch (Exception e)
{
DialogResult reply = MessageBox.Show(string.Format(“{0} {1} {2}nn{3}”, this.dataTable.TableName, this.dataTable.Columns[1].Caption, drv[1].ToString(), e.Message), “Microsoft Dynamics NAV”, MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Error);
if (reply == DialogResult.Abort)
return false;
if (reply == DialogResult.Retry)
retry = true;
}
} while (retry);
}
return true;
}

oh yes, and beside that – you can see that I now use the methods on the Service Connection Interface directly and I do not use the type safe references to Customer – but instead just object. A couple of comments:

The DataView is the only way (I know of) that we can see which records have been deleted in the DataTable.

The Line

object obj = Activator.CreateInstance(this.service.GetObjectType());

does the same as using

object obj = new CustomerRef.Customer();

if the object type of the Service Connection is Customer.

So if you change the location code on a customer to something stupid you now get this error:

image

Of course it doesn’t make much sense to Retry this one. Abort aborts the save – and doesn’t reload. Ignore continues the Save and ends up reloading and you loose the changes, we couldn’t save. Note that Abort is a little strange – it cannot abort the changes that has happened up until you abort – and since it doesn’t reload, it leaves the spreadsheet in a bad state.

Maybe we should just remove the abort option – it just seems like a bad thing only to be able to retry or press ignore on every single record. If I come up with a better way of handling Abort, I will post that.

Can’t wait for part 4?

Part 4 is where we integrate this into NAV and create Actions to call out to open this Excel Spreadsheet on the Client and that includes changes in NAV, a Client Side COM object and a mechanism to transfer parameters to an Excel spreadsheet – stay tuned.

In my original Edit In Excel sample, I use XMLHTTP to retrieve page information and look through that – I will still post the original Edit In Excel source code with part 4 – but note, that there are a LOT of pitfalls in that – and, it doesn’t support Adding records or deleting records, and I have stopped working on that code, even though the need for using XMLHTTP might still be relevant.

The safer way is to use the sample solutions posted with this walk through.

BTW – the NAVTemplate solution can be downloaded here: http://www.freddy.dk/NAVTemplate.zip

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 2 (out of 4)

If you haven’t read Part 1, you should do so before continuing here.

In Part 1 we saw how easy it is to white .net code inside Excel, and get it executed based on an event in Excel, and how easy it is to fill values into cells. But in order to make this really useful we need to go a different way around.

First of all, we need to know more about the record we are working with. We could of course hard code everything – but that is not the preferred way to go. It needs to be flexible.

When referencing the Customer Page in Visual Studio, Visual Studio creates a proxy class (a wrapper for the Customer).

This Proxy class contains members for all fields on the Page and properties to access them, like:

public partial class Customer
{
private string keyField;
private string noField;
private string nameField;
… etc …


public string Key
{
get { return this.keyField; }
set { this.keyField = value; }
}

public string No
{
get { return this.noField; }
set { this.noField = value; }
}

    public string Name
{
get { return this.nameField; }
set { this.nameField = value; }
}

… etc …
}

Meaning that if we have a variable of type Customer, we can get the value of the fields by accessing cust.Name etc., as we saw in part 1. There is no collection of fields, field types and getters/setters we can call – all we have is a class with a number of properties and an Enum with all the field names.

So, in order to be able to build a list of fields, look at their types and get their values without having to hard code everything, we need to use reflection.

Setting up definitions

I wont go into details about reflection here – but basically we need a method like this:

/// <summary>
/// Populate Fields Collection with NAVFieldInfo for all properties in the record
/// Should works with any NAV 2009 Page exposed as WebService
/// </summary>
/// <param name=”objType”>Type of Object (typeof(Customer), typeof(Vendor), …)</param>
/// <param name=”fieldsType”>Type of the Enum holding the property names</param>
private void PopulateFieldsCollection(Type objType, Type fieldsType)
{
// Create columns in Datatable
this.dataTable = new DataTable(objType.Name);
this.fields = new List<NAVFieldInfo>();

    // Key property is not part of the Enum
// Add it manually as the first field
AddField(“Key”, objType);

    // Run through the enum and add all fields
foreach (string field in Enum.GetNames(fieldsType))
{
AddField(field, objType);
}
}

This method enumerates all field names in the fieldsType Enum and call AddField for every field in the record object.

AddField then looks like this:

/// <summary>
/// Add a Column to the DataTable
/// And create a corresponding NAVFieldInfo
/// </summary>
/// <param name=”field”>Field name</param>
/// <param name=”objType”>Type of Object in which the field is (typeof(Customer), typeof(Vendor), …)</param>
private void AddField(string field, Type objType)
{
PropertyInfo pi = objType.GetProperty(field, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
NAVFieldInfo nfi = NAVFieldInfo.CreateNAVFieldInfo(field, pi, objType.Namespace);
// If we encounter unknown Field Types, they are just ignored
if (nfi != null)
{
this.fields.Add(nfi);
this.dataTable.Columns.Add(new DataColumn(field, nfi.fieldType));
}
}

It uses reflection to get the information about the property on the record object (the PropertyInfo).

After calling the PopulateFIeldsCollection, we will have a collection of fields in the this.fields variable and we will have the corresponding columns in the this.dataTable variable, all created with the right names and types on the columns.

For every different field type there is a different class which all derives from NAVFieldInfo. This class holds the field name, the field type and the PropertyInfo class (which is used for invoking the getter and setter on the record object later).

The static CreateNAVFieldInfo which is called from AddField then creates the right NAVFieldInfo class as shown below:

/// <summary>
/// Create a NAVFieldInfo object for a specific field
/// </summary>
/// <param name=”field”>Name of the property</param>
/// <param name=”pi”>PropertyInfo for the property on the record object</param>
/// <param name=”ns”>Namespace for the record object (namespace for the added WebServices proxy class)</param>
/// <returns>NAVFieldInfo or null if the type isn’t supported</returns>
internal static NAVFieldInfo CreateNAVFieldInfo(string field, System.Reflection.PropertyInfo pi, string ns)
{
if (pi.PropertyType == typeof(string))
{
// String Property – is it the KeyField
if (field == “Key”)
return new KeyFieldInfo(field, pi);
else
return new StringFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(decimal))
{
// Decimal Property
return new DecimalFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(int))
{
// Integer Property
return new IntFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(bool))
{
// Boolean Property
return new BooleanFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(DateTime))
{
// DateTime Property
return new DateTimeFieldInfo(field, pi);
}
if (pi.PropertyType.Namespace == ns)
{
// Other Property Types, in the same namespace as the object
// These are enum’s – set up restrictions on OptionFields
return new OptionFieldInfo(field, pi, Enum.GetNames(pi.PropertyType));
}
// Unsupported – ignore

return null;
}

Meaning that there are classes for KeyFieldInfo, StringFieldInfo, DecimalFieldInfo, IntFieldInfo, BooleanFieldInfo, DateTimeFieldInfo and OptionFieldInfo.

On these classes we have 3 virtual methods:

  1. Get the value of the property from a Record Object (GetValue)
  2. Set the value of the property on the Record Object (SetValue)
  3. Format the Excel column used to show this type (AdjustColumn)

Example of the AdjustColumn on the StringFieldInfo is:

/// <summary>
/// Adjust formatting and properties of an Excel column
/// </summary>
/// <param name=”column”>Excel Range of cells to set formatting on</param>
internal override void AdjustColumn(Microsoft.Office.Interop.Excel.Range column)
{
column.EntireColumn.NumberFormat = “@”;
column.EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
column.EntireColumn.AutoFit();
}

Which will set the format, the horizontal alignment and make the column with adjust to the size of the content.

The GetValue is defined as

/// <summary>
/// Get the value from the record object by calling the property getter on the object
/// </summary>
/// <param name=”obj”>The record object</param>
/// <returns>The value in the type specified in fieldType</returns>
internal virtual object GetValue(object obj)
{
return this.pi.GetValue(obj, null);
}

in the base class – and is only overwritten in the BooleanFieldInfo class. The SetValue is defined in the base class is:

/// <summary>
/// Set the value to the record object by calling the property setter on the object
/// </summary>
/// <param name=”obj”>The record object</param>
/// <param name=”value”>The new value for the field</param>
internal virtual void SetValue(object obj, object value)
{
    if (value == DBNull.Value)
{
if (!string.IsNullOrEmpty(this.pi.GetValue(obj, null) as string))
{
this.pi.SetValue(obj, “”, null);
}
}
else
{
this.pi.SetValue(obj, value, null);
}
}

has is overwritten in OptionFieldInfo and BooleanFieldInfo.

The reason for overwriting these functions is, that the property type in the Record Object is different from the type we set in the DataTable. For Boolean – we want to have a Yes/No option in Excel – but the type in the Record Object is a Boolean – not a string.

For Option Fields – the Property Type of the Record Object is an Enumeration defined in the same namespace as the Record Object and the OptionFieldInfo class uses reflection to enumerate the enumeration names.

Loading the data

After having information about all fields ready on our fingertips and the DataTable initialized and ready to recieve data – we just need to read the data and add it to the datatable (and then of course add the datatable to the Spreadsheet).

I have created a method called Load() – which does all of these things:

/// <summary>
/// Load Customers from NAV via Web Services
/// </summary>
private void Load()
{
PopulateFieldsCollection(typeof(CustomerRef.Customer), typeof(CustomerRef.Customer_Fields));
CustomerRef.Customer_Service service = new CustomerRef.Customer_Service();
service.UseDefaultCredentials = true;
this.objects = service.ReadMultiple(null, “”, 0);
PopulateDataTable();
    AddDataToExcel();
}

First of all, call the PopulateFieldsCollection method. Then create a Web Service Connection and read all record objects in an array of objects (note that I am NOT using strongly typed class references, as I want this code to be reusable when I work with other pages via Web Services).

The PopulateDataTable method became amazingly simple:

/// <summary>
/// Populate DataTable based on array of objects
/// </summary>
private void PopulateDataTable()
{
// Populate DataTable with data
foreach (object obj in this.objects)
{
DataRow dataRow = this.dataTable.NewRow();
foreach (NAVFieldInfo nfi in this.fields)
{
dataRow[nfi.field] = nfi.GetValue(obj);
}
this.dataTable.Rows.Add(dataRow);
}
}

Run through all rows and for all rows – run through all columns and get the value from the Record Object into the Data Table.

and the AddDataToExcel really isn’t that hard either:

/// <summary>
/// Add a DataList to Excel
/// This function should work with any Page exposed as Web Service
/// </summary>
/// <param name=”objects”>Array of records to add</param>
private void AddDataToExcel()
{
Application.ScreenUpdating = false;

    // Populate Excel Spreadsheet with data
this.dataList = this.Controls.AddListObject(this.Range[this.Cells[1, 1], this.Cells[this.dataTable.Rows.Count + 1, this.dataTable.Columns.Count + 1]], this.dataTable.TableName);
this.dataList.AutoSetDataBoundColumnHeaders = true;
this.dataList.DataSource = this.dataTable;
this.dataTable.AcceptChanges();

    // Adjust columns in excel with the right formatting based on Field Info
int col = 1;
foreach (NAVFieldInfo nfi in this.fields)
{
nfi.AdjustColumn(this.dataList.Range[1, col++] as Microsoft.Office.Interop.Excel.Range);
}

    Application.ScreenUpdating = true;
}

First, we disable ScreenUpdating, setup a ListObject with the created DataTable, call AdjustColumn on the correct NAVFieldInfo class for the corresponding column and enable screenupdating again.

Running the project with these things gives us:

image

Now – that’s more like it. We have data in a table, formatting on fields and even options in option fields:

image

and… Saving the changes!

The only thing we need to do more is to be able to write data back into NAV, if we change something. But wait – we would of course also like to be able to create records and delete records in the spreadsheet – lets see what is needed for that….

We use DataView to look at a subset of a DataTable (deleted, added or modified) and then we need to do react accordingly.

First we run through the deleted records and delete them from NAV – if you want to add a warning, you should do so before calling service.Delete.

// Run through records marked for deletion and delete those
DataView dv = new DataView(this.dataTable, “”, “”, DataViewRowState.Deleted);
foreach (DataRowView drv in dv)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
service.Delete(((CustomerRef.Customer)obj).Key);
}
}

Then we run through the added records and add them to NAV:

// Run through added records and add them
dv = new DataView(this.dataTable, “”, “”, DataViewRowState.Added);
foreach (DataRowView drv in dv)
{
CustomerRef.Customer customer = new CustomerTemplate.CustomerRef.Customer();
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(customer, drv[nfi.field]);
}
}
service.Create(ref customer);
}

and finally we run through the modified records and update them in NAV:

// Run through modified records and update them
dv = new DataView(this.dataTable, “”, “”, DataViewRowState.ModifiedCurrent);
foreach (DataRowView drv in dv)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(obj, drv[nfi.field]);
}
}
CustomerRef.Customer customer = (CustomerRef.Customer)obj;
service.Update(ref customer);
}
}

and after this – we reload the data.

so… – that was a LOT of code and some explaining. I hope you are with me so far.

I didn’t explain how to add a Ribbon to Excel, nor did I list all the NAVFieldInfo classes in this post, but you can download the solution as source from http://www.freddy.dk/CustomerTemplate.zip and see how these things are done. You can play around with things and/or use pieces of the code in your own solution.

The code shown in this post comes with no warranty – and is only intended for showing how to do things. The code can be reused, changed and incorporated in any project without any further notice.

So that’s all good, but what’s next?

We now have a spreadsheet, which can read customers via Web Services, allow you to modify, add or delete and save the changes through Web Services, which is pretty impressive – but we have 2 more posts on the topic:-)

Next, we want to integrate this into the Customer List Place and get the filter from the List Place to the spreadsheet. To do this we need a Client side COM object, which transfers parameters to the Excel Spreadsheet and launches Excel.

And last, we will make the Edit In Excel work without the Web Reference – meaning that we can just add an action to the pages on which we want the Edit In Excel to work.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 1 (out of 4)

For the last 6-9 months, Microsoft have been showing a demo of how a user could create a filter on a list place and invoke an action called Edit In Excel. This would open Microsoft Excel with the same records, the user selected in the List Place and allow the user to edit values and post modifications back to Microsoft Dynamics NAV, showing how the user would get a runtime error if he was trying to violate validation logic from NAV.

I will divide this post into 4 sections on how to achieve this

  1. Create an Excel Spreadsheet, which reads the entire Customer table and show it in Excel.
  2. Create an Excel Spreadsheet, which reads the entire Customer table, show it and allow the user to modify, delete or add records directly in Excel – and save the changes back to NAV.
  3. Hook this spreadsheet up to the Customer List Place and take the filter defined on the Customer List Place and apply that to Excel.
  4. Make the damn thing loosely coupled – allowing it to be placed on any List Place if you fancy.

I chose to divide it, in order to allow people better to understand the processes and I think that every one of these four steps will take you through a number of new things.

This first post is all about creating a spreadsheet, which reads the entire Customer table and show it in Excel. We could do this from inside NAV (populate an XML document and send it to the Client) but that would put us into a blind alley when going towards post number 2.

So – we are going to use VSTO

What is VSTO?

VSTO (Visual Studio Tools for Office) came out as an add-on to Visual Studio 2005 and in Visual Studio 2008, it is an integrated part of the professional SKU. Basically it allows you to write code inside Excel, Word, Outlook etc. – add toolbars, menu items, subscribe to triggers and do all kinds of things using your favorite .net language – and mine is C#.

VSTO is NOT in Visual Studio Express SKU’s.

I am not going to make this a tutorial on how to use VSTO – but I will just show a couple of basics. When creating a new project in Visual Studio, you have new options:

image

and when you select to create an Excel Template – it actually opens up a spreadsheet inside of Visual Studio.

image

Now Visual Studio might not be your favorite environment for using Excel, but that isn’t the point. If you look to the right you will see a solution and a project with a number of C# files under your .xltx “folder”. These are files, which contains code behind for the different sheets.

Right click on Sheet1.cs and select View Code and you will see:

image

Which kind of indicates where we are going here…

I can write code in the Sheet1_Startup, which connects to our Microsoft Dynamics NAV Web Services and read data into the Spreadsheet – could it really be that simple?

Yes, it really is that simple – but…

… when you see the code beneath in a moment you can see that this is really not scaling – and it really wouldn’t allow us to edit the data – but hey, that is in post number 2 – this was the simple version, let’s continue.

You of course need to add a Web Reference to the Customer page (Page 21 – exposed as Customer) using the following URL (if the NAV is installed as default):

http://localhost:7047/DynamicsNAV/WS/CRONUS_International_Ltd/Page/Customer

and call the reference CustomerRef.

Add the following code to Sheet1_Startup and run your solution.

// Postpone Screen updating
Application.ScreenUpdating = false;

// Initialize the Service
CustomerRef.Customer_Service service = new CustomerSimple.CustomerRef.Customer_Service();
service.UseDefaultCredentials = true;

// Read the customers
CustomerRef.Customer[] customers = service.ReadMultiple(null, null, 0);

// Populate the header line
int row = 1;
this.Cells[row, 1] = “No”;
this.Cells[row, 2] = “Name”;
this.Cells[row, 3] = “Address”;
this.Cells[row, 4] = “Address_2”;
// etc.

// Fill the spreadsheet
foreach (CustomerRef.Customer customer in customers)
{
row++;
this.Cells[row, 1] = customer.No;
this.Cells[row, 2] = customer.Name;
this.Cells[row, 3] = customer.Address;
this.Cells[row, 4] = customer.Address_2;
// etc.
}

// Set formatting for the added cells
Microsoft.Office.Interop.Excel.Range range = this.Range[this.Cells[1, 1], this.Cells[row, 4]];
range.EntireColumn.NumberFormat = “@”;
range.EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
range.EntireColumn.AutoFit();

// Update the Screen
Application.ScreenUpdating = true;

That’s it and that’s that! This should bring up Excel looking like this:

image

Not quite the end-goal, but I hope you get the picture.

As usual – you can download the solution here http://www.freddy.dk/CustomerSimple.zip

So where do we go from here?

In the next post we will start by removing the code we just wrote and write some new and better code in C#. We will still not touch NAV (only from Web Services). The goal here is to read the data from NAV into a table, with knowledge about the field types and add the NAV Key to a hidden column – all in order to be able to post data back to NAV.

My next post will also add a couple of buttons to the Excel toolbar and add the Save and Reload functions. It will still be hard coded to the Customer table though.

In my third post I will explain how to get parameters from code on the Service Tier into our Spreadsheet (like the filter) and hook things up accordingly.

Last, but not least, I will explain how we can do this without having a Web Reference to the Customer or other pages – how can we do this dynamically.

Stay tuned, enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Transferring binary data to/from WebServices (and to/from COM (Automation) objects)

A number of people have asked for guidance on how to transfer data to/from COM and WebServices in NAV 2009.

In the following I will go through how to get and set a picture on an item in NAV through a Web Service Connection.

During this scenario we will run into a number of obstacles – and I will describe how to get around these.

First of all – we want to create a Codeunit, which needs to be exposed to WebServices. Our Codeunit will contain 2 functions: GetItemPicture and SetItemPicture – but what is the data type of the Picture and how do we return that value from a WebService function?

The only data type (supported by Web Services) that can hold a picture is the BigText data type.

We need to create the following two functions:

GetItemPicture(No : Code[20];VAR Picture : BigText)
SetItemPicture(No : Code[20]; Picture : BigText);

BigText is capable if holding binary data (including null terminals) up to any size. On the WSDL side these functions will have the following signature:

image

As you can see BigText becomes string – and strings in .net are capable of any size and any content.

The next problem we will face is, that pictures typically contains all kinds of characters, and unfortunately when transferring strings to/from WebServices there are a number of special characters that are not handled in the WebServices protocol.

(Now you wonder whether you can have <> in your text – but that isn’t the problem:-)

The problem is LF, CR, NULL and other characters like that.

So we need to base64 (or something like that) encode our picture when returning it from Web Services. Unfortunately I couldn’t find any out-of-the-box COM objects that was able to do base64 encoding and decoding – but we can of course make one ourselves.

Lets assume for a second that we have a base64 COM object – then this would be our functions in AL:

GetItemPicture(No : Code[20];VAR Picture : BigText)
CLEAR(Picture);
Item.SETRANGE(Item.”No.”, No, No);
IF (Item.FINDFIRST()) THEN
BEGIN
  Item.CALCFIELDS(Item.Picture);
// Get Temp FileName
TempFile.CREATETEMPFILE;
FileName := TempFile.NAME;
TempFile.CLOSE;

  // Export picture to Temp File
Item.Picture.EXPORT(FileName);

  // Get a base64 encoded picture into a string
CREATE(base64);
Picture.ADDTEXT(base64.encodeFromFile(FileName));

  // Erase Temp File
FILE.ERASE(FileName);
END;

SetItemPicture(No : Code[20];Picture : BigText)
Item.SETRANGE(Item.”No.”, No, No);
IF (Item.FINDFIRST()) THEN
BEGIN
// Get Temp FileName
TempFile.CREATETEMPFILE;
FileName := TempFile.NAME;
TempFile.CLOSE;

  // Decode the bas64 encoded image into the Temp File
CREATE(base64);
base64.decodeToFile(Picture, FileName);

  // Import picture from Temp File
Item.Picture.IMPORT(FileName);
Item.Modify();
// Erase Temp File
FILE.ERASE(FileName);
END;

A couple of comments to the source:

  • The way we get a temporary filename in NAV2009 is by creating a temporary file, reading its name and closing it. CREATETEMPFILE will always create new GUID based temporary file names – and the Service Tier will not have access to write files in e.g. the C:\ root folder and a lot of other places.
  • The base64 automation object is loaded on the Service Tier (else it should be CREATE(base64, TRUE, TRUE);) and this is the right location, since the exported file we just stored is located on the Service Tier.
  • The base64.encodeFromFile reads the file and returns a very large string which is the picture base64 encoded.
  • The ADDTEXT method is capable of adding these very large strings and add them to a BigText (BTW – that will NOT work in the classic client).
  • We do the cleanup afterwards – environmental protection:-)

So, why does the ADDTEXT support large strings?

As you probably know, the ADDTEXT takes a TEXT and a position as parameter – and a TEXT doesn’t allow large strings, but what happens here is, that TEXT in C# becomes string – and the length-checking of TEXT variables are done when assigning variables or transferring parameters to functions and the ADDTEXT doesn’t check for any specific length (which comes in handy in our case).

The two lines in question in C# looks like:

base64.Create(DataError.ThrowError);
picture.Value = NavBigText.ALAddText(picture.Value, base64.InvokeMethod(@”encodeFromFile”, fileName));

Note also that the base64.decodeToFile function gets a BigText directly as parameter. As you will see, that function just takes an object as a parameter – and you can transfer whatever to that function (BigText, Text, Code etc.). You actually also could give the function a decimal variable in which case the function would throw an exception (str as string would return NULL).

So now you also know how to transfer large strings to and from COM objects:

  1. To the COM object, you just transfer a BigText variable directly to an object parameter and cast it to a string.
  2. From the COM object to add the string return value to a BigText using ADDTEXT.
  3. You cannot use BigText as parameter to a by-ref (VAR) parameter in COM.

In my WebService consumer project I use the following code to test my WebService:

// Initialize Service
CodeUnitPicture service = new CodeUnitPicture();
service.UseDefaultCredentials = true;

// Set the Image for Item 1100
service.SetItemPicture(“1100″, encodeFromFile(@”c:\MandalayBay.jpg”));

// Get and show the Image for Item 1001
string p = “”;
service.GetItemPicture(“1001″, ref p);
decodeToFile(p, @”c:\pic.jpg”);
System.Diagnostics.Process.Start(@”c:\pic.jpg”);

and BTW – the source code for the two functions in the base64 COM object are here:

public string encodeFromFile(string filename)
{
FileStream fs = File.OpenRead(filename);
BinaryReader br = new BinaryReader(fs);
int len = (int)fs.Length;
byte[] buffer = new byte[len];
br.Read(buffer, 0, len);
br.Close();
fs.Close();
return System.Convert.ToBase64String(buffer);
}

public void decodeToFile(object str, string filename)
{
FileStream fs = File.Create(filename);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(Convert.FromBase64String(str as string));
bw.Close();
fs.Close();
}

If you whish to download and try it out for yourself – you can download the sources here:

The two Visual Studio solutions can be downloaded from http://www.freddy.dk/VSDemo.zip (the base64 COM object and the VSDemo test project)

The NAV codeunit with the two functions above can be downloaded from http://www.freddy.dk/VSDemoObjects.fob.

Remember that after importing the CodeUnit you would have to expose it as a WebService in the WebService table:

image

And…. – remember to start the Web Service listener (if you are running with an unchanged Demo installation).

The code shown in this post comes with no warranty – and is only intended for showing how to do things. The code can be reused, changed and incorporated in any project without any further notice.

Comments or questions are welcome.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV