Follow-up to #2810073: Excel import of the CSV File with error

Problem/Motivation

The YAML Form module should allows submissions to be downloaded to an Excel spreadsheet.

Proposed resolution

Provide Excel submission exporter

Remaining tasks

  • Determine which PHP library to use to generate Excel documents. (Spout, PHPExcel or PhpSpreadsheet)
  • Decide how relationships should be exported. Maybe create additional sheets for entity references and multiple value elements

References

Development Notes

  • Branch Name: 2822560-provide-excel-exporter
  • Commit message: Issue #2822560: Provide Excel submission exporter
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

jrockowitz created an issue. See original summary.

jrockowitz’s picture

Issue summary: View changes

  • jrockowitz committed 67488a7 on 8.x-1.x
    Issue #2822560: Provide Excel submission exporter
    
fenstrat’s picture

Project: YAML Form » Webform
Version: 8.x-1.0-beta18 » 8.x-5.x-dev
jrockowitz’s picture

Issue summary: View changes
bucefal91’s picture

The amount of work you do in this module is admirable :) but you should lean towards a little lazier approach :)

D8 has Serialization API. Webform export facility should leverage that API so you do not have to actually get your hands dirty with excel (or whatever else format in the future) within the scope of webform module - you'd just prepare the data and then delegate actual data serialization to a specific serialization implementation.

There seems to be already a module https://www.drupal.org/project/xls_serialization for XLS.

In the very idealistic scenario I would try connecting your exporters with Serialization API so whenever a new serialization implementation is installed it just works and you do not have to worry about it. - not sure whether it's truly feasible, I am sure there will be lots of "special and unforeseen" cases, but at least that's a good direction to be moving towards to.

Also, presenting the submissions via Views will help in this kind of feature requests since you can always flick people off saying "Ain't my problem - just export to XLS via Views" ;)

What do you say? Sounds like a plan? I would try to code a bridge between WebformExporterInterface and \Symfony\Component\Serializer\SerializerInterface as an implementation of the former interface, so it becomes available as "yet just another" exporter, but within it's settings it will be possible to use any of the available Serializers. I haven't worked with the Serialization API before in D8 so I am still not sure whether it's fully feasible, but I think it should be.

jrockowitz’s picture

Yes, I need to start delegating certain tasks and functionality to other modules and developers. At the same time, I do feel that the Webform module needs to provide a "complete" form building experience.

I am all for any integration with core APIs that make the Webform module more usable and developer friendly.

I am interested in providing a native Excel export with the goal being, besides generating the main results sheet, we should be able to include data for elements with multiple values in separate sheets. I am waiting for PhpSpreadsheet to get a stable release and hoping someone else might take on this task.

Getting Webform submission data working with Drupal's serialization API is absolutely a worthwhile endeavor worthy of a dedicated ticket.

Some initial notes and thoughts….

  • Entity references and managed files are probably going to require the most work since all that is being stored for these elements is an entity id.
  • Webform elements may need to have "datatyping" added their plugin definition and/or properties. For example, select lists can store strings and/or numbers. We can assume everything is a string but it could be nice to optionally allow some casting of submission data.
bucefal91’s picture

FileSize
6.27 KB

In fact nothing worthwhile comes out of it - Serialization API is not really designed for exporting large datasets - it is rather designed for ..ehm.. serializing data.

The issues I've faced:

  • No support for batch processing whatsoever in Serialization API. WebformExporterInterface does export in batch and this is very good so it scales up to large datasets. However, some serialization formats require access to full data set in order to execute serialization. For example, you can't just keep building up XLS file by appending next 1000 submissions to it on the raw level of file descriptors, you need to have a full list of submissions and write them at once. At least when you work through Serialization API, when you work directly with XLS format, you can just open it, append, close it and then repeat the cycle.
  • Export of webform submissions requires table header notion. Serialization API has no interface for communicating that header. It just serializes data, it does not create tables of data in some format.

Clearly, WebformExporterInterface and core Serialization API refer to different concepts. Fool I am for trying to glue them. I was able to get it working with XLS serializer (https://www.drupal.org/project/xls_serialization) but only at a price of some ugly hacks and I know the same code is very unlikely to work with other serializers.

Let's hope D9 will have Export API built on top of Serialization API so all import/export tasks for any kind of data type can be leveraged through it.

I am going to upload my patch here anyway - so you can see my logic in code, not only in English, but do not consider it as a real patch that should be tested or applied against webform.module.

For the need of my project I'll simply create a custom module based on this patch, I am fine to keep all these hacks in a custom module.

howdytom’s picture

I am highly interested in the Excel submission exporter. A Excel download option is the only missing feature which prevents me from migrating to Webform 8.

jrockowitz’s picture

@howdytom There is an HTML exporter that works perfectly in Excel and the result is same as the D7 Excel export.

I am hoping to get advanced support with multiple values being exported into dedicated sheets.

howdytom’s picture

@Jacob: Thank you for your quick reply. Indeed, I missed that setting. This is exactly I was looking! Also, like the Column options. This why I am able to define which elements are going to export to a clean xls file. We use Excel export all the time. So good.

Webform 8 is packed with lots of new features. Great job!

jrockowitz’s picture

PHPOffice tagged 1.0 on Christmas day. I am willing to take that as a sign that we should look into using it.

vunda’s picture

PHPExcel - DEPRECATED??

jrockowitz’s picture

PHPExcel last version, 1.8.1, was released in 2015. The project is no longer maintained and should not be used anymore.

All users should migrate to its direct successor PhpSpreadsheet, or another alternative.

@see https://github.com/PHPOffice/PHPExcel

jrockowitz’s picture

Issue summary: View changes
jrockowitz’s picture

Status: Active » Closed (won't fix)

At this point due to lack of interest and having an okay workaround of using an HTML table.

I think Excel support should be handled in a dedicated contributed module.

jrockowitz’s picture

Wow! I did find this ridiculously simple solution which is to generate a CSV and then convert it to Excel. This would solve all encoding and formatting issues with Excel.