Excel export: Decimal locale format

Excel export: Decimal locale format

claudiometclaudiomet Posts: 3Questions: 0Answers: 0

Hi users!

I'm using datatables creating the datatable from HTML table content.
In the HTML table the decimal numbers are formatted with dot
( ex: 0.5 | 1.2 | 5.6 | 0.03 | 67.8 )

My local computer configuration is adapted to my country: Dot for thousands and Comma for decimals.

But, when export to Excel using Buttons extension, the numbers containing a zero at the left of the dot, in Excel are represented as text for my local configuration and with dot instead comma.
The other numbers are well, are numbers with comma as decimal separator.
( ex: 0.5 | 1,2 | 5,6 | 0.03 | 67,8 )

How can I solve this without touching the HTML table? Thanks in advance!

Replies

  • ulfmalmulfmalm Posts: 1Questions: 0Answers: 0

    I have the same problem. If I look in the xml data inside the Excel file it turns out that the numbers starting with "0." are stored as strings:

    <c t="inlineStr"><is><t>0.37</t></is></c>

    Numbers that start with something else are stored as numbers:

    <c t="n"><v>4.84</v></c>

    I don't have a solution, other than some workaround in the html.

  • claudiometclaudiomet Posts: 3Questions: 0Answers: 0

    Searching for "inlineStr" in file "buttons.html5.js" there are some lines who read and replace minus and leading zeros to convert number to strings. Testing some modifications to patch this problem.

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

    Could you try the 1.1.2 release of Buttons? I believe this should actually be fixed there.

    Allan

  • KomonyiKomonyi Posts: 1Questions: 0Answers: 0

    Its still converting 0 to string.

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

    Thanks for letting me know. I'll take a look into it as soon as possible.

  • claudiometclaudiomet Posts: 3Questions: 0Answers: 0

    Yes, I have 1.1.2 version and the same problem. Waiting for a fix.
    Thanks allan!

  • ssmssm Posts: 1Questions: 0Answers: 0

    I am facing same issue. Can I get any suggestion. thank.

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

    Hi,

    Sorry for the delay in looking into this. I've just committed the required fix. The nightly version will rebuild in a few minutes time and this fix will be included in the 1.2 release of Buttons which should be next week.

    Regards,
    Allan

  • nomanzafarnomanzafar Posts: 1Questions: 0Answers: 0

    I have downloaded data tables, Thank you for this amazing job.
    Excel button is no more available.
    i used the exact code as in example copy, pdf and column visibility buttons are available but excel button is gone. Apparently your last fixed resulted in this bug.

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

    Can you link to the page showing the issue so we can resolve it before the 1.2 release please.

    Allan

  • rogerarogera Posts: 9Questions: 2Answers: 1

    For me it works as expected in Chrome (version 50) with the nightly release.
    But in Internet Explorer 11 the Excel file can't be opened. Instead I get a dialog with "We found a problem with some content in... Do you want us to try to recover..." but it opens up empty.

    Sample html and javascript

    <!DOCTYPE html>
    <html>
    <head>
    
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">    
    
        <!--<link href='https://cdn.datatables.net/1.10.11/css/jquery.dataTables.min.css' rel='stylesheet'/> -->
        <link href='https://nightly.datatables.net/css/jquery.dataTables.min.css' rel='stylesheet'/>
        <!-- <link href='https://cdn.datatables.net/buttons/1.1.2/css/buttons.dataTables.min.css' rel='stylesheet'/> -->
        <link href='https://nightly.datatables.net/buttons/css/buttons.dataTables.min.css' rel='stylesheet'/>
        
        <script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-1.11.3.min.js"></script>
        
        <!-- <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.11/js/jquery.dataTables.js"></script> -->
        <script type="text/javascript" language="javascript" src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>
        
        <!-- <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/1.1.2/js/dataTables.buttons.js"></script> -->
        <script type="text/javascript" language="javascript" src="https://nightly.datatables.net/buttons/js/dataTables.buttons.js"></script>
        
        <script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    
        <!-- <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/1.1.2/js/buttons.html5.min.js"></script> -->
        <script type="text/javascript" language="javascript" src="https://nightly.datatables.net/buttons/js/buttons.html5.min.js"></script>
        
        <title>test</title>
    </head>
    <body>
    
        <h1>Test excel export</h1>
        
        <table id="example" class="display">
            <thead>
                <tr>
                    <th>Text</th>
                    <th>Number</th>
                    <th>Decimal number</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Testing 0</td>
                    <td>0</td>
                    <td>0.45</td>
                </tr>
                <tr>
                    <td>Testing 1</td>
                    <td>1</td>
                    <td>1.23</td>
                </tr>
                <tr>
                    <td>Testing 2</td>
                    <td>2</td>
                    <td>2.34</td>
                </tr>
                <tr>
                    <td>Testing minus 1</td>
                    <td>-1</td>
                    <td>-1.45</td>
                </tr>
                <tr>
                    <td>Testing minus 2</td>
                    <td>-2</td>
                    <td>-2.45</td>
                </tr>
            </tbody>
        </table>
        
    <script type="text/javascript">
    
        $(document).ready(function() {
            
            var t = $('#example').DataTable({
                dom: 'Bfrtip',
                buttons: ['excel']
            });
    
        } );
        
        
    
    </script>
    
    </body>
    </html>
    
    

    If I use the 1.1.2 version of Buttons and 1.10.11 of Datatable Internet Explorer and Chrome opens the excel file but 0 and 0.45 is formatted as text.

    Regards
    /Roger

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

    Hi Roger,

    Thanks for the file. We'll look into this and post back when fixed. Lots of work going on with the Excel export atm, so there is potential for breakage in the nightly.

    Allan

This discussion has been closed.