If it is possible export excel file with columns definition and data array?

Question

Hi,We can  export excel file, so we have to use a data grid to implement this.It works very well if rows and columns are not too much, but we are working with data more than 50000 row  and more than 200 columns, if we bind it to the grid, grid will freeze, we are wonder if there are function  which can take columns and data arrays as parameter to create excel file? so we don't need to bind the data to data grid.Thanks,CZ

Rajapandi Ravi · Accepted Answer

Hi CZ,

Greetings from syncfusion support

By default, Excel exporting works based on the string manipulation. While using large amount of data( Ex: 50K rows and 200 columns then total cells count as 10,000,000 ) in Grid then it will take considerable time to export the excel document in Grid. 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 records.

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,

https://help.syncfusion.com/file-formats/xlsio/create-read-edit-excel-files-in-asp-net-mvc-c-sharp

https://help.syncfusion.com/file-formats/xlsio/assemblies-required

Refer the below link to know about Syncfusion XLSIO,

https://help.syncfusion.com/file-formats/xlsio/overview

https://help.syncfusion.com/file-formats/xlsio/working-with-data#import-data-from-clr-objects

https://help.syncfusion.com/file-formats/xlsio/worksheet-rows-and-columns-manipulation