jQuery Datatables export to excelHtml5 HYPERLINK issue

Question

I have a Google script generated website using the jQuery plugin Datatables. I'm having an issue with the export to Excel function of the Datatables plugin with Excel HYPERLINK.

I want a clickable hyperlink in my exported Excel file, so I format my link as follows in Javascript:

=HYPERLINK("photourl";"Photo 1")

The Excel export is generated and the format is fine. However it shows the exact above snippet instead of a clickable link. When I select the cell and click once on the definition without making a change it automatically displays the clickable URL.

Is there anything I can do to turn it into a clickable link?

Answer 1

Adding a Custom Style for Links in Excel Exports with DataTables

I wanted to enhance the implementation slightly. Initially, the approach worked for most cases, but for Office 365, the hyperlinks didn't display with blue text. They were visible but lacked the expected blue, underlined styling.

To address this, I created a new style as described in the DataTables documentation.

Here's the updated implementation:

window.DataTableStyle = {};

/**
 * Method that adds a style for a link to the general style template.
 * @see https://cdnjs.cloudflare.com/ajax/libs/datatables-buttons/2.2.0/js/buttons.html5.js
 */
window.DataTableStyle.formatExcelLinksWithStyle = function(xlsx) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var styles = xlsx.xl['styles.xml'];

    // Add a new font style (blue text with underline)
    var fonts = $('fonts', styles);
    var newFontIndex = fonts.children('font').length;

    fonts.append(`
        <font>
            <sz val="11"/>
            <color rgb="FF0000FF"/> <!-- RGB for blue color -->
            <name val="Calibri"/>
            <u/> <!-- Underline -->
        </font>
    `);

    // Update the font count
    fonts.attr('count', newFontIndex + 1);

    // Add a new style to cellXfs
    var cellXfs = $('cellXfs', styles);
    var newStyleIndex = cellXfs.children('xf').length;

    cellXfs.append(`
        <xf fontId="${newFontIndex}" applyFont="1"/>
    `);

    // Increment the style count
    cellXfs.attr('count', newStyleIndex + 1);

    // Apply the new style and hyperlink formula to cells
    $('row c', sheet).each(function() {
        let text = $('is t', this).text();
        const linkMatch = text.match(/<a href="([^"]+)">([^<]+)<\/a>/);

        if (linkMatch) {
            // Set type "str" for the formula
            $(this).attr('t', 'str');

            const url = linkMatch[1];
            const linkText = linkMatch[2].replace(/"/g, ''); // Remove quotes

            $(this).append('<f>' +
                'HYPERLINK("' + url + '", "' + linkText + '")' +
                '</f>');

            // Assign the new style
            $(this).attr('s', newStyleIndex);

            // Remove inlineStr to make the formula work
            $('is', this).remove();
        }
    });
};

Example usage in DataTables button configuration

'customize' =>
    /** @lang JavaScript */ '
    function customize(xlsx) {
        window.DataTableStyle.formatExcelLinksWithStyle(xlsx);
    }',

This code:

1. Adds a custom font style with blue text and underlining to the styles.xml file.
2. Updates the cellXfs section with the new style.
3. Applies the new style to cells containing hyperlinks using the HYPERLINK formula.

Answer 2

I hope my solution will help someone expand links in excel export to the already very helpful library.

After hours of searching, I found a lot of people looking for a solution for links in Excel export here and in the forum at Datatables.

Main problem is that the default export only two different formats are considered. Numbers and inlinestring. A link is neither a inlinestring nor a number, it is a function, witch need typ str.

In my search for a solution I find many helpful parts.

1. You have to adjust the export, the "customize" option is already provided for this. https://datatables.net/extensions/buttons/examples/html5/excelTextBold.html In this example, all cells in column C are considered. We want to loop over all cells and find possible URLs there.

2. We want to replace the links with the formula. By default, it has the cell type inlinesting, this must be replaced by the type str and the formula used as a value. Thanks to Dzyann, who shows how it works. https://datatables.net/forums/discussion/42097/can-you-export-a-table-and-format-a-cell-to-use-a-formula-using-orthogonal-data

3. To underline the link, it should be provided with the format [4]. List of available formats: https://datatables.net/reference/button/excelHtml5#Built-in-styles

My solution that works for my requirement:

    // (1.) customize export
    customize: function( xlsx ) {

        var sheet = xlsx.xl.worksheets['sheet1.xml'];

        // Loop over all cells in sheet
        $('row c', sheet).each( function () {

            // if cell starts with http
            if ( $('is t', this).text().indexOf("http") === 0 ) {

                // (2.) change the type to `str` which is a formula
                $(this).attr('t', 'str');
                //append the formula
                $(this).append('<f>' + 'HYPERLINK("'+$('is t', this).text()+'","'+$('is t', this).text()+'")'+ '</f>');
                //remove the inlineStr
                $('is', this).remove();
                // (3.) underline
                $(this).attr( 's', '4' );
            }
        });
}

