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

[Guillaume]

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 !

[Tsvetomir]

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

[Guillaume]

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.

[Tsvetomir]

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