Excel Export Add Rows and Data

Monaxm · July 2016

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

    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' );
                }

                            });
                        }


                            }]

allan · July 2016

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

Monaxm · July 2016

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:)

allan · July 2016

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

Monaxm · July 2016

Thanks a lot for Info.

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

I haven't found any so far

allan · July 2016

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

NitishAcharya · August 2016

Hi Allan/Monaxm

How to add a new row to downloaded excel?

allan · August 2016

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

Allan

altank52 · August 2016

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

allan · August 2016

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

AugustLee · 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;
                                }

allan · August 2016

Awesome! Thanks for sharing this with us.

Allan

Santha · September 2016

AugustLee,

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

langke · October 2016

Awesome! Thanks very much..

Raghul · January 2017

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?

allan · January 2017

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

Allan

Raghul · 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

allan · January 2017

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

Raghul · January 2017

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

Raghul · January 2017

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]);

allan · January 2017

Very nice - thanks for sharing this with us!

Allan

pratikD · January 2017

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.

allan · January 2017

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

pratikD · January 2017

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

allan · January 2017

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

pratikD · January 2017

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.

allan · January 2017

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

Allan

nvi84123 · April 2017

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

nvi84123 · 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);
            });

pyronerd62 · 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]);
        };
    }
},

LePatay · June 2018

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!

Excel Export Add Rows and Data

Excel Export Add Rows and Data

Replies

Howdy, Stranger!

Categories

DataTables

Excel Export Add Rows and Data

Excel Export Add Rows and Data

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables