Insert new empty row

[Mydatafactory]

For a project I'm currently working on I need my users to be able to add their own rows into an existing excel sheet. However, when I press the 'Insert row below' or 'Insert row above' buttons I get a message saying: "Cannot insert cells due to data loss possibility. Select another insert location or delete the data from the end of your worksheet." Afterwards I noticed that all excel sheets seem to have a fixed height.

I've tried these things:

• $("#spreadsheet").data("kendoSpreadsheet").activeSheet()._rows._count += 5;
• $("#spreadsheet").data("kendoSpreadsheet").activeSheet().insertRow(<current last row index>);
• $("#spreadsheet").data("kendoSpreadsheet").activeSheet().range(<current last row index>).values([''])

I'm not using a datasource, I'm filling the sheet using an AJAX call and the 'fromJSON' method. This is because I need to manipulate some of the data before sending it to the client.

 

[Ivan Danchev]

Hello,

Here's a dojo example, in which row insertion works correctly and the message you posted is not returned. Could you modify it accordingly so that it demonstrates the issue and link it back for further review?

Regards,
Ivan Danchev
Telerik by Progress

[Mydatafactory]

Hello Ivan,

When I use insertRow it returns an error that I can't shift cells of a worksheet. It seems the number of rows is at max the number of received rows, or 200.

I attached a screenshot showing that manually executing the commands does not work, but neither do the buttons for the user to insert a new row.

I changed your dojo example to show the problem: http://dojo.telerik.com/aguKo

As you can see all rows are filled. If I wanted to add a new row I'd press the 'Insert Row' or go to the 'Insert' tab and press the button there, but I receive an error right after it.

[Ivan Danchev]

Hello,

The default number of rows in a sheet  is 200 and is left unchanged by calling insertRow. The spreadsheet shifts the empty rows when the insertRow method is called so if the sheet's last row contains data attempting to shift it will throw an exception, because the Spreadsheet attempts to shift it and that data will be lost on shifting, i.e. it won't move it to a row number 201.
If you want to have more rows in a sheet than the default number you can insert a new sheet and specify the number of rows it will contain. For more details you can see Dimiter's post  in this forum thread.

Regards,
Ivan Danchev
Telerik by Progress

[Anthar]

 

I just added the code in your example to add date validation in two columns. After that, I am unable to insert the rows. giving error unable to insert data.

Waiting for solution.

var sheet = spreadsheet.activeSheet();
    sheet.range("B2:C").validation({
dataType: "date",
                             showButton: true,
                             comparerType: "between",
                             from: 'DATEVALUE("2/1/2017")',
                             to: 'DATEVALUE("2/28/2017")',
                             allowNulls: true,
                             type: "reject",
                             titleTemplate: "Start Date validation error",
                             messageTemplate: "startdate Date should be between year 1900 and 2100." 
    });

[Ivan Danchev]

Hello Anthar,

Specifying the range as "B2:C" prevents the C column from shifting and the row insertion will fail. If you set a range of column C cells that validation applies to, for example "B2:C20" (i.e. all C cells from C2 to C20) the insertion will succeed. Sample dojo.

Regards,
Ivan Danchev
Telerik by Progress