JQuery Datatable Excel 导出数字格式 [英] JQuery Datatable Excel export number format

查看:191
本文介绍了JQuery Datatable Excel 导出数字格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我们有一个带有 excel 导出的 JQuery 数据表,但无法解决数字问题.数字以匈牙利格式显示在数据表中:5 588,9906(空格是千位分隔符,逗号是小数点).现在我们需要在excel中将数据显示为数字,但这并不是每次都有效.在excel设置中,千位分隔符为空格,小数点为逗号.

数据表:数据表格式

Excel 中的结果(下一个可以,上一个是字符串):excel 错误

代码:

var buttonCommon = {出口选项:{格式: {正文:函数(数据,行,列,节点){返回列 === 6 ||列 === 8 ||列 === 9 ||列 === 10 ||列 === 11 ||列 === 12 ||列 === 13?data.replace(',', '.').replace(' ', ',') : 数据;}}}};var table = $('#talaltszamlak').DataTable({dom: 'Blfrtip',纽扣: [$.extend( true, {}, buttonCommon, {扩展:'excelHTML5'}),],页长:50,顺序":[[ 3, asc";]],语: {url: '//cdn.datatables.net/plug-ins/1.10.22/i18n/Hungarian.json'},});

谢谢!

解决方案

以下是您提供自己的自定义 Excel 数字格式的示例.

本例中,Excel格式字符串为:

#,##0.0##

因此,我们最多保留 3 个小数位(最少保留 1 个小数位).

测试数据:

<table id="example";class="显示数据表单元格边框";样式=宽度:100%"><头><tr><th>姓名</th><th>数量</th></tr></thead><tr><td>老虎尼克松</td><td>123,45</td></tr><tr><td>加勒特温特斯</td><td>4 567,892</td></tr><tr><td>阿什顿考克斯</td><td>1 233 445,1</td></tr></tbody>

带有自定义代码的数据表:

