exporting data from datatable with canvas and chart.js to excel
exporting data from datatable with canvas and chart.js to excel
akri
Posts: 3Questions: 1Answers: 0
Hi ,
I am trying to export datatable to excel with datatable inbuilt file export. I am converting the canvas containing the chart to base64 image data and storing it in a blob and trying to export the blob to excel. and excel does not seems to support base64 image string so i ended up using blob also tried exporting as URL by URL.createobjectURL() non of it seems to show the image in exported excel.
thanks.. any help or suggestions please..
var buttonCommon = {
exportOptions: {
format: {
body: function ( data, row, column, node ) {
//var canvas = node.childNodes["0"].$chartjs.toBase64Image();
var canvas = node.childNodes["0"];
var imgdata = canvas.toDataURL("image/png", 1.0);
var contentType = 'image/png';
var b64Data = imgdata.replace(/^data:image\/\w+;base64,/, "");
var blob = b64toBlob(b64Data, contentType);
const objectURL = URL.createObjectURL(blob);
return objectURL;
//var data = imgdata.replace(/^data:image\/\w+;base64,/, "");
//var unicode = atob(data);
//$.fn.dataTable.fileSave( new Blob( [ JSON.stringify( data ) ] ), 'Export.png');
//return unicode;
//const objectURL = URL.createObjectURL(new Blob( [ JSON.stringify( data ) ] ));
//return objectURL;
//var buf = new Buffer.from(data, 'base64');
//var newdata = imgdata.replace(/^data:image\/png/,'data:application/octet-stream');
//return data;
// Strip $ from salary column to make it numeric
//return column === s ? data.replace( /[$,]/g, '' ) : data;
}
}
}
};
$('#sample_table').DataTable({
dom: 'Bfrtip',
buttons: [
$.extend( true, {}, buttonCommon, {
extend: 'copyHtml5'
} ),
$.extend( true, {}, buttonCommon, {
extend: 'excelHtml5'
} ),
$.extend( true, {}, buttonCommon, {
extend: 'pdfHtml5'
} ),
$.extend( true, {}, buttonCommon, {
extend: 'print'
} )
],
"pageLength" : 2,
"iDisplayLength" : 2,
"lengthMenu" : [ [ 2, 4, -1 ], [ 2, 4, "All" ] ],
//"scrollY": "300px",
"scrollX" : true,
//"fixedColumns": {leftColumns: 1},
//"deferRender" : false,
//"pagingType": "full_numbers",
"serverSide" : false,
columnDefs: [
{
targets: '_all',
render: function (row_data, type, row) {
var canvas = document.createElement('canvas');//create canvas to insert the graph
canvas.setAttribute('width', '900');
canvas.setAttribute('height', '250');
var id_no = Math.floor(Math.random() * 1000000) + 1;
canvas.setAttribute('id', id_no.toString());
var docString = "<html>"+ canvas.outerHTML +"</html>";
return docString;
},
createdCell: function( cell, cellData, rowData, rowIndex, colIndex )
{
var x =0;
//create_line_chart(cell.lastElementChild.id, cellData.value_arr, cellData.idx);
}
}
],
"drawCallback": function( settings ) {
var table = $('#sample_table').DataTable();
table.cells({page:'current'}).eq(0).each( function ( index ) {
var cell = table.cell( index );
var data = cell.data();
var nod = cell.node();
var id = nod.lastChild.id;
create_line_chart(id, data.value_arr, data.idx);
} );
}
});
AddChartToTable(response);
},
error : function(err) {
console.log("error..");
}
});
});
This discussion has been closed.
Answers
I don't know enough about the Excel format to really be able to be much help I'm afraid. The way I figured out the export that is currently available is to create a minimal spreadsheet in Excel then rename the file to be .zip and unzip it and inspect the XML in a text editor.
Since it is just a zip archive I suspect you'll need to add the images into the zip file (which you can do in the
customizecallback, but they will need to be properly jpeg / png encoded) and then some kind of reference to them from the XML.Our Excel exporter is not intended to be general purpose beyond basic data. I'd suggest having a look at Sheet JS which is likely to be far more useful for more advanced cases such as this.
Allan
thanks Allan for suggesting this, will try to make use and post it here if it works:)