Issue with Importing Excell file that has sheet of references to cells on other sheets

0 Answers 20 Views
Spreadsheet
George
Top achievements
Rank 3
Bronze
Bronze
Iron
George asked on 13 Jan 2025, 05:19 PM | edited on 13 Jan 2025, 05:37 PM

Hi,

Having issues when attempting to parse the incoming date durring the onImport event in a Kendo Spreadsheet control. We wanted to rename the reports to the expected names if the user imports a spreadsheet with different names.

So, I came up with a function to do this, however it seems like the first sheet, when comprised of Excel references chokes. The below works fine if there are no references to other cells on other sheets.


function resetToOrigSheetNames() {
    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");

    var data = spreadsheet.toJSON();
    var json = JSON.stringify(data, null, 2);//<--[OOF] Uncaught RangeError: Invalid string length! Chokes on a sheet with references.
    var ssData = JSON.parse(json);
    
    var importData = "";
    var sheetcount = ssData.sheets.length;
    var sheets = spreadsheet.sheets();

    let listData = [];
    listData = _view.get("currentLayoutDataList"); //We will use this later for sheet names etc...


    //First load the Sheet types:
    let i = 0;
    $.each(listData, function (index, value) {

        if (i < sheetcount && (value.itemType === "REPORT" || value.itemType === "CUSTOM")) {

            spreadsheet.renameSheet(spreadsheet.sheets()[i], defineName(value));
            console.log("NAME", spreadsheet.sheets()[i]._name );
            i++;
        }

       
    });
    spreadsheet.refresh();
}

I have the above being called in the   excelImport event handler.

 

 It's throwing an exception on the JSON.stringify():

 

Which according to JavaScript documentation means the string being created… is too big. I use the below code all over the application for different things:

 

    var data = spreadsheet.toJSON();

    var json = JSON.stringify(data, null, 2);

    var ssData = JSON.parse(json);


Was wondering if there was a way to import the actual values of the referenced cells and not the references?

 

Thanks!
George

George
Top achievements
Rank 3
Bronze
Bronze
Iron
commented on 15 Jan 2025, 05:55 PM

Update.

Part of my problem was that we have users / testers importing large sheets column wise... 200 to 300 columns with 30 rows. Sometimes for some reason, I think the end of line character might be messing things up. I replaced this:

    var data = spreadsheet.toJSON();
    var json = JSON.stringify(data, null, 2);//<--[OOF] Uncaught RangeError: Invalid string length! Chokes on a sheet with references.
    var ssData = JSON.parse(json);

with just this:

    var ssData = spreadsheet.toJSON();

It stopped barfing but the performance is not great when trying to replace the sheet names.... I don't think I need to replace the sheet names in this scenario /case but so I may be able to...it was originally a requirement... which was the whole point to using the excelImport event handler. trying to figure out what the performance difference is. If Itake out the bit where I am trying to rename the sheets to the expected sheet names... performance improves drastically.

George

Martin
Telerik team
commented on 16 Jan 2025, 09:23 AM

Hello, George,

Could you please provide a runnable example where I can see the issues you are experiencing? Based on the provided information, I am not sure how I can observe the problem. A runnable example would help me to better understand the scenario and provide further guidance. Looking forward to your reply.

George
Top achievements
Rank 3
Bronze
Bronze
Iron
commented on 16 Jan 2025, 07:54 PM | edited

Martin, 

[Updated] Adding the rows and columns with very high initial values seems to be the culprit. Man does that effect the performance when importing an Excel spreadsheet. I have always assumed that you need those... I will have to think about how I size incoming sheets from JSON objects--> into dataSources from now on and make sure they don't render wonky on reinitialization or clearing the sheet.


I haven't had time to study the performance tab that much, but I noticed a lot of garbage collection going on around when I called the resetToOrigSheetNames() function that I thought I was having problems with. I think if you add:

rows: 20000,
columns: 300,

in the spreadsheet initialization/declaration call to the below code you will see what I am talking about.

Change First Three Names | Kendo UI Dojo

Just in case the Kendo example is down or inaccessible or not there :) :


