Intersoft Community - WebGrid Enterprise - Problems when exporting to Excel

Problems when exporting to Excel

Glayaar — Wed, 23 Sep 2009 23:33:01 GMT

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

Problems when exporting to Excel

Mladen.maras@combis.hr — Fri, 18 Sep 2009 04:47:37 GMT

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.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)?

Problems when exporting to Excel

Glayaar — Thu, 17 Sep 2009 23:42:57 GMT

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.

Problems when exporting to Excel

Mladen.maras@combis.hr — Thu, 17 Sep 2009 12:10:35 GMT

We have 2 problems when exporting data to Excel.

One of the columns in grid has text in format 60.60, but when data is exported to excel the text in cell is shown as 60.600.000 and the real value in cell is 6060000.
Another column in grid has multiline text in it. Foreach newline character in the text, in Excel another row is created with all the columns empty except the column that is related to grid column.

It seems that exporting to Excel ignores DataType property of both WebGridColumn and DataTable.

We are using latest WebUI Studio 2009, build 303.

How can this be solved and is there a way to tell Excel not to change values or type of column / cell?