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

Customize raw data to export to make it hyperlink in excel file

3 Answers 1252 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Guillaume
Top achievements
Rank 1
Guillaume asked on 15 Dec 2020, 11:13 AM

Hello,

 

I'm trying when exporting a Grid to excel file to format some columns raw content into hyperlink text. 

 

here is the implementation of my excelExport function:

 

01.function excelExport(e) {
02.        if (!exportFlag) {
03.            e.sender.showColumn(3);
04.            e.sender.showColumn(4);
05.            e.sender.showColumn(5);
06.            exportFlag = true;
07. 
08.            var sheet = e.workbook.sheets[0];
09.            var data = this.dataSource.view();
10. 
11.            for (var i = 0; i < data.length; i++) {
12.                sheet.rows[i + 1].cells[3].formula = "=HYPERLINK(" + data[i].LinkExportPdf + ", Export Pdf " + data[i].Id + ")";
13.                sheet.rows[i + 1].cells[4].formula = "=HYPERLINK(" + data[i].LinkExportPpt + ", Export Ppt " + data[i].Id + ")";
14.            }
15.            e.preventDefault();
16. 
17.            setTimeout(function () {
18.                e.sender.saveAsExcel();
19.            });
20.        } else {
21.            e.sender.hideColumn(3);
22.            e.sender.hideColumn(4);
23.            e.sender.hideColumn(5);
24.            exportFlag = false;
25.        }
26.    }

 

What i tried to do first is to assign direct the value by changing the cell value which doesn't provide any result, 2nd problem, when debugging the columns 3, 4 and 5 aren't into my sheet object even with the showColumn and when downloading the excel file, like any of the operation above isn't take in consideration.

 

My Grid set Events like this:

1..Events(e =>
2.{
3.     e.DataBound("dataBound");
4.     e.ExcelExport("excelExport");
5.})

 

The excel setting is like this:

 

1..Excel(excel => excel
2.       .FileName("ExcelFileName.xlsx")
3.       .AllPages(false)
4.       .ProxyURL(Url.Action("BackOfficeExportFunction", "Grid"))
5.)

 

Thanks in advance for your help !

3 Answers, 1 is accepted

Sort by
0
Tsvetomir
Telerik team
answered on 17 Dec 2020, 10:15 AM

Hi Guillaume,

Thank you for sharing the code snippets for the ExcelExport event. Indeed, the logic for the creation of the hyperlink should be transitioned within the "else" clause. This is due to the fact that the file will be built with visible columns. Also, on my side, the exported file was claimed as corrupted and I alternated the formula string as well:

<script>
    var exportFlag = false;
    function excelExport(e) {
        if (!exportFlag) {
            e.sender.showColumn(3);
            e.sender.showColumn(4);
            e.sender.showColumn(5);
            exportFlag = true;


            e.preventDefault();

            setTimeout(function () {
                e.sender.saveAsExcel();
            });
        } else {
            var sheet = e.workbook.sheets[0];
            var data = this.dataSource.view();

            var url = window.location.protocol
                + "//"
                + window.location.host
                + "/website/Google";

            for (var i = 0; i < data.length; i++) {
                sheet.rows[i + 1].cells[2].formula = '=HYPERLINK("' + url + '","' + data[i].Id + '")';
               sheet.rows[i + 1].cells[2].format = '[Blue]';
               sheet.rows[i + 1].cells[2].underline = true;
                sheet.rows[i + 1].cells[2].textAlign = 'right';
            }

            e.sender.hideColumn(3);
            e.sender.hideColumn(4);
            e.sender.hideColumn(5);
            exportFlag = false;
        }
    }
</script>

For your convenience, I am attaching the sample project where the functionality of interest can be observed.

 

Best regards,
Tsvetomir
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Guillaume
Top achievements
Rank 1
answered on 20 Dec 2020, 11:01 AM

Hello, 

Thanks for your answer, unfortunatly after trying your solution, there are improvments, the excel workbook is correctly modifying after export but with errors, the formula is deleted after each download, it is considered in error but if i had directly the exact same formula in the value and click to process it in excel it is processed well. So i'm wondering if the hyperlink formula is managed in KendoUI JQuery. 

 

Thanks in advance.

0
Tsvetomir
Telerik team
answered on 22 Dec 2020, 12:43 PM

Hi Guillaume,

The formula of the column is required to be in the correct format otherwise, Excel will consider it invalid. Perhaps, you could try using your own customization of the URL for the hyperlink. With what I have tested out locally, the hyperlink has to be set as follows:

var url = window.location.protocol
                + "//"
                + window.location.host
                + "/website/Google";

 sheet.rows[i + 1].cells[2].formula = '=HYPERLINK("' + url + '","' + data[i].Id + '")';

It would be very helpful if you could go ahead and modify the example attached to my previous example and replicate the faulty behavior there. I will then investigate it locally and get back to you with specific recommendations.

 

Kind regards,
Tsvetomir
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Tags
Grid
Asked by
Guillaume
Top achievements
Rank 1
Answers by
Tsvetomir
Telerik team
Guillaume
Top achievements
Rank 1
Share this question
or