Spreadsheet component throwing error

[Jaspreet]

hi,

Spreadsheet component is throwing an error while opening the attached file using fromFile method -> https://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet/methods/fromfile#:~:text=Clears%20the%20spreadsheet%20and%20populates,recent%20version%20of%20other%20browsers.

 

 If I remove the filters than it works fine.

Can you please check.

 

Regards,

Jaspreet 

[Veselin Tsvetanov]

Hi Jaspreet,

Similarly to the previously discussed file, this file has a huge filter definition:

		<autoFilter ref="A2:U878843" xr:uid="{E1303B30-5FBA-4139-94BD-53FD77FAD840}">
			<sortState ref="A3:U878843"
				xmlns:xlrd2="http://schemas.microsoft.com/office/spreadsheetml/2017/richdata2">
				<sortCondition ref="F2:F878843"/>
			</sortState>
		</autoFilter>

that goes up to row number 878 843. This huge number of cells could not be processed in the Spreadsheet.

[Jaspreet]

Hi Veselin,

The new error  we faced is different and not from the attached file. Since we are facing different error for different files, so want to check with you if there is any validator that is recommended which can be used before opening file in spreadsheet or is there any option that spreadsheet provides a better way of handling these scenarios.

Error in new file (Couldn't attach as it got some confidential data)

Following error was found while debugging kendo js.

{
"context": "reading user-defined name: auto_create_refresh",
"error": "External reference not supported (input: [1]Settings!$B$8)"
}

Regards,

Jaspreet

 

[Veselin Tsvetanov]

Hi Jaspreet,

There is no validator that would allow you to check the files before opening them in the Spreadsheet widget. As per the better error handling, there is the following feature request:

https://feedback.telerik.com/kendo-jquery-ui/1440200-alert-error-and-unfreeze-spreadsheet-when-loading-corrupt-xlsx-file

I would suggest you cast your vote for it, as we are constantly monitoring the demand.

Concerning the External reference not supported error, the xlsx file that is being loaded refers to an external workbook (another file). Such a reference is not supported in the Spreadsheet.

[Veselin Tsvetanov]

Hello Jaspreet,

Thank you for the file sent.

If you unzip that XLSX file and review the xl/worksheets/sheet1.xml definition, you will notice the following node:

	<autoFilter ref="A2:U878783" xr:uid="{E1303B30-5FBA-4139-94BD-53FD77FAD840}">
		<sortState ref="A3:U878783"
			xmlns:xlrd2="http://schemas.microsoft.com/office/spreadsheetml/2017/richdata2">
			<sortCondition ref="F2:F878783"/>
		</sortState>
	</autoFilter>

The above is the filter reference which is defined from row 2 (its header) to row 878 783 for columns A to U. Moreover, there is an active sortCondition for column F in the same range. That is a huge number of cells (almost 18.5 million cells) which the Kendo Spreadsheet fails to process. Having that said, in order to resolve the JS error observed, you will need to reduce the number of rows included in the filter/sort range. As an example, attached you will find a modified version of the initially sent file which reduces the filter up to row 200.

Regards,
Veselin Tsvetanov
Progress Telerik

[Jaspreet]

hi Veselin Tsvetanov, thank you for quick reply.

Is there a way to handle these kind of error while opening file . So that UI can show some useful errors to the user. As in this case the spreadsheet doesn't show anything and hangs.

Regards,

Jaspreet

[Veselin Tsvetanov]

Hi Jaspreet,

I am afraid that the Spreadsheet does not provide any means that would allow you to show a useful error message to the user. What I could suggest you is to cast your vote for the following feature request, which covers corrupt XLSX loading scenario:

https://feedback.telerik.com/kendo-jquery-ui/1440200-alert-error-and-unfreeze-spreadsheet-when-loading-corrupt-xlsx-file

Regards,
Veselin Tsvetanov
Progress Telerik

[Jaspreet]

hi Veselin, 

We are facing similar scenarios quite often where the file is not loaded properly in the spreadsheet and console error is thrown. Recently one of the error we got while opening xlsx is 

{
"context": "reading user-defined name: auto_create_refresh",
"error": "External reference not supported (input: [1]Settings!$B$8)"
}

We have tried Open XML SDK\V2.5\tool\OpenXmlSdkTool.exe to check the validity of the file and it doesn't show any error. Is there any other way that you can suggest where we can pre verify the files and show users some meaningful message instead of showing empty or incorrect data in the spreadsheet.

Regards,

Jaspreet