Is it possible to create an excel with multiple sheets in it?

Lookup addSheet() for phpspreadsheet

Apparently it does not possible with this module, since in the `encode` method below, the active sheet index is hard coded to 0.

      // Instantiate a new excel object.
      $xls = new Spreadsheet();
      $xls->setActiveSheetIndex(0);
      $sheet = $xls->getActiveSheet();

      // Set headers.
      $this->setHeaders($sheet, $data, $context);

      // Set the data.
      $this->setData($sheet, $data);

@genellann could you explain a bit more in detail why you would need to create an Excel file with multiple sheets in it?

Hi, I'm also very interested in this feature, I want to generate one excel document with several sheets, one containing for example data from content of an entity type and another sheet containing data from content of another entity type because some of data is cross-referenced between these two entity types. Or another case: In a sheet I want data from content of an entity type and in another sheet I want the complete list of the terms of a taxonomy vocabulary used in the entity type.

How come is this issue outdated? It's a feature of any spreadsheet that it can have multiple sheets.

Still have no idea what should be done here.

Will close it definitely unless someone makes a precise description of what is expected here when exporting a view or serializing some data.

I have given two concrete user cases from my post above...

Well, you are never better served than by yourself...

While waiting for a better answer, we can do this in a custom module.

Create a file modules/custom/my_module/src/Controller/ExcelFileMultiSheetsController.php with the content below:

namespace Drupal\my_module\Controller;
use Drupal\Core\Controller\ControllerBase;
use Drupal\Core\Url;
class ExcelFileMultiSheetsController extends ControllerBase {
  public function startBatchExcelFile() {
    $operations = [];
    //Example with two sheets in the same Excel document
    //You have to create the views first using a block display for example
    $views_to_excel = [
      'machine_name_of_a_view_1' => 'Sheet #1',
      'machine_name_of_a_view_2' => 'Sheet #2',
    ];
    foreach ( $views_to_excel as $view_id => $sheet_name ) {
      $view = \Drupal\views\Views::getView( $view_id );
      $view -> setDisplay( 'default' );
      $view -> execute();
      $total = count( $view -> result );
      $chunks = ceil( $total / 100 );
      for ( $i = 0; $i < $chunks; $i ++ )  {
        $operations[] = [
          '\Drupal\my_module\Service\ExcelFileBatch::processChunk',
          [ $view_id, $sheet_name, $i * 100, 100 ]
        ];
      }
    }
    $batch = [
      'title' => $this -> t( 'Creating Excel File' ),
      'operations' => $operations,
      'finished' => '\Drupal\my_module\Service\ExcelFileBatch::finishBatch',
    ];
    batch_set( $batch );
    return batch_process( Url::fromRoute( 'system.admin_content' )  );
  }
}

Then, create a file modules/custom/my_module/src/Service/ExcelFileBatch.php with the content below:

namespace Drupal\my_module\Service;
use Drupal\Core\File\FileSystemInterface;
use Drupal\views\Views;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class ExcelFileBatch {
  protected static function getFilePath( $filename = 'my_file.xlsx' ) {
    $stream_wrapper_manager = \Drupal::service( 'stream_wrapper_manager' );
    $file_system = \Drupal::service( 'file_system' );
    $user_dir = \Drupal::currentUser() -> id();
    $date_dir = date( 'Y-m-d' );
    $directory = $stream_wrapper_manager -> isValidScheme( 'private' )
      ? "private://views_data_export/$date_dir/$user_dir/"
      : "public://views_data_export/$date_dir/$user_dir/";
    $file_system -> prepareDirectory( $directory, FileSystemInterface::CREATE_DIRECTORY | FileSystemInterface::MODIFY_PERMISSIONS );
    return $directory . $filename;
  }
  public static function processChunk( $view_id, $sheet_name, $offset, $limit, &$context ) {
    $file_path = self::getFilePath();
    if ( file_exists( \Drupal::service( 'file_system' ) -> realpath( $file_path ) ) ) {
      $spreadsheet = IOFactory::load( \Drupal::service( 'file_system' ) -> realpath( $file_path ) );
    } else {
      $spreadsheet = new Spreadsheet();
      $spreadsheet -> removeSheetByIndex( 0 );
    }
    $view = Views::getView( $view_id );
    $view -> setDisplay( 'default' );
    $view -> setItemsPerPage( $limit );
    $view -> setOffset( $offset );
    $view -> execute();
    $data = [];
    foreach ( $view -> result as $row ) {
      $record = [];
      foreach ( $view -> field as $field_name => $field ) {
        $record[ $field_name ] = $field -> advancedRender( $row );
      }
      $data[] = $record;
    }
    if ( empty( $data ) ) {
        return;
    }
    $sheet = null;
    foreach ( $spreadsheet -> getSheetNames() as $index => $name ) {
      if ( $name === $sheet_name ) {
        $sheet = $spreadsheet -> getSheet( $index );
        break;
      }
    }
    if ( !$sheet ) {
      $sheet = new Worksheet( $spreadsheet, $sheet_name );
      $spreadsheet -> addSheet( $sheet );
      $sheet -> fromArray( array_keys( $data[ 0 ] ), NULL, 'A1' );
      $row_num = 2;
    } else {
      $row_num = $sheet -> getHighestRow() + 1;
    }
    foreach ( $data as $row ) {
      $sheet -> fromArray( array_values( $row ), NULL, 'A' . $row_num ++ );
    }
    $temp_path = tempnam( sys_get_temp_dir(), 'phpss_' );
    $writer = new Xlsx( $spreadsheet );
    $writer -> save( $temp_path );
    $data = file_get_contents( $temp_path );
    unlink( $temp_path );
    \Drupal::service( 'file.repository' ) -> writeData(
      $data,
      $file_path,
      FileSystemInterface::EXISTS_REPLACE
    );
  }
  public static function finishBatch( $success, $results, $operations ) {
    if ( $success ) {
      \Drupal::messenger() -> addMessage( 'Excel file has been created successfully.' );
      $file_path = self::getFilePath();
      $url = \Drupal::service( 'file_url_generator' ) -> generateString( $file_path );
      \Drupal::messenger() -> addMessage( '<a href="' . $url . '">Download Excel File</a>' );
    } else {
      \Drupal::messenger() -> addError( 'Excel File creation failed.' );
    }
  }
}

Now, in the file modules/custom/my_module/my_module.module, you have to add this function:

function my_module_file_download( $uri ) {
  if ( str_starts_with( $uri, 'private://views_data_export/' ) ) {
    return [
      'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    ];
  }
  return -1;
}

And, in the file modules/custom/my_module/my_module.routing.yml, you have to add these lines:

excel_file_multi_sheets.create_file:
  path: '/admin/excel_file_batch'
  defaults:
    _controller: '\Drupal\my_module\Controller\ExcelFileMultiSheetsController ::startBatchExcelFile'
    _title: 'Create Excel File multi sheets'
  requirements:
    _permission: 'access content'

From a browser, logged in with a user with appropriate rights, you have to go to /admin/excel_file_batch in order to create the Excel File with multiple sheets.

This is just a first draft, feel free to improve the code above.

Interesting, but I still don't see how it could be integrated with the xls_serialization module.

It would probably be better suited to a dedicated custom module.