Dynamically set options.rows

[Christy]

I am adding data to a spreadsheet using the following:

var spreadsheet = $scope.spreadsheet;
spreadsheet.fromFile(newDataFile)
    .then(function () {
       // do something here
     });

Is there a way to determine the number of rows prior to displaying the spreadsheet?  The problem is that some users will want to upload ALL of their data, which may be several thousand rows.  Others may only look at subsets.  If I set the options.rows to a large number (say, 5000), there will usually be a huge number of extra rows.  Even if I do that, there will eventually be some user with a spreadsheet that is 5001 rows, and row 5000 will be removed.  

Thanks!

[Stefan]

Hello Christy,

The Kendo UI Spreadsheet will create the same number of rows to fit the imported data if it exceeds the default number of rows(200).

The described behaviour can be observed in our demo:

http://demos.telerik.com/kendo-ui/spreadsheet/server-side-import-export

Additionally, the number of rows can be set programmatically using the rows property of the Spreadsheet:

http://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet#configuration-rows  

Let me know if you need additional information on this matter.

Regards,
Stefan
Telerik by Progress

 

 

[Christy]

I don't see that working on my spreadsheet.  I did notice that in the innerText of the element, there is a list of the rows towards the end of the information.  Is there any way to read the rows within the data from the spreadsheet and then reset the rows in the options?  If there is, I can just use a max value and then truncate it.

Thanks!

[Stefan]

Hello Christy,

If the rows are not automatically added depending on the rows in the added file, then probably there is an issue in the application.

Please check the default behaviour of the fromFile method of the Spreadsheet. Notice that when a file with more than 200 rows is added, the Spreadsheet will add more rows:

http://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet#methods-fromFile

Also, please make sure that the jszip script file is included in the page:

http://docs.telerik.com/kendo-ui/framework/excel/introduction#jszip

If this behaviour is not observable in your application, please send a runnable example and we will investigate.

Regards,
Stefan
Telerik by Progress

 

[Christy]

Is there something that happens in the saveUrl?  The demo has it listed as /kendo-ui/spreadsheet/Upload.  

I have a wrapper component around the kendo-upload with options.async.autoUpload set to false and a method assigned for its options.select.  The method has an output of the select's e.files[0].rawfile.

function onFileSelect(e) {
    // Checking extension, etc.
    // ...
    this.onSelect({
        newDataFile: e.files[0].rawFile
    });
}

On the spreadsheet component, once the $onChanges determines that the input changed is for the newDataFile, it calls a function to import that file.  The options for the spreadsheet and the code to import the file as as follows:

this.dataModel = {
    id: "id",
    fields: {
        id: { editable: false, type: "number" },
        // Other fields...
        name: { editable: false },
        code: { editable: true, type: "number" }
    }
}

this.newDataSource = new kendo.data.DataSource({
    schema: {
        model: this.dataModel
    }
});

this.spreadsheetOptions = {
    toolbar: false,
    sheets: [{
        dataSource: this.newDataSource
    }]
};

function importExcelFile(newDataFile) {
    var deferred = $q.defer();
    var spreadsheet = $scope.spreadsheet;
    spreadsheet.fromFile(newDataFile)
        .then(function () {
            deferred.resolve();
        });
    return deferred.promise;
}

Is there something more that I need to add to get this to function correctly with the rows?  It IS working on your demo page.  I should note that the first row is ignoring the styling from the original spreadsheet, too, in my code.  

Thanks!

[Stefan]

Hello Christy,

The async.saveUrl property of the Kendo UI Upload is used to set the URL of the handler that will receive the submitted files:

http://docs.telerik.com/kendo-ui/api/javascript/ui/upload#configuration-async.saveUrl

http://demos.telerik.com/aspnet-mvc/spreadsheet/server-side-import-export

The provided code snippets seem to be correct.

Also, please share with us whether the file is not imported, or only part of the document gets imported?
 

There are many different factors that can interfere with the widgets behaviour. To be able to deeply investigate the issue, we will need a runnable example. 

Regards,
Stefan
Telerik by Progress

 

 

[Christy]

Okay.  This is a demo project that passes the uploaded file from the upload widget to the spreadsheet widget.  I removed everything that appeared to be unnecessary and merged everything into one controller for the purpose of the demo.  

If you upload a file that has more than 200 rows, it will cut off all rows from row 200 to the final row.  The other thing you will notice is that the font and color of the cells is not the same as in the excel file (although the background color is fine).  I'm assuming these are somehow related to whatever is going on to cause the file to load incorrectly.

Please let me know what I need to add.  We are make or break on this in the next couple days, and missing the rows is a definite break.  Thanks!!

[Stefan]

Hello Christy,

Thank you for the runnable example.

I tested the application, and it is working as expected. I made a video demonstrating its behaviour. As shown in the video, the default rows are 200, when I upload the spreadsheet "test 1", the rows are 5611. When I upload the file "test 2" where the rows are less than 200, the rows are back to their default number 200. Also, the first file is with special background colour and font, which are imported correctly:

http://screencast.com/t/SZWdpAWqHc

I can only assume that the issue is caused by the imported files, as the application is working as expected.

Regards,
Stefan
Telerik by Progress

 

 

[Christy]

Thank you for checking this out! The demo files I am using are ones created by exporting data from my Kendo UI Grid with the export to Excel feature. I do have a bit of manipulation on this export, but the resulting file is still a normal Excel file and includes all the rows. The rows are not missing until I upload to the Spreadsheet.  The manipulation is shown below:

excel: {
    fileName: "myExport.xlsx",
    allPages: true
},
excelExport: function (e) {
    var sheet = e.workbook.sheets[0];
    for (var i = 1; i < sheet.rows.length; i++) {
        var row = sheet.rows[i];
        // The data item index is 0-based, while the row is 1-based
        if (e.data[i - 1].aliasId === e.data[i - 1].id) {
            row.cells[3].value = '';
        } else {  // show the alias
            row.cells[3].value = e.data[i - 1].aliasName;
        }
    }
},

I also tried using the Excel file that is on the Spreadsheet Server Import/Export demo, although that one has less than 200 rows. Even still, the font formatting and color is lost on upload.  

[Christy]

I found a 'solution' to the problem.  It seems that the exported files must not have the data in quite the correct format for the upload.  If I open the exported file in Excel and click save, they are able to be uploaded with all rows.  The file size also increases.  

Thanks for looking into this, though!

[Stefan]

Hello Christy,

I'm glad to hear that the issue is resolved, and the file import works correctly.

Regards,
Stefan
Telerik by Progress