CiviCRM Community Forums (archive)

Hello to you all,

A very simple question... at first: has anyone ever tried to offer xls exportation files rather than csv?

Exportations are very useful and sending automatics CiviReports (eg list of members) using such cronjob is a must :

.../liste/sites/all/modules/civicrm/bin/CiviReportMail.php?instanceId=10&reset=1&sendmail=1&output=csv&name=***&pass=***&key=***

However, many of my members, using CiviCRM or recipients of these reports, have not LibreOffice and they have troubles to import CSV files into MS Excel.

I do not want to know if Excel is good or bad, that's not the question. I just want that my militants have easy access to data, even if they only have very limited computer skills.

So, do you think it is possible:

• to offer XLS reports exportation
• to permit the automatic sending of such XLS reports by Cron

Thank you in advance for your answers 

In fact, the easiest solution was to configure the production of CSV in order to allow the correct interpretation by Excel during the opening.
I do not know in other languages​​, but in French, Excel opens the CSV directly when they have " ; " (rather than " , "), encoded in Windows-1252.

So I modified the file /modules/civicrm/CRM/Report/Utils/Report.php :

// Add the headers.
        $csv .= implode(';', $headers) . "\n";
        $displayRows = array();
        $value       = null;
        foreach ( $rows as $row ) {
            foreach ( $columnHeaders as $k => $v ){
                if ( $value = CRM_Utils_Array::value( $v, $row ) ) {
                    // Remove HTML, unencode entities, and escape quotation marks.
                    $value =
                  mb_convert_encoding ($value , "Windows-1252", "UTF-8");
               $value =
                        str_replace('"', '""', html_entity_decode(strip_tags($value)));                   
                    if ( CRM_Utils_Array::value( 'type', $form->_columnHeaders[$v] ) & 4 ) {
                        if ( CRM_Utils_Array::value( 'group_by', $form->_columnHeaders[$v] ) == 'MONTH' ||
                             CRM_Utils_Array::value( 'group_by', $form->_columnHeaders[$v] ) ==  'QUARTER' ) {
                            $value =  CRM_Utils_Date::customFormat( $value, $config->dateformatPartial );
                        } elseif ( CRM_Utils_Array::value( 'group_by', $form->_columnHeaders[$v] ) == 'YEAR' ) {
                            $value =  CRM_Utils_Date::customFormat( $value, $config->dateformatYear );
                        } else {
                            $value =  CRM_Utils_Date::customFormat( $value,'%d/%m/%Y' );
                        }
                    } else if ( CRM_Utils_Array::value( 'type', $form->_columnHeaders[$v] ) == 1024 ) {
                        $value =  CRM_Utils_Money::format( $value );
                    }
                    $displayRows[$v] = '"'. $value .'"';
                } else {
                    $displayRows[$v] = " ";
                } 
            }
            // Add the data row.
            $csv .= implode(';', $displayRows) . "\n";
        }

        return $csv;
    }

Hi guys,

getting back to this old issue, we have the need to get the CSV Report's files in ISO-8859-7 encoding (greek).
Does it make sense to add an new settings in Localization Admin Section (similar to Legacy Encoding field) to set the enconding for all exported CSV files ? (UTF-8 can be set by default)

We can contribute with a Pull Request on this

cheers!