Multiple line fields cause extra rows in XLS Export

This still seems to be a bug - or maybe it's a feature, in which case can it be switched off?

Right now we're exporting as XLS rather than CSV, because CSV doesn't seem to preserve Unicode reliably (accented characters in Spanish names); however, a single row is actually many merged rows, simply because there are newlines in the body field.

Is this even a desirable default? Can it be overridden just in time using a theme function?

For reference, here's a few workarounds that eventually seem to lead to having all your text in a single cell, not split over lines:

* Formatter: plain text
* Style settings: remove any customized HTML, field templates etc. as otherwise they "trap" leading and trailing whitespace
* Rewrite results: remove whitespace; and convert newlines to
tags. Otherwise the plain-text newlines get treated as simple spaces.

Modifying this thread a little to cover other cases of field content (not just body) causing multiple rows in XLS format.

I use Address field which spits out a full address as a single field. In HTML this has DIVs as wrappers around each line of the address.

CSV format strips all html (by default) and leaves everything as one long line with no separators. Leaving HTML in of ocurse leaves all the DIV tags in which is also no good.

XLS has no options and does what is reported here and seems to break each DIV into its own row (which i doubt would ever be correct).

i have managed to fix my issue (with address fields) using this: #1493014: Add line breaks in the rendered address markup to improve address field data exports and possibly helped along with the VDE addon module for using PHPExcel.

You can overwrite the theme file for the export. Replace de

and
by tags which are recognized by Excel.

Go to to the module on your filesystem "modules/views_data_export/theme".
Edit "views-data-export-xls-body.tpl.php".

$tbody = preg_replace('/<\/?(a|span) ?.*?>/', '', $tbody); // strip 'a' and 'span' tags
$tbody = preg_replace('/<p[^>]*?>/', '', $tbody); //replace <p> tags with nothing
$tbody = str_replace(array('</p>','<br />'), '<br style="mso-data-placement:same-cell;" />', $tbody); //replace </p> and <>br /> with a line break which is recognized by MS Excel.
print $tbody; 

Of course you shouldn't put this statement in the "modules/views_data_export/theme/views-data-export-xls-body.tpl.php" but place it in a custom overwrite theme file otherwise your changes are lost when upgrading the views_data_export module.

You can solve this problem with the following solution to add in your theme to remove the HTML from the outputted fields (that will not be interpreted by Excel). You can keep line breaks with the last line of the snippet:

/**
 * Implements hook_preprocess_views_data_export_xls_body().
 */
function YOUR_THEME_preprocess_views_data_export_xls_body(&$vars) {
  foreach ($vars['themed_rows'] as &$row) {
    foreach ($row as &$field) {
      // Remove HTML.
      $output = strip_tags($field);
      // Keep line breaks and replace them with the Excel line break char.
      $field = str_replace( "\n", "
", $output);
    }
  }
}