Show exactly number in formula bar when export excel

(0) Share

Report

Posted on by Long.Thai

130

Dear expert,

I'm facing an issue when exporting data into Excels. I created an EDT extends PERCENT and I set NoOfDecimals is 4. The data in database is shown 3.0543, but when I export into excel, its shown up as 3.0542977453438 in formula bar. I can get it shown exactly the same by go to Files > Options > Advanced > When calculating this workbook > Set precision as displayed. But I want it to be done in X++ not in Excel. Is it possible doing that in code?

Regards.

22743.Untitled.png

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (3)

Long.Thai 130 on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

Dear Zain Bokhari, Vilmos Kintera,

Thank you so much. My problem is now solved.

Regards.

Was this reply helpful? Yes No
Zain Bokhari 3,208 Moderator on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

Please mark all the helpful answers as verified if your issue was resolved, so our time and effort doesn't go in vain and others can also benefit from it, if not please share more details of your problem so it can be resolved. Thanks :)

Was this reply helpful? Yes No
Verified answer

Zain Bokhari 3,208 Moderator on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

and I would also suggest that you don't convert it to string.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

By default AX displays reals on 2 decimal places. Do not view your data in AX, just do the changes as we have suggested, try it, and see what you get back in Excel.

Was this reply helpful? Yes No
Verified answer

Zain Bokhari 3,208 Moderator on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

Again that is just what you see. For some reason the display value in ax print window will still only be 2 decimals if you print the results but if you export it with decRound(value,4) function it will actually give you a number with 4 decimals. Like Vilmos said include this in your logic for exporting the excel file.

I hope this resolves your issue.

Was this reply helpful? Yes No
Long.Thai 130 on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

Dear Vilmos Kintera,

Thank you for your explanation. I have tried the decRound(), but it didn't work. I think the decRound is only return 2 decimals.

I tried decRound(3.135454, 4) and it returned 3.14

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: Show exactly number in formula bar when export excel

This is an Excel feature for which you have no control from AX, unless there is a supported Excel trick/macro/whatsoever, which allows you to change the rendering of the function box.

I think you misunderstood what Zain said. The solution is to round the value in AX as suggested by him as well, before the data is prepared and handed over to Excel. decRound() is the function, you may store the output in a real. The figures above the decimal rounding point will simply be zeroed out, so you will not see the "extra characters" in the function box within Excel most likely, or they will be zeroes.

Take a look at the SysExcel* classes, where you may have control over the export logic.

Was this reply helpful? Yes No
Long.Thai 130 on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

Hi Zain Bokhari,

Its true. I wrote a class to export data into excel. Like you said, change the format only changes the displayed, not the real one.

I'm trying to convert it into string and insert into temp table then export to excel. Do you have any ideas, convert to string its only be rounded in 2 decimals, but I need 4.

Regards.

Was this reply helpful? Yes No
Zain Bokhari 3,208 Moderator on at

Like (1)

Report

RE: Show exactly number in formula bar when export excel

Like I said the value on the back end is always stored with all decimals, changing the format only changes the displayed value not the actual one.

Are you exporting the data to excel through code/X++ ?

Was this reply helpful? Yes No
Long.Thai 130 on at

Like (0)

Report

RE: Show exactly number in formula bar when export excel

Hi Zain Bokhari,

Thank you for quick reply. But maybe you misunderstood my problem.

My problem is the value shown in formula bar of Excel when I imported, its different from the data in database. I have set number format for excels is 0.0000. The value in cell is correct but in formula bar, it haven't rounded. The customer wants it to be exactly the same as value in cell.

DB: 3.0543 => Excel formula bar: 3.0542977453438. It supposed to be rounded like in DB, 3.0543. Is there a way to do it? By coding?

Was this reply helpful? Yes No