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

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

4 BAT files for the Client Tier

If you haven’t done so, please read the post about Multiple Service Tiers before reading this:-)

In my previous post (Multiple Service Tier) I promised to talk about what to do on the Client Tier if you have installed a number of services on a Service Tier computer that all are set to start manually.

Do you really have to go to the Service Tier machine in order to start the services or is there a simpler way?

As you might have guessed – there is (else you wouldn’t be reading this post)

The SC command can actually start services on a different box with the syntax:

SC \machine start servicename

Assuming that we are using the CreateService.bat to create our Service Tier we know what the service name is based on the instance name and we should be able to create 4 BAT files, which enables us to:

1. Start Service Tiers
2. Stop Service Tiers
3. Restart Service Tiers
4. Start the Role Tailored Client using a specific Service Tier (and start the Service Tier if necessary)

The four scripts really makes things easier when dealing with multiple Service Tiers – and here they go…

StartService.bat

@ECHO OFF
IF “%1” == “” GOTO usage
SETLOCAL
SET SERVICETIER=%2
IF NOT “%SERVICETIER%” == “” SET SERVICETIER=\%SERVICETIER%
SET NAVPATH=%~dp0
SC %SERVICETIER% query MicrosoftDynamicsNavServer$%1 > nul
IF ERRORLEVEL 1 GOTO :eof
SC %SERVICETIER% query MicrosoftDynamicsNavServer$%1 | FINDSTR “RUNNING”
IF NOT ERRORLEVEL 1 GOTO :eof
SC %SERVICETIER% start MicrosoftDynamicsNavServer$%1
CALL “%NAVPATH%SLEEP.BAT” 3
SC %SERVICETIER% start MicrosoftDynamicsNavWS$%1
CALL “%NAVPATH%SLEEP.BAT” 3
GOTO :eof
:usage
ECHO.
ECHO Usage:
ECHO.
ECHO startservice instancename [servicetier]
ECHO.

SETLOCAL means that the changes we do to environment variables here are not reflected outside this .BAT file.
The .BAT files checks whether the service exists and whether it already has been started – if that is the case, there is no real reason for starting it.
Note BTW that you will need the Sleep.bat file described in the Multiple Service Tiers post here as well.

StopService.bat

@ECHO OFF
IF “%1” == “” GOTO usage
SETLOCAL
SET SERVICETIER=%2
IF NOT “%SERVICETIER%” == “” SET SERVICETIER=\%SERVICETIER%
SET NAVPATH=%~dp0
SC %SERVICETIER% query MicrosoftDynamicsNavServer$%1 > nul
IF ERRORLEVEL 1 GOTO :eof
SC %SERVICETIER% query MicrosoftDynamicsNavServer$%1 | FINDSTR “STOPPED”
IF NOT ERRORLEVEL 1 GOTO :eof
SC %SERVICETIER% stop MicrosoftDynamicsNavWS$%1
CALL “%NAVPATH%SLEEP.BAT” 3
SC %SERVICETIER% stop MicrosoftDynamicsNavServer$%1
CALL “%NAVPATH%SLEEP.BAT” 3
GOTO :eof
:usage
ECHO.
ECHO Usage:
ECHO.
ECHO stopservice instancename [servicetier]
ECHO.

Kind of the same story as StartService.bat – if the Service is stopped, there is no real reason to try and stop it.

RestartService.bat

You probably guessed by now what this .BAT file is doing – so no reason in explaining.

