Attached is a simple sample of the scenario you are facing.

We could not replicate the issue #1, Column value2 has a Decimal datatype with DataFormatString ""#,##0.##". The value and format in the WebGrid and exported excel will match.

Regarding issue #2, the issue will be replicated using \r\n as the new line, using <br/> will not replicae the issue. Currently, our exporting feature only supports <br/> as the new line indicator, I have forwarded the \r\n issue to our developer, to let the developer look into this issue.

Thank you for your answer.

Maybe I was little vague with issue #1. The data in the column represents phases and may consist of 3 parts (major, medium and minor phase).

For example:

01

01.20

01.100.03

...

100

100.40

100.40.120

In database data is saved as varchar(20), in dataset DataType is string and the corresponding WebGridColumn has also DataType string.

Everything is working fine in WebGrid, it can be sorted, grouped and filtered. It seems that Excel automatically recognizes numbers in the column and converts them from string. The issue won't happen when there are multiple phases in the cell divided with comma like (01.20.01, 01.20.30).

Regarding issue #2, if <br> tag solves the newline issue, does it mean that other "special" characters like ampersand should also be replaced with equivalent html tags?

In what event handler does this need to be done (InitalizeDataSource, InitializeRow or Export)?

Based on our developer response on the first issue, the numeric data will always be formatted as numeric even tough in the WebGrid we already format it as string. This is the default bahavior in Excel.

Regarding issue #2, the \r\n will create a new line in excel, so our workaround is using <br/> to indicate new line in the WebGrid. Other character, such as ampersand, will not conflict with excel so we will not need to replace it.

Franjo also asked:

Our custom aspx pages are embedded in Sharepoint. Users authenticate with their Active Directory login when they first visit it.
When users export data from WebGrid, another Internet Explorer is started and they need to authenticate again. Only when they successfully authenticate, they can choose to open or save the file.
How can this be disabled so the users don't need to authenticate?

We could not disable the authentication, however you could try using Windows Password Manager, accessible from Control Panel > User Accounts > Manage Your Network Password, to save the credential so when you visit the site you will not need to autheticate again