Best way to parse cells in spreadsheet with tab separated row ending data

[George]

Hi.

In the Spreadsheet controller when a user imports a spreadsheet into the controller either by cutting and pasting, or by using the import button, was wondering if there was a best practice way to either get all the values even if the value  property is undefined or the cell is blank and does not exist in the array of cells for a given row?

I need to ultimately parse the control for the value (and eventually formulas)  then putting them in a string with tab separated cells separated by an end of line character for the rows,  then send it to an API where it is ultimately placed in the data base as a clob.

The problem is when I am doing this, in certain cases if the cell is empty  then it does not exist in cells array for the given row, so when I get my sample data the cells for the row do not line up with the headers, that is to say the cells are missing. If the cells do exist but the value property does not exist, I usually get a  JavaScript undefined value (because the value parameter doesn't exist), and I have to use an OR (||) operator to remove it and replace it with a blank.


The only thing I could think of was to iterate through the spreadsheet grid of the active sheet I am working with like this, using the range....but the user would have to select the range prior to kicking of the parsing job.


When the function is done I should have a string of the results with cells separated by \t and rows ending with \n:


DATA in stringified form: {"data":"ID\tInternational_ID\tLast Name\tFirst Name\tDescription\tAuth Role\tApp Server Type\tImport Type\tTime Zone Code\tApplication Type\t\n101\t\tHall\tJoe\tAssit and Admin\tAdvUser\t\t\t102\tAccounting\t\n102\t\tCallet\tBob\t\tAdmin\t\t\t102\tZooology\t\n103\t\tBaker\tDenny\t\tUser\tTripple Node\t\t109\tEngineering\t\n104\t\tBrewster\tFred\t\tAdvUser\t\t\t102\tProject Tracker\t\n105\t\tTurner\tDenise\t\tExecAdmin\tRegular\t\t115\t\t\n106\t\tZenni\tBee\t\tUser\t\t\t102\tAccounting\t\n107\t\tCook\tCarry\t\tAdvUser\t\t\t102\tEngineering\t\n108\t\tMathews\tHarry\t\tUser\t\tInternational\t102\t\t\n109\t\tDean\tDonny\t\tUser\tRegular\t\t102\tProject Tracker\t\n110\t\tFuller\tJames\t\tAdvUser\tTripple Node\t\t102\tScientific\t\n111\t\tAhrens\tJack\t\tAdmin\tRegular\t\t115\t\t\n112\t\tMikes\tGeorge\t\tUser\tHi Capacity\tLocal\t114\tProject Management\t\n113\t\tJones\tArnie\t\tAdvUser\tRegular\t\t116\t\t\n"}

If I cut and paste the above string into an excel spreadsheet sheet it will show the blank cells..and everything aligns.

If I didn't do this, then some of the cells would not exist, and I would getcells shifting into the wrong columns.

I need to capture even the blank cells and those with value == undefined.

Right now I am scanning the cells using a range for the first cell (probably A1) and also the last one... the user needs to set this before the parsing is done and this is very annoying. Is there an easy way to get the used range of the spreadsheet without the user having to set this?

 

Here is a code snippet of some test code:
https://dojo.telerik.com/@[e-mail]/ELelOXuH/5   

1) Import the file using the import file button:

2) Click on the Send Data button but I haven't a clue):

 ... a popup dialog will appear

....and select the range of the cells to be parsed into tab separated values (right now I'd like to figure out the used area automatically, but I haven't a clue).

3) Click the Submit button.

4) Go to console and look at the data:

... you should be able to grab/copy the first bit of data and  paste it into a spreadsheet and get it to align with the columns should be no row shifting.

Then the spreadsheet should look like this:

THE BIG-TICKET ITEM IS: I just want to parse through the cells of the Spreadsheet control once, and without having the user select the range... it would be done automatically. I am just wondering if there isn't an easier, cleaner way to do this? I wish I could send you my test excel spreadsheet it would be easier to constrain what is going on... I sent you a tab separated txt file you should be able to convert that easily to an excel file.

Thanks again for your help and patience!

George

[Neli]

Hello George,

When data is pasted from Excel to Spreadsheet you can retrieve the pasted content including the empty cells in the paste event handler as demonstrated below:

paste: function(e) {
           console.log(e.clipboardContent.data)
}

However, when a file is imported in Excel the only way I could think of is indeed to get the values of a specific range:

excelImport: function(e) {
          setTimeout(function () { 
            var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
            var values = spreadsheet.sheets()[0].range("A1:E10").values();

            console.log(values);
          }, 1000)
        }

