Datatables / TableTools: форматировать данные как текст при экспорте в Excel
Я использую плагин Datatables TableTools, чтобы обеспечить возможность экспорта в Excel для таблицы на моей странице.
В целом все работает как задумано. Моя единственная проблема заключается в том, что мне понадобятся все данные, соответственно. столбцы в итоговой таблице Excel форматируются как текст, иначе я теряю данные в некоторых столбцах.
Примеры: - у меня есть столбец с начальными нулями (например, 0022), который появляется только с обрезанными начальными нулями (например, 22) в файле Excel, если он не отформатирован как текст.
- Другой столбец содержит 19-значные номера счетов (например, 1234567890123456789), который появляется с последними четырьмя цифрами, которые заменяются нулями (например, 1234567890123450000) в файле Excel, если он не отформатирован как текст.
Можно ли как-то установить это в моей инициализации Datatables / TableTools, чтобы она всегда экспортировала все данные в виде текста в файл Excel?
Большое спасибо за любую помощь в этом, Тим.
10 ответов
TableTools не создает настоящий excel файл, он создает csv файл вместо. Они содержат только необработанные данные, без форматирования. Хотя здесь есть ведущие нули, Excel обычно не показывает их. У вас есть несколько вариантов здесь:
- изменить форматирование из Excel
- Открой
csvфайл из открытого диалогового окна Excel, в котором вы сможете пометить столбцы как текст (вам может потребоваться изменить тип файла наtxt) - добавить кавычки вокруг данных
- создать настоящий файл Excel с помощью внешней библиотеки
Я попробовал первый вариант, данный Aureltime, но обнаружил небольшой побочный эффект. Если столбец содержит только цифры и вы используете функцию рендеринга, опция сортировки не работает. Надеемся, что в версии 1.10.12 datatables появилась новая опция для настройки данных перед созданием файла excel.
В этой функции настройки я добавил /u002C, и она отлично работает, даже сортировка чисел.
"buttons": [{
extend: 'excel',
exportOptions: {
orthogonal: 'sort'
},
customizeData: function ( data ) {
for (var i=0; i<data.body.length; i++){
for (var j=0; j<data.body[i].length; j++ ){
data.body[i][j] = '\u200C' + data.body[i][j];
}
}
}
}],
У меня есть решение этой проблемы.
У меня очень много времени разбивалась голова... Так что объяснение ниже этого:
- Это исправление прекрасно работает в DatatableJS версии 1.10.11 (для экспорта в HTML Excel)
- Откройте файл datatables.js и выполните поиск по этому адресу: "DataTable.ext.buttons.excelHtml5 = {"
Ищите в следующих строках, пока не возьмете этот код, и прокомментируйте его:
cells.push( typeof row[i] === 'number' || (row[i].match && $.trim(row[i]).match(/^-?\d+(\.\d+)?$/) && row[i].charAt(0) !== '0') ? '<c t="n"><v>'+row[i]+'</v></c>' : '<c t="inlineStr"><is><t>'+( ! row[i].replace ? row[i] : row[i] .replace(/&(?!amp;)/g, '&') .replace(/</g, '<') .replace(/>/g, '>') .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, ''))+ // remove control characters '</t></is></c>' // they are not valid in XML );Поместите этот новый код:
cells.push( '<c t="inlineStr"><is><t>'+( ! row[i].replace ? row[i] : row[i] .replace(/&(?!amp;)/g, '&') .replace(/</g, '<') .replace(/>/g, '>') .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, ''))+ // remove control characters '</t></is></c>' // they are not valid in XML );Сохраните ваши datatables.js
- Наслаждайтесь своими священными текстовыми клетками навсегда!
Это решение помогает поддерживать число, дату и десятичный формат.
Я изменил код, чтобы заставить писать в текстовом формате все значения из HTML в XLSX.
Если у кого-нибудь возникнут вопросы по поводу этого решения, я постараюсь ответить на все вопросы.
Спасибо всем.
Я хотел бы расширить ответ Ричардса. Как и Ричард, я не мог найти решение на основе документации Datatables. Я хотел экспортировать ExcelHtml5 со всеми полями, экспортируемыми только как текст.
Решение Richards помогло мне найти решение, которое я опубликую ниже.
Для Datatables 1.10.12 код кнопок html5 отображается в отдельном файле buttons.html5.js.
Как заметил Ричард, найдите блок DataTable.ext.buttons.excelHtml5.
Интересующий меня фрагмент кода был:
// 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
} )
}
} )
}
} );
}
Для того, чтобы кнопка excelHtml5 экспортировала ТОЛЬКО текст, я удалил блок IF, который бы идентифицировал поле как потенциальное число. У нашего клиента также был определенный запрос на добавление "<>" в любое пустое поле, поэтому я удалил два метода замены для <и>.
// 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
} )
}
} )
}
} );
Это изменение позволяет кнопке Excel экспортировать все значения в виде текста. Excel больше не переключает мои <и>, и все мои номера - текст, без научных обозначений.
Эта конкретная проблема была решена элегантно в этом посте - /questions/8751841/ostanovit-excel-ot-avtomaticheskogo-preobrazovaniya-opredelennyih-tekstovyih-znachenij-v-datyi/8751865#8751865. Допустим, у вас есть целое число your_integer что вы хотите отображать в виде строки (то есть отображаются первые нули). Затем вы просто форматируете его как таковой, прежде чем экспортировать в Excel - ="<your_integer>", Когда Excel Doc автоматически загружает, ваше целое число уже будет отображаться в виде строки, используя этот метод.
Решение datatable expord CSV со специальными символами. Найдите кодировку в https://cdn.datatables.net/buttons/1.1.2/js/buttons.html5.js или https://cdn.datatables.net/buttons/1.1.2/js/buttons.html5.min.js
и изменить его на UTF-8-BOM от UTF-8
Приведенный ниже код поможет вам создать таблицу данных запроса с экспортом в текстовый файл в качестве пользовательской опции. А также включены в комплект поставки, Excel, CSV.
$('#tableid').DataTable({
buttons: [
{
extend: 'copyHtml5',
filename: function() {
return "Copy Table";
}
},
{
extend: 'excelHtml5',
filename: function() {
return "Export To Excel";
}
},
{
extend: 'csvHtml5',
filename: function() {
return "Export to CSV";
}
},
{
text: 'Text',
footer: false,
header: false,
exportOptions: {
orthogonal: 'sort'
},
action: function(e, dt, node, config) {
var data = dt.buttons.exportData();
var retContent = [];
var retString = '';
for (var i = 0; i < data.body.length; i++) {
var text = '\u200C' + data.body[i];
retContent.push(text);
}
retString = retContent.join(',\r\n');
$.fn.dataTable.fileSave(
new Blob( [ retString ] ),
"Export to text"+ ".txt"
);
}
}
]
});
Библиотеки включены:
- datatables.min.js
- dataTables.bootstrap4.min.js
- dataTables.buttons.min.js
- buttons.flash.min.js
- jszip.min.js
- vfs_fonts.js
- buttons.html5.min.js
и найденные выше библиотеки в этой ссылке https://editor.datatables.net/examples/extensions/exportButtons
Обновить:
Начиная с datatables 1.10.8 и введения API кнопок, tabletools устарела, и вот способ сделать это через API кнопок:
Она расширяет кнопку Excel параметром exportOptions, который допускает несколько вариантов, один из которых ортогональн, когда вы можете сказать, что он должен использовать тип 'sort':
exportOptions: {
orthogonal: 'sort'
}
После того, как вам нужно уточнить тип сортировки и столбцы, на которые нужно воздействовать, с помощью параметра columnDefs:
columnDefs: [{
targets:[1],
render: function(data, type, row, meta){
if(type === 'sort'){
//data = ' ' + data ;
return "\u200C" + data ;
}
return data ;
}
}]
Здесь рассматривается второй столбец (с нулевым индексом). Для типа 'sort' мы ставим дату перед нулевой шириной без соединения, поэтому Excel будет рассматривать ее как строку. Не заметил никаких побочных эффектов.
наслаждаться
Есть способ сделать это через плагин tabletools datatables.
Использование Stop Excel для автоматического преобразования определенных текстовых значений в даты,
и опция кнопки fncellrender, решение состоит в том, чтобы использовать опцию fnCellRender на кнопке xls следующим образом:
{
"sExtends": "xls",
"fnCellRender": function ( sValue, iColumn, nTr,iDataIndex ) {
if ( iColumn === colIndexOfString ) {
return '"=""' + sValue + '"""' ;
}
return sValue;
}
}
Таким образом, данные извлекаются в CSV-файл со специальными символами, необходимыми для того, чтобы Excel рассматривал их как строку и избегал автоматического определения типа.
И да, теперь вы можете сохранить свои начальные и конечные нули
Я попробовал символ, не являющийся объединением, нулевой ширины ('\u200C'), и хотя это сработало, это вызвало хаос, когда я попытался сделать что-либо, связанное с этим столбцом.
Добавление $('row c[r^="A"]', лист).attr('s', '0') решило мою проблему:
buttons: [
{
extend: 'excelHtml5',
orientation: 'landscape',
autoFilter: true,
title: null,
messageTop: '...',
messageBottom: '...',
// BAD IDEA!
//customizeData: function (data) {
// for (var i = 0; i < data.body.length; i++) {
// data.body[i][0] = '"' + data.body[i][0] + '"';
// // for (var j = 0; j < data.body[i].length; j++) {
// // data.body[i][j] = '\u200C' + data.body[i][j];
// // }
// }
//},
customize: function (xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
var sheet2 = xlsx.xl['styles.xml'];
$('row:first c', sheet).attr('s', '2').attr('s', '32'); // first row is bold
$('row c[r^="A"]', sheet).attr('s', '0'); // first column as text
$(sheet.body)
.css('font-size', '10pt');
$(sheet.body).find('table')
.addClass('compact')
.css('font-size', 'inherit');
},
exportOptions: {
columns: [0, 1, 2, 3, 4]
},
},
Для чисел, состоящих из 19 цифр, проблема заключается в том, что Excel при округлении чисел открывает данные. Пожалуйста, смотрите эту ветку для получения дополнительной информации и возможных обходных путей: https://datatables.net/forums/discussion/comment/132633