- Home
- Forum
- ASP.NET Core - EJ 2
- Excel export from server side
Excel export from server side
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<object> toolbarItems = new List<object>(); toolbarItems.Add(new { text = "Ajouter un article", tooltipText = "Ajouter un article", prefixIcon = "e-icons e-plus", id = "Grid_Ajouter" }); toolbarItems.Add("ExcelExport"); toolbarItems.Add("PdfExport"); toolbarItems.Add("CsvExport"); toolbarItems.Add("Search"); toolbarItems.Add("ColumnChooser"); } <ejs-grid id="Grid" dataSource="ViewBag.dataSource" allowSorting="true" allowExcelExport="true" allowGrouping="true" allowPdfExport="true" toolbarClick="toolbarClick" showColumnChooser="true" toolbar="toolbarItems" pdfExportComplete="pdfExportComplete" excelExportComplete="excelExportComplete" allowPaging="true" allowFiltering="true" dataBound="dataBound" locale="fr-FR"> <e-data-manager url="/Product/IndexApi" adaptor="UrlAdaptor"></e-data-manager> <e-grid-pagesettings pageSize="5" pageSizes="@(new string[] { "5", "10", "15" })" pageCount="5"></e-grid-pagesettings> <e-grid-filterSettings type="Menu"></e-grid-filterSettings> <e-grid-columns> <e-grid-column field="productId" allowSearching="false" allowFiltering="false" allowGrouping="false" allowSorting="false" allowReordering="false" is-primary-key="true" headerText="Action" minWidth="100" width="100" template="#button"></e-grid-column> <e-grid-column field="image" headerText="Photo" template="#template" minWidth="100" width="100"></e-grid-column> <e-grid-column field="productName" headerText="Article" minWidth="120" width="120"></e-grid-column> <e-grid-column field="productCode" headerText="Code" minWidth="160" width="160"></e-grid-column> <e-grid-column field="createdAt" headerText="Date création" minWidth="100" customFormat="@(new { type ="date", format="dd/MM/yyyy" })" type="date" visible="false" width="100"></e-grid-column> <e-grid-column field="grammes" headerText="Gr" minWidth="90" width="90"></e-grid-column> <e-grid-column field="price" headerText="PDR" minWidth="100" width="100"></e-grid-column> <e-grid-column field="publicPrice" headerText="PDV" minWidth="100" width="100"></e-grid-column> <e-grid-column field="warehouse.warehouseName" headerText="Magasin" minWidth="120" width="120"></e-grid-column> <e-grid-column field="isSold" headerText="Vendu" displayAsCheckBox="true" textAlign="Center" type="boolean" minWidth="100" width="100"></e-grid-column> <e-grid-column field="sertissage" visible="false" headerText="Sertissage" minWidth="100" width="100"></e-grid-column> <e-grid-column field="carat1" visible="false" headerText="carat1" minWidth="70" width="70"></e-grid-column> <e-grid-column field="carat2" visible="false" headerText="carat2" minWidth="70" width="70"></e-grid-column> <e-grid-column field="carat3" visible="false" headerText="carat3" minWidth="70" width="70"></e-grid-column> <e-grid-column field="totalCarats" headerText="Cts" minWidth="90" width="90"></e-grid-column> </e-grid-columns> </ejs-grid>
JS:
if (args.item.id === 'Grid_excelexport') { gridObj.showSpinner(); this.columns[0].visible = false; this.columns[1].visible = false; this.columns[4].visible = true; this.columns[9].visible = true; this.columns[10].visible = true; this.columns[11].visible = true; this.columns[12].visible = true; this.columns[13].visible = true; gridObj.excelExport(); }
Controller:
public async Task<IActionResult> IndexApi([FromBody] DataManagerRequest dm) { IEnumerable DataSource; if (string.IsNullOrEmpty(user.warehouseId) || user.isSuperAdmin) { DataSource = await _context.Product.AsNoTracking().OrderByDescending(x => x.createdAt).Include(v => v.warehouse).ToListAsync(); } else { DataSource = await _context.Product.AsNoTracking().OrderByDescending(x => x.createdAt).Include(v => v.warehouse) .Where(p => p.warehouseId.Equals(user.warehouseId)).ToListAsync(); } DataOperations operation = new DataOperations(); if (dm.Search != null && dm.Search.Count > 0) { DataSource = operation.PerformSearching(DataSource, dm.Search); //Search } if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting { DataSource = operation.PerformSorting(DataSource, dm.Sorted); } if (dm.Where != null && dm.Where.Count > 0) //Filtering { DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); } int count = DataSource.Cast<Product>().Count(); if (dm.Skip != 0) { DataSource = operation.PerformSkip(DataSource, dm.Skip); //Paging } if (dm.Take != 0) { DataSource = operation.PerformTake(DataSource, dm.Take); } return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource);}
Thanks in advance
SIGN IN To post a reply.
5 Replies
1 reply marked as answer
RS
Rajapandiyan Settu
Syncfusion Team
January 18, 2021 06:21 AM UTC
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
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]
<ejs-grid id="Grid" dataSource="@ViewBag.dataSource" toolbarClick="toolbarClick" allowPaging="true" toolbar="@(new List<string>() { "ExcelExport","PdfExport"})" allowPdfExport="true" allowExcelExport="true" allowSorting="true" allowGrouping="true" showColumnChooser="true" allowFiltering="true">
<e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager>
<e-grid-pagesettings pageSize="5" pageSizes="@(new string[] { "5", "10", "15" })" pageCount="5"></e-grid-pagesettings>
<e-grid-filterSettings type="Menu"></e-grid-filterSettings>
<e-grid-columns>
<e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column>
<e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" width="120"></e-grid-column>
<e-grid-column field="CustomerID" headerText="Customer ID" width="120"></e-grid-column>
<e-grid-column field="ShipCountry" headerText="Ship Country" width="150"></e-grid-column>
</e-grid-columns>
</ejs-grid>
<form action="/Home/PdfExport" id="pdfexportform" method="post">
<input type="hidden" name="GridModel" id="hdnpdfexport" />
</form>
<form action="/Home/ExcelExport" id="excelexportform" method="post">
<input type="hidden" name="GridModel" id="hdnexcelexport" />
</form>
<script>
function toolbarClick(args) {
var gridInstance = document.getElementById("Grid").ej2_instances[0];
var sorted = [];
var where;
var headerText = [];
var fields = [];
var format = [];
// get the current queries in Grid
var gridquery = gridInstance.getDataModule().generateQuery(true).queries;
// generate the sortquery and filerquery from the Grid
for (var i = 0; i < gridquery.length; i++) {
if (gridquery[i].fn == "onSortBy") {
sorted.push({ name: gridquery[i].e.fieldName, direction: gridquery[i].e.direction})
}
if (gridquery[i].fn == "onWhere") {
where = [gridquery[i].e];
}
}
var visibleCols = this.getVisibleColumns(); // get visible columns only in the Grid
// var allCols = this.getColumns(); // get the all the columns in Grid
for (var i = 0; i < visibleCols.length; i++) {
headerText[i] = visibleCols[i].headerText;
fields[i] = visibleCols[i].field;
format[i] = visibleCols[i].format;
}
// bind all the details
var columns = JSON.stringify({ sorted: sorted, where:where, fields: fields, columnsName: headerText, format: format });
if (args.item.text == "PDF Export") {
document.getElementById("hdnpdfexport").value = columns; // bind the column details to the input var form = document.querySelector("#pdfexportform");
form.submit();// submit the form to export the Grid into pdf
args.cancel = true;
}
if (args.item.text == "Excel Export") {
document.getElementById("hdnexcelexport").value = columns; // bind the column details to the input
var form = document.querySelector("#excelexportform");
form.submit();// submit the form to export the Grid into excel
args.cancel = true;
}
}
</script>
|
|
[Home controller.cs]
public class ExportModel: DataManagerRequest // Create the model class to Deserialize the GridModel
{
public List<string> columnsName { get; set; }
public List<string> fields { get; set; }
public List<string> 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<BigData> reports = DataSource.AsQueryable().Cast<BigData>().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<BigData> reports = DataSource.AsQueryable().Cast<BigData>().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.
Please get back to us if you need further assistance with this.
Regards,
Rajapandiyan S
Marked as answer
AN
Anass
January 18, 2021 11:25 PM UTC
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 ?
RS
Rajapandiyan Settu
Syncfusion Team
January 19, 2021 10:43 AM UTC
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.
Please get back to us if you need further assistance with this.
Regards,
Rajapandiyan S
AN
Anass
January 20, 2021 08:39 PM UTC
Thank you very much. It works as a charm
RS
Rajapandiyan Settu
Syncfusion Team
January 21, 2021 04:27 AM UTC
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
SIGN IN To post a reply.
