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

How to cleanse Kendo Grid data exported to an Excel spreadsheet

5 Answers 438 Views
Grid
This is a migrated thread and some comments may be shown as answers.
John
Top achievements
Rank 1
John asked on 29 Jul 2019, 09:38 AM

Hi all,

I'm looking to cleanse data exported from a Kendo Grid to an Excel Spreadsheet and I've not yet been able to find any way of doing this using the available Kendo Grid functions.

Does anyone know of a way to manipulate data exported from a Grid, before it's pushed into an Excel spreadsheet?

For example, I'm looking to escape Excel formula characters to make sure malicious code is not run on a client machine, when they open up an Excel spreadsheet (which has been filled with exported grid data). I need to replace characters such as equal to (=), plus (+), etc.

I'm currently using the Telerik.UI.for.AspNet.Core (2019.2.514) nuget package.

Many thanks in advance,

John

5 Answers, 1 is accepted

Sort by
0
Accepted
Alex Hajigeorgieva
Telerik team
answered on 01 Aug 2019, 07:28 AM
Hello, John,

The Kendo UI Grid emits an ExcelExport event when the built-in excel export is triggered. The event handler receives the generated workbook and you can modify it at this point like this:

.Events(events => events
       .ExcelExport("onExcelExport ")
 
function onExcelExport (e) {
     var workbook = e.workbook;
     var sheet = workbook.sheets[0];
     for (var i = 0; i < sheet.rows.length; i++) {
       for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
         if(typeof sheet.rows[i].cells[ci].value === "string"){
            // replace the undesired literals with another or just empty string
            sheet.rows[i].cells[ci].value = sheet.rows[i].cells[ci].value.replace("'", "*");
         }
       }
     }
   },

Here is a runnable Dojo for your reference: https://dojo.telerik.com/@bubblemaster/uBiyoDAw

Kind Regards,
Alex Hajigeorgieva
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
John
Top achievements
Rank 1
answered on 02 Aug 2019, 11:06 AM

Hi Alex,

That's fantastic, thank you!

Have quickly confirmed that I can configure the event and call it.

Many thanks,

John

0
John
Top achievements
Rank 1
answered on 02 Aug 2019, 01:16 PM

Hi Alex,

Thanks again for your help!

We've also been looking into a server-side solution too and just wondered if you could help validate the approach please?

We're looking at forcing the user down the proxy url route. The user will be sent to the proxy url endpoint and will be prompted to download an Excel spreadsheet, that has also had it's data sanitised.

Does that sound plausible too?

Many thanks,

John

0
John
Top achievements
Rank 1
answered on 02 Aug 2019, 01:29 PM

Sorry Alex, just wanted to add to my latest post:

Could you confirm your recommended way to identify an Excel export vs a grid refresh, please?

0
Alex Hajigeorgieva
Telerik team
answered on 07 Aug 2019, 12:03 PM
Hi, John,

Just to clear any confusion:

- Using the ForceProxy setting that is built-in will still create the workbook on the client (so it will be clean already by the time it gets to the server)
- Using a server-side export is not yet possible but we are in development of this item and the excel export is the last to join the ASP.NET Core suite for server side processing:

https://feedback.telerik.com/document-processing/1356226-document-processing-provide-version-for-net-core

As far as making a distinction between the excelExport and the grid refresh - the grid read triggers the DataBound() event and the excelExport does not. 

Let me know in case further information is needed.

Regards,
Alex Hajigeorgieva
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Grid
Asked by
John
Top achievements
Rank 1
Answers by
Alex Hajigeorgieva
Telerik team
John
Top achievements
Rank 1
Share this question
or