Exporting reports as Excel .xls?

[mathieu]

The topic of exporting reports data as MS Excel .xls has come up a few times, such as:

http://forum.civicrm.org/index.php/topic,21654.0.html
https://civicrm.org/extensions/export-excel/

However, these solutions tend to solve the issue by either generating a type of text format that Excel can somehow understand. Also, the export-excel extension uses the hook_civicrm_export() hook, which seems to only be available from the "Search" results "actions".

Here is what I had in mind, and would like feedback on, since I am sure I am not the first person to run into this issue:

* Add a new hook that makes it possible to add new export methods in CiviReports
* Develop an extension that uses the excellent phpexcel library: https://github.com/PHPOffice/PHPExcel (I have tested it already, works as advertised)
* Admins could then choose whether to export as .xls, .xlsx, .ods (LibreOffice / OpenOffice, and should also be supported by newer MS Excel versions)

As usual, as my client has not budgeted this and can only allocate minimal resources, I will try to hack something quick and dirty as a proof of concept extension that will require a few small patches to core. But of course, before doing that, I would be curious if anyone has either already developed that kind of feature, or other suggestions?  (and of course, if you're willing to test, co-develop or sponsor, even better ;-)

Mathieu

[mathieu]

Proof of concept here:
https://github.com/mlutfy/ca.bidon.civiexportexcel

While talking with Eileen on IRC, she suggested that the Search export should also support the same hooks, which makes sense of course.

Brainstorm todo:

*  Support report e-mails (return the output of the xls, do not output directly as download).
*  Propose a new hook to CiviCRM so that we do not need to patch core.
*  Apply new hook to Search export as well (in the "select fields" step, add a "format" option that calls the hook?)
*  Add column headers in xls export.
*  Add OpenDocument (LibreOffice) support.
*  Add admin settings form to enable excel/opendocument formats?

[xavier]

another place that would need a hook is the participant search.

It would obviously be better to go full excel. For what I recall, it did the hack in the export because one library (I think the one you use) had performance issues and another one (supporting only the xml version of xlsx) was not mature enough.

Have you tried with big exports?

X+

[mathieu]

I did some tests on a membership report:

* with 5000 rows: ~ 40 seconds total
* with 250 rows: ~ 2-3 seconds total

This is a mid-range server (dualcore 3gHz E7500 with 4 GB ram, old SATA disk). It's a 2-3 year old machine from a "cheap" dedicated server hosting provider.

No doubt heavier than generating plain CSV, but not that bad either?

[jaapjansma]

Matthieu,

Your solution sounds really good! I have developed in the post my own report to word output. But that was a bit hackish... So I would really love to see your solution of exporteres based on third-party-plugins (such as the PHPExcel).

Also today we had a discussion on the issue tracker on the issue of exporting UTF-8 csv files and MS Excel not handling/opening the file correctly. This is probably solved if the export is a native excel file. See https://issues.civicrm.org/jira/browse/CRM-14773

[mathieu]

Thanks for the feedback

CSV in Excel works in UTF-8 for me, but only if using the "Data importer", which opens a wizard and lets you select the correct encoding, separator, etc. However, this can be rather exasperating for users. The Excel wizard is really terrible.. (LibreOffice has a great CSV wizard, but well.. we have to choose our battles :-)

Enforcing a specific type of CSV format for Excel has always been tricky, since some Mac versions would not behave the same ways, and it seemed to randomly change between Excel versions generally. (or so it feels like.. documentation on the topic is scarce)

[guruevi]

Has anyone used the OpenOffice Server to export data through to XLS or ODF? I've used PHPExcel in a project in the past but it was relatively painful back then (Microsoft OpenXML is not so Open)

[mathieu]

I've seen it used in the past, but never used it myself. In the setups I saw, it was used for converting existing documents, not generating new documents. For example, converting ".odt/.doc" documents to PDF automatically. You could presumably convert CSV to XLS this way, but there could be character encoding issues, date format issues, etc.

[KevinLevie]

I just ran into this thread when searching for options to output Excel / ODS from CiviCRM. I'm working with a Dutch NGO (along with jaapjansma, among others) who would be very happy if this feature was available. I tested the proof of concept extension and it looks like a great start!

It would be great if search results could be exported to native Excel as well. I've seen another extension (eu.tttp.excel) that simply changes the export format for search results from CSV to XLS.
But it simply generates HTML output that is imported by Excel, so that's definitely less robust than using the PHPExcel library.

I've outputted ODS files in the past using the open-document-spreadsheet-php project, but it was rather basic. Adding ODS support as well would be nice, but it wouldn't be a high priority if the native Excel output was available everywhere.

Let me know if there's anything I can do / contribute to further the development of this extension.

[mathieu]

Hi Kevin,

Thanks for the feedback! This extension (https://github.com/mlutfy/ca.bidon.civiexportexcel) exports to Excel 2007 xlsx format using PHPExcel. It should be pretty easy to get it to output ODS format as well (and I have had a client ask for it too).

The main tasks left to do are:

* propose a way to have hooks in core for report export (to avoid having to patch core)
* implement this for search results as well.
* test with CiviCRM 4.5 beta
* think of a better interface for the export buttons? if there is CSV / Excel / ODS, it starts cluttering the user interface.

Testing, feedback, ideas, patches or financial sponsors are most welcome

[mathieu]

Hi,

Just to update that I added a few changes to the extension so that it is not necessary to patch CiviCRM core anymore.
https://github.com/mlutfy/ca.bidon.civiexportexcel

I have two clients pretty happy with this extension, so I went ahead and added it to the extensions directory:
https://civicrm.org/extensions/export-native-excel

I will post further updates from the Extensions forum eventually:
http://forum.civicrm.org/index.php/board,57.0.html

Thanks for testing & feedback!

Mathieu