Datatable multiple built-in style in excelhtml5

badr221 · March 2020

Am trying to implement multiple style inside the exported Excels file using jQuery but didn't work when apply some different style and overwritten all value

customize:

function(xlsx) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];

        $('row c', sheet).attr('s', '25');
        $('c[r=A3]', sheet).attr('s', '7');
        $('c[r=A1]', sheet).attr('s','7');


    }

and the document demanded for me must be like that inside laravel project.

cheers.

rf1234 · March 2020

there are so many threads on applying styles in Excel exports. I would simply do a search.

But: If you want to create a document like this with a simple data table excel export you'll fight hard. Use something like PHP Spreadsheet server side, much more convenient. This will be extremely hard to do client side.

badr221 · March 2020

okay thanks but the company would like an exported Excel file with AJAX and add custom style defined below.
thanks

rf1234 · March 2020

good luck to your company ... I am not saying that you can't do this but it will be hard. And you would need to write the code yourself. You could generate the same result server side using other tools like PHP Spreadsheet that are much easier to handle and then download the result. For the user it would feel the same as a client side Excel export. Up to you.

badr221 · March 2020

Okay thanks a lot for your help, I appreciate it

rf1234 · March 2020

Trust me I tried both. The last one I did was client side creating a log report as a complete history of changes. All fields that changed over time were to be highlighted in the Excel export. One of the most painful exercises I ever went through. And then this really annoying jQuery bug in "text()": If you apply it on fully numeric values it is not working at all in comparisons. That is probably the reason why @allan adds "#"s in front of the data tables row ids. It was also very difficult to inform the user if the download takes longer than usual. I needed @allan's help for this as well. I have a suboptimal solution on this now: 3 idle seconds before the actual export starts. But otherwise I couldn't get it working at all. All easy with server side ajax calls, but not here ...

All in all it was a terrible night mare. But the result finally worked ...
Here is my code:

buttons: [
      "colvis",
    { extend: "excel",
        title:    function () { return lang === 'de' ? 'Vertrags Log' : 'Contract Log' },
        filename: function () { return lang === 'de' ? 'Vertrags Log' : 'Contract Log' },
    },
    { extend: "excel",
        text:     function () { return lang === 'de' ? 'Excel Vertrags Änderungshistorie' : 'Excel Contract Change History' },
        action: function ( e, dt, node, config ) {
            var that = this; //preserve the context - similar to fat arrow function
            swal({
                title: lang === 'de' ? 'Ihr Bericht wird erstellt!' : 'Your report is being created!',
                text: lang === 'de' ? 
                    "Das kann eine Weile dauern. Bitte bleiben Sie auf \n\
                     dieser Seite bis sich dieses Fenster schließt und der \n\
                     Bericht heruntergeladen wurde." : 
                    "That can take a while. Please do not navigate away from \n\
                     this page until this popup closed and the  download will \n\
                     have been completed.",
                type: '',
                customClass: 'swal-wide',
                showConfirmButton: false
            } );
            //setTimeout( () => {
            setTimeout(function() {
                $.fn.dataTable.ext.buttons.excelHtml5.action.call(that, e, dt, node, config);
                swal.close();
            }, 3000);
        },
        className:     "historyButton",
        name:          "historyButton",
        title:    function () { return lang === 'de' ? 'Vertrags Änderungshistorie' : 'Contract Change History' },
        filename: function () { return lang === 'de' ? 'Vertrags Änderungshistorie' : 'Contract Change History' },
        customize: function( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            var cols = $('col', sheet);
            var rows = $('row c', sheet);
            var i; var y; var ltr; var prefLtr; var colLtrs = [];
            //fill the Excel column letters array
            for ( i=0; i < cols.length; i++ ) {
                if ( i == 0 ) {
                    prefLtr = '';
                    ltr = 'A';
                } else if ( i == 26 ) {                    
                    prefLtr = 'A';
                    ltr = 'A';
                }
                colLtrs.push(prefLtr + ltr);
                ltr = String.fromCharCode(ltr.charCodeAt() + 1); 
            }
            //the first two rows are heading, so we start with
            //the fourth row and compare it with the third etc.
            var newRow = '';
            var oldRow = '';
            for ( i=3; i < rows.length; i++ ) {   
                //loop through all columns within the row
                for ( y=0; y < cols.length; y++ ) { //last col is not relevant for comparison (creator)
                    oldRow = $('row:eq('+(i-1)+') c[r^='+colLtrs[y]+'] t', sheet).text();
                    newRow = $('row:eq('+i+') c[r^='+colLtrs[y]+'] t', sheet).text();
                    if ( colLtrs[y] == "A" ) { //first column contains name
                        oldRow = oldRow.substr(5, 10);
                        newRow = newRow.substr(5, 10);
                    }
                    if ( i === 3 ) { //change without New
                        if ( $('row:eq('+(i-1)+') c[r^="B"] t', sheet).text() != "Neu" &&
                             $('row:eq('+(i-1)+') c[r^="B"] t', sheet).text() != "New"    ) {
                            if ( colLtrs[y] == "B" ) {//we always highlight the action column if a change occurred
                                $('row:eq('+(i-1)+') c[r^='+colLtrs[y]+']', sheet).attr( 's', '5' );  //normal text grey background
                            }
                        } 
                    }
                    //we only hihglight if it is NOT a new variable because a new variable is not a change
                    if ( $('row:eq('+i+') c[r^="B"] t', sheet).text() != "Neu" &&
                         $('row:eq('+i+') c[r^="B"] t', sheet).text() != "New"    ) {
                        if ( $('row:eq('+i+')     c[r^="E"] t', sheet).text() !== 
                             $('row:eq('+(i-1)+') c[r^="E"] t', sheet).text()      ) { //changed contract id   
                            if ( colLtrs[y] == "B" ) {//we always highlight the action column if a change occurred
                                $('row:eq('+(i)+') c[r^='+colLtrs[y]+']', sheet).attr( 's', '5' );  //normal text grey background
                            }
                        } else if ( colLtrs[y] == "B" ) {//we always highlight the action column if a change occurred
                            $('row:eq('+i+') c[r^='+colLtrs[y]+']', sheet).attr( 's', '11' );  //white text red background
                        } else if ( oldRow !== newRow ) {                                        
                        // we don't highlight the editing time columns and not columns if deleted
                            if ( colLtrs[y] != "C" ) {
                                if ( $('row:eq('+i+') c[r^="B"] t', sheet).text() != "Gelöscht" &&
                                     $('row:eq('+i+') c[r^="B"] t', sheet).text() != "Deleted"    ) {
                                    $('row:eq('+i+') c[r^='+colLtrs[y]+']', sheet).attr( 's', '20' );  //blue background
                                }
                            }
                        }
                    }
                }
            }            
        },
        exportOptions: {
            modifier: { order: 'index' } //order as returned from the server
        }
    }
]

