New to Kendo UI for jQuery? Download free 30-day trial

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.

  1. Configure a column with links.
  2. Attach the excelExport event to the Grid.
  3. Generate the hyperlinks array 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>

See Also

In this article