Export to xlsx format issues

[Viksit]

I am using GridView's export functionality of 2016.1.217.45 version, see below the code snippet.

string extension = "xls";
          SaveFileDialog dialog = new SaveFileDialog()
          {
            DefaultExt = extension,
            Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "Excel"),
            FilterIndex = 1
          };
          if (dialog.ShowDialog() == true)
          {
            using (Stream stream = dialog.OpenFile())
            {
              m_oGridView.Export(stream,
               new GridViewExportOptions()
               {
                 Format = ExportFormat.Xlsx,
                 ShowColumnHeaders = true,
                 ShowColumnFooters = false,
                 ShowGroupFooters = false,
               });
            }
          }

 

Following are the issues observed, please provide solutions or workarounds:

1. The first row in the grid is not getting exported as second row in excel, instead it is appearing in the first row of excel after the row headers. See the snapshots - "GridUISnapshot.JPG" and "ExcelSnapshot.JPG".
2. If the users specifies the extension of the file as ".xlsx", it is getting exported but not able to open in excel 2016 version, the error message is shown in the snapshot - "XlsxExtensionOpenError.JPG".  Since in File save dialog, user can specify .xlsx extension as well. In code we are specifying the format to be exported is xlsx, but still it is not compatible.

Thanks,

Viksit

[Stefan Nenchev]

Hello ,

Thank you for the code implementation and the pictures provided.

There seems to be an issue in the way you have set the export mechanism at your end. I have tested your exact implementation and indeed the behavior you explain is observed. However, it seems like you have mixed the Export and ExportToXlsx methods. Have in mind that there is a separate ExportToXlsx method that you can use. Though the approach is explained in the documentation, I am here adding it for your reference:

private void btnExport_Click(object sender, RoutedEventArgs e)
{
    string extension = "xlsx";

    SaveFileDialog dialog = new SaveFileDialog()
    {
        DefaultExt = extension,
        Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, "Excel"),
        FilterIndex = 1
    };

    if (dialog.ShowDialog() == true)
    {
        using (Stream stream = dialog.OpenFile())
        {
            gridViewExport.ExportToXlsx(stream,
                new GridViewDocumentExportOptions()
                {
                    ShowColumnFooters = true,
                    ShowColumnHeaders = true,
                    ShowGroupFooters = true
                });
        }
    }
}

Bare in mind that this method requires some additional assembly references as well. You can find them at the beginning of the article.

We are aware that the information provided in the whole section for exporting RadGridView is to some extend confusing. We are currently in the process of updating it and it should be live on our documentation page soon.

Regards,
Stefan Nenchev
Telerik

[Flemming]

But this new ExportToXlsx removes doublet rows. I have tried all sort of settings, still same issue.

[Stefan]

Hi Flemming,

Can you please clarify whether you are populating RadGridView with duplicated items? Note, that such an approach is generally not recommended as the control is not meant to be used in such manner.

Best Regards,
Stefan X1
Progress Telerik

[Flemming]

That's exactly what I am doing. And the export with these settings:

using (Stream stream = dialog.OpenFile())
                {
                    dg_Data.Export(stream,
                     new GridViewExportOptions()
                     {
                         Items = rdp_Data.Source,
                         Format = ExportFormat.ExcelML,
                         ShowColumnHeaders = true,
                     });
                }

 

This works, most of the time. It always make you have to get past a popup when you open the file and on some computer it just can't open. That's why I would like to use the new way.

[Stefan]

Hi Flemming,

Though I am not completely sure why the popup is not displayed on some machines, I doubt that using the newer export mechanism would make any difference. The approach of using the two mechanisms would be basically the same. Also, as mentioned in my previous reply, populating RadGridView with duplicated data is not recommended.

Best Regards,
Stefan X1
Progress Telerik

[Flemming]

The old way, when it Works, gives you a popup that you have to say yes to. The new version seems to always work but has the duplicate problem. I think my solution must be the new version where I make sure I always have a unique field. Please fix that. Being able to handle duplicate fields should be a basic feature. At least give us the option :)

[Stefan]

Hi Flemming,

As stated in my previous reply, populating RadGridView with duplicated items is not recommended. Moreover, as the data engine of the control is not designed to be used in such manner, I am afraid that we cannot commit ourselves to modifying it. Such implementation would require major modifications of the core functionalities of the control and will eventually result in a breaking change. With this in mind, avoiding the usage of duplicate items is the only solution we can provide.

Best Regards,
Stefan X1
Progress Telerik

[Flemming]

Ok thank you.