badr221 · March 2020

Yes your right, but tell me your solution gonna work with my Excel document ?
the excel export is easy but thats a 3 day am blocked with task of colorization and typography and don't exactly what to do and don't understand some piece of code here i need to accomplish that and ill try the PHP spreadsheet like you tell me but dont know how to do need some help

rf1234 · March 2020

Well that is hard to tell ... You seem to have a rather complex header and you will need to merge cells. At least make some cell borders disappear or so. Will be a lot of reading the docs, experimenting and searching for other posts in this forum to figure it out. This is all open xml manipulated with jQuery. Unlike PHP Spreadsheet the open xml docs are really hard to understand ... at least for someone like me ...

If you haven't done it yet: Read all of this and follow all the links mentioned. Will take a while before you don't feel like you are drowning any longer ...
https://datatables.net/reference/button/excelHtml5
https://sheetjs.com/

Here is an old thread in which I show how to do your own fonts:
https://datatables.net/forums/discussion/comment/148601/#Comment_148601

Just search for "excel rf1234" or "excel export rf1234" in this forum and you'll find a lot more ...

badr221 · March 2020

thanks a lot.

rf1234 · March 2020

Good luck!!
I know I posted parts of it before: But this is the complete version of one of my Excel export buttons with the following features:
- new number formats
- new fonts
- new styles
- setting column length
- move text from one column to another
- number formatting so that Excel "gets it" (exportOptions)
- etc...

