Rad Grid Export to Excel takes long time

0 Answers 269 Views
Grid
M Kumar
Top achievements
Rank 1
Iron
Veteran
M Kumar asked on 03 Mar 2023, 06:33 AM | edited on 03 Mar 2023, 09:18 AM
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
Telerik team
commented on 03 Mar 2023, 09:48 AM

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
Top achievements
Rank 1
Iron
Veteran
commented on 07 Mar 2023, 07:59 AM | edited

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
Telerik team
commented on 07 Mar 2023, 12:27 PM | edited

Hi M Kumar,

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

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
Top achievements
Rank 1
Iron
Veteran
commented on 08 Mar 2023, 09:43 AM

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
Telerik team
commented on 09 Mar 2023, 03:21 PM | edited

M Kumar
Top achievements
Rank 1
Iron
Veteran
commented on 14 Mar 2023, 03:59 AM | edited

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
Telerik team
commented on 15 Mar 2023, 02:39 PM

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
Top achievements
Rank 1
Iron
Veteran
commented on 16 Mar 2023, 03:41 AM | edited

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

No answers yet. Maybe you can help?

Tags
Grid
Asked by
M Kumar
Top achievements
Rank 1
Iron
Veteran
Share this question
or