Override migrate_spreadsheet to have basic XLSX formatting support

migrate_accessories.yml

# migrations/migrate_accessories.yml

id: migrate_accessories
label: 'Migrate accessories from the xlsx file'
migration_group: migration_accessories
source:
  plugin: spreadsheet_with_formatting
  file: assets/accessories.xlsx
  worksheet: 'en'
  header_row: 1
  origin: A2
  keys:
    'Short name':
      type: text
  constants:
    text_format: 'basic_html'
process:
  title: 'Short name'
  body/value: 'Description'
  body/format: constants/text_format
destination:
  plugin: 'entity:node'
  default_bundle: accessory
migration_dependencies:
  required: {  }
  optional: {  }

Spreadsheet.php

<?php
// src/Plugin/migrate/source/Spreadsheet.php

namespace Drupal\my_project\Plugin\migrate\source;

use Drupal\my_project\SpreadsheetIterator;
use Drupal\migrate\MigrateException;
use Drupal\migrate\Plugin\MigrationInterface;
use Drupal\migrate_spreadsheet\Plugin\migrate\source\Spreadsheet as SpreadsheetContrib;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Extends migrate_spreadsheet source to allow to retrieve formatting.
 *
 * @MigrateSource(
 *   id = "spreadsheet_with_formatting"
 * )
 */
class Spreadsheet extends SpreadsheetContrib {

  /**
   * {@inheritdoc}
   */
  public static function create(ContainerInterface $container, array $configuration, $plugin_id, $plugin_definition, MigrationInterface $migration = NULL) {
    return new static(
      $configuration,
      $plugin_id,
      $plugin_definition,
      $migration,
      $container->get('file_system'),
      // Custom SpreadsheetIterator to handle RichText cells.
      new SpreadsheetIterator()
    );
  }

  /**
   * {@inheritdoc}
   */
  protected function loadWorksheet() {
    $config = $this->getConfiguration();

    // Check that the file exists.
    if (!file_exists($config['file'])) {
      throw new MigrateException("File with path '{$config['file']}' doesn't exist.");
    }

    // Check that a non-empty worksheet has been passed.
    if (empty($config['worksheet'])) {
      throw new MigrateException('No worksheet was passed.');
    }

    // Load the workbook.
    try {
      $file_path = $this->fileSystem->realpath($config['file']);

      // Identify the type of the input file.
      $type = IOFactory::identify($file_path);

      // Create a new Reader of the file type.
      /** @var \PhpOffice\PhpSpreadsheet\Reader\BaseReader $reader */
      $reader = IOFactory::createReader($type);

      // Note: this commented line is the only change of the overridden code.
      // Advise the Reader that we only want to load cell data.
      // $reader->setReadDataOnly(TRUE);

      // Advise the Reader of which worksheet we want to load.
      $reader->setLoadSheetsOnly($config['worksheet']);

      /** @var \PhpOffice\PhpSpreadsheet\Spreadsheet $workbook */
      $workbook = $reader->load($file_path);

      return $workbook->getSheet(0);
    }
    catch (\Exception $e) {
      $class = get_class($e);
      throw new MigrateException("Got '$class', message '{$e->getMessage()}'.");
    }
  }

}

SpreadsheetIterator.php

<?php
// src/SpreadsheetIterator.php

namespace Drupal\my_project;

use Drupal\migrate_spreadsheet\SpreadsheetIterator as SpreadsheetIteratorContrib;
use PhpOffice\PhpSpreadsheet\RichText\RichText;

/**
 * Provides a spreadsheet iterator.
 */
class SpreadsheetIterator extends SpreadsheetIteratorContrib {

  /**
   * {@inheritdoc}
   */
  public function current() {
    $keys = $this->getKeys();
    $all_columns = $keys + $this->getColumns();

    if ($row_index_column = $this->getRowIndexColumn()) {
      // We set '@' here so that when it will be sorted, later, it will be the
      // first in the list. Ascii of '@' is lower than ascii of 'A'.
      $all_columns[$row_index_column] = '@';
    }
    elseif (empty($keys)) {
      throw new \InvalidArgumentException("Row index should act as key but no name has been provided. Pass a string in \$config['row_index_column'] key when setting the configuration in SpreadsheetIterator::setConfiguration(\$config), to provide a name for this column.");
    }

    // Arrange columns in their spreadsheet native order.
    asort($all_columns);

    return array_map(
      function ($col_letter) {
        if ($col_letter === '@') {
          return $this->getAbsoluteRowIndex();
        }
        elseif ($cell = $this->getWorksheet()->getCell("$col_letter{$this->getAbsoluteRowIndex()}", FALSE)) {
          // Note: code added to handle the RichText to HTML conversion.
          $value = $cell->getValue();
          if ($value instanceof RichText) {
            return $this->richTextToHTML($value);
          }
          return $cell->getCalculatedValue();
        }
        // Fall back to NULL.
        return NULL;
      },
      $all_columns
    );
  }

  /**
   * Converts RichText element to an HTML string.
   *
   * @param \PhpOffice\PhpSpreadsheet\RichText\RichText $value
   *   The RichText element.
   *
   * @return string
   *   The HTML string.
   */
  protected function richTextToHTML(RichText $value) {
    $result = '';
    foreach ($value->getRichTextElements() as $element) {
      $font = $element->getFont();
      $prefix = '';
      $suffix = '';

      if ($font->getBold()) {
        $prefix .= '<strong>';
        $suffix = '</strong>' . $suffix;
      }
      if ($font->getItalic()) {
        $prefix .= '<em>';
        $suffix = '</em>' . $suffix;
      }

      $result .= $prefix . $element->getText() . $suffix;
    }
    return _filter_autop($result);
  }

}