Set column type as text in DataTable export

user_10052138 0
Viewed 193

I need to set a column as type text in Excel export from DataTable. All columns are in 'General' mode, so if data from a cell is datelike (2020-11-01 e.g.), Excel trats it like a date and I need it to be a string. This is my code regarding the table.

<script>
    $(document).ready( function () {
        var groupColumn = 0;
        var table = $('#Table1').DataTable( {
            "paging":   true,
            "autoWidth": false,
            "info":     false,

            language: {
                search: "_INPUT_",
                searchPlaceholder: "Filtra risultati",
            },
            dom: 'Bfrtip',
            buttons: [
                {
                    text: 'Esporta dati',
                    title: '',
                    header: true,
                    extend: 'excel',
                    orientation: 'landscape',
                    pageSize: 'LEGAL'
                },
            ],
            select: true,

        } );
    } );
</script>

There is a way to force DataTable to set my third column as TEXT?

export datatable excel php
1 Answers

You should try:

// (...)
buttons: [
  {
    extend: 'excelHtml5',
    title: "Your Report Name",
    exportOptions: {
        columns: ':visible',
    },
    customize: function (xlsx, data) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        $('row c[r^="C"]', sheet).attr('s', '0');
    }
},

But note that will set cell type to GENERAL, not TEXT.

This is searching for all cells (row c) in third column (r^="C") and setting the style (s attribute) to 0 (the datatables built-in style for GENERAL content).

If you are reading this answer trying to do the opposite, formating numbers as numbers and dates as dates, the following code did the trick for me:

// (...)
buttons: [
  {
    extend: 'excelHtml5',
    title: "Your Report Name",
    exportOptions: {
        columns: ':visible',
        format: {
            body: function (data, row, column, node) {
                if (column === 3) { // if you have a number in 4th column
                    return parseFloat(data);
                } else if (column === 2) { // if you have a date in 3rd column
                    var dateVal = Date.parse(data); // yyyy-MM-dd
                    var ticks = Math.round(25569 + (dateVal / (86400 * 1000)));
                    return ticks;
                } else {
                    return data;
                }
            }
        }
    },
    customize: function (xlsx, data) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        var styles = xlsx.xl['styles.xml'];
        var dateStyle = "<xf numFmtId='14' fontId='0' fillId='0' borderId='0' applyFont='1' applyFill='1' applyBorder='1' xfId='0' applyNumberFormat='1' />";

        styles.childNodes[0].childNodes[5].innerHTML += dateStyle;

        $('row c[r^="D"][s=65]', sheet).attr('s', '64');

        $('row c[r^="C"][s=65]', sheet).attr('s', '67');
    }
},
user_67325250
Related