Hi,

I have identified a discrepancy in behavior of Telerik Spreadsheet vs Excel in treatment of array formulae (multiplication of arrays)

The spreadsheet attached contains two examples on how the difference on how Excel and Kendo spreadsheets handle array formulas is impacting the tool I'm developing.

The first example is a simple multiplication between a one-column range and a two-column range. Since both have the same number of Rows, Excel multiples both columns of Array 2 by the Array 1 and sum them
Kendo instead expects both ranges to have the same dimensions, so the same array formula throws an error when opened on Kendo


The second example is a the sum of a multiplication between two same-sized matrixes, but conditioned to a flag array. If the flag for that row is true, then the multiplication of the elements of that row should be added to the final result. On Excel, it works as expected.
On the other hand, Kendo seems to consider the “Flag” matrix as a 2-column matrix with the second column left blank, so the result is the multiplication of the first column of array 1 by the first column of array two, conditioned by the flag array:

You can check the results uploading the attached spreadsheet to the Demo available online: https://demos.telerik.com/kendo-ui/spreadsheet/index

Please let me know your feedback and when/if we can expect an alignment of Spreadsheet behavior to Excel's

Kind regards
Andrea

Bug report

If there is a Spreadsheet on a given page and we scroll the page down using the mouse wheel, once the scrolling stops over the Spreadsheet and we try to continue it, we cannot do it. If the Spreadsheet has defined rows that are not currently visible when the mouse wheel is moved, then Spreadsheet is being scrolled, not the current page.

If the Spreadsheet has a lower number of pre-defined rows that are all displayed on the component's initialization the scrolling is again not applying to the page. Instead, moving the mouse wheel doesn't do anything until the mouse cursor is moved outside the Spreadsheet.

Reproduction of the problem

1. Open this Dojo example and run it
2. Move the mouse cursor in the middle of the pane displaying the compiled code.
3. Use the mouse wheel to scroll down the page and stop the scrolling once the cursor is in the zone of the Spreadsheet
4. Scroll down again

Current behavior

Because the cursor is inside the Spreadsheet the page cannot be scrolled. If the rows configuration is commented, when the scrolling is started again, the Spreadsheet data will be scrolled.

Expected/desired behavior

If all the data in the Spreadsheet is displayed, moving the mouse wheel over the component should result in page scrolling. If there is data in the Spreadsheet, there should be some way to prevent the scrolling inside the component.

Environment

• Kendo UI version: 2020.1.114
• jQuery version: x.y
• Browser: [all]

Hello,

 I've been reading through the documentation, and I wanted to reach out in case I am missing something.  In all the Kendo Spreadsheet documentation that is available, I've not seen an MVVM example provided.  Is the MVVM method available with use for the Spreadsheet widget?   I was also wondering if just syntactically I might not have the right items configured in order for the data to populate in the spreadsheet.  I can confirm at the viewmodel that my data is populated in that object, but I just can't seem to get the data to appear in the spreadsheet.  Any guidance or additional documentation would be most helpful.

