数据表导出到Excel并将数字列格式化为货币

I'm trying to export numeric data to Excel. The numeric formatting is as follows:

我正在尝试将数字数据导出到Excel。数字格式如下：

• Thousands grouping separator: "."
• 数千个分组分隔符：“。”
• Decimal point indicator: ","
• 小数点指示符：“，”
• Number of decimal points to show: "0"
• 要显示的小数点数：“0”
• Prefix: "$"
• 前缀：“$”

And the header and footer texts are formatted as bold.

页眉和页脚文本格式为粗体。

This is the table:

这是表：

<table id="idtablainforme_ventaporfamilia" class="table table-striped table-striped table-bordered nowrap dataTable">
    <thead>
        <tr>
            <th>
                <b>FAMILIA</b>
            </th>
            <th>
                <b>VENTA</b>
            </th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th><b>TOTAL</b></th>
            <th><b>$12.925.150</b></th>
        </tr>
    </tfoot>
    <tbody>
        <tr><td>CHEQUERA MUJER</td><td>$5.231.760</td></tr>
        <tr><td>CARTERA</td><td>$3.487.630</td></tr>
        <tr><td>BILLETERA MUJER</td><td>$2.155.120</td></tr>
        <tr><td>NECESER</td><td>$21.980</td></tr>
        <tr><td>COSMETIQUERA</td><td>$10.990</td></tr>
        <tr><td>SET DE VIAJE</td><td>$10.990</td></tr>
    </tbody>
</table>

This is my try:

这是我的尝试：

<script>
    $('#idtablainforme_ventaporfamilia').DataTable({
        destroy: true,
        "searching": false,
        "paging": false,
        "ordering": false,
        "info": false,
        "autowidth": false,
        columns: [
           { data: "1", render: $.fn.dataTable.render.text() },
           { data: "2", render: $.fn.dataTable.render.number('.', ',', 0, '$') }
        ],
        footerCallback: function (tfoot, data, start, end, display) {
            var $th = $(tfoot).find('th').eq(1);
            $th.text($.fn.dataTable.render.number('.', ',', 0, '$').display($th.text()))
        },
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excel',
                footer: true,
                title: 'INFORME DE VENTAS POR FAMILIA',
                text: '<i class="fa fa-file-excel-o"></i>',
                titleAttr: 'Exporta a EXCEL',
            }
        ]
    });
</script>

But it doesn't convert the numeric data, and the header and footer texts are not formatted as bold. They are exported in Excel as string, except for the values 21.98 $, 10.99 $ and 10.99 $ (although they should be $21.980, $10.990 and $10.990) as follows:

但它不转换数字数据，页眉和页脚文本格式不是粗体。它们作为字符串在Excel中导出，除了值21.98 $，10.99 $和10.99 $（尽管它们应该是$ 21.980，$ 10.990和$ 10.990），如下所示：

FAMILIA         VENTA
CHEQUERA MUJER  $5.494.310
CARTERA         $5.231.760
BILLETERA MUJER $2.155.120
NECESER         21.98  $
COSMETIQUERA    10.99  $
SET DE VIAJE    10.99  $
TOTAL           $12.925.150

3 个解决方案

#1

The currency symbol ($) appearing to the right, is a known bug, which the creator of DataTables is aware of.

出现在右侧的货币符号（$）是一个已知错误，DataTables的创建者知道这个错误。

As for the other things, I created a demo

至于其他事情，我创建了一个演示

And based on that:

并基于此：

• You are pre-populating the amount with the dollar sign (at least, according to your posted HTML code); don't do that! just provide the numerical value

  您使用美元符号预先填充金额（至少根据您发布的HTML代码）;不要那样做！只提供数值

• Header and footer are indeed bold, both in display (right side of the screen shot below) and once exported to excel; check to ensure that the CSS isn't being overridden.

  页眉和页脚确实是大胆的，都在显示（屏幕右下方拍摄）和出口到excel;检查以确保未覆盖CSS。

That said, check out for other formatting needs, that DataTables provides.

也就是说，请查看DataTables提供的其他格式化需求。

Edit:

I do not know why my posted JSFiddle link does not include other libs... but for your reference, the following screenshot shows exactly the JS/CSS libs that were included in my re-build of your case:

我不知道为什么我发布的JSFiddle链接不包含其他库...但是为了您的参考，以下屏幕截图显示了我重新构建您的案例中包含的JS / CSS库：

#2

I am finding that in order to get some of the styling desired across all the datatables you may be using in your product a little editing of the buttons.html5.js is in order if you need things to be more automatic and not have to constantly be set table by table.

我发现，为了获得您在产品中可能使用的所有数据表所需的一些样式，如果您需要更自动的东西而不必经常进行，可以对buttons.html5.js进行一些编辑。逐桌设置。

This is because these settings are not baked into the API as of yet. So make sure you keep track of your edits to ensure you know what to do when future updates come from datatables.

