Integer Thousand for Decimal values in Excel Buffer NAV2013

Ravi_Prakash_Goyal

Hi,

I am upgrading my client from 4.0 to NAV 2013. I am facing issues to apply integer thousand i.e. US format to decimal values when exporting to excel.

When I exceute following statement in 4.0 and run report, it outputs decimal values in US format by default i.e. 32223.2 is exported as 32,223.2.

EnterCell(RowNo,ColNo,FORMAT(TotalAmounts),FALSE,FALSE,FALSE,'');

When I exceute following statement in NAV2013 and run report, it outputs decimal values without comma i.e. 32223.2. Please note I added last parameter for cell type 'Numeric' i.e. 0.

EnterCell(RowNo,ColNo,FORMAT(TotalAmounts),FALSE,FALSE,FALSE,'',0);

Should not system automatically export decimal values in US format (as defined in regional settings) as was in 4.0 version?

To resolve, I added number format string '#,##0.00' in above statement,

EnterCell(RowNo,ColNo,FORMAT(TotalAmounts),FALSE,FALSE,FALSE,'#,##0.00',0);

it gave me correct output except decimal places are fixed now to 2 decimals. I want it to be flexible. For e.g. 32223.212 should print as 32,223.212 and 32223.2 should print as 32,223.2 (not 32,223.20) and zeroes should print as 0 (not 0.00). Does anyone know how to achieve this in NAV 2013. In 4.0, it is working like this. Thanks.

Marije_Brummel

The string #,##0.00 is interpreted by Excel for the cell formatting. It is a general and well documented Excel feature.

Easiest is to make sure that you have a cell in Excel that is formatted the way you want, then look at the cell properties and copy this string back to NAV.

The excel buffer in NAV2013 is quite a nightmare. Rather than calling into Excel dotnet Interop directly Microsoft have decided to put a dll in between NAV and Excel. This limits developers in adding extra functionality to Excel. I have no clue why Microsoft have done this but it is highly anoying.

I would recommend investigating this technology instead. If I ever have time I will write a blogpost about this.

http://www.kauffmann.nl/blog/index.php/ ... t-interop/