In a Nav forum there was a question, how to read data from an excel document, change the data in Nav, write data back to the excel document without changing the existing cell formatting. With table ExcelBuffer and the helper assemblies delivered with Nav this cannot be done, because the Write functions e.g. SetCellValueText work with decorators.
So i developed assembly XlsLib. With that assembly read and write actions from an excel document are done very easy. The provided methods are very simple. You can read the cell values, the basic number formats and save it to table Excel Buffer. When writing only the value as is is written back to the cell. No further formatting is read or written.That can also be used, when you work with templates.
Main functions:
XlsWorksheet.GetValue(rowId, colId, value, type):
– Get a value from cell rowId/colId,
– given value is of type text,
– type is of datatype integer, can be 0-3 according option field NumberFormat in table ExcelBuffer, Type=4 means Unknown
XlsWorksheet.SetValue(rowId, colId, value):
– Sets a value in cell rowId/colId. value can be of any type.
Here is a sample code:
TestXlsLib() // variables // XlsWorkbook DotNet XlsLib.Workbook.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a' // XlsWorksheet DotNet XlsLib.Worksheet.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a' // value Text // type Integer // open excel workbook XlsWorkbook := XlsWorkbook.Workbook('c:\temp\test.xlsx'); // select sheet 'Sheet2' XlsWorksheet := XlsWorksheet.Worksheet(XlsWorkbook,'Sheet2'); // read cell values from row=1, column=1-3 XlsWorksheet.GetValue(1, 1, value, type); // e.g. 1234 XlsWorksheet.GetValue(1, 2, value, type); // e.g. 10.10.1990 XlsWorksheet.GetValue(1, 3, value, type); // e.g. 99.12 // Write some values XlsWorksheet.SetValue(1, 1, 5432); // an integer value again XlsWorksheet.SetValue(1, 2, 010175D); // again a date value XlsWorksheet.SetValue(1, 3, 34.45); // a decimal value // read values again to check changes XlsWorksheet.GetValue(1, 1, value, type); XlsWorksheet.GetValue(1, 2, value, type); XlsWorksheet.GetValue(1, 3, value, type); // Save: Save to loaded file filename //XlsWorkbook.Save(); // SaveAs: create a copy, save result to new file XlsWorkbook.SaveAs('c:\temp\test2.xlsx'); // Close Excel App !! XlsWorkbook.Close(); // if an error occurs ... IF XlsWorkbook.Error <> '' THEN MESSAGE(XlsWorkbook.Error); // release memory clear(XlsWorkbook);
You can download XlsLib here.
Hint: When downloading that file, it can be, that you have troubles using it because of security issues, e.g. dll is not visible in list of assemblies, you get an error message like “Cannot open xlslib, because one or more dependencies are not met”, methods are not visible or something else. then start windows explorer, goto folder were you copied the lib files, view file properties. if you see a notice at the bottom of the file properties window like “file is blocked because downloaded from internet, grant access?”, then click on “grant access”.
cheers
Hi,
I am trying to use the .dll which you have provided but I am getting this error .
I am using NAV 15.
Thanks
LikeLike
Hi Jonathan,
When I load this .dll into NAV (Version- 2015). I am getting this error
“Could not load type ‘XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a’.”
What could be the reason.
LikeLike
Did you copy the dll to the addins folder?
Did you set runonclient to yes?
Also check file properties. Maybe the file is blocked.
LikeLike
Yes, all these being set, but still I am getting same error.
LikeLike
Hi Jonathan,
Did you know the reason for above error
LikeLike
which excel version do you use ?
LikeLike
office 2013
LikeLike
i’ve added excel interop in zip file. please, download and try again. regards
LikeLike
Hello Jonathan,
I added it to the addins folder (client and server) and can see it in the development environment. But when I select XLSLIB then the methods, etc. remain empty.
Any idea?
LikeLike
did you set runonclient to yes, also check if the dll is blocked by windows file system (windows explore, select file, file properties, first tab general bottom area). regards
LikeLike
I saw the same behaviour becaus the files were installed on both:
C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client\Add-ins AND on
C:\Program Files\Microsoft Dynamics NAV\90\Service\Add-ins
I deleted the files in de first map, problem solved.
(Still in the testfase with this dll 🙂 )
LikeLike
Hi, when run function: error “reference not set to an instance of an object”…
My code is the same of yours…
HELP!
LikeLike