Using PHPOffice/PhpSpreadsheet

A quick demo using PHPOffice/PhpSpreadsheet. 

https://github.com/PHPOffice/PhpSpreadsheet

https://phpspreadsheet.readthedocs.io/en/develop/

This does not require PHPExcel module that currently uses PHPOffice/PhpExcel as a manually included library, rather this includes the newer PHPOffice/PhpSpreadsheet package and directly invokes that package to generate the spreadsheet.

Composer based requirements

See composer documentation.

"require": {
"phpoffice/phpspreadsheet": "~1"
},

This will install the external library in the vendors directory and create the required autoloads. Obviously, install via composer!

composer require drupal/mymodule

Controller

This example creates a forced file download.

modules/custom/mymodule/src/Controller/MyController.php

namespace Drupal\mymodule\Controller;

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

class MyController extends ControllerBase {

  public function demo() {
    $response = new Response();
    $response->headers->set('Pragma', 'no-cache');
    $response->headers->set('Expires', '0');
    $response->headers->set('Content-Type', 'application/vnd.ms-excel');
    $response->headers->set('Content-Disposition', 'attachment; filename=demo.xlsx');

    // Generate and output code will be inserted here.

    $response->setContent($content);
    return $response;
  }

}

Spreadsheet generation

Misc demo content to populate a Spreadsheet and export this.

modules/custom/mymodule/src/Controller/MyController.php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class MyController extends ControllerBase {

  public function demo() {
    $response = new Response();
    $response->headers->set('Pragma', 'no-cache');
    $response->headers->set('Expires', '0');
    $response->headers->set('Content-Type', 'application/vnd.ms-excel');
    $response->headers->set('Content-Disposition', 'attachment; filename=demo.xlsx');

    $spreadsheet = new Spreadsheet();

    //Set metadata.
    $spreadsheet->getProperties()
      ->setCreator('Test')
      ->setLastModifiedBy('Test')
      ->setTitle("PHPExcel Demo")
      ->setLastModifiedBy('Test')
      ->setDescription('A demo to show how to use PHPExcel to manipulate an Excel file')
      ->setSubject('PHP Excel manipulation')
      ->setKeywords('excel php office phpexcel lakers')
      ->setCategory('programming');

    // Get the active sheet.
    $spreadsheet->setActiveSheetIndex(0);
    $worksheet = $spreadsheet->getActiveSheet();

    //Rename sheet
    $worksheet->setTitle('My File name');

    /*
    * TITLE
    */
    //Set style Title
    $styleArrayTitle = array(
      'font' => array(
        'bold' => true,
        'color' => array('rgb' => '161617'),
        'size' => 12,
        'name' => 'Verdana'
      ));

    $worksheet->getCell('A1')->setValue('TEST PHPEXCEL');
    $worksheet->getStyle('A1')->applyFromArray($styleArrayTitle);

    /*
     * HEADER
     */
    //Set Background
    $worksheet->getStyle('A3:E3')
      ->getFill()
      ->setFillType(Fill::FILL_SOLID)
      ->getStartColor()
      ->setARGB('085efd');

    //Set style Head
    $styleArrayHead = array(
      'font' => array(
        'bold' => true,
        'color' => array('rgb' => 'ffffff'),
      ));

    $worksheet->getCell('A3')->setValue('C1');
    $worksheet->getCell('B3')->setValue('C2');
    $worksheet->getCell('C3')->setValue('C3');

    $worksheet->getStyle('A3:E3')->applyFromArray($styleArrayHead);

    for ($i = 4; $i < 10; $i++) {
      $worksheet->setCellValue('A' . $i, $i);
      $worksheet->setCellValue('B' . $i, 'Test C2');
      $worksheet->setCellValue('C' . $i, 'Test C3');
    }

    // This inserts the SUM() formula with some styling.
    $worksheet->setCellValue('A10', '=SUM(A4:A9)');
    $worksheet->getStyle('A10')
      ->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
    $worksheet->getStyle('A10')
      ->getBorders()->getTop()->setBorderStyle(Border::BORDER_THICK);

    // This inserts the formula as text.
    $worksheet->setCellValueExplicit(
      'A11',
      '=SUM(A4:A9)',
      DataType::TYPE_STRING
    );

    // Get the writer and export in memory.
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    ob_start();
    $writer->save('php://output');
    $content = ob_get_clean();

    // Memory cleanup.
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);


    $response->setContent($content);
    return $response;
  }
}

And don't forget to define your router yml to call the controller

modules/custom/mymodule/mymodule.routing.yml

mymodule.demo_excel:
  path: '/demo-excel'
  defaults:
    _controller: '\Drupal\mymodule\Controller\MyController::demo'
    _title: 'Demo Excel2007 Export'
  requirements:
    _permission: 'access content'