Using DataTables & extend excelHtml5 via JavaScript, Can I insert an image in the “title” property?

Pats2kDynasty

I am using Datatables and the excelHtml5 button extension via Javascript & jQuery. You will see in the beginning of the code the text "Hello" in the title property. I am also using messageTop, customize, and createEmptyCells for other formatting purposes. In the attachment you will see the resulting "Hello" text circled in red.

buttons: [ 
                {
                    extend: 'excelHtml5',
                    text: '<h2>EXPORT TO EXCEL</h2>', //'Export to Excel',
                    //orientation: 'landscape',
                    //pageSize: '11x17', 
                    title: 'Hello',
                    messageTop: function () {

                        var today = new Date();

I need to insert a legend image (.png) on the top. Is there any way to do this? Alternatively, if I cannot get in image in the title property I could format each cell of the title to look like the legend but I am not sure how to do this in the title property (I would need to unmerge the title and get access to each cell)?

I was thinking along the lines of..

title: function() {
                        var imgProcessMap = $("#imgProcessMap");
                        return imgProcessMap;
                    },

...but this returns the object.

colin

That's not possible, I'm afraid. There's a few threads on this, such as here and here, they'll give more details.

Colin

Pats2kDynasty

So as an alternative I could build my legend instead of having an image. I saw this bit of code

customize: function ( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
 
    $('c[r=A1] t', sheet).text( 'Custom text' );
}

from https://datatables.net/reference/button/excelHtml5, but it said it was to "modify the text shown in cell A1". My data spits out from columns A thru K, but could I make/create a legend starting at cell M2 with hard coded text and formatting from within the code?

So something like

customize: function ( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    
    //Legend text
    $('c[r=M2] t', sheet).text( 'Status: Yellow means getting ready' );
    $('c[r=N2] t', sheet).text( 'Status: Green means go' );

    //Legend background color (already works in Status column after adding styles to js file)
    $('c[r="M2"]', sheet).attr( 's', '77' );
    $('c[r="N2"]', sheet).attr( 's', '68' );
}

colin

That should work. Have you tried it?

Colin

Pats2kDynasty

Sorry, should have mentioned that. I could not create data in a cell programmatically like the above example. That is why I was wondering if you could only modify existing data. Sounds weird to me that you can't, but it did not work for me.

Maybe you could try the above example, except change the style to something that exists in yours, to see if you can create data programmatically and format it in a cell. Then I will know if it is just me (maybe syntax mistake) or if it (create vs. modify) can't be done. Thanx.

colin

I just found a property that would be useful - messageTop, see excelHtml5 - working example here. Hopefully that will do the trick,

Colin

Pats2kDynasty

Yup tried that too. It is a good idea but it merges the cells across the top, so it does not work if you need a green background color cell with text in it, then a yellow background cell with text in it, then a red background color cell with text in it, etc. Like the Legend.png that I have attached above. That is why I was trying to start at M2 cell in order to format and stick text in it.

Pats2kDynasty

So now the question is can anyone use the code below to CREATE new text via code in a cell as opposed to MODIFY existing text/data being fed from the datatable?

customize: function ( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
  
     //New text hard-coded
     //Datatable data only goes from col A thru K
    $('c[r=M1] t', sheet).text( 'Custom text' );  
}

Pats2kDynasty

I saw this post (https://datatables.net/forums/discussion/36045/excel-export-add-rows-and-data ) in which this individual is trying to add rows and fill data in the cells. So I believe that since the row already exists (A-K) that I would somehow have to get row 1 and then add cell M to it, in order to put text in it. Can anyone confirm this or have a bit of code to share?

Pats2kDynasty

Since the topic of this discussion has changed should I start a new post regarding how to add/create new cells via code and not based on datatable source data?

colin

It would be OK here. I'm not familiar with the excel XML, but somebody else may reply.

Colin

kthorngren

This might be overly complex for what you want but it shows how to add rows into the SS:
https://datatables.net/forums/discussion/58960/how-to-add-rows-to-excel-export-or-why-are-my-child-rows-not-exported#latest

Kevin

Pats2kDynasty

After days I finally got it. Also NOT a fan of XML.

I started with https://datatables.net/forums/discussion/36045/excel-export-add-rows-and-data/p1# , specifically @Raghul code posted on Jan 2017.

In order to add cells to an existing row I morphed it to the following code, complete with added background colors that I added to the XML style in the buttons.html5.min.js file.

                            function addCells(data) {

                                var sheetData = sheet.getElementsByTagName('sheetData')[0];                     
                                for (i = 0; i < data.length; i++) {
                                   var key = data[i].key;
                                   var value = data[i].value;

                                   var c  = sheet.createElement('c');
                                   c.setAttribute("t", "inlineStr");
                                   
                                   //Set styling (added styles to the buttons.html5.min.js file)
                                   switch (value) {
                                        case 'I':
                                            c.setAttribute("s", "78");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;             
                                        case 'Do':
                                            c.setAttribute("s", "67");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;             
                                        case 'Not':
                                            c.setAttribute("s", "68");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;             
                                        case 'Like':
                                            c.setAttribute("s", "69");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;  
                                        case 'XML':
                                            c.setAttribute("s", "70");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;             
                                        case 'It':
                                            c.setAttribute("s", "71");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;  
                                        case 'Is':
                                            c.setAttribute("s", "72");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;             
                                        case 'Too':
                                            c.setAttribute("s", "73");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;  
                                        case 'Verbose':
                                            c.setAttribute("s", "74");  //REMEMBER ZERO BASED (COUNT -1)
                                            break;             
                                   }

                                   var is = sheet.createElement('is');
                                   var t = sheet.createElement('t');
                                   var text = sheet.createTextNode(value)

                                   t.appendChild(text);                                      
                                   is.appendChild(t);
                                   c.appendChild(is);

                                   //Add cells to row 2 (zero-based) of sheet 
                                    sheetData.childNodes[1].appendChild(c);                            
                                }
                            }
                     
                            //The call to add cells to an existing row (in this case to row 2, which is in the function)
                            addCells([{ key: 'M', value: '' }, { key: 'N', value: 'I' }, { key: 'O', value: 'Do' }, { key: 'P', value: 'Not' }, { key: 'Q', value: 'Like' }, { key: 'R', value: 'XML' }, { key: 'S', value: 'It' }, { key: 'T', value: ' Is' }, { key: 'U', value: 'Too' }, { key: 'V', value: '' }, { key: 'W', value: 'Verbose' }]);

colin

nice, thanks for reporting back,

C

Ousmane Saley

chehin

Hola, tengo un datatable paginado de 100 en 100,cuestion que me exporta de a 100 ¿Alguien sabe como hacer para que me exporte el excel completo? el de todas las paginas

colin

@chehin Is related to this thread? It seems different and probably related to you using serverSide. See this thread. If that doesn't help, please follow the forum guidelines and open a new thread with a supporting test case.

Colin