Excel export from server side

Question

Hello,I have a grid that contains 3000 rows and each rows contain an image, so when I'm doing an export, it's take 4 minutes to export the grid.When I'm inspecting, I realized that all the data (approximately 300 Mega) are sent to the client then the export is done, even if the images are disabled in the export.Is there any way to procced the export in server side and sending the excel file to client side ?I'm using the grid with UrlAdaptor for paging and sorting and filtering.Razor:@{ List

Rajapandiyan Settu · Accepted Answer

Hi Anass, Thanks for contacting Syncfusioin support. Query: Is there any way to procced the export in server side and sending the excel file to client side ? We would like to inform you that, By default the EJ2 Grid supports only client-side exporting. We suggested you follow the below solution to achieve Exporting at the server-side. You need to refer to the “Syncfusion.Pdf” and “Syncfusion.XlsIO” assembly to use Syncfusion Pdf and XLsIO in your project. You can get these assemblies to your project by installing the “Syncfusion.Pdf.Net.Core ” and “Syncfusion.XlsIO.Net.Core” NuGet package. Refer to the following help documentation on exporting at the server side. Excel-Export: https://help.syncfusion.com/file-formats/xlsio/create-read-edit-excel-files-in-asp-net-core-c-sharp PDF-Export: https://help.syncfusion.com/file-formats/pdf/create-pdf-file-in-asp-net-core In the toolbarClick handler, we have used form submit action to call the server-side exporting method(ExcelExport) and send the Grid columns fields, Sorted query and Where query details to the server-side as JSON string. After receiving these properties on the server-side, you can Deserialized this data by using the model class (i.e ExportModel). We can get the Sort and Filter details through this deserialized model and, you can perform Sorting and Filtering action using DataOperations. And finally, we have provided DataSource to the exporting file through the “ImportData” method. Please refer to the below code example. [code example] [index.cshtml]

[Home controller.cs] public class ExportModel: DataManagerRequest // Create the model class to Deserialize the GridModel { public List columnsName { get; set; } public List fields { get; set; } public List format { get; set; } } // Perform PdfExporting public ActionResult PdfExport(string GridModel) { ExportModel exportModel = new ExportModel(); exportModel = (ExportModel)JsonConvert.DeserializeObject(GridModel, typeof(ExportModel)); // Deserialized the GridModel IEnumerable DataSource = BigData.GetAllRecords(); DataOperations operation = new DataOperations(); if (exportModel.Sorted != null && exportModel.Sorted.Count > 0) //perform Sorting { DataSource = operation.PerformSorting(DataSource, exportModel.Sorted); } if (exportModel.Where != null && exportModel.Where.Count > 0) // perform Filtering { DataSource = operation.PerformFiltering(DataSource, exportModel.Where, exportModel.Where[0].Operator); } IList reports = DataSource.AsQueryable().Cast().ToList(); PdfDocument document = new PdfDocument(); PdfPage page = document.Pages.Add(); PdfGraphics graphics = page.Graphics; PdfFont font = new PdfStandardFont(PdfFontFamily.Courier, 20); PdfGrid pdfGrid = new PdfGrid(); // select grid column in the datasource var visualData = reports.Select(xy => new { xy.OrderID, xy.CustomerID, xy.ShipCountry }).ToList(); // all the field in the pdfGrid's dataSource are exported into pdf pdfGrid.DataSource = visualData; pdfGrid.Draw(graphics); MemoryStream stream = new MemoryStream(); document.Save(stream); stream.Position = 0; //Download the Excel file in the browser FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/pdf"); fileStreamResult.FileDownloadName = "Output.pdf"; return fileStreamResult; } // perform ExcelExporting public ActionResult ExcelExport(string GridModel) { ExportModel exportModel = new ExportModel(); exportModel = (ExportModel)JsonConvert.DeserializeObject(GridModel, typeof(ExportModel)); // Deserialized the GridModel IEnumerable DataSource = BigData.GetAllRecords(); DataOperations operation = new DataOperations(); if (exportModel.Sorted != null && exportModel.Sorted.Count > 0) // Perform Sorting { DataSource = operation.PerformSorting(DataSource, exportModel.Sorted); } if (exportModel.Where != null && exportModel.Where.Count > 0) //Perform Filtering { DataSource = operation.PerformFiltering(DataSource, exportModel.Where, exportModel.Where[0].Operator); } using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Import the data to worksheet IList reports = DataSource.AsQueryable().Cast().ToList(); // select grid column in the datasource var visualData = reports.Select(xy => new { xy.OrderID, xy.CustomerID, xy.ShipCountry }).ToList(); // all the field in the visualData are exported into excel worksheet.ImportData(visualData, 2, 1, true); MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); stream.Position = 0; //Download the Excel file in the browser FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel"); fileStreamResult.FileDownloadName = "Output.xlsx"; return fileStreamResult; } } Find the below sample for your reference. Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/core_Grid_export_server-1940387190.zip Please get back to us if you need further assistance with this. Regards, Rajapandiyan S

Anass · Answer

Hello Rajapandiyan,Your solution saved me a lot of time. thank you very much.I have one last issue, when exporting a pdf, a have only one page with less than 30 rows, however I have more than 1000 rows.I tried many solutions but nothing worked for me.Can you help me with that please ?

Rajapandiyan Settu · Answer

Hi Anass, 
 
Sorry for the inconvenience caused. We suggested you to modify the code like below to draw all the pages in pdf export, 





[Home controller.cs] 
 
// Perform PdfExporting 
        public ActionResult PdfExport(string GridModel) 
        { 
            --------- 
            --------- 
            PdfDocument document = new PdfDocument(); 
            PdfPage page = document.Pages.Add(); 
            PdfGraphics graphics = page.Graphics; 
            PdfFont font = new PdfStandardFont(PdfFontFamily.Courier, 20); 
            PdfGrid pdfGrid = new PdfGrid(); 
            // select grid column in the datasource  
            var visualData = reports.Select(xy => new { xy.OrderID, xy.CustomerID, xy.ShipCountry }).ToList(); 
            // all the field in the pdfGrid's dataSource are exported into pdf 
            pdfGrid.DataSource = visualData;  
            pdfGrid.Draw(page, new PointF(10, 10));  // use this code to draw all the pages 
            MemoryStream stream = new MemoryStream(); 
            document.Save(stream); 
            stream.Position = 0; 
            //Download the pdf file in the browser 
            FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/pdf"); 
            fileStreamResult.FileDownloadName = "Output.pdf"; 
            return fileStreamResult; 
        } 

Find the below updated sample for your reference. 

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/core_grid_export_server_allpage-1407895542.zip  

Please get back to us if you need further assistance with this. 

Regards, 
Rajapandiyan S

Anass · Answer

Thank you very much. It works as a charm

Rajapandiyan Settu · Answer

Hi Anass, 
 
We are glad that the provided solution resolved your requirement. 
 
Please get back to us if you need further assistance with this. 

Regards, 
Rajapandiyan S