<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Change First Three Names</title>

  
  <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2023.3.1010/js/jszip.min.js"></script>
  
  
  <script src="https://kendo.cdn.telerik.com/2023.3.1010/js/kendo.all.min.js"></script>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/themes/7.0.1/default/default-ocean-blue.css">
<script>
 let _layoutList = [
  {
    "instName": "SCHEDULE",
    "miId": 200,
    "instanceId": 200,
    "instanceCategory": 3,
    "instTypeId": 80,
    "reportTypeName": "Import Data",
    "executionSequence": 1,
    "commandScript": "#INCLUDE 'Policy Edit Declarations';   SHEET_NAME='Policy_Data'; ",
    "itemType": "IMPORT",
    "instStart": "02-OCT-24",
    "instEnd": "03-OCT-24",
    "scenarioId": 1,
    "scenarioGroupId": 1,
    "timeZoneId": 0,
    "instanceEditId": 0,
    "importType": 27,
    "instDescription": "Import/Edit POLICY Data",
    "parentSequence": 0,
    "linkedToActionInstId": 0,
    "globalClobId": 0
  },
  {
    "instName": "SCHEDULE",
    "miId": 200,
    "instanceId": 201,
    "instanceCategory": 3,
    "instTypeId": 80,
    "reportTypeName": "Import Data",
    "executionSequence": 2,
    "commandScript": "#INCLUDE 'Column Mapping Declarations';   SHEET_NAME='Column Mapping'; ",
    "itemType": "IMPORT",
    "instStart": "02-OCT-24",
    "instEnd": "03-OCT-24",
    "scenarioId": 1,
    "scenarioGroupId": 1,
    "timeZoneId": 0,
    "instanceEditId": 0,
    "importType": 23,
    "instDescription": "Import/Edit COLUMN MAPPING data",
    "parentSequence": 0,
    "linkedToActionInstId": 0,
    "globalClobId": 0
  },
  {
    "instName": "SCHEDULE",
    "miId": 200,
    "instanceId": 202,
    "instanceCategory": 3,
    "instTypeId": 80,
    "reportTypeName": "Import Data",
    "executionSequence": 3,
    "commandScript": "#INCLUDE 'Staff Actionable Report Declarations';   SHEET_NAME='Staff'; ",
    "itemType": "IMPORT",
    "instStart": "02-OCT-24",
    "instEnd": "03-OCT-24",
    "scenarioId": 1,
    "scenarioGroupId": 1,
    "timeZoneId": 0,
    "instanceEditId": 0,
    "importType": 10,
    "instDescription": "Import/Edit Staff and Related Data",
    "parentSequence": 0,
    "linkedToActionInstId": 0,
    "globalClobId": 0
  },
  {
    "instName": "SCHEDULE",
    "miId": 200,
    "instanceId": 203,
    "instanceCategory": 3,
    "instTypeId": 10017,
    "reportTypeName": "Policy",
    "executionSequence": 4,
    "commandScript": "SHEET_NAME='Policy_Data'; ",
    "itemType": "REPORT",
    "instStart": "02-OCT-24",
    "instEnd": "03-OCT-24",
    "scenarioId": 1,
    "scenarioGroupId": 1,
    "timeZoneId": 0,
    "instanceEditId": 0,
    "importType": -1,
    "instDescription": "Policy Report - ALL",
    "parentSequence": 0,
    "linkedToActionInstId": 0,
    "globalClobId": 0
  },
  {
    "instName": "SCHEDULE",
    "miId": 200,
    "instanceId": 204,
    "instanceCategory": 3,
    "instTypeId": 10021,
    "reportTypeName": "Column Mapping Report",
    "executionSequence": 5,
    "commandScript": "SHEET_NAME='Column Mapping'; ",
    "itemType": "REPORT",
    "instStart": "02-OCT-24",
    "instEnd": "03-OCT-24",
    "scenarioId": 1,
    "scenarioGroupId": 1,
    "timeZoneId": 0,
    "instanceEditId": 0,
    "importType": -1,
    "instDescription": "Column Mapping Report",
    "parentSequence": 0,
    "linkedToActionInstId": 0,
    "globalClobId": 0
  },
  {
    "instName": "SCHEDULE",
    "miId": 200,
    "instanceId": 205,
    "instanceCategory": 3,
    "instTypeId": 10016,
    "reportTypeName": "Staff",
    "executionSequence": 6,
    "commandScript": "SHEET_NAME='Staff'; ",
    "itemType": "REPORT",
    "instStart": "02-OCT-24",
    "instEnd": "03-OCT-24",
    "scenarioId": 1,
    "scenarioGroupId": 1,
    "timeZoneId": 0,
    "instanceEditId": 0,
    "importType": -1,
    "instDescription": "Staff Report - ALL",
    "parentSequence": 0,
    "linkedToActionInstId": 0,
    "globalClobId": 0
  }
];
</script>
</head>
<body>
  <div id="spreadsheet"></div>
