Exporting Links To Excel
| Product | Grid for Progress® Telerik® UI for ASP.NET MVC |
Description
I have a Telerik UI for ASP.NET MVC 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 interact with it. I want to be able to click on the exported links.
Solution
The Workbook API exposes a configuration that enables you to set hyperlinks to specific cells.
To achieve the desired outcome:
- Configure a column with by using the Template component.
- Subscribe to the
ExcelExportevent of the Grid. - Within the handler, generate the
hyperlinksarray by using information from the already generated Workbook. -
(Optional) Define an optional method that enables you to convert the current Grid column index to a corresponding Excel column name. For example:
A, B, C ... AA.
@{
var data = new List<GridModel>()
{
new GridModel
{
Name = "Jane Doe",
Link = "https://google.com"
},
new GridModel
{
Name = "Jane Doe",
Link = "https://youtube.com"
}
};
}
@(Html.Kendo().Grid<GridModel>()
.Name("grid")
.ToolBar(toolbar => toolbar
.Excel()
)
.Columns(columns =>
{
columns.Bound(column => column.Name);
columns.Bound(column => column.Link).ClientTemplate(Html.Kendo().Template()
.AddHtml(@<text>
<a href="${data.Link}">${data.Link}</a>
</text>)
);
})
.DataSource(dataSource => dataSource
.Ajax()
.Read(read => read.Action("Read_Data", "Grid"))
)
.Events(events => events.ExcelExport("onExcelExport"))
.BindTo(data)
)
public class GridModel
{
public string Name { get; set; }
public string Link { get; set; }
}
<script>
function onExcelExport(e) {
let columnIndex = 1, // The Link 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>