---

---

---

---

UPDATE!! IE11

After neirda found out that IE11 had problems adding a non-HTML object to $ (this), another solution had to be found. same basis: <f> HYPERLINK

File: buttons.html5.js

Line: 1098

Inserted a switch that creates the Celle differently for URL content. (As a formula, with HYPERLINK)

// Formula HYPERLINK for http-content, 
// is a URL if: a) started first char of cell content and 
//      b) without blanks
// s:4 use to unterline
if (    (row[i].indexOf("http") === 0) 
    &&
    (row[i].indexOf(" ") < 0 )  ) {

    cell = _createNode( rels, 'c', {
        attr: {
            t: 'str',
            r: cellId,
            s: 4
        },
        children:{
            row: _createNode( rels, 'f', { text: 'HYPERLINK(\"'+text+'\",\"'+text+'\")' } )
        }
    } );
} else {
    // String output - replace non standard characters for text output
    cell = _createNode( rels, 'c', {
        attr: {
            t: 'inlineStr',
            r: cellId
        },
        children:{
            row: _createNode( rels, 'is', {
                children: {
                    row: _createNode( rels, 't', {
                        text: text
                    } )
                }
            } )
        }
    } );
}

Answer 3

Export in Execl is very tough job without using any server side language,but you can write XML code to export data table in xls formate i have some working example please find the code and file here

this is jquery plugin

and I am writing sample code to export the file

<html>
        <head>
            <meta charset="UTF-8">
            <link  rel="stylesheet"  href="css/chintanTableDesign_1.css"/>
            <title></title>
           <script src="http://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
             <script src="http://eportal.esparkbiz.com/asset/js/export_execl_js/jquery.battatech.excelexport.js" language="javascript" type="text/javascript"></script>

        </head>
        <body>
            <table class="tableChintan" width="100%">
                <tr>
                    <th colspan="10" style="font-size: 25px;text-align: center">
                        ABC Pvt. ltd.
                    </th>
                    <th>
                        <select id="type_account" name="type_account" onchange="GetFilter();">
                            <option value="ALL" >ALL</option>
                            <option value="AC" >AC</option>
                            <option value="CASH" >CASH</option>
                            <option value="PF" selected>PF</option>                        
                        </select>
                    </th>
                    <th>
                        <a id="btnExport" href="javascript:void(0);"> EXPORT</a>
                    </th>
                </tr></table>
            <table class="tableChintan" width="100%" id="tblExport">
                <tr>
                    <th>Employee Name</th>
                    <th>Month</th>
                    <th>BASIC</th>
                    <th>DA</th>
                    <th>HRA</th>
                    <th>TA</th>
                    <th>Sp Allownce</th>
                    <th>LEAVE ENCASH</th>
                    <th>abs_days</th>
                    <th>extra_days</th>
                    <th>PF EMP</th>
                    <th>PF COMP</th>
    <!--                <th>ESI EMP</th>
                    <th>ESI COMP</th>-->
                    <th>PT</th>
                    <th>TOTAL SAL CHEQUE</th>
                    <th>actual_sal </th>
                    <th>actual_sal WP</th>
                    <th>NA</th>
                    <th></th>

                </tr>
               </tr></table>       
     </table>
        </body>
    </html>

    <script type="text/javascript">
        $(document).ready(function () {
            $("#btnExport").click(function () {
                $("#tblExport").btechco_excelexport({
                    containerid: "tblExport"
                    , datatype: $datatype.Table
                });
            });

        });
</script>

PLease don't forgot to include your jquery.min.js

please do try if you want to forcefully rename file then let me know I have another jquery plugin for same

Enjoy !!!!!!!!!!!!

Answer 4

One solution is to use an expression in the Excel Hyperlink Formula Format, e.g.:

='=HYPERLINK("https://[my website].com/' & [identifier] &'","' & [Friendly Excel Value] & '")'

Then you will find that within Excel it doesn't automatically recognise the formula by default. To force recognition the easiest way is to Replace (Ctrl+H) All equals '=' with equals '='.

The link should then work.

http://office.microsoft.com/en-gb/excel-help/hyperlink-function-HP010062412.aspx

https://superuser.com/questions/448376/what-is-the-excel-hotkey-to-re-calculate-all-formula-in-sheet