Webform export to CSV programmatically

Question

I'm looking to write a module that automatically uploads the submissions of a webform to an ftp server every week or so.

I'm looking for the recommended way to use the CSV export plugin that comes with the WebForm module to render a csv file to a string. I would want to set the following options:

• separator (default is , but should be ; because Europe)
• start date and end date of submissions (weekly export)

I see there are a lot of classes and configuration options available, but I'm not sure about the best way to string them together to achieve what I need.

Answer 1

After looking at the drush webform-export code I came up with the following code sample. It appears to work, however jrockowitz advises me that using drush straight up should be the best solution. I'm sure there's something wrong with the following code. For one, it probably breaks when the export is too large.

$webform_id = 'easykit_vip_formulier';
$webform = \Drupal\webform\Entity\Webform::load($webform_id);
$submission_exporter = \Drupal::service('webform_submission.exporter');
$export_options = $submission_exporter->getDefaultExportOptions();

$export_options['delimiter'] = ';';
$export_options['multiple_delimiter'] = ',';
$export_options['range_type'] = 'date';
$export_options['range_start'] = '2018-02-18';
$export_options['range_end'] = '2018-02-26';

$submission_exporter->setWebform($webform);
$submission_exporter->setExporter($export_options);
$submission_exporter->generate();
$file_path = $submission_exporter->getExportFilePath();

print file_get_contents($file_path);

unlink($file_path);

Answer 2

In-case if anyone interested in a custom script, check this out:

mymodule/src/Controller/DownloadCSVController.php

namespace Drupal\mymodule\Controller;
use Drupal\Core\Controller\ControllerBase;
use Symfony\Component\HttpFoundation\Response;


class DownloadCSVController extends ControllerBase{

    public function downloadCustomers(){

        $submissions = array();
        $row = array();
        $select = \Drupal::service('database')
        ->select('webform_submission_data', 'wsd')
        ->fields('wsd', array('sid'))
        ->orderBy('wsd.sid', 'DESC')
        ->condition('wsd.webform_id', 'webform_name', '=')
        ->execute();

        // Get all the results.
        $results = $select->fetchAll(\PDO::FETCH_COLUMN);
        if ($results != null && count($results) >= 1) {
            $storage = \Drupal::entityTypeManager()->getStorage('webform_submission');
            $submissions = $storage->loadMultiple($results);

        }

        if(count($submissions) > 0) foreach ($submissions as $submission) {
            $data = array(
                $submission->getElementData('first_name'),
                $submission->getElementData('last_name'),
            );

            $rows[] = implode(',', $data);
        }

        $content = implode("\n", $rows);
        $response = new Response($content);
        $response->headers->set('Content-Type', 'text/csv');
        $response->headers->set('Content-Disposition','attachment; filename="sample.csv"');

        return $response;
    }

}

mymodule/mymodule.routing.yml

mymodule.downloadCustomers:
  path: '/mymodule/downloadCustomers'
  defaults:
    _title: 'Download Customers'
    _controller: \Drupal\mymodule\Controller\DownloadCSVController::downloadCustomers
  requirements:
    _permission: 'access content'

Answer 3

Better to create a service so you can use it anywhere:

<?php

namespace Drupal\my_module\Services;

use Drupal\Core\Archiver\ArchiverManager;
use Drupal\Core\Config\ConfigFactoryInterface;
use Drupal\Core\Database\Query\SelectInterface;
use Drupal\Core\File\FileSystemInterface;
use Drupal\Core\StreamWrapper\StreamWrapperManagerInterface;
use Drupal\webform\Entity\WebformSubmission;
use Drupal\webform\Plugin\WebformElementManagerInterface;
use Drupal\webform\Plugin\WebformExporterManagerInterface;
use Drupal\webform\WebformSubmissionExporter;
use Drupal\webform\WebformSubmissionForm;
use Drupal\Core\Logger\LoggerChannelFactory;
use Drupal\Core\Entity\EntityTypeManagerInterface;
use Drupal\Component\Utility\Crypt;
use Drupal\Component\Serialization\Json;
use Drupal\Core\Database\Connection;
use PDO;

/**
 * Class DailySubmissionCSVExport.
 */
class DailySubmissionCSVExport extends WebformSubmissionExporter
{

  protected $connection;

  /**
   * {@inheritDoc}
   */
  public function __construct(
    ConfigFactoryInterface $config_factory,
    FileSystemInterface $file_system,
    EntityTypeManagerInterface $entity_type_manager,
    StreamWrapperManagerInterface $stream_wrapper_manager,
    ArchiverManager $archiver_manager,
    WebformElementManagerInterface $element_manager,
    WebformExporterManagerInterface $exporter_manager,
    Connection $connection
  )
  {
    parent::__construct($config_factory, $file_system, $entity_type_manager, $stream_wrapper_manager, $archiver_manager, $element_manager, $exporter_manager);
    $this->connection = $connection;
  }

  /**
   * {@inheritdoc}
   */
  public function generate()
  {
    $entity_ids = $this->getQuery()->execute()->fetchAll(PDO::FETCH_COLUMN, 'sid');
    $webform_submissions = WebformSubmission::loadMultiple($entity_ids);

    $this->writeHeader();
    $this->writeRecords($webform_submissions);
    $this->writeFooter();
  }

  /**
   * {@inheritdoc}
   */
  public function getQuery()
  {
    $webform = $this->getWebform();

    /** @var SelectInterface $query * */
    $query = $this->connection->select('webform_submission_data', 'wsd');
    $query->join('webform_submission', 'ws', 'wsd.sid = ws.sid');
    $query->fields('wsd', array('sid'));
    $query->orderBy('wsd.sid', 'DESC');
    return $query;
  }
}

Define service in my_module.service.yml file

  my_module.exporter:
    class: Drupal\my_module\Services\DailySubmissionCSVExport
    arguments: ['@config.factory', '@file_system', '@entity_type.manager', '@stream_wrapper_manager', '@plugin.manager.archiver', '@plugin.manager.webform.element', '@plugin.manager.webform.exporter', '@logger.factory', '@database']