Mark Brummel - Author of Microsoft Dynamics NAV 2009 Application Design

Mark Brummel - Author of Microsoft Dynamics NAV 2009 Application Design

Recent Posts

Tags

News

  • Published : Microsoft Dynamics NAV 2009 Application Design

Community

Email Notifications

Other Blogs

Archives

NAV2013 | Excel Buffer

The Excel Buffer table. Loved by many, unkown to most.

Here is the description on MSDN.

If you still don't have a clue what I'm talking about, then allow me to explain. Else skip this paragraph and continue to the end of this blog post.

Excel is the most used application to use in combination with ERP but it was not always in Dynamics NAV (Navision).

The interface to Excel that we know as the Excel Buffer table was intruced in the same timeframe as Automation Control using that technology to provide the interface.

Currently the interface looks like this

 

The reason I call it an interface rather than a table is because that's what it is. There is never data stored in the table on SQL Server. It is always used as a temporary table.

I've written small example application on Mibuso that demonstrates how this is used. With almost 7000 downloads it is by far the most popular one of my downloads on Mibuso. Probably because Google likes "Excel Buffer" and people download it who don't have a clue about NAV.

So to create an interface with NAV the excel buffer is your friend. It allowes both reading from and writing to excel which most of us know best from the Bugetting functionality in NAV. See : How to: Import or Export a Budget (MSDN).

And now what. Now we know what to use Excel buffer is for. But the Excel buffer has it's limmitations. Here are the ones I find missing:

  • Multiple Sheets
  • Font
  • Size
  • Backgroundcolor (preferably HTML coded)
  • Foregroundcolor
  • Hyperlinks

Now before NAV2013 you could easily add this functionality yourself. Each cell is represented by a value of the list in the interface (let's use dotnet terminology) and by adding members to the list which you then map to the properties of the cell.

Like this:

That was possible because NAV was using the Office Automation libraries directly. However in NAV2013 they are using a wrapper dll to accomodate Office Interop.

And the wrapper DLL simply does not have the functions.

 

And this has been recognised by both the channel and Microsoft as an issue.

Here is a link to a discussion on Mibuso

And  here are solutions provided by the NAV Team. (one and two)

But the workarounds by the NAV team are difficult and not as neat as we had in the old days.

Now there are three different ways you can handle this.

First one is to move back to Automation Control. Simply import the Excel Buffer from NAV2009 and you're done. But that won't work on the service tier and is dreadfully slow.

Second one if to implement ClosedXML as hinted by Arend Jan Kauffmann in his blog.

I can really recommend this solution. The ClosedXML is a wrapper around OpenXML (see humor here, laugh please) and basically provides the same interface we had with Automation Control to DotNet. It's extremely fast and very easy to work with.

It allowes you to use the colors and hyperlink parameters and using of extra sheets and tons and tons more.

What I did is renumber the excel buffer table to something outside the customer number range (it does not have to be in the license, it's an interface, not a table) and implement ClosedXML.

If enough comments are left on this blog I will make it available as download on Mibuso.

But the real solution would be to have the NAV team implement the things everyone creates on projects. I think my list is a good start.

Last week I was in Vedbaek and they asked me to provide a list of the things I need. Here it is. Do you have any other request? Please leave them as a comment of mail them to me.

 

Thanks for reading. 

 

Posted: 2013-6-17 20:38 by Marq | with 9 comment(s)
Bookmark and Share

Comments

Mark Brummel - Microsoft Dynamics NAV Blog said:

The Excel Buffer table. Loved by many, unkown to most. Here is the description on MSDN . If you still

# June 17, 2013 10:30 PM

Mark Brummel - Microsoft Dynamics NAV Blog said:

The Excel Buffer table. Loved by many, unkown to most. Here is the description on MSDN . If you still

# June 17, 2013 10:45 PM

Olli said:

Thanks for the summary of excel buffer hints. Another request is an available request page for a report used with the saveaspdf function. It's annoying that you loose this kind of user flexibility while saving reports as pdf with c/al code.

# June 18, 2013 8:56 AM

Andri Wianto said:

I just know that there is "Demo of Excel buffer usage" download in mibuso :P

Just download it, and start to learn it.

Thank you Mark!

# June 18, 2013 9:14 AM

Gunnar Þór Gestsson said:

Hi Mark

I used ClosedXML and have created lively Excel documents in NAV 2009.  There are two things that I came across.  

1. Cell Comments where not supported at that time.  Have not checked if that has changed.

2. It is easy to break the XML created with an invalid character in the value.

I create an helper class to wash invalid characters from the value, you can see it here -> http://www.dynamics.is/?p=706

I can send you all the NAV code I use if you like, just drop me a line.

# June 18, 2013 10:01 AM

Eric Bourg said:

I've implemented the borders.

Quite simple and very nice in terms of design...

# June 18, 2013 8:16 PM

Theo van de Ven said:

Hi Mark,

I recently had to build something in NAV2013 using the ExcelBuffer table to export some data to Excel files.

Normally you fill this table, create the Excel workbook, show it and give control to the user. It is then up to the user to decide if he will save the workbook and on what location.

What I needed to do is to generate several Excel files in a batch kind of way. For this I was searching for a function to save the created Excel file without having to give control to the user. So I created a function GfncSaveBook which takes a filename including path as a parameter to set the path and name of the file I want the Excel file to be saved as.

I don't know if it could be a useful function to be added as well. Did not have a look at the ClosedXML yet so maybe something similar is already there.

# June 20, 2013 12:51 AM

Mark Brummel - Microsoft Dynamics NAV Blog said:

The Excel Buffer table. Loved by many, unkown to most. Here is the description on MSDN . If you still

# June 28, 2013 9:21 PM

Daniele Rebussi said:

Hi Mark,

Were you able to find a way to dynamically setup the Excel file name in GiveUserControlDotNet function?

Inside it Magicpath is used to avoid the prompt but you could agree file names like "__TEMP__5376fa9c5f3a4f4085bd70084add889c1.xlsx" are not so user friendly :-)

I guess if there's a method to achieve that mainting the same file opening behavior.

Thanks

Daniele Rebussi

# December 6, 2013 10:16 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)