Decimal separator in excel.

Visi_V

Hello Everyone.

I got a question about decimal separator in excel.

I want to make my decimal look like this 1.234,56 from this 1234,56. in my excel document.
It should do it when i run the report 82(Export to excel), from G/L budget.
My navision version is 2015 and location is DK.

Where should I make the chance??? I have done nearly everything, like format it, but it won't change it.

Jan_Veenendaal

Since this export is using the Excel Buffer, the FORMAT will not work.
I also guess that you are not ready to change your regional settings (anyway: that will only work for your workstation, not for other people opening the Excel sheet)

I suggest modifying report 82. [ I did this in the W1 , but I am pretty sure the DK version is the same ]
There is one line in the report code that says:

FALSE,'',ExcelBuf."Cell Type"::Number);

Replace this line with:

FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);

What happens here is that you enter a specific formatting IN EXCEL for the cell containing the amount - and that formatting will work on any workstation where the worksheet is opened.

PS it might be that in your case you would need the comma and the dot just the other way round, then it would be:

FALSE,'#.##0,00',ExcelBuf."Cell Type"::Number);

Sowkarthika

Hi,

You have to use FORMAT function to get the decimal in your required format . You have to write FORMAT function while passing data to excel. refer the link https://dynamicsuser.net/nav/f/developers/34821/how-to-generate-decimal-in-txt-in-2-decimal-places

zaidtariq

Hi @Visi_V
There could be Multiple soultion like you may change you region settings or you may define desired format in FORMAT command in C/AL to export data to excel.

zohaibu95@hotmail.com

@Visi_V if you want to change decimal separate from ',' to '.' then all you need is to change the regional settings on your pc/server.

To do this, click on your clock and go to Date and time settings.
Select region and lanaguage and click on Additional date,time & regional settings.
Click on change date time and number format
Click on Additional settings
Change Decimal symbol to '.' or whatever you want.

Now export and it will work like a charm!

Jan_Veenendaal

Since this export is using the Excel Buffer, the FORMAT will not work.
I also guess that you are not ready to change your regional settings (anyway: that will only work for your workstation, not for other people opening the Excel sheet)

I suggest modifying report 82. [ I did this in the W1 , but I am pretty sure the DK version is the same ]
There is one line in the report code that says:

FALSE,'',ExcelBuf."Cell Type"::Number);

Replace this line with:

FALSE,'#,##0.00',ExcelBuf."Cell Type"::Number);

What happens here is that you enter a specific formatting IN EXCEL for the cell containing the amount - and that formatting will work on any workstation where the worksheet is opened.

PS it might be that in your case you would need the comma and the dot just the other way round, then it would be:

FALSE,'#.##0,00',ExcelBuf."Cell Type"::Number);

vaprog

Hi,

Is there any useful documentation on the methods of Microsoft.Dynamics.Nav.OpenXml (the assembly Excel Buffer uses to generate the worksheet)?

My report 82 already uses the format string '#,##0.00', which causes the cell in Excel to be of type Zahl (Number) and showing numbers in Excel's default number format like 1.160,00 (note the usage of comma and dot according to German language setting, contradicting the given separators in the format string!). I would expect excel using a custom format with a custom format string given.

If I change the format in the report to '' (empty string), the number is formatted as Standard (default), not showing any trailing 0-decimal digits, nor a thousands-separator.

My RTC client language is set to German, if that matters, as is my Windows'.

rmv_RU

Use following function to convert delimiter.

ConvertDelim(lExpression : Text[250]) : Text[250]
lText := FORMAT(1.1);
delim := COPYSTR(lText, 2, 1);
lExpression := CONVERTSTR(lExpression, ',', FORMAT(delim));
lExpression := CONVERTSTR(lExpression, '.', FORMAT(delim));
EXIT(lExpression);

Visi_V

Thanks for the feedback