Want to change format and styling of Exported Excel file

Want to change format and styling of Exported Excel file

mrumarasgharmrumarasghar Posts: 12Questions: 4Answers: 1

I am using dataTables export buttons. But while using it I get that format of output ( http://imgur.com/a/bXxyQ ) .

But my expected output should be like this ( http://imgur.com/a/07uW3 ).

So, the expected output should be merged data and without border/ grid for a few row.

Is there any documentation that I can search to explore that feature?

this is my code

                  customize: function(xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            var numrows = 3;
                            var clR = $('row', sheet);

                            //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 () {
                                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) {
                                msg='<row r="'+index+'">'
                                for(i=0;i<data.length;i++){
                                    var key=data[i].key;
                                    var value=data[i].value;
                                    msg += '<c t="inlineStr" r="' + key + index + '">';
                                    msg += '<is>';
                                    msg +=  '<t>'+value+'</t>';
                                    msg+=  '</is>';
                                    msg+='</c>';
                                }
                                msg += '</row>';
                                return msg;
                            }


                            //insert
                            var r1 = Addrow(1, [{ key: 'A', value: $('#report_title').text() }]);
                            var r2 = Addrow(2, [{ key: 'A', value: 'Company Name: '+$('#company_name').val() }]);
                            //var r3 = Addrow(3, [{ key: 'A', value: 'TEST' }, { key: 'B', value: 'TEST' }]);

                            sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + sheet.childNodes[0].childNodes[1].innerHTML;

                            // set the background color of the entire row
                            // https://datatables.net/reference/button/excelHtml5
                            //$('row:first c', sheet).attr('s', '7');
                            //$('row:nth-child(4) c', sheet).attr('s', '8');
                            $('row:nth-child(3) c', sheet).attr('s', '7');
                            $('row:first', sheet).attr( 's', '2' );
                            //$('row c[r*="1"]', sheet).attr( 's', '2' );
                            $('row[r="2"]', sheet).attr( 's', '2' );
                            //$('row c[r*="2"]', sheet).attr( 's', '2' );
                            //$('row:nth-child(4) c', sheet).attr('s', '2');
                        },
                        title: $('#report_title').text(),
                    },  

Answers

  • mrumarasgharmrumarasghar Posts: 12Questions: 4Answers: 1

    You can see my code as we are adding new rows via sheet object. So in my opinion there should be an option to update the style of that sheet. Can I get its documentation that from where you get that adding new rows code and from where you are creating that sheet? from which source you allow that features? Is there any source or documentation that I can go through to find the solution for my issue?

  • mrumarasgharmrumarasghar Posts: 12Questions: 4Answers: 1

    As i found that link ( http://officeopenxml.com/SSstyles.php ) but when I visit that link it shows that

    "Not Found

    The requested URL /SSstyles.php was not found on this server.

    Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request."

  • allanallan Posts: 63,350Questions: 1Answers: 10,443 Site admin

    Odd - it appears to load okay for me. Perhaps a temporary blip in their hosting. However, using the customize method is the correct way to do this, but I'm afraid I haven't documents the XML - for that you'd need to refer to the XSLX spec.

    Allan

This discussion has been closed.