This is a migrated thread and some comments may be shown as answers.

export to excel problem in WPF : GridView

10 Answers 363 Views

GridView

This is a migrated thread and some comments may be shown as answers.

Terje Johansen asked on 28 Sep 2011, 11:40 AM

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

10 Answers, 1 is accepted

answered on 11 May 2012, 01:29 AM

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("'", "'");

}