We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Problems when exporting many rows to excel.

Hi,

I have a grid in an Angular 6 application and I need to export all the rows to Excel (around 60000).
When I click on Export to Excel, the screen is blocked.
I have tried the same grid but with fewer rows (1000) and it works well.
Is there any limit to export to Excel?

Greetings.

5 Replies

MS Madhu Sudhanan P Syncfusion Team February 22, 2019 05:38 AM UTC

Hi Juan, 

Greeting from syncfusion. 

By default, Excel exporting works based on the string manipulation. While using large amount of data( Ex: 60K rows and  4 columns then total cells count as 2,40,000 ) in Grid then it will take considerable time to export the excel document in Grid. 

Regards, 
Madhu Sudhanan P 



JU Juan February 22, 2019 06:41 AM UTC

Thanks for your quick response, but the process is too slow.
And is it possible to send all the contents of the grid to the backend so that it generates Excel with XlsIO?
Or just send the filtered columns and column headers to the backend in case they have filtered or moved columns?
Thank you.


MS Madhu Sudhanan P Syncfusion Team February 26, 2019 11:42 AM UTC

Hi Juan, 

Thanks for the update. 

You can achieve this requirement like as following code snippet in which exported the EJ2 Grid at the server side using Syncfusion XLSIO and exported only the filtered records, 

App.component.html 
 
<button (click)="onClickMe()" ejs-button [isPrimary]="true">Export to Excel</button> 
<ejs-grid #grid [dataSource]='data' allowFiltering='true' [allowPaging]="true" > 
    <e-columns> 
        <e-column field='OrderID' headerText='Order ID' textAlign='Right' width=90></e-column> 
        <e-column field='CustomerID' headerText='Customer ID' width=120></e-column> 
        <e-column field='Freight' headerText='Freight' format='C2' width=90></e-column> 
        <e-column field='ShipCity' headerText='Ship City' width=120></e-column> 
    </e-columns>   
</ejs-grid> 
 
<form action="/Home/ExcelExport" (ngSubmit)="onSubmit()" method="POST"> 
    <input name="GridModel" hidden id="btn" /> 
</form> 
 
App.component.ts 
 
export class AppComponent { 
  title = 'EJ2Grid'; 
  public data: Object[]; 
  @ViewChild('grid') 
  public gridInstance : GridComponent ;   
 
  ngOnInit(): void { 
    this.data = data; 
  } 
 
  onClickMe(e){ 
    var query = this.gridInstance.getDataModule().generateQuery(true); 
    var queries = new UrlAdaptor().processQuery(new DataManager({ url: '' }), query); 
    var columns = JSON.stringify({ columns: this.gridInstance.columns, queries: JSON.parse((queries as any).data) }); 
 
    (document.getElementById("btn") as any).value = columns; 
    var form = document.getElementsByTagName("form")[0]; 
    form.submit(); 
  } 
} 
 
Controller: 
 
using Syncfusion.EJ2.Base; 
using Syncfusion.XlsIO; 
using System; 
using System.Collections; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.Mvc; 
using Syncfusion.EJ2.Grids; 
using Newtonsoft.Json; 
 
namespace EJ2Grid.Controllers 
{ 
    public class ExportModel // Created the model class to Deserialize the GridModel 
    { 
        public List<GridColumns> Columns { get; set; } 
        public DataManagerRequest Queries { get; set; } 
    } 
 
    public class HomeController : Controller 
    { 
        public static List<Orders> order = new List<Orders>();  
         
        public void ExcelExport(string GridModel) 
        { 
            ExportModel exportModel = new ExportModel(); 
            exportModel = (ExportModel)JsonConvert.DeserializeObject(GridModel, typeof(ExportModel));  // Deserialized the GridModel 
            using (ExcelEngine excelEngine = new ExcelEngine()) 
            { 
                IApplication application = excelEngine.Excel; 
                application.DefaultVersion = ExcelVersion.Excel2013; 
                IWorkbook workbook = application.Workbooks.Create(1); 
                IWorksheet worksheet = workbook.Worksheets[0]; 
                IEnumerable DataSource = order; 
 
                if (exportModel.Queries.Where != null) 
                { 
                    DataOperations operation = new DataOperations(); 
                    if (exportModel.Queries.Where != null && exportModel.Queries.Where.Count > 0) //Filtering 
                    { 
                        DataSource = operation.PerformFiltering(DataSource, exportModel.Queries.Where, exportModel.Queries.Where[0].Operator); // Filtered the export datasource based the filter query 
                    } 
                } 
 
                //Import the data to worksheet 
                IList<Orders> reports = DataSource.AsQueryable().Cast<Orders>().ToList(); 
                worksheet.ImportData(reports, 2, 1, true); 
                workbook.SaveAs("Excel.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.Open); 
            } 
        } 
   } 
} 

In this code, we have used form submit action to send the Grid properties to the server side as JSON string. After receiving this properties in server side, we will Deserialized this records. We have created the model class (i.e ExportModel) to deserialize this grid model. We can get the column details and filter query through this deserialized model. After that we have used this filter query to filter the datasource and finally we have provided this filtered datasource to the exporting file through the importData method. 

You must need to refer the following assemblies to use Syncfusion XLSIO in your project, 

Syncfusion.XlsIO.Base
Syncfusion.Compression.Base 
 
You can get this assemblies to your project by installing the following nuget package, 

Syncfusion.XlsIo.AspNet.Mvc5 

Refer the below links to know more details about required assemblies to use XLSIO, 



Refer the below link to know about Syncfusion XLSIO, 




Regards, 
Madhu Sudhanan P 



HH Hiram Hernandez August 31, 2022 04:36 PM UTC

I have a grid, that the export works perfectly when it has a few rows, 1000 or 2000 rows the export works perfectly. But when the grid has a lot of rows 50 000 for example, All the screen frozes, and after several time the file is downloaded and everything works again. 


I do not have any problem with the time to download the file. The problem is that the entire browser is frozen. 


Is there a way to do not froze the entire browser, while the file is downloading? In fact, If there is a way to track the process and show the user a loader, or something that the file is processing for download.... That would be perfect.



JC Joseph Christ Nithin Issack Syncfusion Team September 1, 2022 01:24 PM UTC

Hi Hiram,


  Thanks for your update.


  Based on your query, you want to show the spinner in the grid when the excel exporting starts and remove the spinner when the excel exporting completes. Your requirement, can be achieved by calling the methods ‘showSpinner’ and `hideSpinner` the `beforeExcelExport` and `excelExportComplete` events of the EJ2 Grid respectively.


Please refer the below code example.


 

 beforeExcelExport(args) {

    this.grid.showSpinner();

  }

 

  excelExportComplete(args) {

    this.grid.hideSpinner();

  }

 

 


Sample: https://stackblitz.com/edit/angular-hjtkvu?file=app.component.html,app.component.ts


Please get back to us for further details.


Regards,

Joseph I.


If this post is helpful, please consider Accepting it as the solution so that other members can locate it more quickly.


Loader.
Live Chat Icon For mobile
Up arrow icon