@ECHO OFF
IF “%1” == “” GOTO usage
SETLOCAL
SET SERVICETIER=%2
IF NOT “%SERVICETIER%” == “” SET SERVICETIER=\%SERVICETIER%
SET NAVPATH=%~dp0
SC %SERVICETIER% query MicrosoftDynamicsNavServer$%1 > nul
IF ERRORLEVEL 1 GOTO :eof
SC %SERVICETIER% query MicrosoftDynamicsNavServer$%1 | FINDSTR “STOPPED”
IF NOT ERRORLEVEL 1 GOTO dontstop
SC %SERVICETIER% stop MicrosoftDynamicsNavWS$%1
CALL “%NAVPATH%SLEEP.BAT” 3
SC %SERVICETIER% stop MicrosoftDynamicsNavServer$%1
CALL “%NAVPATH%SLEEP.BAT” 3
:dontstop
SC %SERVICETIER% start MicrosoftDynamicsNavServer$%1
CALL “%NAVPATH%SLEEP.BAT” 3
SC %SERVICETIER% start MicrosoftDynamicsNavWS$%1
CALL “%NAVPATH%SLEEP.BAT” 3
GOTO :eof
:usage
ECHO.
ECHO Usage:
ECHO.
ECHO restartservice instancename [servicetier]
ECHO.

The .BAT file more or less just does a StopService followed by a StartService.

RTC.bat

Now this is the fun stuff – this .BAT file starts the Role Tailored Client connecting to a specific Service Tier – but first it starts the Service Tier in question if it wasn’t already started.

@ECHO OFF
IF “%1” == “” GOTO usage
SETLOCAL
SET COMPANY=%3
REM IF ‘%COMPANY%’ == ” SET COMPANY=”CRONUS International Ltd.”
IF ‘%COMPANY%’ == ” SET COMPANY=”CRONUS USA, Inc.”
ECHO.%COMPANY%
SET COMPANY=%COMPANY:”=%
SET COMPANY=%COMPANY:,=#%
:again
SET BEFORE=%COMPANY%
FOR /F “tokens=1* delims= ” %%A IN (‘ECHO.%COMPANY%’) DO (
IF NOT “%%B” == “” SET COMPANY=%%A%%20%%B
)
IF NOT “%BEFORE%” == “%COMPANY%” GOTO again
SET COMPANY=%COMPANY:#=,%
SET MACHINE=%2
SET SERVICETIER=%MACHINE%
IF “%SERVICETIER%” == “” SET SERVICETIER=localhost
CALL STARTSERVICE.BAT %1 %SERVICETIER%
START dynamicsnav://%SERVICETIER%/%1/%COMPANY%/
GOTO :eof
:usage
ECHO.
ECHO Usage:
ECHO.
ECHO RTC instancename [servicetier] [“Company”]
ECHO.

As you can see in the usage section, RTC can be started specifying the Service Tier instance name, the Service Tier machine and the Company name.

Launching a Role Tailored Client pointing to the default installed Service Tier would be:

C:\Prog…60>RTC DynamicsNAV localhost “CRONUS International Ltd.”

or just

C:\Prog…60>RTC DynamicsNAV

because localhost and CRONUS are the default values for the 2nd and 3rd parameters. No real reason for that except for the fact that this has been working for me.

Note, that the FOR loop in the .BAT file replaces spaces in the COMPANY variable with %20. Another thing to notice is, that I replace commas with # before the loop (since commas will change the loop) and replace them back afterwards, meaning that you cannot have # in your Company names. You also cannot have ” in your company name.

If you have other special characters or if you do have # in your company names you would have to change the name mangling on the COMPANY name.

I use this .BAT file for a number of shortcuts on my desktop to launch Role Tailored Clients to specific Service Tiers and on my Service Tier box I run a command every night shutting down all my service Tiers:

for /f %D in (‘dir /ad/b’) do ( CALL STOPSERVICE.BAT %D )

The .BAT files can of course also be used on the Service Tier (except for the RTC one) for stopping and starting services – but they are a big help on the Client Tier – the way I am running things at least.

Once again – the people who has read all the way to the end will be able to download a ZIP file containing the .BAT files from http://www.freddy.dk/4ForTheClientTier.zip this ZIP file also contains a .BAT file called StopServices.bat (which actually only runs on the Service Tier = Stops all Running NAV Service Tiers).

I promise this is my last post with .BAT files:-)

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV