Excel Interoperability Woes in NAV 2013

Handling Excel files in NAV 2013 (including R2) is not as easy as it seems at first. The Excel Buffer table – an obvious choice of the old days – supports only Open XML format (*.xlsx), and Excel Interoperability implementation seems a bit buggy (check the comments in my old post about .NET interoperability: http://vjeko.com/blog/the-beauty-and-the-beast-nav-and-net). You can still use automation, but then you must handle everything, or at least most of it, on the client end. And, to make it all just a bit worse, none of these approaches, even when they work, will impress you with their speed. All in all, if you want to handle legacy Excel files (*.xls) you will have to make some compromises.

Last week, Mark asked me for a bit of help on a project where he had to import *.xls files into NAV 2013, and he hit all of the possible walls provided by the Excel Buffer, .NET Interoperability components for Excel, and automation walls possible.

When I gave Mark my solution, he said: you should blog about this. So, here I am, blogging about a simple way to read data from Excel, any version, lightning fast.

Let me start by explaining the problem first: all Excel Interoperability bugs (or headaches) come from a very simple fact – there is no native .NET interoperability component for Excel, or any other Office application for that matter. All of them are simply – the good old Automation (COM) components, wrapped into .NET through COM Interoperability feature of .NET. So, when talking to Excel through .NET, you are actually doing the same thing as if accessing it through Automation, except that you are doing that through an extra layer, which is all but lightweight.

If you want my advice – avoid it.

And now, to the solution. What most people are not aware is that you can treat Excel files as external databases. And in Windows world, external databases means that you can access them through native Windows database access components. There have been multiple names for these, as technologies shifted, so you may know these data access components as ODBC, OLEDB, or ADO. They are, of course, not nearly the same thing, but this does not matter for this post.

What matters is that you can use ADO.NET to connect to any Excel file, as if it were a database.

Yes, of course, it does have prerequisites – either you must install Excel on your machine, or you must have Microsoft Data Access Components, or Microsoft Access Database Engine, or whichever other database access pack provided by Microsoft includes it. If you have troubles googling (sorry, binging) it up, let me know and I’ll post some links here.

Once you have those, everything is a matter of connecting to the Excel file using the correct connection string to feed to the ADO.NET Connection object. The connection string does not always look the same, and it depends on whether you are using Open XML or legacy files, but in the end, it will look something like this:

[code]
Provider=Microsoft.ACE.OLEDB.15.0; data source=C:\pathtofile.xls; Extended Properties="Excel 8.0;HDR=YES"
[/code]

To look up the right connection string for your specific situation, take a look here: http://www.connectionstrings.com/excel/ (this site has most of the connection strings for most of known databases).

Once you have done that, use the ADO.NET to access the data in the file as if it were a table in a database. This piece of code shows how to do that:

[code]
Adapter := Adapter.OleDbDataAdapter('SELECT * FROM [Sheet 1$]','Provider=Microsoft.ACE.OLEDB.15.0; data source=C:\Temp\ExcelFile.xls; Extended Properties="Excel 8.0;HDR=YES"');
Table := Table.DataTable;
Adapter.Fill(Table);

Rows := Table.Rows;
EnumRows := Rows.GetEnumerator;
Columns := Table.Columns;

WHILE EnumRows.MoveNext DO BEGIN
Row := EnumRows.Current;
FOR i := 0 TO Columns.Count - 1 DO BEGIN
ValVar := Row.Item(Columns.Item(i).ColumnName);
// here you have the value
END;
END;

MESSAGE('Processed %1 rows with %2 columns',Rows.Count,Columns.Count);

[/code]

All of the types should be obvious once I tell you that they are from System.Data namespace (Adapter, Table, Rows, Columns, and Row). EnumRows is the IEnumerator. Again, if you need more help than this, comment, and I’ll put all the instructions here.

And that’s all there is. And it’s way faster than you can imagine. Go – try it, and let me know!

15 thoughts on “Excel Interoperability Woes in NAV 2013”

  1. This solution is good if you only need to import or export data from or to excel.
    If you ask me.. the real headache comes when you need to format your excel sheet in a specific way.
    For instance the customer has an excel template which you want to fill with data add some layout-functionality.
    IMHO the best solution here is to use .NET Interop in combination with ClosedXML (http://closedxml.codeplex.com/). Even with the limitions of .net interop in NAV… it works pretty well.

  2. To give you some more background: I had asked Mark to take over a few tasks, for me, and he had handed the performance part of this one to Vjeko. It’s good to have some friends in the channel :-).
    Vjeko’s solution, as described in his blog post, has taken the Excel import speed from “no go” to “no problem”. This was not about adding some speed to make a functionality more comfortable, but about whether a key business process can be made to work or not, since it was closely connected to processing a lot of data in a relatively short time frame.
    We need to get accustomed to looking into .NET integration for tasks like this.

  3. @wakestar

    Don’t forget that we can use JetReport to fill data to a Excel document with predefined layout.
    ClosedXML is also limited to the Open XML file type.

    Thank you Vjeko, Mark and Gary. Great solution.

  4. Hi Vjeko,

    can you give us an overview about used variables? We have tried to connect via ADO, but we dont get it work.

    Appreciate it.

  5. Name DataType Subtype Length
    OleDBAdapter DotNet System.Data.OleDb.OleDbDataAdapter.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
    OleDataTable DotNet System.Data.DataTable.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
    OleRows DotNet System.Data.DataRowCollection.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
    OleEnumRows DotNet System.Collections.IEnumerator.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
    OleColumns DotNet System.Data.DataColumnCollection.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
    OleRow DotNet System.Data.DataRow.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

    I have used this variables, but I have a problem with the provider and the “Microsoft.ACE.OLEDB.12.0″ connection string.

    I always get the error message, that the provide isn’t registered on client machine. I have installed all kind of access database connection tools of the web (on local client and NST). Does anyone have any suggesstions?

  6. microsoft ace oledb 12 provider is not registered on the local machine

    That is the error message at the moment :((((((((((

    1. @Robert: I am not quite sure I understand this question. What exactly are you trying to achieve? Is it: how to make it possible for the user to select a sheet from an existing Excel file, regardless of the Excel version, in a request page for a report? Or maybe I misunderstood it :(

  7. Hi Vjeko,

    yea of course. The user should select a sheet in the request page of a report, before importing the excel file . There is only one problem!

    The function SelectSheetName of the Excel Buffer dont work with xls files. So I’m actual searching for a way to get this run. Until now I don’t have found a way to get it work with nav 2013 r2 and a xls-file.

    Secondly I tested the code in this blog and it skips the first row of each excel file, so the import starts always in the second row of a excel file.

    best regars :)

Leave a Reply