$(document).ready(function() {var table = $('#example').DataTable({dom: 'Brftip',纽扣: [{扩展:'excelHtml5',文本:'Excel',出口选项:{格式: {正文:函数(数据,行,列,节点){return reformatNumber(data, row, column, node);}}},自定义:函数(xlsx){addCustomNumberFormat(xlsx, '#,##0.0##');formatTargetColumn(xlsx, 'B');//Excel B列}}]});});函数reformatNumber(数据,行,列,节点){//用空替换空格;用点代替逗号.如果(列 === 1 ){var newData = data.replace(',', '.').replaceAll(' ', '');返回新数据;} 别的 {返回数据;}}函数 addCustomNumberFormat(xlsx, numberFormat) {//这会为 Excel样式"添加一个新的自定义数字格式;文档:var numFmtsElement = xlsx.xl['styles.xml'].getElementsByTagName('numFmts')[0];//假设 6 种自定义数字格式已经存在,下一个可用的 ID 是 176:var numFmtElement = '';$( numFmtsElement ).append( numFmtElement );$( numFmtsElement ).attr("count", "7");//增加计数//现在添加一个新的cellXfs";单元格格式化程序,它使用我们的新数字格式 (numFmt 176):var celXfsElement = xlsx.xl['styles.xml'].getElementsByTagName('cellXfs');var cellStyle = '<xf numFmtId="176";fontId=0"fillId =0"边界ID =0"xfId=0"applyNumberFormat="1"'+ ' applyFont="1";applyFill =1"applyBorder="1"/>';//这将是第 8 个xf";元素 - 因此,当我们稍后使用它时,索引为7":$(celXfsElement).append(cellStyle);$(celXfsElement).attr("count", "69");//增加计数}函数格式目标列(xlsx,col){var sheet = xlsx.xl.worksheets['sheet1.xml'];//选择地址以 prvoided 字母开头的所有单元格//在 'col' 中,为样式编号 68 添加样式 (s) 属性:$( 'row c[r^="' + col + '"]', sheet ).attr( 's', '68' );}

代码在Excel样式XML表中添加了新的数字格式记录;然后使用该记录创建新的单元格格式记录.最后,它定位 Excel 电子表格 B 列中的每个单元格并应用单元格格式化程序.

最终结果是一个值显示在数据表中,如下所示:

1 233 445,1

会像这样在Excel中显示:

1,233,445.1

您可以使用任何您想要的 Excel 数字格式字符串,而不是 #,##0.0##.

We have a JQuery datatable with excel export, but cant solve a problem with numbers. Numbers displays in the datatable in hungarian format: 5 588,9906 (whitespace is the thousand separator, comma is the decimal point). Now we need to display the datas as numbers in excel, but thats not working every time. In excel settings, the thousand separator is whitespace, the decimal point is comma.

Datatable: datatable format

Result in Excel (lower one is ok, upper one is a string): excel error

The code:

var buttonCommon = {
    exportOptions: {
        format: {
            body: function ( data, row, column, node ) {
                return column === 6 || column === 8 || column === 9 || column === 10 || column === 11 || column === 12 || column === 13
                ? data.replace(',', '.').replace(' ', ',') : data;
            }
        }
    }
};

var table = $('#talaltszamlak').DataTable({
    dom: 'Blfrtip',
    buttons: [
        $.extend( true, {}, buttonCommon, {
            extend: 'excelHtml5'
        } ),
    ],
    pageLength: 50,
    "order": [[ 3, "asc" ]],
    language: {
        url: '//cdn.datatables.net/plug-ins/1.10.22/i18n/Hungarian.json'
    },
});

Thank You!

解决方案

Here is an example where you provide your own custom Excel number format.

In this case, the Excel format string is:

#,##0.0##

So, we will get up to 3 decimal places (and a minimum of 1 decimal place).

The test data:

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Amount</th>
            </tr>
        </thead>
        <tbody>
            <tr><td>Tiger Nixon</td><td>123,45</td></tr>
            <tr><td>Garrett Winters</td><td>4 567,892</td></tr>
            <tr><td>Ashton Cox</td><td>1 233 445,1</td></tr>
        </tbody>
    </table>

</div>

The DataTable with custom code:

$(document).ready(function() {

  var table = $('#example').DataTable( {
    dom: 'Brftip',
    buttons: [
      {
        extend: 'excelHtml5',
        text: 'Excel',
        exportOptions: {
          format: {
            body: function ( data, row, column, node ) {
              return reformatNumber(data, row, column, node);
            }
          }
        },
        customize: function( xlsx ) {
          addCustomNumberFormat(xlsx, '#,##0.0##');
          formatTargetColumn(xlsx, 'B'); // Excel column B
        }
      }
    ]
  } );
    
} );

function reformatNumber(data, row, column, node) {
  // replace spaces with nothing; replace commas with points.
  if (column === 1 ) {
    var newData = data.replace(',', '.').replaceAll(' ', '');
    return newData;
  } else {
    return data;
  }
}             

function addCustomNumberFormat(xlsx, numberFormat) {
  // this adds a new custom number format to the Excel "styles" document:
  var numFmtsElement = xlsx.xl['styles.xml'].getElementsByTagName('numFmts')[0];
  // assume 6 custom number formats already exist, and next available ID is 176:
  var numFmtElement = '<numFmt numFmtId="176" formatCode="' + numberFormat + '"/>';
  $( numFmtsElement ).append( numFmtElement );
  $( numFmtsElement ).attr("count", "7"); // increment the count
    
  // now add a new "cellXfs" cell formatter, which uses our new number format (numFmt 176):
  var celXfsElement = xlsx.xl['styles.xml'].getElementsByTagName('cellXfs');
  var cellStyle = '<xf numFmtId="176" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"' 
      + ' applyFont="1" applyFill="1" applyBorder="1"/>';
  // this will be the 8th "xf" element - and will therefore have an index of "7", when we use it later:
  $( celXfsElement ).append( cellStyle );
  $( celXfsElement ).attr("count", "69"); // increment the count
}

function formatTargetColumn(xlsx, col) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  // select all the cells whose addresses start with the letter prvoided
  // in 'col', and add a style (s) attribute for style number 68:
  $( 'row c[r^="' + col + '"]', sheet ).attr( 's', '68' );  
}

The code adds a new number format record to the Excel styles XML sheet; it then uses that record to create a new cell format record. Finally, it locates every cell in column B of the Excel spreadsheet and applies the cell formatter.

The end result is that a value which is displayed in the DataTable like this:

1 233 445,1

Will be displayed in Excel like this:

1,233,445.1

You can use whatever Excel number format string you want, instead of #,##0.0##.

这篇关于JQuery Datatable Excel 导出数字格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