Excel Render Load time on large excel files
Hi Carlo D'Ugo,
We kindly suggest that you place your .xlsx file in a folder and upload the folder as a .zip or .7z file to this forum. Alternatively, you can use the link below to upload the files and then copy and share the uploaded file link.
https://www.syncfusion.com/support/file-upload
We have reviewed your reported issue and attempted to replicate it by importing a large Excel file using our local service. However, we were unable to reproduce the problem, and the file was imported quickly on our end.
For your reference, we have attached a video demonstration along with the checked sample below.
Video: Please see the attachment.
Client-sample: https://stackblitz.com/edit/react-qlhe1a-k6qttb?file=index.js
And we suspect that you may be using our hosted Syncfusion service URLs for Open and Save actions. We would like to inform you that hosted Syncfusion service URLs are used only for demo purposes. Therefore, kindly avoid using this online service for your development purposes; instead, we recommend creating and using your own local service on your end. Using a local service can improve the performance of Open and Save actions.
We kindly inform you that we have implemented the Open and Save (server-side) functionality in ASP.NET Core and ASP.NET MVC. We are using the Syncfusion XLSIO library for these actions on our server end.
When opening an Excel file in our spreadsheet, in the server end we will read the imported file using the XLSIO library and convert the file into the supported format of Spreadsheet. Before loading the data into the spreadsheet, we maintain a workbook model in backend that consists of entire data being loaded as a JSON data. Therefore, it takes some time to read the file and convert it into a JSON data based on the features used on the imported file.
For your convenience, we have attached the local service sample below.
Service sample: Please see the attachment.
After taking the local service from the attachment, please follow the below steps to launch the local service on your end.
- Download and unzip the attached local service (WebAPI).
- Open the WebAPI.sln file in the WebAPI folder.
- In that right click on the Dependencies folder inside WebAPI.
- Then, click the Manage Nuget Packages.
- In Browse, search Syncfusion.EJ2.Spreadsheet.AspNet.Core package and install the latest package.
- If already, the package exists, remove the package, and re-install it to get the latest package with its necessary dependent packages will get downloaded.
- Now, build the solution and run in a local host.
For your convenience, we have attached the open and save code snippets of local service below.
Code snippet:
[HttpPost] [Route("Open")] public IActionResult Open([FromForm]IFormCollection openRequest) { OpenRequest open = new OpenRequest(); open.File = openRequest.Files[0]; return Content(Workbook.Open(open)); }
//Save method [HttpPost] [Route("Save")] public IActionResult Save([FromForm]SaveSettings saveSettings) { return Workbook.Save(saveSettings); } |
After, launching the local service, you need to update the open and save URL in the Client-Side sample like in the below code snippet,
saveUrl= 'https://localhost:{port number}/api/spreadsheet/save'
openUrl= 'https://localhost:44354/api/spreadsheet/open' saveUrl= 'https://localhost:44354/api/spreadsheet/save' |
Note: Launch the service before running the client-side sample.
For local service you need ASP.NET Core project as a backend for Open/Save functionality in your client application.
Local service available in below GitHub location also:
https://github.com/SyncfusionExamples/EJ2-Spreadsheet-WebServices/
Create ASP.NET Core project and generate the license based on the below KB link.
https://www.syncfusion.com/kb/8976/how-to-generate-license-key-for-essential-studio-products
And include the licensed key in your project startup file as like in the below documentation link.
https://help.syncfusion.com/common/essential-studio/licensing/license-key
You can also host and run our Spreadsheet-oriented service as a Docker image. Utilizing a Docker image necessitates only a simple and basic Docker environment with minimal commands to effortlessly host and run our service.
You can utilize the below service and use it for Spreadsheet Open and Save services, by pulling the spreadsheet docker image and following the steps from the URL mentioned below.
Spreadsheet docker image: https://hub.docker.com/r/syncfusion/spreadsheet-server
For more information regarding the docker deployment, please refer the below documentation,
https://ej2.syncfusion.com/react/documentation/spreadsheet/docker-deployment
For more details regarding our Open and Save support, please refer to the documentation link provided below,
https://ej2.syncfusion.com/react/documentation/spreadsheet/open-save
Limitations:
There are limitations on importing Excel files into the spreadsheet component. Our spreadsheet rendered a maximum of 1 million cells that depends upon the rows and columns. The time stamp in the spreadsheet varies depending upon the cell count and formats applied in the spreadsheet, please find the below benchmark of our Spreadsheet.
Scenario | Rows * Columns |
Import with normal data (without any formatting) | 50,000 * 20 (1 million cells) |
Import data with format (Row height/ cell formats & Merging Wrap text randomly) | 30,000 * 10 |
Based on our limitations, if the formats and formulas are applied in the imported Excel, the threshold limit may vary. So, loading such a large file into the spreadsheet is not possible because the construction of JSON and the loading of such large JSON into the spreadsheet are tedious processes.
If you need to skip the loading of a larger file without hanging the spreadsheet, you have two options to restrict the file by importing into the spreadsheet and causing the server to get hanged. Those properties are.
- MaximumDataLimit. (Value set to the maximum data (cell) count allowed)
- MaximumFileSize (File size to be set in Byte)
You can enable this in the Open action on the server side, as demonstrated in the code snippet shared below.
Code snippet:
[HttpPost] [Route("Open")] public IActionResult Open([FromForm]IFormCollection openRequest) { OpenRequest open = new OpenRequest(); open.File = openRequest.Files[0];
open.Guid = openRequest["Guid"]; // Data limit set to maximum limit. Open.ThresholdLimit.MaximumDataLimit = 1000000; // For reference value has been set to 5MB limit. Open.ThresholdLimit.MaximumFileSize = 5000000;
return Content(Workbook.Open(open)); }
|
It will throw the below alert dialog to skip the opening of larger size in spreadsheet. By clicking the Cancel button, it will skip the importing progress. If you click OK button, then spreadsheet again tries to load the file.
Before we proceed, please share the following details with us to help us continue our investigation:
- Please confirm whether you are facing this problem while using our local service for import and export, or while using our online service.
- We suggest using a local service as recommended above. Please confirm if your reported problem is resolved with the local service or if the issue persists.
- Additionally, please share the Excel file in which you encountered the reported issue during the import process.
Please share all the requested information above from your end. Based on that, we will review the details and provide you with a suitable solution quickly.
Attachment: Attachments_194263_9b9831ed.zip