<script>
  $(document).ready(function() {
    
    //Create a kendo observable:
    _view = kendo.observable({
      currentSelectedInstanceId: 202, //Staff should rename 1st sheet to Staff.
      currentLayoutDataList: _layoutList

    });    
      
    //Declare and initialize the spreadsheet control:
    $("#spreadsheet").kendoSpreadsheet({
      
      render: (e) => {
        console.log("IN RENDER:")
        e.sender.element.innerHeight(750);
        $('#spreadsheet').data("kendoSpreadsheet").resize();// a little hack to get the 
        // empty spreadsheet grid to render completely.

        // Hides the remove icon
        $(".k-spreadsheet .k-spreadsheet-sheets-bar .k-spreadsheet-sheets-remove").hide();

        //This code prevents the 'X' delete button from reappearing:
        $(".k-tabstrip-item").on("dblclick", function () {
          setTimeout(function () {
            //On-blur event triggers reset after user has tried to muck with the controls.

            $("input.k-spreadsheet-sheets-editor").on("blur", function () {
              //Hides the remove icon
              $(".k-spreadsheet .k-spreadsheet-sheets-bar .k-spreadsheet-sheets-remove").hide();
            });
          });
        });
      },
      
      excelImport: function(e) {

                     //The excelImport event provides a promise (e.promise) 
                     //  that resolves when the import operation completes.
            e.promise
                .progress(function (e) {
                    /* The result can be observed in the DevTools(F12) console of the browser. */
                    console.log(kendo.format("{0:P} complete", e.progress));
                })
                .done(function () {
                    //want the sheet names to not change:
                    console.log("excelImport(): IN DONE block");
                    resetToOrigSheetNames();
                });
      }
    });
  });
  
  //The whole purpose of this function is to rename the sheets
  // to the names of Reports that are listed as "Report" Item.types in the 
  // current layout list and visible in a kendoTreeList (not shown in this demo)
  // 
  // When we started putting this together, we had reports coming in that later could
  // be used as input data into our system...and we wanted the imported excel sheets
  // names to match those names... dunno if we will continue this as the specification and
  // requirements have changed. :(
  // 
  // Also at a point in time we added the ability to import excel sheets to be used as
  // configuration data... the thought was.. these would have a ItemType of "IMPORT"
  // and was thought it would ALWAYS be one sheet... that has changed and now we have a 
  // big mess. because our tester/ customer support person is creating these  IMPORT excel files
  // with multiple sheets that are referenced in the first.
  // 
  // Point being we are having some recursive issues... I hope to replicate
  // caused by some one and done code in the render event handler...I think but I don't know
  // how to turn that off ....yet. I have added that code in this Kendo Dojo test code in
  // hopes of replicating this problem.
  // In the actual code on the server and running with Visual Studio I am hitting render
  // once every time a sheet is hit.
  //
  // Got to be some sort of process that is blocking this stuff... that I cannot replicate here.
  // -- -- thus far I can't repeat this issue -- --
  // 
  function resetToOrigSheetNames() {
    console.log("Inside resetToOrigSheetNames()");
    
    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    
    var data = spreadsheet.toJSON();// this might be problematic for 
																	  // big sheets.

    // Directly work with the object data
    var ssData = data;

    var importData = "";
    var sheetcount = ssData.sheets.length;
    var sheets = spreadsheet.sheets();

    let listData = [];
    listData = _view.get("currentLayoutDataList"); //We will use this later for sheet names etc...


    //First load the Sheet types:
    let i = 0;
    //This will rename the current sheets incoming to the preexisting sheet/report names
    // In order to what they appear in the currentLayoutDataList.
    $.each(listData, function (index, value) {

        console.log("value.itemType: "+ value.itemType);
        console.log("sheetcount ("+sheetcount+") vs index ("+index+") vs i ("+i+")");
        if (i < sheetcount && (value.itemType === "REPORT" || value.itemType === "CUSTOM")) {

            spreadsheet.renameSheet(spreadsheet.sheets()[i], defineName(value));
            console.log("NAME: " ,spreadsheet.sheets()[i]._sheetName);
            i++;
        } else if (sheetcount === 1 && //In the case of one sheet we rename to a selected sheet(from a control
                   										 // not shown in this demo); hard code it in observable to 202 should be 
                                       // rename the one sheet to Staff.
                   _view.get("currentSelectedInstanceId") == value.instanceId && 
                   value.itemType === "IMPORT") {
            spreadsheet.renameSheet(spreadsheet.sheets()[i], defineName(value));
            console.log("NAME (IMPORT): " ,spreadsheet.sheets()[i]._sheetName);
            return false;
        }
        //i++;
       
    });
    spreadsheet.refresh();
}
  
