This is a migrated thread and some comments may be shown as answers.

Insert new empty row

5 Answers 1127 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Mydatafactory
Top achievements
Rank 1
Mydatafactory asked on 09 Feb 2017, 12:22 PM

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.

 

5 Answers, 1 is accepted

Sort by
0
Ivan Danchev
Telerik team
answered on 10 Feb 2017, 09:34 AM
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
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Mydatafactory
Top achievements
Rank 1
answered on 10 Feb 2017, 10:22 AM

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.

0
Ivan Danchev
Telerik team
answered on 14 Feb 2017, 09:01 AM
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
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Anthar
Top achievements
Rank 1
answered on 13 Apr 2017, 01:37 PM

 

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." 
    });

0
Ivan Danchev
Telerik team
answered on 14 Apr 2017, 01:02 PM
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
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Tags
Spreadsheet
Asked by
Mydatafactory
Top achievements
Rank 1
Answers by
Ivan Danchev
Telerik team
Mydatafactory
Top achievements
Rank 1
Anthar
Top achievements
Rank 1
Share this question
or