问题描述
在使用数据表导出到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格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!