Export data to Excel using ODBC in NAV 2013/2015

In the Nav Forum community.dynamics.com one member asked for usage of ODBC in Nav 2013, because it was used in the older version 3.6 for exporting data to Excel. So i researched a little bit and wrote some test code. Here is the result.

Odbc can be used in Nav 2013 (or higher) with the according .net classes.
Following sample code inserts 2 text values into an excel sheet.

– preparation of excel file
create a new excel file, for that sample with format excel 97-2003 xls.
save it into folder c:\temp, call it book1.xls.
the first sheet must have the name ‘Sheet1’.
write FName into cell A1, LName into cell B1.
save the excel file, close excel.

the prepared excel sheet:

– variables: set property runonclient of dotnet variables to yes
OdbcConnection : DotNet : System.Data.Odbc.OdbcConnection.‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’
Command : DotNet : System.Data.Odbc.OdbcCommand.‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’
Query : Text
CmdResult : Integer

– the sample code:
OdbcConnection := OdbcConnection.OdbcConnection;
OdbcConnection.ConnectionString := ‘Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\temp\book1.xls;ReadOnly=0’;
OdbcConnection.Open;
MESSAGE(FORMAT(OdbcConnection.State.ToString));

Query := ‘insert into [Sheet1$] (Fname,Lname) values (?,?)’;
Command := Command.OdbcCommand(Query, OdbcConnection);
Command.Parameters.AddWithValue(‘?’, ‘value 1’);
Command.Parameters.AddWithValue(‘?’, ‘value 2’);
CmdResult := Command.ExecuteNonQuery;
MESSAGE(‘Odbc Command Result: ‘ + FORMAT(CmdResult));

OdbcConnection.Close;
MESSAGE(FORMAT(OdbcConnection.State.ToString));
CLEAR(OdbcConnection);

result:

It seems that using Odbc is an interesting alternative to table Excel Buffer.

cheers

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s