export to excel problem in WPF : GridView

[Terje Johansen]

Hi,
I export to Excel and it work fine, but when one of the rows contains a xml value I get an error when opening up the exported file in Excel: 

"Problems during load:
Problems came up in the following areas during load:
Table - file cannot be opened because of errors."

The error log is like this:
XML PARSE ERROR:  Malformed or illegal processing instruction
  Error occurs at or below this element stack:
    <ss:Workbook>
     <ss:Worksheet>
      <ss:Table>
       <ss:Row>
        <ss:Cell>
         <ss:Data>

I guess Excel gets confused when there's two xml tags in the file?
I use 'xml' extension and ExportFormat.ExcelML

I read another thread about export where there was a problem regarding german umlaut letters.
The solution was to use encoding

byte[] buffer = Encoding.UTF8.GetBytes("<meta charset=\"utf-8\">");
stream.Write(buffer, 0, buffer.Length);

I there a similar way to fix this? to somehow format/encode the xml value so that the file can be exported.... 

[Vlad]

Hello,

 The grid export will not escape special characters by default. You can do this using ElementExporting event - get/set e.Value.

All the best,
Vlad
the Telerik team

[Mark]

Hey Vlad,

Wow - I just ran into this problem as well, and am surprised at this! 

So, as soon as our data might possibly have "<" ">" or any other special characters, we have to do the encoding ourselves?  Why is this not done against the cell data for us?  (Just curious as it seems pretty straightforward - and it happens when exporting to HTML.)

Thanks,
Mark.

[Andrew]

I also encountered this bug and was also surprised. Please modify the feature to "escape" the data into valid XML before exporting.

I ran the file through W3 validator and realized that all the & characters and < characters will causing problems, although only the < character prevented Excel from opening the doc.

Completely validated XML would be appreciated!

[Pavel Pavlov]

Hi,

Our latest versions actually support that. In order to tell RadGridView it needs to escape the values , you need to :

1. Handle the ElementExporting event
2. Inside the handler set: 
    e.ShouldEncodeValue = true;

This should solve the issue.

Greetings,
Pavel Pavlov
the Telerik team

[Andrew]

Thanks for the tip.

Myself and others expected this to be the default behavior when exporting in an XML format and to not have to write extra code. Can I highly recommend that this be changed?

[Vlad]

Hello,

 If we change it to true by default we will introduce breaking change. Maybe not all of our customers will be happy. 

Regards,
Vlad
the Telerik team

[Andrew]

For the record, from my perspective, it would be a bug fix and not a breaking change, and it appears that others share my perspective.

[John]

I agree with Andrew (and others here).

Currently you are allowing export (by default) of invalid XML when set to XML format. 

That also didn't seem to work.  We still had issues when opening in Excel 2010.

We used the following to get this to work:

/// <summary>
/// Event handler to properly escape XML characters in values
/// </summary>
/// <param name="sender">Grid</param>
/// <param name="e">element being exported</param>
static void ListGridElementExporting(object sender, GridViewElementExportingEventArgs e)
{
    if (e.Value is string)
    {
        e.Value = XmlEscapeSpecialCharacters((string)e.Value);
    }
}
/// <summary>
/// Generic method to escape special characters in Xml
/// </summary>
/// <param name="content">string to convert</param>
/// <returns>string with all special characters (&, <, >, ', ") with escape values</returns>
public static string XmlEscapeSpecialCharacters(string content)
{
    return content.Replace("&", "&").Replace("<", "<").Replace(">", ">").Replace("\"", """).Replace("'", "&apos;");
}

[Matus]

I totaly vote for fixing this. There are people not aware of this 'feature' of creating invalid excel exports by default (with the right data of course) and they are goind to be pretty surprised when a client calls them that their excel export is not working.

[Dimitrina]

Hi,

Thank you for sharing your feedback. We are currently working on improving the export experience to allow direct exporting to xlsx and pdf formats. It is going to be introduced with the upcoming Q1 2014.

You can also follow the RadGridView: Export Content as *.xlsx feature request in the feedback portal.

Regards,
Dimitrina
Telerik