Exporting large result sets to Excel

[Rob]

Hi,

I have 2 questions about the built-in Export to Excel function on the RadGridView.

1. If you are displaying a paged result set, is there a way to export the entire result set from the same grid?  Right now, I have a display grid which is paged, and an export grid, which is invisible but non-paged.  I don't really feel like this is that viable of a solution, as I am already starting to see CPU and memory spikes in my performance monitoring.
   
   
2. Is there a way to use the Export function with a BackgroundWorker or other type of multi-threaded approach?  I haven't been able to find a way to get around making blocking call to RadGridView.Export() on the UI thread.  Ideally, I would like to update a progress bar or at least have the UI remain responsive while my export is happening. 

Thanks,

Rob

[Vlad]

Hi Rob,

 Unfortunately exporting cannot be done in a background thread in Silverlight (you can do this in WPF only) and unfortunately you cannot show progress since both operations will be in the UI thread. As to the paging you need to download all your data if you want the grid to export everything (you can check this demo for more info) or you can use Telerik Reporting instead to create the document on the server and just download the output. 

Greetings,
Vlad
the Telerik team

[Paul Grothe]

You say that you can perform this work on a background thread in WPF, do you have any examples or do you know how you would do this in the background in WPF?? I've tried it and get cross-thread exceptions...

[Vlad]

Hello,

The idea is to "clone" the original grid in the new thread. Here is an example:

private void Button_Click(object sender, RoutedEventArgs e)
{
    var dialog = new SaveFileDialog();
    dialog.DefaultExt = "xls";
    dialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", "xls", "Excel");
    dialog.FilterIndex = 1;

    if (dialog.ShowDialog() == true)
    {
        var columns = RadGridView1.Columns.OfType<GridViewDataColumn>();
        var context = RadGridView1.DataContext;
        var source = RadGridView1.ItemsSource;

        new Thread(() =>
            {
                using (var stream = dialog.OpenFile())
                {
                    var grid = new RadGridView()
                    {
                        DataContext = context,

                    };
                    grid.DataContext = context;
                    grid.ItemsSource = source;
                    grid.AutoGenerateColumns = false;

                    foreach (var column in columns)
                    {
                        grid.Columns.Add(new GridViewDataColumn()
                        {
                            DataMemberBinding = column.DataMemberBinding
                        });
                    }

                    var exportOptions = new GridViewExportOptions();
                    exportOptions.Format = ExportFormat.Html;
                    exportOptions.ShowColumnFooters = true;
                    exportOptions.ShowColumnHeaders = true;
                    exportOptions.ShowGroupFooters = true;

                    grid.Export(stream, exportOptions);
                }
            }) { ApartmentState = ApartmentState.STA }.Start();
    }
}

This however will work in WPF only! Best wishes,
Vlad
the Telerik team

[Paul Grothe]

Thanks, it works great, but it doesn't preserve the formatting of the original grid. All of the columns are smooshed together, widths aren't preserved. I'm using the grid.ToExcelML() method, is it preferred to use the .Export() method? What is the difference between the two?

Thanks!

[Paul Grothe]

Nevermind, I got the widths to come out correctly by storing the Binding and Width variables in a separate class and passing a List of that to the thread instead of the actual columns like you did.

I still don't know whether I should use the .Export or .ToExcelML though, which one is better??

[Vlad]

Hi,

Generally  ToXXX() methods are obsolete and will be removed. Please use Export() method instead!

Kind regards,
Vlad
the Telerik team

[Paul Grothe]

Okay, I switched, the results are identical.
It's too bad you can't do this on a separate thread in Silverlight, I plan on porting this or something like it to Silverlight some day; is this a Silverlight limitation or a limitation of the Telerik control? Are there any planned workarounds?

I've noticed many things causing the grid to be re-rendered on the UI thread such as sorting, filtering, grouping, and the initial render of course. If you have a large grid maximized, ala Excel, you see the UI constantly freezing as it re-renders with every manipulation of the data source. Are there any workarounds like the one you've come up with here for the Export function?

Thanks again.

[Vlad]

Hello,

 Unfortunately this is general limitation in Silverlight - maybe it will be improved in Silverlight 5. 

Generally the rendering should be always in the UI thread (you cannot measure, interact, etc. with UI components in different thread) however some of the data operations like sorting, grouping and filtering can be applied in the background and you can turn on our Asynchronous DataLoadMode to achieve this. 

Regards,
Vlad
the Telerik team

[Paul Grothe]

I'm using WPF, and DataLoadMode="Asynchronous" doesn't help at all??

[Pavel Pavlov]

Hello Paul Grothe,

I am afraid we can not push the Silverlight limitations further. Since the export  works over the visual tree we can not get away form the UI thread.

At this stage the only solution we can think of is a more radical approach - do the export manually over the data. I mean traverse the source collection and write the export file with some custom code.

Regards,
Pavel Pavlov
the Telerik team

[Paul Grothe]

I've opened a support ticket on this and mentioned the Catel project on Codeplex which can render a PleaseWaitWindow on a separate thread on top of the UI so when the UI is busy I can still show a "Please wait..." window at least. My response was:

"Hello Paul,

Thank you again for your recommendations.
We will research the case and consider its implementation.

Regards,
Maya
the Telerik team"

So this sounds promising, I think?