That's what format does. When you use format, SQL server formats numbers into Text, and when you use that with populatefromdatatable with useDataTypes, EXCELwill now treat that as a TEXT field. How about just using it as it is (with DataTypes) and then format it in EXCEL?
The users want the excel populated and format ready. If there is no other way then I will discuss alternatives with them. Thank you for confirming the behavior.
You could file an enhancement request for the ColumnFormat like AsCurrency (there is already one for AsPercentage).
I have submitted this enhancement on IdeaStream.
Currently when opening the same Table View we can get the formatting we want in a native Excel Table View (right-justified currency fields) but not in the OneStream Spreadsheet tool (left-justified Text field).
