jQuery Datatables导出到excelHtml5 HYPERLINK问题 [英] jQuery Datatables export to excelHtml5 HYPERLINK issue

问题描述

我使用jQuery插件Datatables生成了一个Google脚本生成的网站。
我在使用Excel HYPERLINK导出到Datatables插件的Excel功能时出现问题。

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.

我想在导出的Excel文件中有一个可点击的超链接，所以我在Javascript中格式化我的链接如下：

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

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

生成Excel导出并且格式正常。但是它会显示上面的确切片段而不是可点击的链接。当我选择单元格并在定义上单击一次而不进行更改时，它会自动显示可单击的URL。

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?

推荐答案

我希望我的解决方案可以帮助某人将excel导出中的链接扩展到已经非常
有用的库。

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

经过几个小时的搜索，我发现很多人在这里寻找Excel导出链接的解决方案以及在Datatables的论坛。

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.

主要问题是默认导出仅考虑两种不同的格式。数字和inlinestring。
链接既不是inlinestring也不是数字，它是一个函数，需要typ str。

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. 您必须调整导出，已为此提供自定义选项。 https://datatables.net/extensions/buttons/examples/html5/excelTextBold。 html
   在此示例中，将考虑C列中的所有单元格。我们希望遍历所有单元格并在那里找到可能的URL。

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.

我们想用公式替换链接。默认情况下，它具有内联的单元格类型，必须替换为str类型和用作值的公式。
感谢Dzyann，他展示了它的工作原理。
https://datatables.net/forums/discussion/42097/can-you-export-a-table-and-format-a-cell-to-use-a-公式使用正交数据

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

要为链接加下划线，应提供格式[4]。
可用格式列表： https://datatables.net/reference / button / excelHtml5＃内置式

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

在neirda发现IE11在向$（this）添加非HTML对象时遇到问题后，必须找到另一个解决方案。相同的基础：< f> HYPERLINK

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

文件：buttons.html5.js

File: buttons.html5.js

行：1098

插入一个开关，为URL内容创建不同的Celle。 （作为公式，使用HYPERLINK）

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
                    } )
                }
            } )
        }
    } );
}

这篇关于jQuery Datatables导出到excelHtml5 HYPERLINK问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！