Excel Export Add Rows and Data

Excel Export Add Rows and Data

MonaxmMonaxm Posts: 3Questions: 0Answers: 0

Hello everyone,

i am now trying to modify the table before saving it as EXCEL and i don't know how to add two sentences before the table starts. For example add one new row before the table and input there a text. I am now using the following code to make the column G blue. I can use the customize ability, but no idea how :smiley:

    buttons: [{
                        extend: 'excel',
                        text: 'Save in EXCEL',
                        filename: 'td900',
                        customize: function(xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                           // $('c[r=A1] t', sheet).text( 'Custom text' );

                            // Loop over the cells in column `F`
                            $('row c[r^="G"] ', sheet).each( function () {
                                // Get the value and strip the non numeric characters

                                   if ( $( this).text() !== "needed Adjustment" ) {
                    $(this).attr( 's', '20' );
                }

                            });
                        }


                            }]
«1

Replies

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

    The commented out line on line 7 - does that rewrite the text for that cell as you would expect? It looks like it should.

    The problem I think you are running into is that you basically need to move every cell down by one to make a new row at the top. What you might find easier is to use the customizeData option of the excelHtml5 button type to modify the data and insert what you need there. I've not tried that for this specific use case, but it might be a little easier...

    Allan

  • MonaxmMonaxm Posts: 3Questions: 0Answers: 0

    Hey Allan, thanks a lot for your respond. I was thinking to do this with the for (...) loop, but unfortunately i can change only text values in the Excel with this parameters

    $('c[r=A1] t', sheet).text( 'Custom text' );

    When I am using other parameters such as
    $('[r=B2] v', sheet).text( 'New TEXT or new numeric value ' ); than after downloading it shows the message :
    "We found a problem with some content in '***.xlsx'. Do you want us to try to recover as much as we can?"

    Do You know the right parameter to change the numeric values ? I have also tried following syntax;
    $('[r=B2] v', sheet).val( 'New TEXT or new numeric value ' )

    but it doesn't help either:)

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

    v is the correct tag to use for a number, but it can contain only a number - you can't just put text into it.

    You could refer to the Open Office XML documentation to see what the valid markup is.

    As I say, I suspect that using customizeData might be a little easier since then you won't need to muck around with the XML.

    Allan

  • MonaxmMonaxm Posts: 3Questions: 0Answers: 0

    Thanks a lot for Info.

    Do you know { and can link me to :) } any examples of _customizeData _ .

    I haven't found any so far :(

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

    I don't think there is actually I'm afraid. I'll try to make some time in the next week or two to create one.

    Allan

  • NitishAcharyaNitishAcharya Posts: 1Questions: 0Answers: 0

    Hi Allan/Monaxm

    How to add a new row to downloaded excel?

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

    Use the customizeData option or the customize option. See the excelHtml5 documentation for more details.

    Allan

  • altank52altank52 Posts: 1Questions: 0Answers: 0

    Hello @Monaxm , @allan and @NitishAcharya ,
    Is the case already solved? I also need to add a sentence just on first row. If there is already proper answer, would you guys please share also here ?
    Thank you

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

    You can use the customize option to modify the created XLSX document. Basically you would need to move all cells down one row and then insert your message in the top row. Probably not trivial to do I'm afraid.

    Allan

  • AugustLeeAugustLee Posts: 1Questions: 0Answers: 0
    edited August 2016

    Use "customize" option to move all cell down one row is work.
    A Simple Sample which show As Below:

                                customize: function (xlsx) {
                                        console.log(xlsx);
                                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                                        var downrows = 3;
                                        var clRow = $('row', sheet);
                                        //update Row
                                        clRow.each(function () {
                                            var attr = $(this).attr('r');
                                            var ind = parseInt(attr);
                                            ind = ind + downrows;
                                            $(this).attr("r",ind);
                                        });
    
                                        // Update  row > c
                                        $('row c ', sheet).each(function () {
                                            var attr = $(this).attr('r');
                                            var pre = attr.substring(0, 1);
                                            var ind = parseInt(attr.substring(1, attr.length));
                                            ind = ind + downrows;
                                            $(this).attr("r", pre + ind);
                                        });
    
                                        function Addrow(index,data) {
                                            msg='<row r="'+index+'">'
                                            for(i=0;i<data.length;i++){
                                                var key=data[i].k;
                                                var value=data[i].v;
                                                msg += '<c t="inlineStr" r="' + key + index + '" s="42">';
                                                msg += '<is>';
                                                msg +=  '<t>'+value+'</t>';
                                                msg+=  '</is>';
                                                msg+='</c>';
                                            }
                                            msg += '</row>';
                                            return msg;
                                        }
    
                                        //insert
                                        var r1 = Addrow(1, [{ k: 'A', v: 'ColA' }, { k: 'B', v: '' }, { k: 'C', v: '' }]);
                                        var r2 = Addrow(2, [{ k: 'A', v: '' }, { k: 'B', v: 'ColB' }, { k: 'C', v: '' }]);
                                        var r3 = Addrow(3, [{ k: 'A', v: '' }, { k: 'B', v: '' }, { k: 'C', v: 'ColC' }]);
                                       
                                        sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2+ r3+ r4+ sheet.childNodes[0].childNodes[1].innerHTML;
                                    }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Awesome! Thanks for sharing this with us.

    Allan

  • SanthaSantha Posts: 1Questions: 0Answers: 0

    AugustLee,

    Awesome! I got the answer but it's not working in IE11 .Can you please help me.

  • langkelangke Posts: 1Questions: 0Answers: 0

    Awesome! Thanks very much..

  • RaghulRaghul Posts: 4Questions: 0Answers: 0

    Hi @allan I am trying to add extra rows and data in them before exporting the datatable in excel. I tried the solution suggested by @AugustLee. Its is working fine for chrome and firefox. But for IE and Safari the new rows are not getting added. is there a solution for this? can you guys help?

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

    Try checking the exported XML. It might be that Safari and IE are adding empty name space tags.

    Allan

  • RaghulRaghul Posts: 4Questions: 0Answers: 0
    edited January 2017

    Hi @allan Thanks for the quick reply. I tried adding name space tags for new rows. But still no luck. I have made a demo in fiddle https://jsfiddle.net/xevpdeo1/12/ . In chrome new rows are getting added but not in IE. Please check it out and suggest some pointers. Thanks in advance

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

    My point was that you need to remove them rather than adding them. IE at least adds them incorrectly.

    What it might be worth doing is exporting both from Chrome and IE and then comparing the output from the two to see what the difference is.

    Allan

  • RaghulRaghul Posts: 4Questions: 0Answers: 0

    Hi @allan i tried comparing the xml of exported excel sheet from both chrome and IE. It seems that the new rows itself is not getting added in IE. (i.e)
    sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2+ r3+ r4+ sheet.childNodes[0].childNodes[1].innerHTML; This step is not happening. please check the fiddle https://jsfiddle.net/xevpdeo1/20/. I have consoled the rows of the excel sheet after adding the rows. The rows are not getting added in IE. It would be of great help if you suggest some solution for this cause. Thanks

  • RaghulRaghul Posts: 4Questions: 0Answers: 0

    IE and safari does not support innerHTML property. Because of which the new rows were not getting added in IE and safari browsers. Below piece of code works fine in all browsers.

    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var numrows = 4;
    
    //update Row
    clR.each(function () {
      var attr = $(this).attr('r');
      var ind = parseInt(attr);
      ind = ind + numrows;
      $(this).attr("r", ind);
    });
    
                       // Create row before data
                         $('row c ', sheet).each(function (index) {
                               var attr = $(this).attr('r');
    
                               var pre = attr.substring(0, 1);
                               var ind = parseInt(attr.substring(1, attr.length));
                               ind = ind + numrows;
                               $(this).attr("r", pre + ind);
                           });
    
                           function Addrow(index, data) {
                            var row = sheet.createElement('row');
                            row.setAttribute("r", index);               
                               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");
                                   c.setAttribute("s", "2");
                                   c.setAttribute("r", key + index);
    
                                   var is = sheet.createElement('is');
                                   var t = sheet.createElement('t');
                                   var text = sheet.createTextNode(value)
    
                                   t.appendChild(text);                                       
                                   is.appendChild(t);
                                   c.appendChild(is);
    
                                   row.appendChild(c);                                                                                                                          
                               }
    
                               return row;
                           }
    
    var r1 = Addrow(1, [{ key: 'A', value: '' }, { key: 'B', value: 'ID' }]);
    var r2 = Addrow(2, [{ key: 'A', value: '' }, { key: 'B', value: 'Report Date' }]);                           
    var r3 = Addrow(3, [{ key: 'A', value: '' }, { key: 'B', value: 'Export Date' }]);
    var r4 = Addrow(4, [{ key: 'A', value: '' }, { key: 'B', value: 'Name' }]);             
    
    
                            var sheetData = sheet.getElementsByTagName('sheetData')[0];
    
                            sheetData.insertBefore(r4,sheetData.childNodes[0]);
                            sheetData.insertBefore(r3,sheetData.childNodes[0]);
                            sheetData.insertBefore(r2,sheetData.childNodes[0]);
                            sheetData.insertBefore(r1,sheetData.childNodes[0]);
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Very nice - thanks for sharing this with us!

    Allan

  • pratikDpratikD Posts: 4Questions: 1Answers: 0

    Hi,

    @Raghul & @allan thanks for sharing this piece of code helped me lot. Just one more thing is there any function like(sheet.createElement('row')) for edit/update specific column.

    I have one column which contain percentages. But I am not getting decimal values in xlsx, working fine in the csv. I am getting 100% and I want this 100.52%. xlsx rounding off the value.

    Thanks In advance.

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

    If you click on that cell in Excel and look in the editing box at the top, is the full unrounded value there?

  • pratikDpratikD Posts: 4Questions: 1Answers: 0

    Hello,
    @allan yes full unrounded value is coming in the editing box at the top.
    But for values like 600.00% it coming as 600% and for values like 600.11% it is coming unrounded value in editing box at the top.

    I have created demo : http://jsfiddle.net/xevpdeo1/28/
    Please look into this.

    Thanks In advance

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

    Buttons will detect a percentage number with a single decimal place and format that accordingly. It doesn't detect two decimal places as there isn't a built in formatter for that in Excel (that I'm aware of), hence why the number is displayed rounded.

    You would need to add a custom formatter using the customize option to the Excel file that is generated if you would like to have the two decimal places shown in the spreadsheet.

    Allan

  • pratikDpratikD Posts: 4Questions: 1Answers: 0

    Hi,

    do you have any reference code, that I can look up. I have tried the customize option but in that case IE and safari downloaded as blank column data.

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

    The Excel export code in Buttons is basically the limit of my knowledge on the topic of creating Excel XLSX files.

    Allan

  • nvi84123nvi84123 Posts: 2Questions: 0Answers: 0

    after customise function, excel sheet only show data upto Z column, AA column and after that all columns ar empty.

  • nvi84123nvi84123 Posts: 2Questions: 0Answers: 0
    edited April 2017

    I have solved this issue using:

        $('row c ', sheet).each(function () {
    
                    var attr = $(this).attr('r');
    
                    if (attr.length == 3) {
                        var pre = attr.substring(0, 2);
                        var ind = parseInt(attr.substring(2, attr.length));
                    } else {
                        var pre = attr.substring(0, 1);
                        var ind = parseInt(attr.substring(1, attr.length));
                    }
                    ind = ind + downrows;
                    $(this).attr("r", pre + ind);
                });
    
  • pyronerd62pyronerd62 Posts: 1Questions: 0Answers: 0
    edited September 2017

    I know I'm late to the game but here's a smaller version using both customize and customize data without having to specifically edit the xml and deal with browser issues.

    {
        extend: 'excel',
        header:false,
        customize: function ( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            //Bold Header Row
            $('row[r=3] c', sheet).attr( 's', '2' );
            //Make You Input Cells Bold Too
            $('c[r=A1]', sheet).attr( 's', '2' );
            $('c[r=A2]', sheet).attr( 's', '2' );
        },
        customizeData: function(data){
            /We want the first line so we disabled the header above. Let's add in our descriptions. Then we're going to add them to the top of the body and do the bolding ourselves with the customize function.
            var desc = [
                ['ID','TEST ID'],
                ['Report Date',' TEST Report Date']
            ];
            data.body.unshift(data.header);
            for (var i = 0; i < desc.length; i++) {
                data.body.unshift(desc[i]);
            };
        }
    },
    
  • LePatayLePatay Posts: 20Questions: 3Answers: 1

    Thanks for this practical case.

    I ran into several more browser-related issues with this, I summarized the best way to go in this StackOverflow answer: https://stackoverflow.com/a/50719888/5426777.

    Hope it will help you!

This discussion has been closed.