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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Problems when exporting many rows to excel.

Thread ID:

Created:

Updated:

Platform:

Replies:

142829 Feb 21,2019 09:15 AM UTC Feb 26,2019 11:42 AM UTC Angular - EJ 2 3
loading
Tags: Grid
Juan
Asked On February 21, 2019 09:16 AM UTC

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.

Madhu Sudhanan P [Syncfusion]
Replied On 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 


Juan
Replied On 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.

Madhu Sudhanan P [Syncfusion]
Replied On 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 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;