数据表:导出Excel格式 [英] Datatables: Export Excel Formatting

问题描述

在使用数据表导出到Excel时尝试格式化数据时遇到一些问题.我的其中一列包含小数点，并且在浏览器中以表的形式显示时显示OK.当我将表格导出到excel时，这是在该列中四舍五入的数字，我不想发生这种情况.例如，如表'220419.07109'中所示，当导出为'220419.0711'时，我希望这只是一个字符串来保持完整的数字.

I'm having some issues when trying to format data when exporting to Excel using datatables. One of my columns contain a decimal point and displays OK when viewed in the browser as a table. When I export the table to excel this is rounding up the number in that column, this I do not want to happen. e.g shown in table '220419.07109' and when exported '220419.0711' I would prefer if this was just a string to maintain the full number.

  function formatDataForExport(data, row, column, node) {

    var string = data.toString();

    return string;


}

function drawDatatable(JSONData) {

    var dataSet = [];

    table = $("#div").DataTable({
        data: dataSet,
        columns: columns(),
        columnDefs: [{
             "targets": columnTargets(showConcludedColumns),
             "visible": false,
             "searchable": false
        }],
        info: false,
        searching: false,
        paging: false,
        ordering: false,
        autoWidth: true,
        responsive: true,
        buttons: [{
            extend: 'excel',
            text: "Export to Excel",
            exportOptions: {
                columns: ":visible",
                format: {
                    body: formatDataForExport
                }
            }
        }]
    });

}

推荐答案

您可以使用以下解决方案.

You could use the following solution.

// Get the max value of an attribute of elements' list
var getMaxValue = function(elements, attr) {
    var values = elements.map(function(){
        return this.getAttribute(attr) || -Infinity;
    }).toArray();

    return Math.max.apply(Math, values);
}

$('#example').DataTable( {
    dom: 'Bfrtip',
    columns: [
        { data: 'Number' },
    ],
    buttons: [
        {
            extend: 'excelHtml5',
            customize: function(xlsx) {
                //Get the built-in styles
                //refer buttons.html5.js "xl/styles.xml" for the XML structure
                var styles = xlsx.xl['styles.xml'];

                //Create a custom number format
                //Get the available id for the custom number format
                var numFmtId = getMaxValue($('numFmts numFmt', styles), 'numFmtId') + 1
                //XML Node: Custom number format for the timestamp column
                var nFmt = '<numFmt numFmtId="' + numFmtId + '" formatCode="0.######################"/>';
                // Add new node
                el = $('numFmts', styles);
                el.append(nFmt).attr('count', parseInt(el.attr('count'))+1);
                //Create our own style to use the custom number format above
                //XML Node: Custom style for the timestamp column
                var style = '<xf numFmtId="' + numFmtId + '" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
                // Add new node
                el = $('cellXfs', styles);
                el.append(style).attr('count', parseInt(el.attr('count'))+1);
                // Index of our new style
                var styleIdx = $('xf', el).length - 1;

                //Apply new style to the first (A) column
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                //Set new style default for the column (optional)
                $('col:eq(0)', sheet).attr('style', styleIdx);
                //Apply new style to the existing rows of the first column ('A'), skipping header row
                $('row:gt(0) c[r^="A"]', sheet).attr('s', styleIdx);
            },
        },
    ]
} );

工作 JSFiddle

您可以在其中使用不同种类的格式:

You could use different kinds of formatting there:

• 0.###################### -小数点后显示的位数与#"相同；
• #.###################### -与上述相同，但在0.1234之类的数字中没有0；
• 0.?????????????????????? -与上面相同，但以小数点对齐

• 0.###################### - will show as many digits after decimal point as you have "#";
• #.###################### - the same as above, but with no 0 in a number like 0.1234;
• 0.?????????????????????? - the same as above, but aligned by the decimal point

简化版本，生成的文件在Excel中正确打开，但是快捷方式可能会影响其他XLSX文件读取软件: JSFiddle

Simplified version, the resulted file opens correctly in Excel, but the shortcuts could affect other XLSX files reading software: JSFiddle

这篇关于数据表:导出Excel格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！