export to excel problem in WPF : GridView

11 posts, 0 answers
  1. Terje Johansen
    Terje Johansen avatar
    28 posts
    Member since:
    Mar 2010

    Posted 28 Sep 2011 Link to this post

    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.... 
  2. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 30 Sep 2011 Link to this post

    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

    Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get it now >>

  3. UI for WPF is Visual Studio 2017 Ready
  4. Mark
    Mark avatar
    44 posts
    Member since:
    Jun 2009

    Posted 07 Dec 2011 Link to this post

    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.
  5. Andrew
    Andrew avatar
    5 posts
    Member since:
    Jul 2012

    Posted 06 Apr 2012 Link to this post

    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!
  6. Pavel Pavlov
    Admin
    Pavel Pavlov avatar
    2039 posts

    Posted 07 Apr 2012 Link to this post

    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

    Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get it now >>

  7. Andrew
    Andrew avatar
    5 posts
    Member since:
    Jul 2012

    Posted 09 Apr 2012 Link to this post

    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?
  8. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 09 Apr 2012 Link to this post

    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

    Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get it now >>

  9. Andrew
    Andrew avatar
    5 posts
    Member since:
    Jul 2012

    Posted 09 Apr 2012 Link to this post

    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.
  10. John
    John avatar
    1 posts
    Member since:
    Jan 2012

    Posted 10 May 2012 Link to this post

    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;");
    }
  11. Matus
    Matus avatar
    5 posts
    Member since:
    Sep 2014

    Posted 11 Feb 2015 Link to this post

    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.
  12. Dimitrina
    Admin
    Dimitrina avatar
    3769 posts

    Posted 12 Feb 2015 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
Back to Top
UI for WPF is Visual Studio 2017 Ready