KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

DataTable export to Excel - some data in column amount become text #2995

Open Katherine Wong opened this topic on on Feb 22, 2023 - 1 comments

Katherine Wong commented on Feb 22, 2023

Hi, I am using the Buttons extension for DataTables to export data to excel.

I found some problem which after export, some data in column amount / number will become text, especially those with thousand separator. May I know how to resolve it?

Sebastian Morales commented on Feb 22, 2023

It looks like you formatted the column with thousand separator and decimal point. Thus, the Buttons extension exports those values as text. Nevertheless, you can try this export option to see if it removes the comma and period symbols to export those values as numbers:

DataTables::create(array(
    ...
    "options" => array(
        ...
        "buttons" => array(
            array(
                "extend" => "excelHtml5",
                "exportOptions" => array(
                    "format" => array(
                        "body" => "function ( data, row, column, node ) {
                            if (column === 2)  // change 2 to the order of your numeric column
                                data = data.replace( /[,\.]/g, '' ); // remove all commas and periods
                            if (column === 4)  // change 4 to the order of your numeric column
                                data = data.replace( /[,\.]/g, '' ); // remove all commas and periods
                            return data;
                        }"
                    )
                )
            )
        )
    )
));

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None