Rad Grid Export to Excel takes long time

[M Kumar]

Hi,
   We try to export date from rad grid to excel using following code(Rad Grid Export Code), if have small data no problem, but having 10000+ data getting too long like 20 to 30 minutes to wait. Here i share my code for export.

radgrdcount.ExportSettings.FileName = filename
radgrdcount.ExportSettings.IgnorePaging = True
radgrdcount.ExportSettings.ExportOnlyData = True
radgrdcount.ExportSettings.OpenInNewWindow = True
radgrdcount.MasterTableView.ExportToExcel()

Any way to speed up the process ? Please replay asap

Thanks

[Rumen]

Hi,

The built-in export functionality of the Grid is heavy since it tries to export data while respecting column styles. Having that in mind, when there are too many columns and rows, it could be a significant performance hit. To improve that, one option would be to reduce the number of columns or export only a certain number of rows.

An alternative approach is to use the Telerik Document Processing libraries to export the data source directly. We have an example that you can try to see how it works and RadGrid is there for visualizing the data: Export large amount of data to PDF, XLSX and CSV using the Telerik Document Processing libraries. As you can see in the article, it is better to get the raw DataSource instead of the Grid items and construct the Excel file manually. You will continue to use RadGrid for visualization and take advantage of the RadSpreadStreamProcessing library for the export of the datasource.

[M Kumar]

Hi,  Thank you for reply. i followed above step and can export to excel successfully, but after export , the format for number column changed to text format, so cant sum formula in that column, each time we need to change the column type from "Data" menu in order to change the column.
So how to avoid this ? 

Thanks

[Rumen]

Hi M Kumar,

Please refer to the following two RadSpreadStreamProcessing-related articles on the topic:

• Format Codes
• Number Formatting.

which will show you how to control the export formatting. 

If you have any RadSpreadStreamProcessing questions please post them in the dedicated support channel or forum for the Document Processing library.

[M Kumar]

Hi, Following code i used to generate excel file, in that how to pass General format for cells in order to export number as number format ?

                    For Each row As DataRow In dt.Rows
                        Using rowExporter As IRowExporter = worksheetExporter.CreateRowExporter()
                            For Each item In row.ItemArray
                                Dim normalFormat As SpreadCellFormat = New SpreadCellFormat()
                                normalFormat.FontSize = 10
                                normalFormat.VerticalAlignment = SpreadVerticalAlignment.Center
                                normalFormat.HorizontalAlignment = SpreadHorizontalAlignment.Center

                                Using cellExporter As ICellExporter = rowExporter.CreateCellExporter()
                                    cellExporter.SetValue(item.ToString())
                                    cellExporter.SetFormat(normalFormat)
                                End Using
                            Next
                        End Using
                    Next

Thanks

[Rumen]

You can check the following article: Culture Spescific Predefined Formats.

More examples are available at https://docs.telerik.com/devtools/document-processing/libraries/radspreadstreamprocessing/model/cells#set-a-format. 

[M Kumar]

Could you please explain in details how to pass General in SpreadCellFormat ?  or share some code with above my code, bcz i used above code to export to excel.
I tried to change code like this, but still same problem i am getting.

            For Each item In row.ItemArray

                                Dim normalFormat As SpreadCellFormat = New SpreadCellFormat() With {
    .NumberFormat = "General",
    .FontSize = 10,
    .VerticalAlignment = SpreadVerticalAlignment.Center,
    .HorizontalAlignment = SpreadHorizontalAlignment.Center
}


                                Using cellExporter As ICellExporter = rowExporter.CreateCellExporter()
                                    cellExporter.SetValue(item.ToString())
                                    cellExporter.SetFormat(normalFormat)
                                End Using
                            Next

[Yoan]

Hello M Kumar,

I tested the provided code snippet and managed to reproduce the mentioned behavior and locate its source. The NumberFormat property of the SpreadCellFormat affects cells with a number value in them. But when passing the values in the SetValue method, they are being converted to a string instead and that is the reason why the formatting is not being applied.

In order for the approach to work as expected, each item of each row should be parsed to a double if possible and then have a format set to it:

Using cellExporter As ICellExporter = rowExporter.CreateCellExporter()
   Try
       Dim number As Double = Convert.ToDouble(item)
       cellExporter.SetValue(number)
       cellExporter.SetFormat(numberFormat)
   Catch
       cellExporter.SetValue(item.ToString())
   End Try
End Using

Hope this helps. If there are any other questions or feedback you'd like to share, we'd be glad to hear you out.

Regards,

Yoan

[M Kumar]

Thank You so much @Yoan , it worked. Thank you @Rumen