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.

3 Replies

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

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 01:41 AM

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 06:42 AM

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 


Loader.
Live Chat Icon For mobile
Up arrow icon