how to add ImportRequest type

[Mahesh Machina]

I am trying to read excel file saved in database and bind to Spreadsheet control. please assist

I cannot resolve ImportRequest type

Is it possible to hide Insert/Formulas/Data/View/File menus

[Sangeetha Priya Murugan]

Hi Mahesh,

Query #1: Regarding OpenRequest related query:

We suspect that the issue is due to you haven’t properly installed the server dependencies for spreadsheet component. for more details regarding the server-side dependencies please refer to the below link.

https://ej2.syncfusion.com/aspnetcore/documentation/spreadsheet/open-save

And you need to load the excel file form the database into the spreadsheet, it can be achievable by using the server-side open method.
For your convenience, we have prepared the sample that load/save the excel file as byte array in the database and attached below. To refer more details regarding this requirement, please refer to the below link.

https://support.syncfusion.com/kb/article/10471/how-to-save-and-retrieve-the-spreadsheet-data-as-a-byte-array-in-the-database

Related KB link:

https://support.syncfusion.com/kb/article/10357/save-and-retrieve-the-javascript-spreadsheet-data-as-json-in-database

Please check this issue by referring the proper dependencies and kindly get back to us with more details, if you still getting the same issue or need further assistance on this.

Query #2: To hide Insert/Formulas/Data/View/File menus.

Your requirement to hide the ribbon toolbar items can be achievable by using the hideFileMenuItems method and hideRibbonTabs method as shown below.

Code Block:

 

 

  function onCreated(){

        var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

        spreadsheetObj.hideRibbonTabs(['Formulas', 'Insert', 'View','Data'], true);

        spreadsheetObj.hideFileMenuItems(['File'], true);

    }

API Link: https://helpej2.syncfusion.com/javascript/documentation/api/spreadsheet/#hidefilemenuitems

https://helpej2.syncfusion.com/javascript/documentation/api/spreadsheet/#hidetoolbaritems

If you need to hide Home tab text alone you can use the cssClass property to hide the Home tab text content.

Code Block:

 

<style>

   /* To hide Home tab text*/

    .e-custom.e-spreadsheet

    .e-ribbon

    .e-tab-header

    .e-toolbar-items {

        display: none;

    }

</style>

For your convenience, we have prepared the sample for the above two requirements, please find the below attachment.

Attachment: SpreadsheetSaveToSQLTable_7025897f.zip

[Mahesh Machina]

Thank you very much Sangeetha. it worked.

However, when I tried with large file (23 MB) 30k records. It is taking lot of time to load file. Do you have any solution for this?

We are trying to load 3 sheets with 3 files from database on page load. Hope it is possible

[Vasanth Ravi]

Hi Mahesh,

There are limitations over importing the excel file into the spreadsheet component. Find the below benchmark of our Spreadsheet with importing the excel file.

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.

1. MaximumDataLimit. ( Value set to the maximum data (cell) count allowed )
2. MaximumFileSize ( File size to be set in Byte )

Enable this in the Open action on the server side, as shown below.

CODE BLOCK:

 

public IActionResult Open([FromForm]IFormCollection openRequest)

{

    OpenRequest open = new OpenRequest();

    open.File = openRequest.Files[0];

    // For reference value has been set to 5MB limit.

    open.ThresholdLimit.MaximumFileSize = 5000000;

    var openbook = Content(Workbook.Open(open));

    return openbook;

}      

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.

Attached Service and API for reference.

Service Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/WebAPI1798173678

API Link: https://help.syncfusion.com/cr/aspnetcore-js2/Syncfusion.EJ2.Spreadsheet.ThresholdLimit.html

NOTE: If this post is helpful, please mark it as an answer so that other members can locate it more quickly.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

As you were stated the file being imported is about 23 MB in size and contains about 30k records – we suggest you try out the above suggested solution to restrict the time taken. If still need any clarifications share the below requested details to proceed further.

1. If possible, share the dummy excel file that contains the same limit as stated for further validation.
2. How many sheets are there within the file.? You have stated the file contains 3 sheets with 30k records. Is the 30k records is combined with 3 sheets are with individual.?
3. What kind of data has been included in the file.?
4. Share the features and functionalities used within the file which is being imported.