Export Links To Excel
Environment
| Product | Progress® Kendo UI® Grid for jQuery |
| Operating System | All |
| Browser | All |
| Browser Version | All |
Description
I have a Grid column that contains a hyperlink. The link is clickable in the Grid, however when I export the data to Excel I can no longer click it. I want to be able to click on the exported links, how do I do that?
Solution
The Workbook API exposes a configuration that enables you to set hyperlinks to specific cells. You can combine this configuration with the excelExport event of the Grid to make the links clickable in the exported file.
- Configure a column with links.
- Attach the
excelExportevent to the Grid. - Generate the
hyperlinksarray by using information from the already generated workbook.
<div id="grid"></div>
<script>
$("#grid").kendoGrid({
toolbar: ["excel"],
columns: [
{ field: "name" },
{ field: "myLink", template: ({ myLink }) => `<a href="${myLink}">${myLink}</a>` }
],
dataSource: [
{ name: "Jane Doe", myLink: "https://google.com" },
{ name: "John Doe", myLink: "https://facebook.com" }
],
excelExport: function(e) {
let columnIndex = 1, // The myLink column is in the first index.
colName = getColumnName(columnIndex), // We need the excel column name to use as a reference. E.g. A, B, C, D, AA, AB, AC, BA, BB, etc.
sheet = e.workbook.sheets[0];
// Create a hyperlink for each cell under the "link" column.
sheet.hyperlinks = constructHyperlinks(sheet.rows, columnIndex, colName);
}
});
function constructHyperlinks(rows, colIndex, colName) {
const result = [];
rows.forEach((row, i) => {
if (row.type === "data") {
const value = row.cells[colIndex].value,
colRef = colName + (i + 1); // A1,B1,C1, etc. are the headers. We want to start from A2, B2, etc.
result.push({ ref: colRef, target: value });
}
});
return result;
}
// https://stackoverflow.com/a/8241071
// This is an optional method that enables you to convert the current Grid column index to a corresponding Excel column name - A, B, C ... AA, AB ... BA, BB ... CA, CC, etc.
function getColumnName(n) {
var ordA = 'A'.charCodeAt(0);
var ordZ = 'Z'.charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while(n >= 0) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
}
</script>