Export excel and csv with clickable link (single and multiple urls) using angular slickgrid

Question

I am using angular slickgrid to export excel and csv, and also try to export single & mutiple url values against single cell. Any solution to format cell as hyperlink for single and multiple urls.

Code which I wrote for export

import { Component, OnInit } from '@angular/core';
import { GridOption, FieldType, FileType, DelimiterType, ExcelExportOption } from '@slickgrid-universal/common';
import { ExcelExportService } from '@slickgrid-universal/excel-export';
import { TextExportService } from '@slickgrid-universal/text-export';

@Component({
    selector: 'OrganisationInfoSearchList',
    templateUrl: 'OrganisationInfoSearchList.html'
})

export class OrganisationInfoSearchList implements OnInit {

    constructor(public excelExportService: ExcelExportService, public textExportService: TextExportService) { }

    public gridOptions: GridOption = {
        enableExcelExport: true,
        enableExport: true,
        gridMenu: {
            hideExportExcelCommand: true,
            hideExportCsvCommand: true,
            dropSide: 'right',
            commandItems: [{
                command: "cspfm-excel-export",
                titleKey: "EXPORT_TO_EXCEL",
                iconCssClass: "fa fa-file-excel-o",
                action: (event, callbackArgs) => {
                    let excelExportOptions: ExcelExportOption = {
                        filename: 'OrganisationInfoSearchList',
                        format: FileType.xlsx
                    }
                    return this.excelExportService.exportToExcel(excelExportOptions).catch(error => {
                        alert(error.message);
                        return error;
                    });
                }
            }, {
                command: "cspfm-csv-export",
                titleKey: "EXPORT_TO_CSV",
                iconCssClass: "fa fa-download",
                action: (event, callbackArgs) => {
                    let exportOptions: any = {
                        filename: 'OrganisationInfoSearchList',
                        format: FileType.csv,
                        delimiter: DelimiterType.comma
                    }
                    return this.textExportService.exportToFile(exportOptions).catch(error => {
                        alert(error.message);
                        return error;
                    });
                }
            }],
        },
        exportOptions: {
            exportWithFormatter: true
        },
        excelExportOptions: {
            exportWithFormatter: true,
            sanitizeDataExport: false
        },
        enableTextExport: true,
        textExportOptions: {
            exportWithFormatter: true,
            sanitizeDataExport: false
        },
        registerExternalResources: [this.excelExportService, this.textExportService]
    }

    public columnDefinitions = [{
        id: 'name',
        name: 'Name',
        field: 'name',
        type: FieldType.string,
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? value : '');
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? `<span title='${value}'>${value}</span>` : '');
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer',
    }, {
        id: 'foundedon',
        name: 'Founded on',
        field: 'foundedon',
        type: FieldType.date,
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? value : '');
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? `<span title='${value}'>${value}</span>` : '');
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer'
    }, {
        id: 'category',
        name: 'Category',
        field: 'category',
        type: FieldType.string,
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? value : '');
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? `<span title='${value}'>${value}</span>` : '');
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer'
    }, {
        id: 'income',
        name: 'Income',
        field: 'income',
        toolTip: 'Income',
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? value : '');
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? `<span title='${value}'>${value}</span>` : '');
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer'
    }, {
        id: 'officialwebsite',
        name: 'Official Website',
        field: 'officialwebsite',
        toolTip: 'Official Website',
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? value.split('~~') : '');
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? `<span title='${value}'>${value}</span>` : '');
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer'
    }, {
        id: 'additionalwebsite',
        name: 'Additional Website',
        field: 'additionalwebsite',
        type: FieldType.unknown,
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? value.split('~~') : '');
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value ? `<span title='${value}'>${value}</span>` : '');
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer'
    }, {
        id: 'currentlyactive',
        name: 'Currently Active',
        field: 'currentlyactive',
        type: FieldType.string,
        exportCustomFormatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return (value);
        },
        exportWithFormatter: true,
        formatter: (row: number, cell: number, value: any, columnDef: any, dataContext: any, grid: any) => {
            return `<span title='${value}'>${value}</span>`;
        },
        cssClass: 'left-align cs-slickgrid-inline-edit-pointer'
    }]
    
    /* Sample dataset */
    public dataset = [{
        'name': 'org1',
        'foundedon': '21/Jun/2002',
        'category': 'Government',
        'income': '₹10,000.00',
        'officialwebsite': 'https://www.google.com',
        'additionalwebsite': 'https://www.google.com~~www.org1.com',
        'currentlyactive': false
    }]
}

