Datatables removes first zeros on excel excel export

Hi, Im using a datatables library in a laravel project, i have for example a table in first column that has: 024132236 while exporting that to excel i get 24132236 , also i see that the format of the column is General instead of Text.

i tried to change from buttons.html5.js to declare the first column as Text on export like:

	var addRow = function ( row ) {
			var cells = [];
		
			for ( var i=0, ien=row.length ; i<ien ; i++ ) {
				var cellData = row[i].replace(/&(?!amp;)/g, '&amp;');
				if (i === 0) { 
					cellData = "=\"" + cellData + "\"";
				}
				cells.push('<c t="inlineStr"><is><t>' + cellData + '</t></is></c>');
			}
		
			return '<row>'+cells.join('')+'</row>';
		};

also i tried that code in table_script.blade.php:

 buttons: [
                'colvis', 'copy',
                {
                    extend: 'excelHtml5',
                    title: '{!! Lang::get('panel.print_schedule') !!}',
                    message: getPrintTitle('{!! Lang::get('panel.print_schedule_title') !!}'),
                    exportOptions: {
                        columns: ':visible',
                        format: {
                            body: function(data, column, row, node) {
                                if (column === 0) {
                                    return "=\"" + data + "\"";
                                } else {
                                    return data;
                                }
                            }
                        }
                    },
                },

but nothing works always exporting without first zero! is there any way to fix that? or another library?

LaryAI

3 years ago

Level 58

To prevent Datatables from removing the first zeros on Excel export, you can use the customizeData option in the excelHtml5 button configuration. This option allows you to modify the data before it is exported to Excel. You can use a regular expression to check if the data starts with a zero and add an apostrophe before it to force Excel to treat it as text. Here's an example:

$('#example').DataTable( {
    buttons: [
        {
            extend: 'excelHtml5',
            customizeData: function(data) {
                for (var i = 0; i < data.body.length; i++) {
                    for (var j = 0; j < data.body[i].length; j++) {
                        var cell = data.body[i][j];
                        if (/^0/.test(cell)) {
                            data.body[i][j] = "'" + cell;
                        }
                    }
                }
            }
        }
    ]
} );

This code checks each cell in the table data and adds an apostrophe before it if it starts with a zero. This will force Excel to treat the cell as text and preserve the leading zeros.

1 like

Please or to participate in this conversation.