When using Datatables excel button, the line breaks are missing in the excel file.

Matt Jacobson commented on Jul 19, 2023

I have a table created with Datatables and some of the cells have <br> tags in the to force a line break at the correct position. When I click on the button to export to an Excel file, it concatenates all the lines in that cell. The code I'm using is below. I've also tried using 'extend'=>'excelHtml5', but that didn't work.

Any advice would be great!

DataTables::create(array(
	"dataStore"=>$this->dataStore('results'),
	"columns"=>$columns,
	"plugins" => ["Buttons", "FixedHeader", "Responsive", "Scroller"],
	"cssClass"=>array(
		"table"=>"table table-striped table-bordered",
	),
	"options"=>array(
		"dom" => 'B lfrtip',
		"colReorder"=>true,
		"stateSave" => true,
		"themeBase"=>"bs",
		"fixedHeader"=>true,
		"select"=>true,
		"searching"=>true,
		"paging"=>true,
		"pageLength" => 25,
		"lengthMenu" => [ [10, 25, 50, -1], [10, 25, 50, "All"] ],
		"buttons" => [
			array(
				'extend'=>'excel',
				'text'=>'<i class="fas fa-file-excel"></i> Excel',
				'titleAttr'=>'Excel',
				'footer'=>true,
				'className'=>'btn btn-sm btn-primary'
			),
			array(
				'extend'=>'pdf',
				'text'=>'<i class="fas fa-file-pdf"></i> PDF',
				'titleAttr'=>'PDF',
				'footer'=>true,
				'className'=>'btn btn-sm btn-success'
			),
			array(
				'extend'=>'colvis',
				'text'=>'<i class="fal fa-eye"></i> Column Visibility',
				'titleAttr'=>'Column visibility',
				'className'=>'btn btn-sm btn-primary'
			),
		],
	),
	"searchOnEnter" => true,
	"searchMode" => "or",
));

David Winterburn commented on Jul 27, 2023

Instead of using "<br>" pls try using "\r\n" to see if it created line break in excel. Another method is using the following button configuration:

    "buttons" => [
            ...
            [
                'extend' => 'excelHtml5',
                ...
                'exportOptions' => [
                    'format' => [
                        'body' => 'function(data, column, row) {
                            if (typeof data === "string" || data instanceof String) {
                                data = data.replace(/<br\\s*\\/?>/ig, "\\r\\n");
                            }
                            return data;
                        }'
                    ]
                ]
            ]
    ]

Let us know if these work for you or not.

Matt Jacobson commented on Jul 27, 2023

Awesome! Using exportOptions worked perfectly. Thank you again for your wonderful support!

KoolReport's Forum

When using Datatables excel button, the line breaks are missing in the excel file. #3099

Build Your Excellent Data Report