Existing export file:

Excel	CSV

Version I have used:

npm	Version
@angular/cli	13.3.9
angular-slickgrid	4.3.1
@slickgrid-universal/common	1.4.0
@slickgrid-universal/excel-export	1.4.0
@slickgrid-universal/text-export	1.4.0

Any solution to format cell as hyperlink for single and multiple urls.

Expected export file:

Excel	CSV

I posted an answer, if that helps then please upvote and/or accept the answer so that we know it's helpful — ghiscoding, Jul 19 '23 at 18:40

ghiscoding · Answer 1 · 2023-06-27T15:21:33.123

Note that I'm the author of Angular-Slickgrid

The Excel export uses the excel-builder.js library which is unfortunately no longer being supported and its website is gone but you can still find the documentation on the WayBack Machine, what you will need is an hyperlink formula and on the WayBack Machine we can find this excel-builder formula from this link: excel-buider formulas. I found that we need a formula by searching the internet and found this other SO answer A complete example using excel-builder.js which has a demo about hyperlink

Now that we know that a hyperlink formula is required, you can then go to the Angular-Slickgrid Wikis and read the Excel Export (cell value parser) - Wiki which shows how to provide your own custom parser that will be required for this special use case.

If I modify one of the Angular-Slickgrid example and change the data so that every 5th cell (% 5) has a link, we can then add certain code logic to detect if it's an hyperlink found then we will return the hyperlink formula or else return the regular text data. So our logic could look like this

this.columnDefinitions = [
{
    id: 'description', name: 'Description', field: 'description', filterable: true, sortable: true, minWidth: 80,
    formatter: Formatters.hyperlink,
    excelExportOptions: {
      valueParserCallback: (data, col, excelFormatterId, excelStylesheet) => {
        if ((data as string).startsWith('http')) {
          // const url = 'https://www.microsoft.com';
          const url = data;
          const text = 'Visit Microsoft';
          return { value: `HYPERLINK("${url}", "${text}")`, metadata: { type: 'formula' } };
        }

        // or else return regular data
        return data;
      }
    }
  },
];


randomData() {
  // ...

  // display a link every 5th row
  description: (i % 5) ? 'desc ' + i : 'https://www.microsoft.com',
}

This logic can be updated to best fit whatever you want to achieve, for example you could change the logic to turn any text into an hyperlink directly. Also note that data argument is equal to the cell content value, and the 2nd col argument is the column definition.

If we execute the code above, we then get the result you're looking for

However please note that hyperlink in Excel seems to be detected as a security risk and they might not be showing up directly when opening the Excel file, at least they don't show on my side unless I click on the Editing button, I see this at first

You might also get some other security errors like the one below, for that this Microsoft blog answer might be helpful https://answers.microsoft.com/en-us/msoffice/forum/all/excel-hyperlink-security-warning/054c401c-1051-4aa8-99f7-9a67f4919e8c

Conclusion

So this should help you make it happen but I'm not sure if it should really be used considering that Excel throws a lot of warning about potential security issue. I mean your users might get all these warnings and might decide to not open about your Excel file for that reason... Anyway, you now have the answer and it's up to you to decide if you really want to use it or not

Export excel and csv with clickable link (single and multiple urls) using angular slickgrid

1 Answers1

Conclusion