//In the case where there is no SHEET_NAME=...  in the commandScript attribute/property, need to revert to reportTypeName.
function defineName(value) {
      console.log("Inside defineName()");
    if (value.commandScript == null || value.commandScript == undefined || value.commandScript == '')
    {
         console.log("commandScript was empty? commandScript: "+ value.commandScript);
        return -1;//value.reportTypeName;
    }

   //Just want the SHEET NAME:
    var comScript = value.commandScript.substring(value.commandScript.indexOf("=") + 1).replace(/'/g, '').replace(/;/g, '');

    return comScript;
}
</script>
</body>
</html>
I think there is some sort of threading issue in the Chrome and Edge browser? Way over my head. I can send screen shots of the Performance analyzer in DevTools.

 

Hope this helps,
George

Martin
Telerik team
commented on 21 Jan 2025, 11:18 AM

Hello, George,

I tested the provided code snippet with the number of rows and columns suggested. The result was that it took the Spreadsheet around 5-6 seconds to import a file. Then I removed the code in the render and the excelImport events, and the file was imported instantly. The code in those events triggers the render events several times, and when combined with such a huge amount of cells, results in a drop of the performance. Still, I am unable to observe the error mentioned in the initial reply. Given the configuration and the logic, I would say that it is expected to observe a slight drop in the performance. Let me know if I am missing something from the issue.

George
Top achievements
Rank 3
Bronze
Bronze
Iron
commented on 21 Jan 2025, 03:40 PM | edited

Martin,

I am sorry ...

This:

In  function resetToOrigSheetNames() the above doesn't happen unless I use:

function resetToOrigSheetNames() {
    console.log("Inside resetToOrigSheetNames()");
    
    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");   
    var data = spreadsheet.toJSON();
//Remove the next two lines... I don't need a stringified version and the JSON.parse(json) I think converts it back to the way it was in spreadsheet.toJSON();
    var json = JSON.stringify(data, null, 2);//<--[OOF] Uncaught RangeError: Invalid string length! Chokes on a sheet with references.
    var ssData = JSON.parse(json); // Why did I do this?...captured from some other code that I had but doesn't make sense


instead of what you see:


function resetToOrigSheetNames() {
    console.log("Inside resetToOrigSheetNames()");
    
    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    
    var data = spreadsheet.toJSON();// this eliminated the above error.

eliminated that error by myself by removing the stringify() and that extra parse(), but I was still experiencing some performance issues... and I should have addressed that in a different question.

Sorry about that,

George

 

 


Martin
Telerik team
commented on 24 Jan 2025, 12:35 PM

Hello, George,

I am glad to hear that you managed to resolve the error. Do I understand correctly that the topic of this thread has been resolved?

George
Top achievements
Rank 3
Bronze
Bronze
Iron
commented on 24 Jan 2025, 03:08 PM | edited

Yes Please consider this answered.

No answers yet. Maybe you can help?

Tags
Spreadsheet
Asked by
George
Top achievements
Rank 3
Bronze
Bronze
Iron
Share this question
or