exporting data from datatable with canvas and chart.js to excel

exporting data from datatable with canvas and chart.js to excel

akriakri 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..");
        }
    });
});

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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 customize callback, 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

  • akriakri Posts: 3Questions: 1Answers: 0

    thanks Allan for suggesting this, will try to make use and post it here if it works:)

This discussion has been closed.