I am setting the data-role="spreadsheet", mimicking other examples of mvvm that I've seen.  I've also set the data-bind to my dataSource (data-bind="source:spreadsheetDataSource" while also populating a dataSource object within my data-sheets property.  I wanted to verify that these are in fact supported before I continue down this road.  I would also like to understand which properties are required by default (data-columns, data-rows, data-sheets) etc...and the properties that exist within the arrays (such as data-sheets), since that also doesn't seem to be present in the documentation. 

I'd also like to point out that I am using a windows server 2016, since that option was not listed in the fields below for the ticket.  The .NET Framework is 4.5.2

I appreciate all assistance in this matter. 

Hi Team,

I am using a kendospreadsheet. I want to find the rownumber of last non blank cell for column C.

When i try the =SUMPRODUCT(MAX(($C$1:$C100<>"")*(ROW($C$1:$C100)))) in excel it works.

The same formula does not work in kendospreadsheet.

Please suggest if this is a bug or if formula needs any change to fit the kendospreadsheet.

Regards

Bharathy B

Hi There,

I am facing one issue in kendo spreadsheet onchange event.

Suppose I have applied formula on one cell A1 and formula is =SUM(B1:B3)

Now one change of values in B1 to B3 on change event gets fired but whenever cell A1 values gets updated i.e. on entering value in B1 to B2, on change event doesn’t get fired.

How can I make onchange event fire for formula cell.

Thank you,

Rakesh

We are looking to see whether all the cells in the formula are filled or not  for that we need all the cells information in the formula. 

Is there any way to get list of cells present in formula cell.

Bug report

Regression introduced in R3 2019.

Reproduction of the problem

Open this Spreadsheet demo. Even though the Toolbar configuration is set to false, it is not hidden. This applies only for ASP.Net Core and ASP.Net MVC.

Current behavior

The Toolbar is visible when set to false.

Expected/desired behavior

The Toolbar should be hidden.

Environment

• Kendo UI version: 2019.3.917
• Browser: [all]

When using the URL feature of the spreadsheet it seems to use "_blank" as the target (opens in a new window).

My spreadsheet is in a single page application with some javascript already loaded.

I'd like to have the url be something like "javascript:myfunction('test');" which works well even with an a tag "<a href='javascript...'>"

I do this quite a lot with templates on the Grid control.

Not asking for templates on the spreadsheet, just let us specify the target and/or use local javascript functions in an "a href";

The Workbook.FromDocument() method freezes the panes based on the coordinates of the TopLeftCellIndex of the pane instead of the XSplit and YSplit properties of the pane. Reproduced with the attached Excel file

• https://github.com/telerik/kendo/blob/master/dpl/Telerik.Web.Spreadsheet/Models/Workbook.FromDocument.cs#L50

var pane = documentWorksheet.ViewState.Pane;
if (pane != null && pane.State == PaneState.Frozen)
{
    sheet.FrozenRows = pane.TopLeftCellIndex.RowIndex;
    sheet.FrozenColumns = pane.TopLeftCellIndex.ColumnIndex;
}

Solution:

var pane = documentWorksheet.ViewState.Pane;
if (pane != null && pane.State == PaneState.Frozen)
{
    sheet.FrozenRows = pane.YSplit;
    sheet.FrozenColumns = pane.XSplit;
}

WORKAROUND: Loading the Excel file with DocumentProcessingLibrary API and change the pane

var fileName = <YourFilePathHere...>;
if (!File.Exists(fileName))
{
    throw new FileNotFoundException(String.Format("File {0} was not found!", fileName));
}

Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook;
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();

using (Stream input = new FileStream(fileName, FileMode.Open))
{
    workbook = formatProvider.Import(input);
    foreach (var sheet in workbook.Worksheets)
    {
        if (sheet.ViewState.Pane != null && sheet.ViewState.Pane.State == Telerik.Windows.Documents.Spreadsheet.Model.PaneState.Frozen)
        {

            var originalPane = sheet.ViewState.Pane;
            var pane = new Pane(new CellIndex(originalPane.YSplit, originalPane.XSplit), originalPane.XSplit, originalPane.YSplit, originalPane.ActivePane);
            sheet.ViewState.Pane = pane;
        }
    }
}
var sheets = Telerik.Web.Spreadsheet.Workbook.FromDocument(workbook).Sheets;

Currently, only the List and Number validation rules are supported when using Workbook.ToDocument();

• https://github.com/telerik/kendo/blob/master/dpl/Telerik.Web.Spreadsheet/Models/Workbook.ToDocument.cs#L221 

It happens when the type of validation is text or custom 

for example:

If I saved :

"validation": {
                "type": "reject",
                "comparerType": "custom",
                "dataType": "custom",
                "from": "AND(LEN(A2)<3)",
                "showButton": true,
                "allowNulls": true
              }

I get back:

"validation": {
                "type": "reject",
                "comparerType": "equalTo",
                "dataType": "number",
                "from": "=AND(LEN(A2)<3)",
                "to": "\"=\"",
                "allowNulls": true
              }

It happens also when the comparer type is text - it saves it as a number

Also when I use the create validator with your UI and choose a custom formula it can't be saved because the compare type is null. As a result, I must define manually the custom validator

Bug report

Reproduction of the problem

Reproducible in the demos:

1. Create a second sheet.
2. Select the new sheet ("Sheet1") and scroll the horizontally to column U. Add a value to the U1 cell.
3. Select the first sheet ("Food Order").
4. Select A17 and enter the following value: =SUM(C5,
5. Select "Sheet1" and try to navigate to U1 either through the keyboard or through scrolling the sheet.

Current behavior

A popup with an error message appears.

Expected/desired behavior

The user should be able to navigate to the desired cell, in order to add it as a reference to the formula.

Environment

• Kendo UI version: 2021.2.511
• jQuery version: x.y
• Browser: [all]

Bug report

Reproduction of the problem

If you want to have only some of the default tools, you cannot do that with the Spreadsheet wrapper.

1. Declare the tools you want, e.g.,

.Toolbar(t =>
{
	t.Home(h =>
	{
		h.ExportAs();
	});
})

Current behavior

All the tools are displayed.

Expected/desired behavior

Only the ExportAs tool is displayed.

Environment

• Kendo UI version: 2021.3.914
• jQuery version: x.y
• Browser: [all ]

Bug report

Reproduction of the problem

Dojo example: https://dojo.telerik.com/UWicoqeJ

1. Focus an empty cell and paste the following formula: =IF(AND(H4-I4>=B4, H4+I4<=C4), "OK", "NO OK")

Current behavior

The cell will display: "NO OK"

Expected/desired behavior

The cell should display: "OK" as it does in Excel.

Environment

• Kendo UI version: 2022.3.1109
• jQuery version: x.y
• Browser: [all]