Incorrect number format in Excel

battoosai

Hello Everyone,

Here is my problem, I developed a report in Navision 2009 SP1 (Classic) and created an "export to excel" functionality using the built-in Excel Buffer table. The amount ( decimal type) fields is not displayed in the correct format.
i.e. Cust. Ledger Entry.Amount = 5,500.25 in the report. When exported to excel is displayed as 5500.25. I want to display it as 5,500.25 also so I added a parameter for the numberformat field: (here is my code)

FillCellNum(Row,5,FORMAT(ABS("Cust. Ledger Entry".Amount)),TRUE);

FillCellNum(intRowNo : Integer;intColNo : Integer;txtCellValue : Text[255];pBold : Boolean)
WITH recExcelBuffer DO BEGIN
INIT;
VALIDATE("Row No.", intRowNo);
VALIDATE("Column No.", intColNo);
"Cell Value as Text" := txtCellValue;
NumberFormat := '#,##0.00';
Bold := pBold;
INSERT;
END;

It still doesn't work. Btw, for some reports the format is correct.

I hope you can help me on this one. Thanks in advance.

vijay_g

Have a look at Format function. or search the forum.

battoosai

vijay_g wrote:

Have a look at Format function. or search the forum.

Hi Thanks for the response. I have already tried using different format types. In the report the format is correct however, when exported to excel the format changes. For some reports the number format is correct even though the coding is exactly the same.

When I tried to use numberformat = '@' which is 'Text' is excel, the format exported is correct but the cell is not considered as a number.

rsaritzky

battoosai wrote:

vijay_g wrote:

Have a look at Format function. or search the forum.

Hi Thanks for the response. I have already tried using different format types. In the report the format is correct however, when exported to excel the format changes. For some reports the number format is correct even though the coding is exactly the same.

If you have 2 reports where in one the format is correct and the other it is not, try looking at the ExcelBuffer table and see if there's any noticible difference. In order to view the ExcelBuffer table, you may have to do something like make the ExcelBuffer table not Temporary, or comment out some DELETEALL statements (depending on how you coded the reports).

Pay attention to the rows above the improperly formatted number (in the same column). I have seen issues with Excel where it "remembers" a format based on the previous rows.

Good Luck.

battoosai

rsaritzky wrote:

battoosai wrote:

vijay_g wrote:

Have a look at Format function. or search the forum.

Hi Thanks for the response. I have already tried using different format types. In the report the format is correct however, when exported to excel the format changes. For some reports the number format is correct even though the coding is exactly the same.

If you have 2 reports where in one the format is correct and the other it is not, try looking at the ExcelBuffer table and see if there's any noticible difference. In order to view the ExcelBuffer table, you may have to do something like make the ExcelBuffer table not Temporary, or comment out some DELETEALL statements (depending on how you coded the reports).

Pay attention to the rows above the improperly formatted number (in the same column). I have seen issues with Excel where it "remembers" a format based on the previous rows.

Good Luck.

Hi Ron,

Hmmmm....good point. There might me wrong values that was inserted in Excel buffer. Will try your suggestion. Thanks!