这是因为这些设置尚未烘焙到API中。因此，请确保跟踪您的编辑内容，以确保在将来更新来自数据表时知道该怎么做。

For the $ on the right, specifically, I did the following edits in the plug-in file:

对于右边的$，具体来说，我在插件文件中进行了以下编辑：

at line 567 (in my version) or around that line look for this block of code:

在第567行（在我的版本中）或在该行周围查找此代码块：

        '<numFmts count="6">'+
            '<numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>'+
            '<numFmt numFmtId="165" formatCode="&quot;£&quot;#,##0.00"/>'+
            '<numFmt numFmtId="166" formatCode="[$€-2]\ #,##0.00"/>'+
            '<numFmt numFmtId="167" formatCode="0.0%"/>'+
            '<numFmt numFmtId="168" formatCode="#,##0;(#,##0)"/>'+
            '<numFmt numFmtId="169" formatCode="#,##0.00;(#,##0.00)"/>'+
        '</numFmts>'+

After the last numFmt 169 add a new entry:

在最后一个numFmt 169之后添加一个新条目：

'<numFmt formatCode="$#,##0.00_);[Red]($#,##0.00)" numFmtId="170"></numFmt>'

This particular format will put the $ on the left of the number use a comma as the thousand separator and put negative numbers in Red and in parentheses. If you need a different format, just open Excel and use the custom formating option to find the format you desire and copy and paste the format it gives you into the "formatCode" attr.

这种特殊格式将数字左边的$用逗号作为千位分隔符，并将负数放在红色和括号中。如果您需要不同的格式，只需打开Excel并使用自定义格式化选项查找所需的格式，然后将其提供的格式复制并粘贴到“formatCode”attr中。

Next you'll need to make it so you can access this new style you've created. So find the closing tag:

接下来，您需要创建它，以便您可以访问您创建的这种新样式。所以找到结束标记：

'</cellXfs>'+

Just before that there will be 67 or so lines similar to:

就在此之前，将有67个左右的线类似于：

'<xf numFmtId="1" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'+
'<xf numFmtId="2" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'+

Just before that closing tag add this:

就在结束标记之前添加：

'<xf numFmtId="170" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"></xf>'+

Notice that numFmtId="170" is matching the numFmts entry from before. Now up your count on the opening tag for that section from 66 to 67:

请注意，numFmtId =“170”与之前的numFmts条目匹配。现在，您可以将该部分的开头标记从66调整到67：

'<cellXfs count="67">'+

Becomes:

变为：

'<cellXfs count="68">'+

Now the last thing you need to make this pick up for all dollar formated cells automatically:

现在你需要做的最后一件事就是自动获取所有美元格式的单元格：

find:

找：

var _excelSpecials = [

Inside that array you'll note some lines that find various regex patterns and apply a style number. Replace:

在该数组中，您将注意到一些找到各种正则表达式模式并应用样式编号的行。更换：

{ match: /^\-?\$[\d,]+.?\d*$/,  style: 57 }, // Dollars

with your new style number

用你的新款号

{ match: /^\-?\$[\d,]+.?\d*$/,  style: 67 }, // Dollars

That number corresponds to the placement of your style in the cellXfs list. It is the 67th (from a 0 index, so item 68 is index 67) entry.

该数字对应于您的样式在cellXfs列表中的位置。它是第67个（从0索引，因此项68是索引67）条目。

Build if you need to then refresh your browser with the datatable and try your export. It should work with the new formating for any dollar field matching the format.

如果您需要构建，然后使用数据表刷新浏览器并尝试导出。它应该与匹配格式的任何美元字段的新格式一起使用。

Using this method you can add other styles to the default list DataTables provides. It's not the optimal solution, but until the htm5 button API can handle this sort of update as an obstraction, it's the best way I've found and isn't that much worse than having a custom CSS file at the end of the day. Just keep track of your edits for the future and if you source control the file, then it'll do that for you anyway.

使用此方法，您可以将其他样式添加到DataTables提供的默认列表中。这不是最佳解决方案，但是直到htm5按钮API可以将这种更新作为障碍处理，这是我发现的最好的方式，并且比在一天结束时拥有自定义CSS文件更糟糕。只需跟踪您对未来的编辑，如果您控制文件，那么无论如何它都会为您完成。

Hope this helps! I'm still wrestling some other things but maybe what I've found so far will be useful to others with similar problems.

希望这可以帮助！我还在摔跤其他一些东西，但也许我到目前为止发现的东西对于有类似问题的其他人也会有用。

#3

Just set the style to 64 and it will remove the $ symbol.

只需将样式设置为64，它将删除$符号。

$('row[r!=2] c[r^="B"]', sheet).attr('s', '64');

$（'row [r！= 2] c [r ^ =“B”]'，sheet）.attr（'s'，'64'）;