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

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 2

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

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 !!!!!!!!!!!!