CODEWITHSUNDEEP
jqueryexceldatatables
DavidM
DavidM

Reputation: 307

Datatables export to Excel and format a numeric column as currency

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

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:

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

Upvotes: 4

Views: 20411

Answers (5)

Nirav Dalsaniya
Nirav Dalsaniya

Reputation: 11

in Excel - Pre-defined strings to build a basic XLSX file from b-html5.datatables.js

Actual String is formatted wrong..

<numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>

Just replace formatCode with:

<numFmt numFmtId="164" formatCode="[$$-45C]\#,##0.00"/>

It will work for data of any rows including Header and Footer values.

Upvotes: 0

Dave
Dave

Reputation: 4436

I ended up using the Format output data - export options from https://datatables.net/extensions/buttons/examples/html5/outputFormat-orthogonal.html

<table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Extn.</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Extn.</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>

$(document).ready(function() {
    $('#example').DataTable( {
        ajax: '../../../../examples/ajax/data/objects.txt',
        columns: [
            { data: 'name' },
            { data: 'position' },
            { data: 'office' },
            { data: 'extn' },
            { data: 'start_date' },
            { data: 'salary', render: function (data, type, row) {
                return type === 'export' ?
                    data.replace( /[$,]/g, '' ) :
                    data;
            } }
        ],
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'copyHtml5',
                exportOptions: { orthogonal: 'export' }
            },
            {
                extend: 'excelHtml5',
                exportOptions: { orthogonal: 'export' }
            },
            {
                extend: 'pdfHtml5',
                exportOptions: { orthogonal: 'export' }
            }
        ]
    } );
} );

Upvotes: 3

Candice
Candice

Reputation: 92

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

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

Upvotes: 0

innerurge1
innerurge1

Reputation: 728

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.

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.

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:

        '<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 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.

enter image description here

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:

'<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:

'<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.

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.

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.

Upvotes: 2

Rushikumar
Rushikumar

Reputation: 1812

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

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

  • 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.

left side of the screnshot is Excel; right side is what's displayed on JSFiddle

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

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

Upvotes: 4

Related Questions