This is one of the more complex examples in the Editor suite, but it shows the power of using the Editor's multi-row editing capability. Here we make use of three different Editor instances:
For ease of use in the demonstration, we make use of the Buttons extension to allow CSV export of data from the table, which can then immediately be imported using the import functionality provided here. Select all and Select none buttons are also available which is handy for deleting the data from the whole table, before an import (if you so wish).
| First name | Last name | Position | Office | Start date | Salary |
|---|---|---|---|---|---|
| First name | Last name | Position | Office | Start date | Salary |
The Javascript shown below is used to initialise the table shown in this example:
// Display an Editor form that allows the user to pick the CSV data to apply to each column
function selectColumns(editor, csv, header) {
var selectEditor = new DataTable.Editor();
var fields = editor.order();
for (var i = 0; i < fields.length; i++) {
var field = editor.field(fields[i]);
selectEditor.add({
label: field.label(),
name: field.name(),
type: 'select',
options: header,
def: header[i]
});
}
selectEditor.create({
title: 'Map CSV fields',
buttons: 'Import ' + csv.length + ' records',
message: 'Select the CSV column you want to use the data from for each field.',
onComplete: 'none'
});
selectEditor.on('submitComplete', function (e, json, data, action) {
// Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
editor.create(csv.length, {
title: 'Confirm import',
buttons: 'Submit',
message:
'Click the <i>Submit</i> button to confirm the import of ' +
csv.length +
' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
});
for (var i = 0; i < fields.length; i++) {
var field = editor.field(fields[i]);
var mapped = data[field.name()];
for (var j = 0; j < csv.length; j++) {
field.multiSet(j, csv[j][mapped]);
}
}
});
}
// Regular editor for the table
var editor = new DataTable.Editor({
ajax: '../../controllers/staff.php',
table: '#example',
fields: [
{
label: 'First name:',
name: 'first_name'
},
{
label: 'Last name:',
name: 'last_name'
},
{
label: 'Position:',
name: 'position'
},
{
label: 'Office:',
name: 'office'
},
{
label: 'Start date:',
name: 'start_date',
type: 'datetime'
},
{
label: 'Salary:',
name: 'salary'
}
]
});
// Upload Editor - triggered from the import button. Used only for uploading a file to the browser
var uploadEditor = new DataTable.Editor({
fields: [
{
label: 'CSV file:',
name: 'csv',
type: 'upload',
ajax: function (files, done) {
// Ajax override of the upload so we can handle the file locally. Here we use Papa
// to parse the CSV.
Papa.parse(files[0], {
header: true,
skipEmptyLines: true,
complete: function (results) {
if (results.errors.length) {
console.log(results);
uploadEditor
.field('csv')
.error('CSV parsing error: ' + results.errors[0].message);
}
else {
selectColumns(editor, results.data, results.meta.fields);
}
// Tell Editor the upload is complete - the array is a list of file
// id's, which the value of doesn't matter in this case.
done([0]);
}
});
}
}
]
});
$('#example').DataTable({
ajax: '../../controllers/staff.php',
buttons: [
{ extend: 'create', editor: editor },
{ extend: 'edit', editor: editor },
{ extend: 'remove', editor: editor },
{
extend: 'csv',
text: 'Export CSV',
className: 'btn-space',
exportOptions: {
orthogonal: null
}
},
{
text: 'Import CSV',
action: function () {
uploadEditor.create({
title: 'CSV file import'
});
}
},
{
extend: 'selectAll',
className: 'btn-space'
},
'selectNone'
],
columns: [
{ data: 'first_name' },
{ data: 'last_name' },
{ data: 'position' },
{ data: 'office' },
{ data: 'start_date' },
{ data: 'salary', render: DataTable.render.number(null, null, 0, '$') }
],
dom: 'Bfrtip',
select: true
});
// Display an Editor form that allows the user to pick the CSV data to apply to each column
function selectColumns(editor, csv, header) {
let selectEditor = new DataTable.Editor();
let fields = editor.order();
for (let i = 0; i < fields.length; i++) {
let field = editor.field(fields[i]);
selectEditor.add({
label: field.label(),
name: field.name(),
type: 'select',
options: header,
def: header[i]
});
}
selectEditor.create({
title: 'Map CSV fields',
buttons: 'Import ' + csv.length + ' records',
message: 'Select the CSV column you want to use the data from for each field.',
onComplete: 'none'
});
selectEditor.on('submitComplete', function (e, json, data, action) {
// Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
editor.create(csv.length, {
title: 'Confirm import',
buttons: 'Submit',
message:
'Click the <i>Submit</i> button to confirm the import of ' +
csv.length +
' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
});
for (var i = 0; i < fields.length; i++) {
let field = editor.field(fields[i]);
let mapped = data[field.name()];
for (let j = 0; j < csv.length; j++) {
field.multiSet(j, csv[j][mapped]);
}
}
});
}
// Regular editor for the table
const editor = new DataTable.Editor({
ajax: '../../controllers/staff.php',
table: '#example',
fields: [
{
label: 'First name:',
name: 'first_name'
},
{
label: 'Last name:',
name: 'last_name'
},
{
label: 'Position:',
name: 'position'
},
{
label: 'Office:',
name: 'office'
},
{
label: 'Start date:',
name: 'start_date',
type: 'datetime'
},
{
label: 'Salary:',
name: 'salary'
}
]
});
// Upload Editor - triggered from the import button. Used only for uploading a file to the browser
const uploadEditor = new DataTable.Editor({
fields: [
{
label: 'CSV file:',
name: 'csv',
type: 'upload',
ajax: function (files, done) {
// Ajax override of the upload so we can handle the file locally. Here we use Papa
// to parse the CSV.
Papa.parse(files[0], {
header: true,
skipEmptyLines: true,
complete: function (results) {
if (results.errors.length) {
console.log(results);
uploadEditor
.field('csv')
.error('CSV parsing error: ' + results.errors[0].message);
}
else {
selectColumns(editor, results.data, results.meta.fields);
}
// Tell Editor the upload is complete - the array is a list of file
// id's, which the value of doesn't matter in this case.
done([0]);
}
});
}
}
]
});
const table = new DataTable('#example', {
ajax: '../../controllers/staff.php',
buttons: [
{ extend: 'create', editor },
{ extend: 'edit', editor },
{ extend: 'remove', editor },
{
extend: 'csv',
text: 'Export CSV',
className: 'btn-space',
exportOptions: {
orthogonal: null
}
},
{
text: 'Import CSV',
action: function () {
uploadEditor.create({
title: 'CSV file import'
});
}
},
{
extend: 'selectAll',
className: 'btn-space'
},
'selectNone'
],
columns: [
{ data: 'first_name' },
{ data: 'last_name' },
{ data: 'position' },
{ data: 'office' },
{ data: 'start_date' },
{ data: 'salary', render: DataTable.render.number(null, null, 0, '$') }
],
dom: 'Bfrtip',
select: true
});
In addition to the above code, the following Javascript library files are loaded for use in this example:
The HTML shown below is the raw HTML table element, before it has been enhanced by DataTables:
This example uses a little bit of additional CSS beyond what is loaded from the library files (below), in order to correctly display the table. The additional CSS used is shown below:
button.btn-space {
margin-left: 1em;
}
The following CSS library files are loaded for use in this example to provide the styling of the table:
This table loads data by Ajax. The latest data that has been loaded is shown below. This data will update automatically as any additional data is loaded.
The script used to perform the server-side processing for this table is shown below. Please note that this is just an example script using PHP. Server-side processing scripts can be written in any language, using the protocol described in the DataTables documentation.