Excel formatting - currency & percentages

Hi fellas,

this is another one of those "RS to Excel formatting" questions :)

I have reports with a large number of columns containing either percentages or currency figures. These numbers show up in Excel with the General format - how can i get Excel to recognise them for what they are? I preferably want to keep the $ and % signs included, as it makes the report a lot easier to read if they are retained, so formatting the numbers as decimals etc. (as other answers have suggested) is probably a last resort.

As a related question, is it possible to write a custom filter or renderer that can be inserted between RS and Excel?

Thanks for any answers/ideas!

sluggy

I believe if you use the c and p format that Excel will render it correctly. c# for currency where the # is the decimals to carry. p# for percentage where the # is the number of decimals to carry. Another useful one is n# (general number format with commas).|||

Thanks for the answer Lonnie, but it doesn't work. Using the currency fields as an example, i have always used FormatCurrency() to format them, and changing this to Format(value, "c0") has no effect on the problem.

Cheers

sluggy

|||Use the Format field on the textbox, and put the "c0" in there. This way the renderer can apply the formatting; in Excel's case, it will translate the value of the Format field to an Excel format and apply it to the cell that the textbox appears in.|||

Geoff,

thanks, that was the answer. There was a subtlety in there that tripped me up, and i'm going to mention it because it could help someone else.

In the Value property of the textboxes in the report, i was using FormatPercent() to format the results of an expression, but the output of that function (and its sisters) is a string, which meant that when i used a format code in the Format property it was not being applied. The solution was to get rid of any formatting in the Value property, move the formatting to the Format property, and ensure that the Value field only evaluated to numeric values so that the formatting could be applied. It might sound simple, but i never saw anything mentioning this in any of the doco i read :)

Many thanks,

sluggy