Export (to Excel) and cell formatting

[Andrew]

This is possibly me missing something, or it is a suggestion for a feature in the Grid which would make things better in many cases.

The basic issue is that when a grid is exported to Excel, none of the column formats are observed, I have to set them all in code behind using the mso-number-format cell style.

From what I can discover, to get a grid formatted nicely in the resulting excel sheet, one has to write code for the ExcelExportCellFormatting event for the grid. In that code, the mso-number-format has to be set explicitly for each cell (except perhaps those with plain text). This involves a case statement switching on the UniqueName, setting the mso-number-format in the style. Effectively repeating all the formatting instructions already given in the declaration.

So my questions/suggestions are:

1. Would it not be possible to have an option to automatically covert the DataFormatString set in the declaration into an equialent mso-number-format. This would surely cover 90% of the cases where we want exactly the same format on the display and on the exported sheet - as an example, convert the DataFormatString="{0:C2}" into a e.Cell.Style["mso-number-format"] = @"\£\0022\#\,\#\#0\.00"
2. For cases where this is not quite right (maybe the auto convert does the wrong thing), provide a declarative atribute of the column (called say MsoNumberFormat) where we can declaratively set the format to be used on the export to excel - thus avoiding most (99% of) cases where additional code behind is required. If this attribute is set then of course it will overrride any automatic setting.
3. Of course there will be occasions where the code behind event handler is needed, so keep that as well to allow precise overriding of the above.
4. In the structures available in the event (ExcelExportCellFormatting) handler, I can't find the DataFormatString value (easily), so I have to write the big switch statement on each column by name - if I coudl refer to say e.FormattedColumn.DataFormatString I could do a smaller and generic switch statement for all my grids where I set the e.Cell.Style["mso-number-format"] based on the format string originally declared - basically if my format string is a 2 decimal place percentage I wil almost always want the same 2 decimal place percentage on my excel export.

 

I suggest all this because it seems relatively simple, and would make the Excel Export so much more "automatic" and hence the whole Telerk library so much more valuable.

At present the testing is tedious beyond belief - I just have to misspell one column name in the switch statement and unless I have carefully tested the resulting export column by column I will miss the error.

Andrew

[Daniel]

Hello Andrew,

Thank you for your suggestions.

1. We will consider this suggestion although I can't commit to an exact timeframe for conclusion. Currently we set the mso-number-format according to the DataType of the corresponding column.

2. We don't want to complicate the server API further - this would be specific property related to specific export format.

3. Of course we won't touch this event due to breaking change concerns.

4. It is easy to get the DataFormatString here - you just need to cast e.FormattedColumn to GridBoundColumn.

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Andrew]

Thanks for the response.