There is nothing on "new borders" but you'll figure it out because it should work very similarly. Take a look at "buttons.html5.js", search for "xl/styles.xml"

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
    customize: function( xlsx ) {
// see built in styles here: https://datatables.net/reference/button/excelHtml5
// take a look at "buttons.html5.js", search for "xl/styles.xml"
//styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
//styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
//styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
//styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
//styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
//styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
//on the last line we have the 67 currently built in styles (0 - 66), see link above

        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
        var lastFontIndex = $('fonts font', sSh).length - 1; 
        var f1 = //bold and underlined font
        '<font>'+
                '<sz val="11" />'+
                '<name val="Calibri" />'+
                '<b />'+'<u />'+
        '</font>'

        var i; var y;
//n1, n2 ... are number formats; s1, s2, ... are styles
        var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
        var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
        //define the style with the new font (number passed in as a variable)
        var s2 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center"/></xf>';         
//s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
//just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
        var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
        var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center" wrapText="1"/></xf>'
        sSh.childNodes[0].childNodes[0].innerHTML += n1;  //new number format
        sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4; //new styles

        var fourDecPlaces    = lastXfIndex + 1;
        var greyBoldCentered = lastXfIndex + 2;
        var twoDecPlacesBold = lastXfIndex + 3;
        var greyBoldWrapText = lastXfIndex + 4;

        var sheet = xlsx.xl.worksheets['sheet1.xml'];
    //create array of all columns (0 - N)
        var cols = $('col', sheet);
    //set lenght of some columns: col number: length (excl. first column)
        var colsLength = ['01:12', '02:12', '03:16', '04:10', '05:12', '06:16',
                          '07:16', '08:16', '09:16', '10:16', '11:16', '12:16',
                          '13:16', '14:16', '15:16'];
        for ( i=0; i < colsLength.length; i++ ) {
            $( cols [ parseInt( colsLength[i].substr(0,2) ) ] )
                    .attr('width', parseInt( colsLength[i].substr(3) ) );                
        }

//two decimal places columns          
        var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'];            
        for ( i=0; i < twoDecPlacesCols.length; i++ ) {
            $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
        $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
//                $('row c', sheet).attr( 's', '25' ); //for all rows
        $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
        $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
        $('row:last c', sheet).attr( 's', '2' );  //bold

//move text from column B to column A and empty columns B through F of rows with totals
        var rows = $('row c', sheet);  //create array of all rows (0 - N)      
        var copyPaste; var dateRefRate; var newDate;
        var emptyCellCols = ['B', 'C', 'D', 'E', 'F'];
        var twoDecPlacesBoldCols = ['G', 'H', 'I', 'J', 'L', 'M', 'O', 'P'];
        var boldCols = ['A'];
        for ( i=2; i < rows.length; i++ ) {                  
            copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();
            if ( copyPaste == 'Summen per Laufzeitende' ||
                 copyPaste == 'Totals'                     ) {
                $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste);                    
                for ( y=0; y < emptyCellCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+emptyCellCols[y]+']', sheet).text('');
                }
                for ( y=0; y < twoDecPlacesBoldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+twoDecPlacesBoldCols[y]+']', sheet).attr( 's', twoDecPlacesBold );
                }
                for ( y=0; y < boldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+boldCols[y]+']', sheet).attr( 's', '2' );  //bold
                }
            }           
        }            
    },
    exportOptions: {
        columns: function(column, data, node) {
            if (column > 15) {
                return false;
            }
            return true;
        },
        //passing "undefined" also works but only with quotes
        modifier: { selected: null }, //make sure all records show up
        format: {
            body: function ( data, row, column, node ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {  
                        if (column === 3 || column === 4 || column > 5) {
                            if (lang == 'de') { //this time we use the English formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods with spaces
                                arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                                //join the pieces together with a period if not empty
                                if (arr[0] > ''  || arr[1] > '') {
                                    data = arr[0] + '.' + arr[1];
                                } else {
                                    return '';
                                }
                            } else {
                                data = data.toString().replace( /[\,]/g, "" );
                            }
                            //result a number still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            data = data.toString().replace( /[^\d.-]/g, "" ); 
                            //percent must be adjusted to fraction to work ok
                            if (column == 4) {
                                if (data !== '') {
                                    data = data / 100;
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                return data;
            }
        }
    }
};

badr221 · March 2020

Tell me, for exemple if if i apply the code inside comment thats gonna work with copy and paste for exemple have an element r
$('c[r=A2]', sheet).attr('s', '7');
and want to add 51 style (center)
How it gonna work with the code that your write on the link

badr221 · March 2020

Yes i have 3 line 1 is of title and 2 and 3 is from message using \n so how to draw it like in picture the problem is when am adding another style is overwrite all other styles thanks

rf1234 · March 2020

"the problem is when am adding another style is overwrite all other styles thanks"

exactly! For that reason you need to make your own styles. You can only apply ONE style to a cell not several! So if you want to apply multiple features you must create a single style that has it all. That is the only reason why I made all of that stuff!

Needless to say that all of this is much easier with PHP Spreadsheet

badr221 · March 2020

okay thanks a lot

rf1234 · March 2020

You are welcome!
That's the problem with the client side Excel export: It is great and easy if you don't need additional customization. As soon as you need more: It becomes a night mare for most developers ... That is not @allan's or anybody else's fault but it is worth noting.
Whenever you are sure the standard Excel export won't be enough: Do it server side with the right tools!

Datatable multiple built-in style in excelhtml5

Datatable multiple built-in style in excelhtml5

Answers

Howdy, Stranger!

Categories

DataTables

Datatable multiple built-in style in excelhtml5

Datatable multiple built-in style in excelhtml5

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables