Performance of excel export

[Nathalie Kreis]

Hello Syncfusion-Team,

I am actually trying to export a SfDataGrid to a xls-file. The exporting is very slow and therefore my visualization is hanging for the time of the export. Are there any ways to increase the performance of exporting?

To get an increase in performance I am trying to outsource the exporting in a new STA-thread. In this thread I am creating a new SfDataGrid because the SfDataGrid which I want to export is stacked in the Mainthread. Therefore I'm giving the newly created datagrid all the informations in code behind. But my problem is, that the option "View" isn't filled automatically by setting the ItemsSource in code behind as it is descriped on your documentation. The "View" is null and therefore the exported xls-file is empty. How can I solve this? Below is a little code snippet:

            if (getType.Name == "SfDataGrid")

            {

                SfDataGrid copyGrid = new SfDataGrid();

                copyGrid.DataContext = this;

                copyGrid.ItemsSource = (copyGrid.DataContext as ReportViewerAdapter).DatabaseView;

                copyGrid.Height = 650;

                copyGrid.AutoGenerateColumns = false;

                copyGrid.AutoGenerateRelations = false;

                copyGrid.RowHeight = 20;

                var columns = GetProperties(producedSkids[0]);

                foreach (var column in columns)

                {

                    if (column.PropertyType == typeof(string) || column.PropertyType == typeof(double) || column.PropertyType == typeof(long))

                    {

                        copyGrid.Columns.Add(new GridTextColumn() { HeaderText = column.Name, MappingName = column.Name, TextAlignment = TextAlignment.Center, Width = 200 });

                    }

                    else if (column.PropertyType == typeof(DateTime))                        

                    {

                        DateTimeFormatInfo info = new DateTimeFormatInfo();

                        info.LongDatePattern = "dd-MM-yyyy HH:mm:ss";

                        copyGrid.Columns.Add(new GridDateTimeColumn() { HeaderText = column.Name, MappingName = column.Name, DateTimeFormat = info, TextAlignment = TextAlignment.Center, Width = 200 });

                    }

                    else if (column.Name == "Robots")

                    {

                        GridViewDefinition item = new GridViewDefinition();

                        item.RelationalColumn = column.Name;

                        item.DataGrid.AutoGenerateColumns = false;

                        item.DataGrid.Columns.Add(new GridTextColumn() { HeaderText = "Index", MappingName = "lwVar01", TextAlignment = TextAlignment.Center, Width = 120});

                        item.DataGrid.Columns.Add(new GridTextColumn() { HeaderText = "iIndex", MappingName = "lwVar02", TextAlignment = TextAlignment.Center, Width = 120 });

                        item.DataGrid.Columns.Add(new GridTextColumn() { HeaderText = "Roboter", MappingName = "sVar01", TextAlignment = TextAlignment.Center, Width = 120 });

                        item.DataGrid.Columns.Add(new GridTextColumn() { HeaderText = "Farbnummer", MappingName = "sVar02", TextAlignment = TextAlignment.Center, Width = 120 });

                        item.DataGrid.Columns.Add(new GridNumericColumn() { HeaderText = "Lackmenge [ML]", MappingName = "fVar12", TextAlignment = TextAlignment.Center, Width = 120 });

                        copyGrid.DetailsViewDefinition.Add(item);

                    }

                }

                var options = new ExcelExportingOptions();

                options.ExcelVersion = ExcelVersion.Excel2016;

                var excelEngine = copyGrid.ExportToExcel(copyGrid.View, options);

                var workBook = excelEngine.Excel.Workbooks[0];

                workBook.SaveAs("D:\\IPC\\TFS\\AB15021\\Report\\" + name + "_" + SelectedStartDate.ToString("yyyyMMdd") + "_" + DateTime.Now.ToString("yyyyMMdd") + "_" + DateTime.Now.ToString("HHmmss") + ".xls");

            }

Thank you in advance.

Kind regards,

Nathalie

[Gnanasownthari Thirugnanam]

Hi Nathalie, 

 

Excel exporting for the Grid will take considerable amount of time based on records and columns count, We have some feasibility to improve the performance and will include the fix for that in our 2018 Volume 1 Service Pack 1 release which will be expected to be available in March, 2018. In the meantime, could you please share your records count and view model details which you have used. Also we are unable to export the DataGrid control to excel without creating a View for Grid. We could recommend that, you can show the progress bar for the exporting to know the process in the application. Please find the sample for the same from the below link.

Sample link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/SfDataGridDemo244690945 

 

Regards, 

Gnanasownthari T. 

[Urja Patel]

Hello Syncfusion-Team,

As per stated above "We could recommend that, you can show the progress bar for the exporting to know the process in the application". 

Progress Bar shows its final status on UI when ExportToExcel Task completes its process and not while ExportToExcel Task is executing, it is still freezing UI on the Main Thread.

As given in sample code, progressBar value gets updated from CellExportingHandler at the same time progress should be updated on UI, but that progress does not get updated on UI.

As I am facing similar issue while Exporting Grid to Excel as well as PDF.
Please provide a sample which does not block Main Thread, as well as should show actual updated progress when ExportToExcel Task is executing.  
Please provide solution for this. 

Thank you,
Urja.

[Adhikesevan Kothandaraman]

Hi Urja, 

Thanks for your update. 

We have analyzed your reported scenario at our end. If you want to run the exporting without blocking the UI thread, you can use the busy Indicator to show the progress. 

Refer to the following code snippet and sample. 

Code Snippet: 

private async void ExcelExporting_Click(object sender, RoutedEventArgs e) 

{ 

    VisualStateManager.GoToState(this.copyGrid, "Busy", true); 

    await this.copyGrid.Dispatcher.BeginInvoke(new Action(() => 

    { 

        ExcelEngine excelEngine = new ExcelEngine(); 

        var options = new ExcelExportingOptions(); 

        options.ExcelVersion = ExcelVersion.Excel2013; 

 

        excelEngine = copyGrid.ExportToExcel(copyGrid.View, options); 

 

        var workBook = excelEngine.Excel.Workbooks[0]; 

        VisualStateManager.GoToState(this.copyGrid, "Normal", true); 

        SaveFileDialog sfd = new SaveFileDialog 

        { 

            FilterIndex = 2, 

            Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx" 

        }; 

 

        if (sfd.ShowDialog() == true) 

        { 

            using (Stream stream = sfd.OpenFile()) 

            { 

 

                if (sfd.FilterIndex == 1) 

                    workBook.Version = ExcelVersion.Excel97to2003; 

 

                else if (sfd.FilterIndex == 2) 

                    workBook.Version = ExcelVersion.Excel2010; 

 

                else 

                    workBook.Version = ExcelVersion.Excel2013; 

                workBook.SaveAs(stream); 

            } 

 

            //Message box confirmation to view the created workbook. 

 

            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", 

                                MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) 

            { 

 

                //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] 

                System.Diagnostics.Process.Start(sfd.FileName); 

            } 

        } 

    }), System.Windows.Threading.DispatcherPriority.Background); 

}      

 

Sample: 

https://www.syncfusion.com/downloads/support/forum/135970/ze/SfDataGridDemo-557993147  

Regards, 

Adhi