The code above will return null for the empty cells in the given range.

Both snippets are demonstrated in the Dojo linked here - https://dojo.telerik.com/@NeliK/eliSaBeD

I hope this helps. 

Regards,
Neli
Progress Telerik

[George]

Just an update to this question.....

So... the way our users will use this 99% of the time, it looks like they will use the import button.

That being said I figured a crude way to calculate the right most, bottom most cell for the end of the range. I still don't trust it LOL because sometimes when the use selects a row, the "activated" max row can be the max row value (e.g. like row: 20000) of the spreadsheet component. So, I still have a pop-up for the user to look at so they can change it if it seems incorrect.

Once the sheet has been imported the user clicks the:  "Send Data" button...

...and the pop-up window still appears:

But this time the start cell, A1 is pre-populated, and the end cell is calculated. The user can still muck with the range though before they submit and parse it into \t tab separated values.

Still needs some refactoring, as I scavenged some code from a different project buuut the click handler for the Send Data kendo button looks like this:

 click: async () => {

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        var { sheetcount, ssData, activeSheetIndex } = getCurrentSpreadSheet(spreadsheet);

        //Validate we have used cells in the sheet aka not blank.
        if (ssData.sheets[activeSheetIndex].rows.length > 0) {

            var rowCount = ssData.sheets[activeSheetIndex].rows.length;

            //This gives me the right most bottom most cell:
            let calcEnd = getColumnLetter(ssData.sheets[activeSheetIndex].rows[0].cells.length) + rowCount;

            //Stick this in the textbox for the user to view, incase we get a wildly wrong range.
            // they can still change it.
            document.getElementById("startCell").value = "A1";
            document.getElementById("endCell").value = calcEnd;

            console.log("In on click event handler for import button.");
            let popUpWindow = $("#pop-up-window").data("kendoWindow");
            popUpWindow.open();

            var activeSheet = $("#spreadsheet").data("kendoSpreadsheet").activeSheet();
            console.log("active sheet is: ", activeSheet._sheetName);

        } else {
            //If no cells used then tell user they have a blank sheet:
            alert("Sheet has no used cells -- You must import a sheet or cut and paste data.");

        }

    }

 

I refactored out getting the spreadsheet in the format I need, into its own function. However, I think I need to refactor it a bit, but it works:

    //This gets the information I need -->
    // I know this is weird but we need to 
    // get:
    //  - ssData: a parsed structure version of the spreadsheet component.
    //  - sheetcount: number of sheets
    //  - activeSheet index: the element (integer) of the active sheet.
    function getCurrentSpreadSheet(spreadsheet) {
      var data = spreadsheet.toJSON();
      var json = JSON.stringify(data, null, 2);
      var ssData = JSON.parse(json);
      var sheetcount = ssData.sheets.length;
      var activeSheetIndex = 0;

      for (a = 0; a < sheetcount; a++) {

        if (ssData.activeSheet == ssData.sheets[a].name) {
          activeSheetIndex = a;
        }
      }

      return { sheetcount, ssData, activeSheetIndex};
    }

Getting the range end identifier code (the right most bottom most cell) looks like:

    //Takes in a column index 0 ... N and returns an
    // Excel letter column location like A, B, C...AB, AC, AD..etc..
    function getColumnLetter(columnIndex) {
      let columnLetter = '';
      while (columnIndex > 0) {
        let remainder = (columnIndex - 1) % 26;
        columnLetter = String.fromCharCode(65 + remainder) + columnLetter;
        columnIndex = Math.floor((columnIndex - 1) / 26);
      }
      return columnLetter;
    }

Seems to work for my purposes so far, will update if I find any issues...only other issue is converting the date into the original string version of the date and NOT that OLE date format that Excel uses...but that is another question found in the forum.

Here is a link to the working code in the DOJO:

Convert Data to Tab and End of Line | Kendo UI Dojo (telerik.com)

( https://dojo.telerik.com/owEFuvoV/6 )

Thanks again!

George

 

[Neli]

Hi George,

Thank you very much for sharing your approach and findings with the community. I am sure your reply will be of a great help to the other users.

As you mentioned the other issue - converting the date into the original string version of the date and NOT that OLE date format that Excel uses - I am pasting the related forum thread below.

- https://www.telerik.com/forums/how-to-import-an-excel-spreadsheet-as-text-w-o-format----is-there-a-way 

Thank you once again.

Regards,

Neli