Grid export data takes a long time

[Manolo]

Hi,

I load data with a lot of records (3k, 4k), so I use a webMethod for do it.

But, I want export to excel, and it cost a long time, in the example over 3 or 4 minutes, but with 4k records, it take over 20 minutes.... too much.

How can I reduce this time?

I attach an example for test

Attachment: WebApplicationExport_836eb9aa.zip

[Balaji Marimuthu]

Hi Manolo,

Thanks for contacting the Syncfusion support.

Since the grid export has performed through tabular method it took ~4min to export. Hence we have tested the grid data export using the “XlsIO - Improving performance and memory while importing data” feature of the XlslO and it took nearly 15sec to export 1k records and tested your sample using the below code snippet.  So we suggest you to use the below code example to reduce the time for Export the Grid data.

Refer to the modified sample in following link: Sample

protected void gridDataReport2_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

        {

          

            var db = new Model1();

            var datos = db.InformeDiarioGasDetalles.ToList<InformeDiarioGasDetalles>();

            ExcelExport exp = new ExcelExport();

            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            IWorkbook workbook = application.Workbooks.Create();

            workbook.Worksheets[0].ImportData((IEnumerable)datos, 1, 1, true);

            workbook.Version = ExcelVersion.Excel2013;

            workbook.SaveAs("Export.xlsx", HttpContext.Current.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010);

           

        }

Please refer the below link for more details on using the above improved performance exporting.

http://help.syncfusion.com/file-formats/xlsio/improving-performance#importing-datatable

The limitation on using the above technique.

   1. Styles cannot be applied


Regards,
Balaji Marimuthu

[Manolo]

ok! thank you very much!

Maybe, in the future, styles and grouping can be applied in large data?

[Balaji Marimuthu]

Hi Manolo,


Thanks for the update.

Query:1 Grouping with large Data

We have considered the requirement “Improve performance of Grid Excel Export when Grouping with large data” as a feature and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates. 

https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents 
Query:2 Styling with large Data

If apply styling for large data then need to process all the cells to apply style while exporting and it will reduce the performance of Exporting. So we suggest you to use the XLSIO style properties and apply the style property to differentiate the Header cell. Refer to the Sample, code example, and help document as follows,
Sample: WebApplication1

Help Document: http://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting#apply-global-style

protected void gridDataReport2_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

        {

          

            var db = new Model1();

            var datos = db.InformeDiarioGasDetalles.ToList<InformeDiarioGasDetalles>();

            ExcelExport exp = new ExcelExport();

            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            IWorkbook workbook = application.Workbooks.Create();

            IWorksheet worksheet = workbook.Worksheets[0];

            //IListObject table = worksheet.ListObjects.Create("Table1", worksheet ["A1:C8"]);

            //// Formatting table with a built-in style

            //table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium9;

            workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));

            //Defining header style

            IStyle headerStyle = workbook.Styles.Add("HeaderStyle");

            headerStyle.BeginUpdate();

            headerStyle.Color = Color.FromArgb(255, 174, 33);

            headerStyle.Font.Bold = true;

            headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;

            headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;

            headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;

            headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;

            headerStyle.EndUpdate();

            //Add custom colors to the palette.

            workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));

           

            workbook.Worksheets[0].ImportData((IEnumerable)datos, 1, 1, true);

            workbook.Version = ExcelVersion.Excel2013;

            //Apply Header style.

            worksheet.Rows[0].CellStyle = headerStyle;

            //Auto-fit the columns

            worksheet.UsedRange.AutofitColumns();

        

            workbook.SaveAs("Export.xlsx", HttpContext.Current.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010);

            //exp.Export(gridDataReport2.Model, (IEnumerable)datos, "Details.xlsx", ExcelVersion.Excel2010, true, true, "none");
        }

Regards,
Balaji Marimuth

[Manolo]

Ok, thank you

[Balaji Marimuthu]

Hi Manolo,

Thanks for the update.

We are happy that the provided solution helped you. Please get back to us if you need any further assistance.

Regards,
Balaji Marimuthu