excelHtml5

etamin419

Hi all
Why does Excel cut off numbers after the decimal point? PDF and CSV show the correct numbers.

I use

buttons: ['pageLength',
                    {
                        extend: 'copyHtml5',
                        text: '<i class="fa fa-files-o"></i>',
                        titleAttr: 'Copy'
                    },
                    {
                        extend: 'excelHtml5',
                        filename: 'ArchiveDPI',
                        title: 'Table',
                        //autoFilter: true,
                        sheetName: 'Exported data',
                        titleAttr: 'Excel',
                        //customize: function (xlsx) {
                            //Get the built-in styles
                            //refer buttons.html5.js "xl/styles.xml" for the XML structure
                            //var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            //$('row:gt(0) c[r^="B"]', sheet).attr('s', '64');
                        //},
                        split: [
                            {
                                extend: 'pdfHtml5',
                                filename: 'DPI',
                                text: 'PDF',
                                title: 'Table',
                                pageSize: 'A3',
                                orientation: 'landscape',
                                download: 'open',
                                //messageTop: 'PDF created by PDFMake with Buttons for DataTables.'
                                /*exportOptions: {
                                    modifier: {
                                        selected: true
                                    },
                                    columns: [ 0, 1, 2, 5 ]
                                }*/
                            },
                            {
                                extend: 'csv',
                                filename: 'ArchiveDPI',
                            }],
                    },
                    {
                        extend: 'print',
                        title: 'Table',
                    }

                ]

rf1234

Why does Excel cut off numbers after the decimal point?

That's because you are not using a decimal point. Use a decimal point and it is going to work.

Excel interprets your data: You are exporting "14,2". For Excel this is just something broken and hence it discards the comma in your case.

If you export 14.2 it might still show up in your local Excel version as 14,2 because:
- Excel understands you are sending a number (proper decimal point)
- Excel will convert the number to your respective regional settings which may be using a decimal comma instead of a decimal point

Or in a nutshell: The Excel import interface understands American English only - if you will - but Excel can "speak" other languages - depending on the regional settings of your Excel installation.

rf1234

@allan: Would you know whether the Excel export could be configured in a way that you can select the regional settings that Excel should apply during workbook creation? For example in the case above: If we could apply German or other continental European settings the export with the decimal comma should work.

But that requires that the Excel export can be configured in this way at all - which I don't know. That would make life a lot easier for many users.

This looks promising:
https://answers.microsoft.com/en-us/msoffice/forum/all/attempting-to-add-a-locale-to-the-exporting-excel/84e05bde-7aa7-4bed-b1f9-5c92e6558e2d

"Yes, there is a way to insert the locale into an Excel file during export. You can set the locale in the Excel file by changing the language settings in the Excel Options. To do this, go to File > Options > Language. From there, you can select the language and locale that you want to use for the file."

If that could be done programmatically during the export, that would be great.

etamin419

That's because you are not using a decimal point. Use a decimal point and it is going to work.
Excel interprets your data: You are exporting "14,2". For Excel this is just something broken and hence it discards the comma in your case.
If you export 14.2 it might still show up in your local Excel version as 14,2 because:
- Excel understands you are sending a number (proper decimal point)
- Excel will convert the number to your respective regional settings which may be using a decimal comma instead of a decimal point
Or in a nutshell: The Excel import interface understands American English only - if you will - but Excel can "speak" other languages - depending on the regional settings of your Excel installation.

Thank you! I didn't think this was related to local installation. I changed the input to dot and it worked

allan

Would you know whether the Excel export could be configured in a way that you can select the regional settings that Excel should apply during workbook creation?

I strongly suspect so, but I don't actually know. The raw data format is with a period for the decimal place. But it is possible to configure a formatter for the cell in Excel to have it display in different formats a comma decimal place. What that format isn't I haven't looked into it (yet - its on my list ).

Think of it like dates and times - always use ISO8601 for the data "at rest" or "on the wire" and then format it for the end user in question at display time. The same applies to numbers - use a period decimal place and no thousands separators.

Allan

rf1234

Think of it like dates and times - always use ISO8601 for the data "at rest" or "on the wire" and then format it for the end user in question at display time. The same applies to numbers - use a period decimal place and no thousands separators.

I've been doing it that way, Allan - also using extensive customization. I even made my own fonts ... But of course if it is avoidable by setting a locale for export that would make life a lot easier: I could allow the rendered fields to be exported 1:1, without needing to convert them back to the ISO standard!

Here is a link on what I've been doing regarding customization and the like:
https://datatables.net/forums/discussion/77996/more-flexibel-excel-export

Search for "customize: function( xlsx )" on that page and you see what I mean.

Roland