File Export Issues

ibrahimayhan

Hi Brothers,
There Are Some Problems In Excel And PDF Transfer
Currency Looks Like I Want to Dry Money in a Table, but is Corrupted by Excel Transfer
View in Table

View on Excel Page

There are also 15 Columns for PDF Transfer
PDF Page Vertical Transfer and 8 Columns Other Columns Does Not Fit To Page
How can I solve these problems for now?

Apezdr

https://datatables.net/reference/button/excelHtml5

This should help you get started but the pertinent bits...

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa140066(v=office.10)#ssnumberformat-tag
This should give you an idea of what you need to do with the next piece of information I came across for you.

$('#myTable').DataTable( {
    buttons: [
        {
            extend: 'excelHtml5',
            text: 'Save as Excel',
            customize: function( xlsx ) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                var lastCol = sheet.getElementsByTagName('col').length - 1;
                var colRange = createCellPos( lastCol ) + '1';
                //Has to be done this way to avoid creation of unwanted namespace atributes.
                var afSerializer = new XMLSerializer();
                var xmlString = afSerializer.serializeToString(sheet);
                var parser = new DOMParser();
                var xmlDoc = parser.parseFromString(xmlString,'text/xml');
                var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
                var filterAttr = xmlDoc.createAttribute('ref');
                filterAttr.value = 'A1:' + colRange;
                xlsxFilter.setAttributeNode(filterAttr);
                sheet.getElementsByTagName('worksheet')[0].appendChild(xlsxFilter);
            }
        }
    ]
} );

function createCellPos( n ){
    var ordA = 'A'.charCodeAt(0);
    var ordZ = 'Z'.charCodeAt(0);
    var len = ordZ - ordA + 1;
    var s = "";

    while( n >= 0 ) {
        s = String.fromCharCode(n % len + ordA) + s;
        n = Math.floor(n / len) - 1;
    }

    return s;
}

Took the above code from a post on https://datatables.net/reference/button/excelHtml5 by F12Magic.

Based on what I was able to find you will need to dig into the file itself and change the code above to accommodate your field type requirements.

Good luck,
Adam

colin

Hi @ibrahimayhan ,

If Adam's suggestion doesn't work, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

ibrahimayhan

@Apezdr Thank you for your support,
In my worksheet, the Currency on L Column is Dry
how do I edit the code you specify
Can you describe me by L column?

ibrahimayhan

thank you for informing,

{
extend: 'excel',
text: 'Testing Excel',
customize: function (xlsx, row) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row c[r^="L"]', sheet).attr('s', 57);
}
}
Which format should I use as 5,3316 in Turkish lira format?