I would like to expand on Richards answer. Like Richard, I could not figure out the solution based on the Datatables documentation. I wanted an excelHtml5 export with all fields being exported as text only.
Richards solution helped me get to the solution that I will post below.
For Datatables 1.10.12 the html5 buttons code appears in a separate file buttons.html5.js.
As Richard noted, search for the DataTable.ext.buttons.excelHtml5 block.
The piece of code I was interested in was:
// Detect numbers - don't match numbers with leading zeros or a negative
// anywhere but the start
if ( typeof row[i] === 'number' || (
row[i].match &&
$.trim(row[i]).match(/^-?\d+(\.\d+)?$/) &&
! $.trim(row[i]).match(/^0\d+/) )
) {
cell = _createNode( rels, 'c', {
attr: {
t: 'n',
r: cellId
},
children: [
_createNode( rels, 'v', { text: row[i] } )
]
} );
}
else {
// Replace non standard characters for text output
var text = ! row[i].replace ?
row[i] :
row[i]
.replace(/&(?!amp;)/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '');
cell = _createNode( rels, 'c', {
attr: {
t: 'inlineStr',
r: cellId
},
children:{
row: _createNode( rels, 'is', {
children: {
row: _createNode( rels, 't', {
text: text
} )
}
} )
}
} );
}
In order to make the excelHtml5 button export ONLY text, I removed the IF block that would identify a field as a potential number.
Our customer also had a specific request to have '<>' in any field that was blank so I removed the two replace methods for < and >.
// Replace non standard characters for text output
var text = ! row[i].replace ?
row[i] :
row[i]
.replace(/&(?!amp;)/g, '&')
.replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '');
cell = _createNode( rels, 'c', {
attr: {
t: 'inlineStr',
r: cellId
},
children:{
row: _createNode( rels, 'is', {
children: {
row: _createNode( rels, 't', {
text: text
} )
}
} )
}
} );
This change allows the excel button to export all values as text. Excel no longer switches my < and > and my numbers are all text, no scientific notation.
