When printing input data in a datatable, the values ​​do not appear.

gused

Hello everyone, can you help me? I have a problem. I have a table with inputs. I entered numerical values ​​with commas. When I give the option to export to Excel, it does not save them with the commas.

this code

$(document).ready(function () {
            let exportOptions = {
                format: {
                    body: function (valor, row, col, node) {
                        if ($(node).children("input").length > 0) {
                            return $(node).children("input").first().val();
                        } else {
                            return valor;
                        }
                    }
                },
                columns: [1, 2, 3, 4]
            };
            $('#example1').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'excelHtml5',
                        exportOptions: exportOptions
                    },
                    {
                        extend: 'csvHtml5',
                        exportOptions: exportOptions

                    },
                    {

                        extend: 'pdfHtml5',
                        title: 'Expense Report',
                        filename: 'expense_report',
                        exportOptions: exportOptions
                    },

                ]

            });
        });

rf1234

I entered numerical values ​​with commas.

It all depends on what you mean with "commas"! Is a "comma" in your country an American or English decimal point? Or is it just a thousand separator?

In my country an American decimal point is a comma. And a period is a thousand separator. Just like in many or even most European countries. In some European countries a space is the preferred thousand separator. Or in Switzerland it is a single quote - sometimes, not always ...

Excel wants American inputs: If you change your inputs for the Excel export to be American style, it should work.

You'll find many examples in this forum.

I use this little helper function to get it right - depending on the user's language. "de" is German and "else" is English / American.

function toFloat(fn, spaceIfNaN) {
    if ( typeof spaceIfNaN === "undefined" ) {
        spaceIfNaN = false;
    }
    if (lang == 'de') {
        fn = fn.toString().replace( /[\.]/g, "" );
        fn = fn.toString().replace( /[\,]/g, "." );            
    } else  {
        fn = fn.toString().replace( /[\,]/g, "" );
    }
    fn = parseFloat(fn);
    if ( ! isNaN(fn) ) {
        return fn;
    }
    if ( spaceIfNaN ) {
        return "";
    }
    return 0;
}

allan

Or in Switzerland it is a single quote - sometimes, not always ...

I use that one personally - unambiguous, but I do get some funny looks sometimes

Allan

rf1234

I found this here: https://konzept-info.de/punkt_oder_komma.html

Here is a translation. So it is only partly a "language thing". Quite many Spanish speaking countries in the Americas use a decimal point while Spain itself uses a decimal comma. And in Switzerland it is really complicated ...

“Decimal point countries”

Countries that use a point as a decimal separator:

Australia, Botswana, Canada (English-speaking part), Costa Rica, Dominican Republic, El Salvador, Guatemala, Honduras, Hong Kong, India, Ireland, Israel, Japan, Korea (North and South), Liechtenstein (only for monetary amounts), Malaysia, Mexico, Namibia, Nicaragua, Nigeria, New Zealand, Pakistan, Panama, Philippines, Switzerland (only for monetary amounts and coordinates), Singapore, South Africa, Taiwan, Thailand, United Kingdom, United States

“Decimal comma countries”

Countries that use a comma as a decimal separator:

Albania, Andorra, Argentina, Belgium, Bolivia, Brazil, Bulgaria, Canada (French-speaking part), Chile, Colombia, Croatia, Cuba, Denmark, Ecuador, Estonia, Faroe Islands, Finland, France, Georgia, Germany, Greece, Greenland, Indonesia, Iceland, Italy, Latvia, Liechtenstein (except monetary amounts), Lithuania, Luxembourg, Macedonia, Moldova, Netherlands, Norway, Austria, Paraguay, Peru, Poland, Portugal, Romania, Russia, Sweden, Switzerland (excluding monetary amounts and coordinates), Serbia, Zimbabwe, Slovakia, Slovenia, Spain, Czech Republic, Turkey, Ukraine, Hungary, Uruguay, Venezuela, Belarus

Translated with DeepL.com (free version)

allan

Wikipedia have a nice graphic showing the distribution around the world:

gused

Here in Colombia, commas are used for decimals, but I have not yet found a solution to export input values ​​with commas to Excel.

rf1234

but I have not yet found a solution to export input values ​​with commas to Excel.

Just use my "toFloat" helper function above. The "de" logic is the one relevant for you.

exportableNumber = 
inputNum.toString().replace( /[\.]/g, "" ).replace( /[\,]/g, "." );

This converts e.g. 1.000.000,00 to 1000000.00 - and that is what Excel and your DBMS will understand. If you don't use thousand separators you can skip the first "replace" statement.

I have this logic in the "exportOptions" of my Excel export. I want to convert amounts and percentages so that Excel understands them. I don't want zeroes if empty but spaces. This uses the helper function above.

exportOptions: {
    format: {
        body: function ( data, row, column, node ) {
            if ( exportColumnsTwoDecPlaces[column] || exportColumnsFourDecPlaces[column] ) {
                data = toFloat(data, true);
                //percent must be adjusted to fraction to work ok
                if ( exportColumnsFourDecPlaces[column] ) {
                    if (data !== '') {
                        data = data / 100;
                    }
                }   
            }
            return data;
        },